ORA-12154: TNS: Could not resolve service name – This issue is generally faced in some oracle versions. Mostly the Oracle 10g and Oracle 11g Express edition users face this issue.
Whenever you or any other application try to connect to Oracle Service via Oracle Networking SQL*Net then it look for the connect data for the service.
And when this lookup fails the Oracle client starts showing an error message ORA-12154.
Sometimes this error message often comes when one database instance tries to connect to other database instance via the database link.
Root Cause behind the error ORA-12154:
When you try to connect to Oracle Database but the service name is either missing from TNSNAMES.ORA file or incorrectly defined.
Solutions:
Option-1:
You need to make sure that TNSNAMES.ORA file exists in the correct directory and is accessible.
%ORACLE_HOME% /network/admin/
Ex: In Oracle 11g express, is located at the given below path.
C:\oraclexe\app\oracle\product\11.2.0\server\network\ADMIN
Option-2:
You need to make sure that the service name that you are trying to connect is in TNSNAMES.ORA file and is correctly defined.
Option-3:
Ensure that TNSNAMES is listed as one of the values of names.directory_path parameter in sqlnet.ora Oracle Net profile.
Option-4:
You need to ensure that the syntax of TNSNAMES.ORA file is correct.
For instance, if there is an unmatched brackets in the file (ie: open bracket without the corresponding close bracket), them the file will be rendered unusable.
XE =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = TESTING.COM)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = XE)
)
)
Hope the above solution let you fix the issue but if not so then the last option left with you is to use a third party repair tool. You can use Oracle Repair tool to fix ORA-12154 error. This repair tool is capable of fixing any sort of corruption, damaged or inaccessible issue in your oracle database. This tool looks for all sort of Oracle error that are present in your system and fix them efficiently. It even display the preview of the recovered file before restoring it. It is very easy to use and does not require any technical skills to operate it.
Oracle Database Repair Tool 