I ran into a problem
when creating a database link that uses a new TNS entry. The database link
seemed to work fine when I created it, but the developers complained about
getting TNS-12154 errors when they tried to use it. Checked it again on my
side, and the database link worked just fine.
After some discussion
with the development team, we noticed that the database did work when I connected locally from the database server, but it
threw the TNS-12154 when connecting through the listener. So I focussed on why
the listener prevented the database link from recognizing my newly created TNS
entry. Turns out it has a lot to do with setting a TNS_ADMIN environment variable,
the dynamic registration to the listener and the way the listener uses
environment variables.
So, to paint a picture
of the situation:
·
We
have a database on Linux (probably any Linux or UNIX variant will have this
problem)
·
We
have set the TNS_ADMIN environment variable
·
The
listener uses both static and dynamic service registration (because we are
using OEM)
Solutions proposed so far
This problem is
described in a lot of places, but I could not find a satisfactory solution until
recently. The answers I found so far are not satisfactory, some more so than
others. They include:
·
Restart
the database. When creating a new TNS entry, this will not been seen by the
dynamic service registration to the listener. Restarting the database somehow
resets this and then the database link also “magically” works.
o
My
problem with this: Restarting a (Production) database is not something I want
to do when I just want to add a new TNS entry for a database link.
·
Use
a different naming method when creating the database link. Instead of the USING
clause with a TNS entry, you can specify an EZConnect string or the full
description of the TNS entry. Both imply that server and database specific
information is to be used in the database link itself.
o
But:
I use the tnsnames.ora to have a central location for all descriptions and use
that to point to any database/service I need. I don’t want to repeat
sevrer/instance information in every database link I create.
·
Set
the ENVS clause of the static registration (SID_LIST_LISTENER) to include
TNS_ADMIN.
o
This
has several drawbacks: You need to use static registration and static
registration only. Dynamic takes precedence over Static, and Dynamic cannot be
used to set TNS_ADMIN. Even when you decide to use only Static, then you have
to maintain the TNS_ADMIN in the environment variable itself and in the
listener.ora which means extra maintenance.
·
Don’t
use TNS_ADMIN. When not using TNS_ADMIN, all works fine. As the database and
the listener will then both use the default location ($ORACLE_HOME/network/admin),
they can always find the tnsnames.ora and will pick up new entries right away.
o
The
problem is obvious: we use TNS_ADMIN to create a centralized location for our
tnsnames.ora file. Not using the TNS_ADMIN would defeat this initial setup.
My solution
The last “solution”,
however, opens up a final solution. When the listener (the dynamic service registration)
fails to pick up (or refresh, or whatever you like to call it) changes to
tnsnames.ora in the TNS_ADMIN location, it will look at the default location.
Creating an OS link there will allow the listener to find the file anyway and
use the new TNS entry without having to restart the database. And yes, there is
a drawback to this solution as well: you need to create this link in every
RDBMS home you created and point it to the TNS_ADMIN location. I think this is
still slightly better than not using TNS_ADMIN at all, because we will still
have a centralized tnsnames.ora file, and only use links to that file in the
RDBMS home directory.
Technical “proof”
The example sketched
below is from an AIX machine, using an 11gR2 database. I also tested this on a
12c database on Linux. Both have the exact same results when it comes to
recognizing the new TNS entry.
Using the original
setup, I modified the tnsnames.ora file in the TNS_ADMIN location:
dbserver::../home/oracle:> cd
$TNS_ADMIN
dbserver::../oracle/tns:> ls -l
total 16
-rw-r--r-- 1
oracle dba 483 Dec 16 16:37 listener.ora
-rw-r--r-- 1
oracle dba 1581 Dec 16 16:14 tnsnames.ora
dbserver::../oracle/tns:> vi
tnsnames.ora
I added an “orcldb_self_ref.world”
entry, pointing toward my original database. After this, without restarting the
database or reloading the listener, I created the database link.
dbserver::../oracle/tns:> sqlplus
system@orcldb
SQL*Plus: Release 11.2.0.2.0 Production on Thu Dec 17
09:41:34 2015
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Enter password:
Connected to:
Oracle Database 11g Enterprise Edition Release
11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real
Application Testing options
SQL> create database link dblink_test_erik using
'orcldb_self_ref.world';
Database link created.
SQL> select * from dual@dblink_test_erik;
select * from dual@dblink_test_erik
*
ERROR at line 1:
ORA-12154: TNS:could not resolve the connect
identifier specified
And there you have it,
the TNS-12154. Normally, you would think that we did something wrong defining
the TNS entry or something like that. However, when not connecting through the
listener, the same database link does work:
dbserver::../oracle/tns:> sqlplus
system
SQL*Plus: Release 11.2.0.2.0 Production on Thu Dec 17
09:41:34 2015
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Enter password:
Connected to:
Oracle Database 11g Enterprise Edition Release
11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real
Application Testing options
SQL> select * from dual@dblink_test_erik;
D
-
X
So, on to the next
part, creating an OS link:
dbserver::../home/oracle:> cd
$ORACLE_HOME/network/admin
dbserver::../network/admin:> ln -s
$TNS_ADMIN/tnsnames.ora ./tnsnames.ora
dbserver::../network/admin:> ls -l
total 8
drwxr-xr-x 2
oracle dba 256 Oct 23 09:49 samples
-rw-r--r-- 1
oracle dba 187 May 07 2007 shrept.lst
lrwxrwxrwx 1
oracle dba 28 Dec 17 09:45 tnsnames.ora
-> /u01/oracle/tns/tnsnames.ora
Directly after
creating the link, our database link works as it should. Just to prove it works
right away, I dropped and re-created it.
dbserver::../oracle/tns:> sqlplus
system@orcldb
SQL*Plus: Release 11.2.0.2.0 Production on Thu Dec 17
09:49:10 2015
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Enter password:
Connected to:
Oracle Database 11g Enterprise Edition Release
11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real
Application Testing options
SQL> drop database link dblink_test_erik;
Database link dropped.
SQL> create database link dblink_test_erik using
'orcldb_self_ref.world';
Database link created.
SQL> select * from dual@dblink_test_erik;
D
-
X
Note that when you are
already connected, it might be necessary to re-connect to pick up the changes.
But that beats restarting the entire database...
Final remarks
Though this is my
preferred solution, it doesn’t mean that it is the only solution. If any of the
other options suits your needs better, then by all means: use them ;-)