Re: Connection problem through sqlplus

"Anoop" <anoopkumarv_at_gmail.com> wrote in message news:1155657677.379757.253780_at_i42g2000cwa.googlegroups.com...
>
> 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
>>
>> When connecting to the 10g you need to have ORACLE_HOME pointing to the
>> 10g home. When connecting to 9i ... the 9i home. I think you may need to
>> take a look at the values of your environment variables.
>> --
>> Daniel A. Morgan
>> University of Washington
>> damorgan_at_x.washington.edu
>> (replace x with u to respond)
>> Puget Sound Oracle Users Group
>> www.psoug.org
>
> 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
>

You can set TNS_ADMIN environment variable. Point it to the directory of your tnsnames.ora file. This way you can use one tnsnames.ora file even when using multiple oracle_homes.

-- 
Terry Dykstra