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
Received on Tue Aug 15 2006 - 10:52:11 CDT
