Connection problem through sqlplus

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 Received on Mon Aug 14 2006 - 22:32:07 CDT