ORA-12560: TNS: protocol adapter error

Question:  I installed Oracle 10g on my server but there is a problem with error "ORA-12560:TNS:protocol adapter error".  When I sign on I get the error "ORA-12560:TNS:protocol adapter error"...
Enter password:
ERROR:  ORA-12560: TNS:protocol adapter error

Enter password:
ERROR:  ORA-12560: TNS:protocol adapter error

Answer:-
Is there a listener running? Have you run the netca program to configure and start the listener?  The Oracle docs note that you should troubleshoot the ORA-12560 by tracing the connection:

ORA-12560: TNS: protocol adapter error

Cause: A generic protocol adapter error occurred.

Action: Check addresses used for proper protocol specification. Before reporting this error, look at the error stack and check for lower level transport errors. For further details, turn on tracing and re-execute the operation. Turn off tracing when the operation is complete.

Steps to troubleshoot Oracle connectivity:

To troubleshoot & diagnose Oracle connectivity problems, I like to start at the simplest, outermost level, and follow this checklist:

1. Check with "ping" using the IP address ("ping 198.11.34.43"
   
   
2. Check with "ping" using DNS name (e.g. "tnsping uranus")
   
   
3. Try telnet to the IP on port 1521 (telnet 1.2.3.4 1521)
   
   
4. Check with "tnsping" using TNS service name
   
   
5. Invoke SQL*Plus from the OS command line "sqlplus fred@flintstone".  If this fails, check to ensure that your listener has the flintstone service defined.
   
   
6. Sign-on to SQL*Plus and connect with the TNS name ("connect fred/flintstome@service_name"
   
   
7. Within SQL*Plus, try a select from table@remote_db_link

There are a variety of common network connectivity error messages, and most DBA's have seen TNS error messages these at sometime in their careers.  Here is just a small sample of possible TNS network connectivity-related errors:

• TNS-12545: Connect failed because target host or object does not exist
• ORA-12154: TNS: Could not resolve service name
• ORA-12157: TNS Internal network communication error

In the simplest of terms, the Oracle*Net architecture is like peeling on onion, with one transport layers nested inside the inner payer.  The lowest level of Oracle transport is TCP/IP (or rarely other protocols), where the packets are shipped between the servers.

Steps to troubleshoot Oracle connectivity

To troubleshoot & diagnose Oracle connectivity problems, I like to start at the simplest, outermost level, and follow this checklist:

1. Check with "ping" using the IP address ("ping 198.11.34.43"
2. Check with "ping" using DNS name (e.g. "tnsping uranus")
3. Try telnet to the IP on port 1521 (telnet 1.2.3.4 1521)
4. Check with "tnsping" using TNS service name
5. Invoke SQL*Plus from the OS command line "sqlplus fred@flintstone".  If this fails, check to ensure that your listener has the flintstone service defined.
6. Sign-on to SQL*Plus and connect with the TNS name ("connect fred/flintstome@service_name"
7. Within SQL*Plus, try a select from table@remote_db_link

Let's look closer as we peel away the layers of our onion.

Server-level connectivity layer in TNS

When the Oracle DBA creates their tnsnames.ora file to define remote databases they often specify the host name of the foreign server that contains the remote Oracle database.  For example, a entry in the tnsnames.ora file for a remote database might look like this:

sonu =
   (DESCRIPTION =
     (ADDRESS_LIST =
         (ADDRESS =
           (COMMUNITY = TCP)
           (PROTOCOL = TCP)
          (HOST =sujeet)
           (PORT = 1521)
          )
      )
     (CONNECT_DATA = (SID = orcl))
    )

Here we see a TNS service name of sonu, which defines a connection to a remote server named sujeet that contains an Oracle database named orcl. When a remote connection request is made from the UNIX server, the /etc/host file is accessed to get the IP address for the sujeet server. 

From the listing below, we see that the sujeet server is located at 192.133.13.12.  In sum, the /etc/host file is used to isolate the IP address from the tnsnames.ora file.  If the IP address should ever change, the UNIX systems administrator only needs to change the IP address in one place.

root> cat /etc/hosts
192.133.13.22  sujeet.oracle.com   sujeet
192.144.13.22  sonu.oracle.com sonu