Tnsnames.ora - Strange Connectivity problem

"ORA-12154: TNS:could
not resolve service name".

means the name you specified in your connect string is not found in
your tnsnames.ora .. i.e. if your tnsnames looks like this:

fred =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = myserver)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = fred)
)
)
#

and you issue 'connect system-at-freddie' you'll get the error because
you don't have an entry for freddie. Also, check you sqlnet.ora file
for names.default_domain. If you have, say, name.default_domain=world,
then in the above example, you 'connect system-at-fred', you will need a
tnsnames entry called fred.world.

Might help if you posted your tnsnames.ora, sqlnet.ora, and full text
of your connect statement.

"ORA-12154: TNS:could
not resolve service name".

means the name you specified in your connect string is not found in
your tnsnames.ora .. i.e. if your tnsnames looks like this:

fred =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = myserver)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = fred)
)
)
#

and you issue 'connect system-at-freddie' you'll get the error because
you don't have an entry for freddie. Also, check you sqlnet.ora file
for names.default_domain. If you have, say, name.default_domain=world,
then in the above example, you 'connect system-at-fred', you will need a
tnsnames entry called fred.world.

Might help if you posted your tnsnames.ora, sqlnet.ora, and full text
of your connect statement.

Hi,

I'm having a few connection problems. Basically, I have two 9i instance
running on Redhat Linux. Both instances share the same ORACLE_HOME and
are running fine i.e. both also accept external connections so the
listener is ok.

Yesterday I applied an Oracle Patch (bug 4192148 ) and since then I
cannot connect to a remote instance - either using a Database link or
SqlPus-at-remote_instance. When doing so I get error "ORA-12154: TNS:could
not resolve service name".

The tnsnames.ora is in the default location and is complete (no changes
since it last worked). I even tried specifiying the default tns
location - ORACLE_HOME/network/admin -using the TNS_ADMIN
environmental variable but this also generated the same error.

I've also checked tnsnames.ora for any errors but there were none. The
tns entries also use service names.

When I use tnsping against the remote instances it works fine so it is
being picked up here. All the environmental variables are correct and
if ORACLE_SID = INSTANCE1 then it is not possible to connect to
INSTANCE2 using sqlplus-at-instance2 and vice versa.

I'm a bit stumped as to where I start looking next. I'm not a greatly
experienced DBA and therefore any help would be appreciated.

Thanks in advance
John

"ORA-12154: TNS:could
not resolve service name".

means the name you specified in your connect string is not found in
your tnsnames.ora .. i.e. if your tnsnames looks like this:

fred =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = myserver)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = fred)
)
)
#

and you issue 'connect system-at-freddie' you'll get the error because
you don't have an entry for freddie. Also, check you sqlnet.ora file
for names.default_domain. If you have, say, name.default_domain=world,
then in the above example, you 'connect system-at-fred', you will need a
tnsnames entry called fred.world.

Might help if you posted your tnsnames.ora, sqlnet.ora, and full text
of your connect statement.

Hi,

I'm connecting to a remote instance called oranc1 using sqlplus as
below (enter password manually).

sqlplus osgdba-at-oranc1

The tns entries for oranc1 are below (2 entries - one includes the
global name). They are in network/admin as per default and worked prior
to applying patch.

ORANC1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = ncluxora1)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.191.10.61)(PORT = 1521))
)
(CONNECT_DATA = (SERVICE_NAME = oranc1.osg.com))
)

ORANC1.OSG.COM =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = ncluxora1)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.191.10.61)(PORT = 1521))
)
(CONNECT_DATA = (SERVICE_NAME = oranc1.osg.com))
)

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-at-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

Hi,

I'm connecting to a remote instance called oranc1 using sqlplus as
below (enter password manually).

sqlplus osgdba-at-oranc1

The tns entries for oranc1 are below (2 entries - one includes the
global name). They are in network/admin as per default and worked prior
to applying patch.

ORANC1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = ncluxora1)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.191.10.61)(PORT = 1521))
)
(CONNECT_DATA = (SERVICE_NAME = oranc1.osg.com))
)

ORANC1.OSG.COM =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = ncluxora1)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.191.10.61)(PORT = 1521))
)
(CONNECT_DATA = (SERVICE_NAME = oranc1.osg.com))
)

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-at-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

Hi,

I'm connecting to a remote instance called oranc1 using sqlplus as
below (enter password manually).

sqlplus osgdba-at-oranc1

The tns entries for oranc1 are below (2 entries - one includes the
global name). They are in network/admin as per default and worked prior
to applying patch.

ORANC1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = ncluxora1)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.191.10.61)(PORT = 1521))
)
(CONNECT_DATA = (SERVICE_NAME = oranc1.osg.com))
)

ORANC1.OSG.COM =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = ncluxora1)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.191.10.61)(PORT = 1521))
)
(CONNECT_DATA = (SERVICE_NAME = oranc1.osg.com))
)

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-at-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

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-at-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

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-at-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

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 Database 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).