Re: Connection problem through sqlplus

"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