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.

ORA 12154 Error

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.

3 thoughts on “Resolved ORA-12154: TNS:could not resolve the connect identifier specified”

  1. Avinash Thotlagiri

    I have the same problem. I cross checked all the resources and the values in all the files. But couldn’t figure it out. Please suggest an another solution for resolving the issue.

    Regards,

    Avinash T

    (AVINASH THOTLAGIRI)

  2. Poonam

    I have checked my file TNSNAMES.ORA .i didn’t get the below option what exectally want to do in following step.
    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.

  3. K PriyaDARshaN

    Hello,
    I’ve installed two database(Oracle 11g Enterprise) for testing purpose on my system. 1. ORCL, 2. ORCLDB. when I’m trying to connect ORCLDB, at that time I get ORA-12154 Error message, but ORCL is connecting. But when I’m changing SET ORACLE_SID=ORCLDB on cmd, at that time my ORCLDB is connected but ORCL isn’t connected. how to solve this problem?

Leave a Comment

Your email address will not be published. Required fields are marked *

shares
Scroll to Top