ORA-12154: TNS:could not resolve service name after creating a new oracle home

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

Imagine that you need to set up Oracle Application Express and Oracle HTTP server on an existing system (Oracle 10R2 SE on Windows 2003 server in my case).
The installation requires that you install Oracle HTTP server in a new oracle home.

After successfully installing Oracle HTTP Server (Apache) and Apex, you are so happy because everything installed seamlessly until you get a call eight hours later because some batch loader scripts stopped working.
Luckily those scripts are logging errors and what i find in the logs is the following error message:

ORA-12154: TNS:could not resolve service name

When you find this type of error, the best thing is to verify if you get the same error when manually connecting to the database using the same connection string from the same machine where the error was returned.
Don't try from a different client pc, because it wouldn't make sense in this case.

sqlplus user/pwd@connector

If sqlplus returns the same error, it means that the currently used TNSNAMES.ORA does not contain a valid connector, but you go to the usual folder where the TNSNAMES.ORA file is located and everything looks fine.
So, which TNSNAMES.ORA file is being used then?
That can be easily verified using command TNSPING.

TNSPING will return the location of the TNSNAMES.ORA file being used to resolve the connector, which in turn depends on the priority of the Oracle homes and this takes us back to the beginning of this post: I've just created a new oracle home for the Oracle HTTP server and, as a consequence, the new oracle home was moved on the top of the list and therefore the TNSNAMES.ORA being used now is located in a different place. Note however that running programs may still be using the old one if they were launched before the new oracle home was created, which explains why they may still run ok.
This is why you should ALWAYS stop any processes connecting to the db when doing an installation of some component, because otherwise you can easily find in an inconsistent state: some things work, some don't and you don't understand why.

After realizing that the presence of the new oracle home caused the troubles, it's necessary to run the Oracle Installer and change the environment settings so that the "main" oracle home is restored on the top of the list.

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

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.