Thursday, 21 May 2015

ORA-12560 : TNS Protocol Adapter Error Solution | Oracle Database

ORA-12560 : TNS PROTOCOL ADAPTER ERROR

How do we solve ORA-12560 Error? How to solve TNS Protocol Adapter Error? Tips and Tricks to resolve ORA-12560 TNS Error in Oracle Database

This is a most common and disturbing error which is faced my most of the Oracle users while connecting to the database. Even I also had faced it many times. So what I am going to share here is the method which I use to fix this problem.

ORA-12560 comes when you are making a connection to the database and somehow it is not able to connect. At that time, it simply throws TNS Protocol Adapter Error. The reason can be one of the following:

1. Listener is not active.
2. Third Party Software.
3. Problem with the tnsnames.ora file.

1. Listener is not active


This may be a case when you might haven't noticed that your listener process is not working and you are trying to make a connection with the database. At that time, ORA-12560 error will be thrown.

Solution:

  • Simply press Windows + R
  • Type services.msc and press enter



Look for listener process in the services list and check whether it is running or not. If it's not running, press right click button and start the process.


After you have started the listener service, you are allowed to make a connection with the database.

2. Third Party Software


This problem is also very common with the different Oracle users who use third party software to make a connection with the database. For example, prior to Oracle 11g database, we have to install PL/SQL Developer manually which was externally added to the system. Now sometimes ORA-12560 error problem also comes with this tool.

Solution:

Third Party Tools sometimes aren't able to get the connection string by their own. So we have to check the preference setting in these tools and fix the problem in case if it is required.



Just go to Preference -> Connection and set the path there. You'll solve the connection problem.


3. Problem with the tnsnames.ora file


To connect to a database, you have to configure tnsname.ora file. At the time of default database creation, configuration is automatically added to the tnsnames.ora file. But in case if you want to connect to some other database on the network, you have to manually insert the settings in the tnsnames.ora file which includes the insertion of new IP and Port.

Tnsnames.ora file looks something like this:


Solution:

There may be a case where your database name and the other database name (which is on some other machine) are same. In that case, you have to manually give them different names so that you can differentiate between them. Like in the above image "DEMON" is the database name given for the local connectivity. I can also change it to DEMON1 or DEMON2 or can be any other name but make sure that you are not changing the SERVICE_NAME. By default my Service_Name and the name which I am using for the connectivity are both same. But you can replace "DEMON" with anything you want. Just don't change the SERVICE_NAME.

There can be different databases with the same service name on a network. All you need to do is, provide a different name to this connection like mine is DEMON.

4. Making TNS_ADMIN Entry in Environment Variables.

In case you have multiple Oracle Environments (Oracle Softwares) installed on your system, there are chances that the Environment is unaware of the TNS_ADMIN directory it needs to use. To resolve the same, add the entry of TNS_ADMIN in the System Environment Variables on your system. Below mentioned are the demos of TNS_ADMIN paths, 

%ORACLE_HOME%\network\admin
$ORACLE_HOME/network/admin

I hope this helps !!

No comments:

Post a comment