Troubleshooting & diagnosing TNS SQL*Net connectivity errors

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 

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 1.1.4.3")
2. Check with "ping" using DNS name (e.g. "tnsping database")
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 abcd@database".  If this fails, check to ensure that your listener has the database service defined.
6. Sign-on to SQL*Plus and connect with the TNS name
7. Within SQL*Plus, try a select * from table
   
   
   
   
   Above mentioned steps are the first sign of checking the connection error. My server was able to clear all the above seven steps. Still I am not able to connect to database via my application. Then I changed my sqlnet.ora. There were multiple entries, to which I reduced them to a single line
   
   
   names.directory_path= (tnsnames)
   
   
   Then on investigation I found out, the code that was causing trouble is:
   
   
   SQLNET.AUTHENTICATION_SERVICES = (NTS)

   names.default_domain = world
   
   
   So even if I commented these two lines in my original sqlnet.ora, it worked. Hope this works for you as well.