ORA-12154 TNS:could not resolve service name

You might have come accross this error when connecting to an Oracle server from an Oracle client. Oracle client will throw this error if it is unable to connect to the server by using the service name specified. This error actually means that Sql * Net is  unable to recognize the host/service name specified in the connection Parameters.

Explanation about how sql * net client connects to the server:

The parameters that are required to connect to an Oracle server.

1. Username
2. Password
3. Service name/connection String

What is a service name ?

Service name specifies an entry in the tnsname.ora file. Each service name consists of  hostname/ip address of the Oracle database server you want to connect, Port at which Oracle listener is working, the database instance you want to connect to, and the protocol you are using for the connection.

Oracle client can connect to multiple instances of Oracle server by using the entries in tnsnames.ora file, by a directory server or by Oracle names server.

Tnsnames.ora file located in the /network/admin path of your Oracle home directory.

Reasons why you might face this ORA-12154 TNS:could not resolve service name error :

• Wrong entry in the tnsnames.ora file.
• Incorrect host information, port number Or Database instance specified in the tnsnames.ora file.
• Mistyped or Misspelled Or Incorrect correct entry in the tnsnames.ora file.

Solutions to the Ora-12154 :
1. Check whether you system is have multiple instances of Oracle homes and multiple tnsnames.ora files and you are making the right entry to the right tnsnames.ora file.

 
2. Instead of manually placing an entry in the tnsnames.ora file , use the SQL Net Easy Configuration wizard.

 
3. Check the tns names.ora file to validate that the instance name has been typed correctly. If you are in Unix, validate that there are no control characters at the end of the name

 
4. If everything is fine and you are still facing the problem, go through the steps specified below, it will definitely work out.

Step 1 : Download a tool called Oracmd (oracle command line tool) from this link. This software is a shareware , so you can use it for 30 days with out a problem.

Step 2 : Install the tool Oracmd.The speciality of this tool it does not have any dependency on Oracle client installed on your system. it can even work if there is no Oracle client installed on your system. It does not require a tnsname.ora file.
       

Step 3 : Run the Oracmd tool . At the prompt type  connect username/password@host:port:SID

Host : host stands for Sever name or ip address where the oracle server is residing.
port : port stands for port on which oracle listener is working.
Sid :  System identifier is instance of oracle database you want to connect.

You can copy the host,port,sid values from tnsnames.ora file.

if you are able to perfectly connect to you Oracle database server , then you have right hostname, port and sid information. if you are unable to connect, then there should be a problem with the network connections to the Oracle server or firewall Problems or wrong hostname,port and sid information. Proceed to step 4 only if you are able to connect to Oracle with Oracmd.

Step 4 : Back up the tnsname.ora as tnsnames.ora.old file

Step 5 : Run  Start>Programs>Oracle >SQL Net Easy Configuration.

  Select the option to Add a new entry for network service name.
  Enter the parameters are required in the forms.
  Confirm the entry.

Step 6 : Check out the new changes in the tnsnames.ora file

Step 7 : Try to Connect now to Oracle databse server.

Step 8 : if you are Still unable to connect to the Oracle Server by the above process , Remove all the entries in the tnsname.ora file, add them one by one by using Sql Net Easy Configuration.