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

This issue is faced in some versions of oracle as ORA-12154: TNS: Could not resolve service name. Mostly this issue comes in Oracle 10g and Oracle 11g Express edition which i have faced.

When you or any application try to connect to an Oracle Service via Oracle Networking SQL*Net, it looks up the connect data for the service. When this lookup fails, Oracle client show an error message ORA-12154.

Sometimes this error comes when one database instance tries to connect to another database instance via database link.

Root Cause:

You try to connect to Oracle but the service name is either missing from the TNSNAMES.ORA file or is incorrectly defined.

Resolution:

Option-1:

You have to make sure that the TNSNAMES.ORA file exists in the correct directory and accessible.

%ORACLE_HOME% /network/admin/

Ex: In Oracle 11g express, it is located at below path.

C:\oraclexe\app\oracle\product\11.2.0\server\network\ADMIN

Option-2:

You have to make sure that the service name that you are trying to connect is included in the TNSNAMES.ORA file and that it is correctly defined.

Option-3:

Make sure that TNSNAMES is listed as one of the values of the names.directory_path parameter in the sqlnet.ora Oracle Net profile.

Option-4:

You have to make sure that the syntax of TNSNAMES.ORA file is correct.

For example, if there are unmatched brackets in the file (ie: open bracket without the corresponding close bracket), the file will be rendered unusable.

XE =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = TESTING.COM)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = XE)
)
)

If you are still facing the same issue, please write to us in comment section. We will try to help you.