Tuesday, September 1, 2015

TNS-12560: TNS:protocol adapter error

This is one of the most common and generic errors you may face while trying to connect with the database. There could be a lot of reasons for this. To troubleshoot this, you may want to go through following checklist
Check your TNS entry in TNSNAMES.ORA file, if it is set correctly and there is no syntax error and it is pointing to correct server and instance.
Use “tnsping” command to check the TNS service. This will also show the path of sqlnet.ora file to confirm where your TNS_ADMIN is pointing to. If you see anything wrong, set your TNS_ADMIN correctly to point to correct $ORACLE_HOME/network/admin location.

C:\Users\00140^OperaSupport>tnsping test

TNS Ping Utility for 32-bit Windows: Version - Production on 19-AUG-2015 01:45:19

Copyright (c) 1997, 2003, Oracle.  All rights reserved.

Used parameter files:

Used TNSNAMES adapter to resolve the alias
TNS-12560: TNS:protocol adapter error

There could be a possibility that listener port (1521 is default) is blocked for security reasons. Use telnet command from client to connect with port 1521 of the server to check if 1521 port is open and accessible. Error as shown below would mean that port is not accessible. If telnet succeeds, it would just show a blank screen without any error message.

Microsoft Windows [Version 6.1.7601]
Copyright (c) 2009 Microsoft Corporation.  All rights reserved.

C:\Users\salmqure>telnet MYSERVER 1521
Connecting To MYSERVER...Could not open connection to the host, on port 1521: Connect failed

Make sure there are enough resources available on both client side and database server side. If not enough resources (CPU, RAM, pagefile/swapspace) available, you might be facing TNS-12560 error. Open task manager (or use “top” command on Linux) to check current resources utilization.
Check if network connectivity from client to database server (and vice versa) working fine. Even if “ping” command is working fine, still there could be some network connectivity issue which might be the reason of TNS-12560. 
Once I faced this error message, and to check client server network connectivity I tried to access database server “d:” drive by typing \\<host_IP>\d$ in run command box; and it returned me error “The name limit for the local computer network adapter card was exceeded” as you can see bellow.

Microsoft provides instructions here to troubleshoot this problem. In my case, IT team helped to resolve this problem and after that TNS-12560 was gone

No comments: