ORA-12154 TNS:could not resolve service name

ORA-12154 error is thrown during the logon process to a database. This error indicates that the communication software (TNS) in Oracle ( SQL *Net or Net8 ) did not recognize the host/service name specified in the connection parameters.

For a database name to be recognized, it must be resolved by Oracle Client. To resolve a name, Oracle client uses either a TNSNames.ora, A Directory Server or an Oracle Names Server. ORA-12154 error most likely indicates that the resolution is not working and that there is an error in the TNSNAMES.ORA or SQLNET.ORA. For database connectivity to work your database name has to be entered into the TNSNAMES.ORA at a minimum to make this work.

To fix an ORA-12154 error:

1. Check that the instance or database name is being typed in correctly. It is sometimes also referred to as the connect string of the database
2. Check the tns names.ora file to validate that the instance name has been entered correctly. If you are in Unix, validate that there are no control characters at the end of the instance or database name and that all paranthesis around the tns entry are properly terminated
3. If everything looks good in tnsnames.ora, validate that the domain name entry in sqlnet.ora is not conflicting with the full database name resolution.
   
4. If you are unable to find a solution, best practice is to delete and recreate the entry to eliminate any character or control character issues.

If the entry you are looking for in your tnsnames.ora is missing, use the following procedure to add a new entry using the SQL*Net Easy configuration utility.

• Start>Programs>Oracle >SQL Net Easy Configuration.
• Select the option to Add a new instance alias.
• Enter the parameters are required in the forms. If you do not have this information, contact your dba.
• Confirm the new entry.
• Repeat for as many aliases as needed.

Tip : Most ORA-12154 tnsnames issues arise out of multiple oracle homes, incorrect entries made somewhere in the file etc. 

1. Try searching the drive to identify how many tnsnames.ora files you have

2. Try renaming the  tnsnames.ora file in the oracle home where you are facing the issue and add just the entry you are having an issue with. If it works, you know that the issue is somewhere else in the file. 

Tip for DBA's : Use a centralized naming service to avoid most issues with client connectivity and the generation of ORA-12154 errors. 

Related Links :

• 10G and DBCA
• SQLPlus Connection Problem
• Strange Connectivity Problem
• TNS Ping Resolves but ORA-12154
• Database Links ORA-12154
• Search Oracle Forums