Connections to DataBase Hang Including “/ as sysdba”

Recently I faced one issue where all the connection to database hung and it was also not possible to login to database using “/ as sysdba”.
To get access of sqlplus I used the following syntax:

$ sqlplus -prelim / as sysdba

With “prelim” option we can run some commands which will help in collection useful information about the problem.

This will work only in Oracle 10g and higher version.

After successfully getting connected run the following commands to generate Hanganalyze and systemstate traces:

SQL> oradebug setmypid

SQL> oradebug unlimit

SQL> oradebug dump systemstate 266

SQL> oradebug tracefile_name

— This will give you the name of the tracefile generated.

SQL > oradebug dump hanganalyze 2

SQL > oradebug tracefile_name

To analyze these trace files one should be aware of Metalink Note: 215858.1.

After analyzing these files I found that following event was active and causing the hang:

<span style="font-family: arial,helvetica,sans-serif;"><span style="font-size: small;">"resmgr:cpu quantum"
Cmd: PL/SQL Execute

It means that the sessions are waiting for their turn on CPU.

This event occurs when resource manage is active and controls the allocation of CPU to processes.

We can also see the command which is causing all this: i.e some PL/SQL code was executing and spnning on for CPU.

After finding out this, checked with “TOP” command, got the PID of the process consuming all the cpu and killed that process with “kill -9”

After killing that process the users were able to connect.

So the cause of the Hang was found i.e PL/SQL, but it is still unknown why  PL/SQL caused problems. 🙂

Cheers!!!

Saurabh Sood

Tags: , , , , , ,

2 thoughts on “Connections to DataBase Hang Including “/ as sysdba””

  • Raj

    Good tip Sourabh!!! This would be handy.
    One more addition to it, you can trace the PID [pertaining to PL/SQL or whatever ]that is hanging the system by using any of the OS tracing tools like:
    i. systemtrap in linux
    ii.Dtrace in Solaris
    This would give you insight into the Oracle kernel code where it actually is during the hang.
    However, one should be cautious not to run OS tracing utilities which gets in the way of normal execution of the kernel code for the process that is being traced which might alter its state.
    For example, “pstack” in linux.
    —Raj

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.