:

SQLPlus Hangs

We were seeing an intermittent problem very recently whereby the listener would not accept connections for all DBs that it was listening for. A restart of the listener would resolve the issue, but within 5 minutes of the restart it would be stop accepting connections again.

We had no idea what was causing the issue, so we attempted to turn listener tracing on using the following command in the listener.ora file:

DIAG_ADR_ENABLED_LISTENER=on
TRACE_LEVEL_LISTENER=10

I looked at the resultant trace files but there were no errors and the SQL plus sessions were still hanging. Existing connections were fine and there were no errors in the alert log. Very strange indeed!

While looking through the trace files I did notice this piece of information which I thought might help to provide a clue as to what the problem was:

2012-05-15 17:26:57.257695 : nsnainit:”or” info flags: 0xd Translations follow:
native service(s) is (are) wanted
NA disabled remotely for this connection
NA services unavailable on both processes – negotiation not needed
“and” info flags: 0xd Translations follow:
native service(s) is (are) wanted
NA disabled remotely for this connection
NA services unavailable on both processes – negotiation not needed

Unfortunately this did not reveal anything upon further investigation.

The one thing that I did notice was that it seemed to always get to the same point when it stopped working, and that point was the line at the bottom “snlpcss:entry”. I also noticed that the connection was always being made to the same DB, which can be seen from the line
“nlpcaini:arg[0] = oracle<SID>”
where <SID> is your DB name.

2012-05-15 17:26:57.258650 : nlpcaini:prg = oracle
2012-05-15 17:26:57.258659 : nlpcaini:arg[0] = oracleDW3SDE
2012-05-15 17:26:57.258666 : nlpcaini:arg[1] = (LOCAL=NO)
2012-05-15 17:26:57.258673 : nlpcaini:env[0] = ALLUSERSPROFILE=C:\Documents and Settings\All Users

2012-05-15 17:26:57.258681 : nlpcaini:exit
2012-05-15 17:26:57.258691 : nsbeqaddr:connecting…
2012-05-15 17:26:57.258699 : nsmal:1496 bytes at 0x5603500
2012-05-15 17:26:57.258707 : nsmal:2944 bytes at 0x56eaa20
2012-05-15 17:26:57.258719 : ntpcon:entry
2012-05-15 17:26:57.258726 : ntpcon:toc = 6
2012-05-15 17:26:57.258732 : ntpcon:exit
2012-05-15 17:26:57.258739 : nsopen:opening transport…
2012-05-15 17:26:57.258745 : ntpcon:entry
2012-05-15 17:26:57.258751 : ntpcon:toc = 1
2012-05-15 17:26:57.258761 : sntpcall:entry
2012-05-15 17:26:57.258770 : snlpcss:entry

So, I took a look at that database and I could not connect to it, even without using the listener. This looks promising! I asked to shutdown the DB, restarted the listener and waited….After 20 minutes it was all OK so I restarted the other DB to have a further look.

Very unfortunately, there was no alert log for this DB. The reason being because the ADR base was on the wrong drive. And that is the reason it failed in the first place. The diagnostic_dest init.ora parameter was set to point at a different drive and it just so happens that we blew away that drive and replaced it this morning. This is when the other DB seemed to get its knickers in a twist and stop working.

Still, we managed to solve the problem 5 minutes before we went home for the day so all is well that ends well!

In addition to the problem outlined above, I came across another problem when SQL Plus was hanging while attempting to connect to a database without using the listener, using a local connection, for example:

SET oracle_sid=TESTDB
SQLPLUS "sys/password as sysdba"

Normally this works and there shouldn’t be any problems but for some reason SQL Plus just hangs at this point indefinitely. No control is returned to the user, nor is there an error message.

The Solution

On Metalink I found a note [ID 473630.1] that suggested removing SQLNET.OUTBOUND_CONNECT_TIMEOUT from the sqlnet.ora file for a similar problem. I tried it and it worked! I’m not sure exactly why this works but it seemed to in our case. The original setting that we had for the SQLNET.OUTBOUND_CONNECT_TIMEOUT was 120. 120 is the number of seconds.

Seems a little strange but if there is no particular reason why you have this setting and it works without it then I would suggest leaving it out so that your local connections work properly.

I hope this helped you solve your problem.

Like it, share it...

Category: SQL Plus


Related Posts

Leave a Reply

Your email address will not be published. Required fields are marked *