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.