Usually, when you connect to Oracle, you get errors that give you some feedback on what is happening.
Today, I ran into an issue where everything just hung. TNSPing, SQLPlus… you name it. The issue had to be connectivity related but what is it?
In our case, we use an LDAP server as a directory server in addition to tnsnames.ora. Our sqlnet.ora file looks like this:
NAMES.DIRECTORY_PATH= (LDAP,tnsnames)
Tracing with sqlnet.ora entries – TRACE_LEVEL & TRACE_DIRECTORY :
To troubleshoot the issue with tnsping hanging, all you need to do is add these settings in sqlnet.ora to trace tnsping
TNSPING.TRACE_LEVEL = ADMIN
TNSPING.TRACE_DIRECTORY = C:\Temp\TNS
In my case, it is my Windows box which had a folder c:\Temp\TNS. In your case it may be a Linux box. Alter the path accordingly.
Now, if you run tnsping to a known database again, a tracefile with the name “tnsping.trc” would get generated in the path TNSPING.TRACE_DIRECTORY.
Review the file and it will show what the last operation was. In my case, it was having issues connecting to the LDAP server since that was the last entry on the trace file.
I was able to confirm that this was the issue by using tnsnames alone and that connected fine. Below, you can see that I just removed the LDAP to check if that was the issue.
NAMES.DIRECTORY_PATH= (tnsnames)
This quick tip could save you some frustration when trying to troubleshoot a hang issue with no error message.
SQL Jana 