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

Often users face this issue while establishing a connection to their database. In layman terms, it implies that Oracle is unable to understand the user-defined connection. The most common reason for such an error is syntax errors, perhaps in a user’s connection string – most probably in a user’s tnsnames.ora configuration file. Moreover, the issue may occur when the tnsnames.ora file doesn’t exist or is inaccessible.

Whenever there is a need for a program to link to an Oracle service through an Oracle Networking program such as SQL*Net), it has to find the link data needed for that service. If there is a failure in locating the data, then the Oracle’s client code shows up the error ORA-12154. Most users face such a technical problem on connecting an application to an Oracle-based database service. There are also chances of its existence when there is problem in connecting two database instances through a database link.

It is also to be noted that the error generated is due to an Oracle-based client code present on the local host server where the program is executing rather than on the remote Oracle service location where a user is trying to connect.

In order to locate the link data, the following ways can be implemented for the Oracle-based client code.

• The tnsnames.ora configuration file
• Lightweight Directory Access Protocol (LDAP)
• Oracle Name service

When the error ORA-12154 displays with the text, then the program is trying to locate an existing Oracle-based client install, but the specified Oracle service is not located among LDAP, Oracle Name service, or tnsnames.ora config file. Common causes related to ORA-12154 are:

• The tnsnames.ora file is missing an entry.
• The tnsnames.ora file’s entry is incorrect.
• The program is not able to use the right ORACLE_HOME to access the tnsnames.ora file.
• There is lack of a proper service name and no default domain has been enabled in the sqlnet.ora file.

A number of sites use tnsnames.ora, but there are also a few that prefer LDAP server protocol and Oracle Names. For them the initial step is to find the name resolution that is under deployment on a user’s site. Furthermore, if the user is not having administrator privileges, then he/she will have to contact the people behind the Oracle systems managing department for their suggestions on the preferable method for usage. Many a times, the officials at Oracle systems will help out users and provide help for resolving any issues in relation to a user’s site standards.

It is the client code that makes the decision of the type of process preferable by the system that is based on sqlnet.ora file. Both sqlnet.ora and tnsnames.ora are mostly located in Oracle’s install directory (ORACLE_HOME), stored in network/admin/. However, overriding this location is possible by the use of the environment variable: TNS_ADMIN. If there is no sqlnet.ora file present, then Oracle Net usually prefers tnsnames.ora as its substitute.

For Windows:

ORANTNET80ADMIN

ORACLEORA81NETWORKADMIN

ORAWIN95NETWORKADMIN

ORAWINNETWORKADMIN

For Linux/Unix:

$ORACLE_HOME/network/admin/

/etc/

/var/opt/oracle/