If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

How to troubleshoot Oracle DB hanging?

Hi,

Are there any general tips on how to troubleshoot Oracle DB hanging or appear to be hanging? I have two DB on the same server, one appeared to be hanging while the other one is OK. For the hanging one, there is no unusual entries in the ALERT log, nor trace files. Listener.log file shows it is accepting connections to the hung DB. The sympton is that whenever a connection is made to the hung DB, it never returns anything...just stay as is. It was fixed by restarting it. I turned on ARCHIVELOG mode and started auto archiving last weekend but the ALERT log shows it's quite normal except there was nothing after 22:31 (unless I misread it):

So it looks like the connection is made. However, the SQLPLUS never returns with SQL> prompt. It stopped at:

SQL*Plus: Release 8.1.7.0.0 - Production on Tue Dec 10 14:41:12 2002

(c) Copyright 2000 Oracle Corporation. All rights reserved.

And that's it. There are no errors I can find.

The only thing special is that because it's behind the firewall, I had to turn on USE_SHARED_SOCKET for it to accept outside connections. This was done about a month ago. I also notice when listener redirect ports upon incoming connections, it only uses port 1026-5000. I have programs that connect to this database from another server every 30 seconds. So in the listener log, there are at least 5 connection entries for every minute.

Also, I turned on ARCHIVELOG mode last weekend because I have a standby server setup for this server. It is MANUAL STANDBY and it should not affect this problem at all.

The thing that bothers me the most is that it did not give out error messages.

Yes. Automatic archiving has been turned on. As a proof, redo logs have been rotating among the 6 log groups for many times, as recorded in the ALERT log and ARCx in general cought up pretty fast, at most 1 or 2 logs behind in terms of archiving.

Thanks for the idea. But the problem is: when it hung, I was not able to log in, not even with CONNECT INTERNAL. The same result as the above shown. I've tried it on the server itself and from remote. Same results.