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.

Get Free Blogging Tips & Technology updates in your Email !!!

Subscribe via RSS feed

The following two tabs change content below.
Anil Kumar
IT Blogger Tips focus on Blogging Tips, SEO Tips, Social Media, SQL Tips, PL/SQL Tips, Oracle DBA, Linux/Unix, Latest Technology, How Tos and Technical Solutions. You can find us on Facebook | Twitter |
Find on Google+

  • 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?

  • 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.

Siteground SALE
60% OFF SiteGround WordPress Hosting – $3.95/month (Exclusive Offer !!!)