If you are familiar with database links on Oracle database, you may have already encountered the error “ORA-12154: TNS:could not resolve the connect identifier specified“. That is a common error which can be caused by a lot of factors. Today, I’m going to focus on the importance of environment variables to avoid this type of error message (particularly the “TNS_ADMIN” environment variable).

Context

For the rest of this post, I will work on a schema called “ARO” on “DBL121” database (Oracle 12.1.0.2). The server called “mylab01” runs on Linux.
My database link “MY_DB_LINK” has been created as follows:

mylab01> . oraenv
ORACLE_SID = [DBL121] ? DBL121
[...]

mylab01> sqlplus aro
[...]

SQL> CREATE DATABASE LINK my_db_link USING 'DBL122';

Database link created.

The database link points to “DBL122” database on server “mylab02” (same Oracle & OS version). The “DBL122” database also holds a schema “ARO” with the same password, that’s why I did not mentioned “CONNECT TO **** IDENTIFIED BY ****” during the database link creation.

Because I only specify “USING ‘TARGET_DB'” as connect string, it will use “local naming” method in order to resolve the net service name “DBL122” (that is, it will use the information store in a tnsnames.ora file).

My tnsnames.ora file located in “$ORACLE_HOME/network/admin” on “mylab01” contains the following information:

DBL122 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = mylab02 )(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = DBL122)
    )
  )

My Oracle environment variables on “mylab01” are:

ORACLE_SID=DBL121
ORACLE_BASE=/app/mylab01/oracle
ORACLE_HOME=/app/mylab01/oracle/product/12.1.0
TNS_ADMIN=/app/mylab01/oracle/product/12.1.0/network/admin

I will only work on “mylab01” for the rest of this post!

Basic tests

Before testing the new database link, we can make some basic tests.

Server “mylab02” is reachable from “mylab01” on port 1521
Database “DBL122” is up and running on “mylab02”. The listener is ready to accept new connections.
I can connect to the remote database “DBL122” using the net service name “DBL122” with “ARO” user:

mylab01> sqlplus aro@DBL122
[...]

SQL> select sys_context('userenv','db_name') from dual;

SYS_CONTEXT('USERENV','DB_NAME')
--------------------------------------------------------------------------------
DBL122

At this point, everything seems to be fine!

Database link tests

From there, things will start to get complicated…

Local connection

For my first test, I will use a local connection (Bequeath NT Protocol), that is, I will not use Oracle Net to connect to my database.

(Be careful: if the “TWO_TASK” environnement variable is set in your environnement, you will not get a local connection but a connection through Oracle Net!)

mylab01> . oraenv
ORACLE_SID = [DBL121] ? DBL121
[...]

mylab01> sqlplus aro
[...]

SQL> select count(*) from TABLE1@my_db_link;

  COUNT(*)
----------
         4

It works!

Connection through listener

Now, I will try to connect to the same database, same schema and execute the same query but using Oracle Net (if your software that connects to the database resides on another host, this is the method that will be used to connect to the database).

mylab01> . oraenv
ORACLE_SID = [DBL121] ? DBL121
[...]

mylab01> sqlplus aro@DBL121
[...]

SQL> select count(*) from TABLE1@my_db_link;
select count(*) from TABLE1@my_db_link
                            *
ERROR at line 1:
ORA-12154: TNS:could not resolve the connect identifier specified

Very confusing…

What’s going on?

Warning: I did not find anything in Oracle documentation about that point so it will only be base on my personal findings.

Oracle will not deal with database links in the same way depending on the type of connection you have established with the database. Especially for net service name resolution.

If you connect locally (using Bequeath protocol) to your database, Oracle will resolve the net service name of your database link using your current value for “TNS_ADMIN” environment variable.

If you connect through a listener (using Oracle Net) to your database, Oracle will resolve the net service name of your database link using the value of “TNS_ADMIN” which was set on your server hosting the database at the time where your database has been started.

That’s a very important point, because when you startup a database, you always check your “ORACLE_HOME” and “ORACLE_SID” environment variables but rarely “TNS_ADMIN” (personally, I never checked before encountering this problem).

How to check the environment variables of your database processes

Now, you might want to check which environment variables were set when you started your database. There is two ways to achieve that.

The “system way”

I called this the “system way” because you will not use any Oracle utility using this method. The command depend of your OS.

For each platform, the first step consist to identify the PID of the “smon” process of your database instance. There are many ways, here is the one I use:

mylab01> ps -ef|grep smon
oracle    8258     1  0 Dec04 ?        00:00:00 ora_smon_DBL121

Then, choose the command that match your operating system.

Linux

mylab01> strings /proc/8258/environ|grep TNS_ADMIN

(In my case, the command returned “TNS_ADMIN=/tmp” because I set up the value for testing purpose before starting my “DBL121” database).

AIX

myaix01> ps eww 8258| tr ' ' '\n' | grep TNS_ADMIN

Solaris

mysol01> pargs -e 8258

HP-UX

Unfortunately, it seems there is no simple way to find process environ on HP-UX using system commands.

The “Oracle way”

If you prefer to use Oracle utility, you can get the same information using… lsnrctl!

You need do adapt the command to your environment. In my case, the database “DBL121” is registered on a listener called “LISTENER”.

mylab01> lsnrctl

LSNRCTL> set current_listener LISTENER
Current Listener is LISTENER

LSNRCTL> set displaymode verbose
Service display mode is VERBOSE

LSNRCTL> services

Next, you need to identify the service of your database and look at the line containing “ENVS =“. You will find the value of the variable “TNS_ADMIN” which was defined when starting the database.

The advantage of this method is that it also works on HP-UX.

How to correct the problem

If you are in the same situation I described, you have two ways to correct the problem.

If you are allowed to, restart the database instance after checking that your environment variable “TNS_ADMIN” is set correctly.

If you are not allowed to restart the database instance, you can fix the problem by creating a symbolic link to the “good” tnsnames.ora file (or create a new one with the proper informations) in the path identified in the previous step (“/tmp” in my example).

mylab01> ln -s $ORACLE_HOME/network/admin/tnsnames.ora /tmp/tnsnames.ora

Control:

mylab01> sqlplus aro@DBL121
[...]

SQL> select count(*) from TABLE1@my_db_link;

  COUNT(*)
----------
         4

Perfect 🙂

Stay tuned for more DBA stuff!

Database Link: ORA-12154: TNS:could not resolve the connect identifier specified