Re: Connection problem through sqlplus

From: Anoop <anoopkumarv_at_gmail.com>
Date: 15 Aug 2006 07:21:17 -0700
Message-ID: <1155651677.811048.122780@p79g2000cwp.googlegroups.com>

StefanKapitza wrote:
> Anoop wrote:
> > 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

> did you check your tnsnames in the 10g Oracle Home ?
> Which Client are you using (10G or 9i) ?
> It seems your path is pointing to the 10g Client.

> regards

>
> stefan Kapitza

Hi All,

Thanks so much for pointing me in the right directions...

I think I now fixed it.

As I said, I had both 10g and 9i on this box. The problem was that each comes with its own tnsnames.ora file and only the latest version's tnsnames.ora file is referred to. So once I added the same entry for c1remdev (the problematic db).. I could do a sqlplus and tnsping on c1remdev.

I had to however add an entry like this:

c1remdev.WORLD =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(Host = redbull)(Port = 1521))     )
    (CONNECT_DATA =
      (SID = c1remdev)
    )
  )

I think I could have put localhost instead of the server name (redbull) as I will be connecting from this server itself... wonder if there is any disadvantage in that.

Anyways.. now my connection issue is resolved,. I can run my shell scripts in peace.

Thanks all so much,
Anoop Received on Tue Aug 15 2006 - 09:21:17 CDT