| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Connection problem through sqlplus
DA Morgan wrote:
> Anoop wrote:
> > Terry Dykstra wrote:
> >> "Anoop" <anoopkumarv_at_gmail.com> wrote in message
> >> news:1155612727.627326.123500_at_b28g2000cwb.googlegroups.com...
> >>> Hi All,
> >>>
> >>> Need some help in this baffling issue..
> >>>
> >>> I have 2 instances of oracle installed on my solaris box. The oracle
> >>> versions are 10g and 9i.
> >>>
> >>> I hardly use the 10g and have 4 databases configured on 9i.
> >>>
> >>> I have written some shell scripts to connect (from the same box) to the
> >>> 9i databases and they all make use of the sqlplus command like this:
> >>>
> >>> sqlplus <user>/<password>@<db>
> >>>
> >>> Of the 4, I can connect successfully to 3 of the databases using the
> >>> above format in my shell scripts...
> >>>
> >>> But the 4th, I just dont seem to be able to connect and am not sure
> >>> what the problem is..
> >>> I am connecting from the server box itself, I mean the database is
> >>> installed on the same box.
> >>>
> >>> If I use a complete qualifying command like this, I can connect
> >>> perfectly fine:
> >>>
> >>> sqlplus
> >>> 'rem@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=redbull)(PORT=1521)))(CONNECT_DATA=(SID=c1remdev)))'
> >>>
> >>> but, if I do the following I cannot connect:
> >>>
> >>> sqlplus rem/cap_at_c1remdev
> >>>
> >>> I get this error:
> >>> ERROR:
> >>> ORA-12154: TNS:could not resolve the connect identifier specified
> >>>
> >>> Also, the tnsping works for all 3 except the above database. Here is
> >>> the output...
> >>>
> >>> [oracle_at_redbull ~]$ tnsping c1remdev
> >>>
> >>> TNS Ping Utility for Linux: Version 10.2.0.1.0 - Production on
> >>> 14-AUG-2006 22:02:30
> >>>
> >>> Copyright (c) 1997, 2005, Oracle. All rights reserved.
> >>>
> >>> Used parameter files:
> >>> /opt/app/oracle/product/10/network/admin/sqlnet.ora
> >>>
> >>> TNS-03505: Failed to resolve name
> >>>
> >>>
> >>> I have also tried updating / fiddling with the tnsnames.ora and
> >>> listener.ora files. Here are the contents (but to no avail):
> >>>
> >>>
> >>> [oracle_at_redbull ~]$ more product/10/network/admin/listener.ora
> >>> SID_LIST_LISTENER =
> >>> (SID_LIST =
> >>> (SID_DESC =
> >>> (GLOBAL_DBNAME = fedex)
> >>> (ORACLE_HOME = /opt/app/oracle/product/10)
> >>> (SID_NAME = fedex)
> >>> )
> >>> (SID_DESC =
> >>> (GLOBAL_DBNAME = captest)
> >>> (ORACLE_HOME = /opt/app/oracle/product/10)
> >>> (SID_NAME = captest)
> >>> )
> >>> (SID_DESC =
> >>> (GLOBAL_DBNAME = c1rem)
> >>> (ORACLE_HOME = /opt/app/oracle/product/9.2.0)
> >>> (SID_NAME = c1rem)
> >>> )
> >>> (SID_DESC =
> >>> (GLOBAL_DBNAME = c1apdev)
> >>> (ORACLE_HOME = /opt/app/oracle/product/9.2.0)
> >>> (SID_NAME = c1apdev)
> >>> )
> >>> (SID_DESC =
> >>> (GLOBAL_DBNAME = c1remtst)
> >>> (ORACLE_HOME = /opt/app/oracle/product/9.2.0)
> >>> (SID_NAME = c1remtst)
> >>> )
> >>> (SID_DESC =
> >>> (GLOBAL_DBNAME = c1aptst)
> >>> (ORACLE_HOME = /opt/app/oracle/product/9.2.0)
> >>> (SID_NAME = c1aptst)
> >>> )
> >>> (SID_DESC =
> >>> (GLOBAL_DBNAME = c1remdev)
> >>> (ORACLE_HOME = /opt/app/oracle/product/9.2.0)
> >>> (SID_NAME = c1remdev)
> >>> )
> >>> )
> >>>
> >>> LISTENER =
> >>> (DESCRIPTION =
> >>> (ADDRESS = (PROTOCOL = TCP)(HOST = redbull)(PORT = 1521))
> >>> )
> >>>
> >>> [oracle_at_redbull ~]$ more product/9.2.0/network/admin/tnsnames.ora
> >>> c1aptst =
> >>> (DESCRIPTION =
> >>> (ADDRESS_LIST =
> >>> (ADDRESS = (PROTOCOL = TCP)(HOST = redbull)(PORT = 1521))
> >>> )
> >>> (CONNECT_DATA =
> >>> (SERVICE_NAME = c1aptst)
> >>> )
> >>> )
> >>> c1apdev =
> >>> (DESCRIPTION =
> >>> (ADDRESS_LIST =
> >>> (ADDRESS = (PROTOCOL = TCP)(HOST = redbull)(PORT = 1521))
> >>> )
> >>> (CONNECT_DATA =
> >>> (SERVICE_NAME = c1apdev)
> >>> )
> >>> )
> >>> c1remdev =
> >>> (DESCRIPTION =
> >>> (ADDRESS_LIST =
> >>> (ADDRESS = (PROTOCOL = TCP)(HOST = redbull)(PORT = 1521))
> >>> )
> >>> (CONNECT_DATA =
> >>> (SERVICE_NAME = c1remdev)
> >>> )
> >>> )
> >>>
> >>>
> >>> Can somebody please point me as to where am I making the obvious
> >>> mistake?
> >>>
> >>> I do not want to use the long version of sqlplus to connect (which
> >>> always works) because I have a lot of shell scripts and I do not want
> >>> to modify all of them for this sake.
> >>>
> >>> Thanks,
> >>> Anoop
> >>>
> >> Your 9.2 tnsnames.ora has only 3 entries (whereas you are expecting 4), so
> >> it appears that is the one being picked up by your script.
> >>
> >> --
> >> Terry Dykstra
> >
> > Yes Terry,
> >
> > you are right - but the 9.2 is not even being referred to, I think as
> > the 10g tnsnames.ora is taking precedence....
> > I should have noticed that myself beforehand.. my bad!!
> >
> > Thanks,
> > Anoop
>
What I see on our server is that we have the ORACLE_HOME pointing to the 10g...
Is that the reason why the 10g tnsnames.ora is taking precedence? I do not use 10g for any of my work... so should I reset my oracle_home everytime I need to connect to 9i dbs??
Thanks,
Anoop
Received on Tue Aug 15 2006 - 11:01:17 CDT
 |
 |