SQLPlus, TNSPing & everything just hang without errors

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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s