Tnsnames.ora - Strange Connectivity problem

[Database Administrator]

Looks like you might have multiple copies of tnsnames.ora, and you're
not using the one you think you are...

You're trying to connect FROM you linux box TO a db on some other box,
right? Check for multiple copies of tnsnames.ora on the box you are
connecting FROM. Also check for a value for a variable called TNS_ADMIN
in the environment from which you are running sqlplus.

Also, instead of actually trying to connect, let's simplify and just
'tnsping oranc1'. This will test your tnsnames file and routing, but
not try to actually connect. In fact, you can successfully tnsping for
a non-existent database, as long as you have the correct entries in
your tnsnames.ora (client side) and listener.ora (server side).

[Database Administrator]

Looks like you might have multiple copies of tnsnames.ora, and you're
not using the one you think you are...

You're trying to connect FROM you linux box TO a db on some other box,
right? Check for multiple copies of tnsnames.ora on the box you are
connecting FROM. Also check for a value for a variable called TNS_ADMIN
in the environment from which you are running sqlplus.

Also, instead of actually trying to connect, let's simplify and just
'tnsping oranc1'. This will test your tnsnames file and routing, but
not try to actually connect. In fact, you can successfully tnsping for
a non-existent database, as long as you have the correct entries in
your tnsnames.ora (client side) and listener.ora (server side).

[Database Administrator]

On 3 May 2006 08:32:17 -0700, "Johne_uk" wrote:

>There is actually no sqlnet.ora file specified although I did try
>tinkering on with a few test values.
>
>Thanks
>
>OUTPUT from connecting :-
>
>-bash-2.05b$ sqlplus osgdba@oranc1
>
>SQL*Plus: Release 9.2.0.7.0 - Production on Wed May 3 12:35:06 2006
>
>Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
>
>Enter password:
>ERROR:
>ORA-12154: TNS:could not resolve service name


Too bad you didn't include output from the tnsping command, as that
would have shown whether you are using the correct directory.

I never remember the difference between
12514 and 12154
One of the two refers to the *service_name of the database*.
Assuming the SID of the database is oranc1 (hopefully)
you could try to replace
connect_data=(service_name=...))
by
connect_data=(sid=...))

Whoever decided the error number for an incorrect database service
name should be a permutation of the error number of the tnsnames.ora
service_name, ought to be shot at once.

--
Sybrand Bakker, Senior Oracle DBA

[Database Administrator]

Hi,

There are no duplicate tnsnames.ora files on any disk (I have
searched). The tns file is also in the correct location (see below).
Also, I tried making the changes to the tnsnames.ora file (i.e.
changingh service name to SID) but again no success.

Thanks for the assistance

-bash-2.05b$ echo $TNS_ADMIN
/u01/oracle/ora92/network/admin


OUTPUT FROM TNSPING
--------------------------------------
-bash-2.05b$ tnsping oranc1

TNS Ping Utility for Linux: Version 9.2.0.7.0 - Production on
04-MAY-2006 07:06:49

Copyright (c) 1997 Oracle Corporation. All rights reserved.

Used parameter files:

Used TNSNAMES adapter to resolve the alias
)CONNECT_DATA = (SERVICE_NAME = oranc1.osg.com))(PORT = 1521))
OK (190 msec)

[Database Administrator]

Hi,

There are no duplicate tnsnames.ora files on any disk (I have
searched). The tns file is also in the correct location (see below).
Also, I tried making the changes to the tnsnames.ora file (i.e.
changingh service name to SID) but again no success.

Thanks for the assistance

-bash-2.05b$ echo $TNS_ADMIN
/u01/oracle/ora92/network/admin


OUTPUT FROM TNSPING
--------------------------------------
-bash-2.05b$ tnsping oranc1

TNS Ping Utility for Linux: Version 9.2.0.7.0 - Production on
04-MAY-2006 07:06:49

Copyright (c) 1997 Oracle Corporation. All rights reserved.

Used parameter files:

Used TNSNAMES adapter to resolve the alias
)CONNECT_DATA = (SERVICE_NAME = oranc1.osg.com))(PORT = 1521))
OK (190 msec)

[Database Administrator]

Hi,

There are no duplicate tnsnames.ora files on any disk (I have
searched). The tns file is also in the correct location (see below).
Also, I tried making the changes to the tnsnames.ora file (i.e.
changingh service name to SID) but again no success.

Thanks for the assistance

-bash-2.05b$ echo $TNS_ADMIN
/u01/oracle/ora92/network/admin


OUTPUT FROM TNSPING
--------------------------------------
-bash-2.05b$ tnsping oranc1

TNS Ping Utility for Linux: Version 9.2.0.7.0 - Production on
04-MAY-2006 07:06:49

Copyright (c) 1997 Oracle Corporation. All rights reserved.

Used parameter files:

Used TNSNAMES adapter to resolve the alias
)CONNECT_DATA = (SERVICE_NAME = oranc1.osg.com))(PORT = 1521))
OK (190 msec)

[giteshtrivedi]

Hi,

Sometimes said error is occured due to mismatching of /etc/hosts file configuration and modification.