ORA-12154: TNS:could not resolve service name is returned by db link

Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.

This posting refers to the occurrence of ORA-12154 in combination with database links, other scenarios are not covered.

Suppose that you created a database link pointing to a remote db, located on a machine that we call server_2 for convenience, using a syntax like this:

CREATE DATABASE LINK server2_db
CONNECT TO dbuser IDENTIFIED BY pwd
USING 'SERVER2_DB';

Upon executing a query like the following from a client PC:

select * from remote_tab@server2_db;

you get the following error:

ORA-12154: TNS:could not resolve service name

the instinct may be to check out the content of your local TNSNAMES.ORA file where you see that a SERVER2_DB service name is actually existing, which is puzzling at first.
Or you might find out that such entry doesn't exist at all, but even after adding it, the db link doesn't work, returning exactly the same error...

As a matter of fact when the USING clause is specified as above, Oracle tries to locate the service name in the TNSNAMES.ORA file, but hang on a second, which TNSNAMES.ORA file?

If you created the db link on a remote database (that from now on we call server_1), the TNSNAMES.ORA file is the TNSNAMES.ORA of server_1, not the local TNSNAMES.ORA on your PC.
This fact is obvious if you think about it, but the instinct, when you get TNS errors, is to look at your local network configuration.

After identifying the cause of the problem you must ensure that you are using the correct service name, assuming that such entry actually exists in the TNSNAMES.ORA file on server_1, which could be a false assumption in the end.

Moreover the access to the server directory where the TNSNAMES.ORA file is located might be restricted, so you could have no way to look at the content of that file.

If you are in this situation, you have two options:

1. ask the DBA for the service name (if any)
2. create the database link in the following way:

CREATE DATABASE LINK server2_db
  CONNECT TO dbuser IDENTIFIED BY pwd USING
 '(DESCRIPTION =
   (ADDRESS_LIST =
     (ADDRESS = (PROTOCOL = TCP)(HOST = server_2)(PORT = port_num))
   )
   (CONNECT_DATA =
     (SERVICE_NAME = server2_db)
   )
 )';

Clearly you need to know all relevant parameters of server_2 (dns or IP address, TCP port number and instance name), otherwise you'll get other types of errors at run time.

See message translations for ORA-12154 and search additional resources.