How to troubleshoot Oracle DB hanging?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: How to troubleshoot Oracle DB hanging?

  1. #1
    Join Date
    Apr 2001
    Posts
    257

    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):

    ---------
    ...
    Mon Dec 09 20:33:18 2002
    Thread 1 advanced to log sequence 2384
    Mon Dec 09 20:33:18 2002
    Current log# 5 seq# 2384 mem# 0: C:\ORACLE\ORADATA\SILICON1\REDO5A.LOG
    Current log# 5 seq# 2384 mem# 1: C:\ORACLE\ORADATA\SILICON1\REDO5B.LOG
    Mon Dec 09 20:33:19 2002
    ARC1: Beginning to archive log# 4 seq# 2383
    Mon Dec 09 20:33:20 2002
    ARC1: Completed archiving log# 4 seq# 2383
    Mon Dec 09 22:31:38 2002
    ARC1: Beginning to archive log# 5 seq# 2384
    Mon Dec 09 22:31:38 2002
    Thread 1 advanced to log sequence 2385
    Mon Dec 09 22:31:38 2002
    Current log# 6 seq# 2385 mem# 0: C:\ORACLE\ORADATA\SILICON1\REDO6A.LOG
    Current log# 6 seq# 2385 mem# 1: C:\ORACLE\ORADATA\SILICON1\REDO6B.LOG
    Mon Dec 09 22:31:39 2002
    ARC1: Completed archiving log# 5 seq# 2384
    ------ END OF ALERT LOG ------------

    Does anybody have tips on what I can do to find out the possible source of problem?

    Appreciated!

  2. #2
    Join Date
    Feb 2001
    Posts
    99
    Are you in NT or Unix?
    What version of Oracle?
    What level of listener loggin are you using? How big is your log file

    When you say it accepts the connection but returns nothing, does that mean you can make a sqlplus connect but if you do a select statement you are not getting any data back?

    Are you seeing any other errors?

    More info pse!!

  3. #3
    Join Date
    Apr 2001
    Posts
    257
    Hi,

    It is Oracle 8.1.7.3 on Windows 2000 with SP2. Listener trace level is 0. The listener log file is about 36MB. When I made a connection to the hung DB, it produced this line in listener.log file:

    10-DEC-2002 09:35:10 * (CONNECT_DATA=(SERVICE_NAME=sil.gs.com)(CID=(PROGRAM=C:\oracle\ora81\bin\SQLPLUS.EXE)(HOST=ADY)(USER =ady))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.222)(PORT=1057)) * establish * silicon1.getsilicon.net * 0

    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.

    Thanks,

  4. #4
    Join Date
    Aug 2001
    Location
    Waterloo, On
    Posts
    547
    Did you turn on automatic archiving when you turned your database to archivelog mode? If not, you have to manually archive your logs, they may be full.

    Raminder Singh

    Oracle Certified DBA: Oracle 8i, 9i


    Mail me at raminderahluwalia@rediffmail.com.

  5. #5
    Join Date
    Apr 2001
    Posts
    257
    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.

  6. #6
    Join Date
    Aug 2001
    Location
    Waterloo, On
    Posts
    547
    You might have to re-install sqlplus if it is not asking for username/ password.

    Raminder Singh

    Oracle Certified DBA: Oracle 8i, 9i


    Mail me at raminderahluwalia@rediffmail.com.

  7. #7
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    when it hangs go to local server, do connect internal or sys as sysdba then query v$session_wait and see what is causing the hang

  8. #8
    Join Date
    Apr 2001
    Posts
    257
    Raminder,

    I think you misunderstood my description of the problem. When I did

    sqlplus user/pwd@svc

    it gave me:

    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.

    But it never returned:

    Connected to:
    Oracle8i Release 8.1.7.3.0 - Production

    And the SQL> prompt.

    -----------
    Pando,

    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.

    Is connecting using SQLPLUS /NOLOG the same as using SVRMGRL?

    Thanks!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width