Oracle DBA: Hanganalyze and system state dump



Last updated on August 31st, 2016 at 01:11 pm

Many times Oracle database get hangs due to locks or latching issue and Nobody can login to the system. It becomes critical and  And Usually we do shutdown abort to immediately resolve the issue. But  it is good to take system state dump/hanganalyze  at 1 min interval to analyze the locking or latching  issue afterwards before doing shutdown abort so that we can find the fix for the problem and avoid that in future.Oracle Support also usually ask for these when the database hangs for some reason

Here are the steps to take system state dump

In RAC system this need to be executed on all the instance

Syntax for system state dump:
Logon to sqlplus as sysdba
SQL> oradebug setmypid
SQL> oradebug unlimit
SQL> oradebug dump systemstate 10
…….. Wait at least 1 min
SQL> oradebug dump systemstate 10
…….. Wait at lease 1 min
SQL> oradebug dump systemstate 10

Similarly Hanganalyze is a good utility to analysis.

Here are the steps

Syntax for hangaanalyze for single instance
Logon to sqlplus as sysdba
oradebug setmypid;
oradebug unlimit;
oradebug hanganalyze 3;

Syntax for hanganalyze for RAC
Logon to sqlplus as sysdba
oradebug setmypid;
oradebug unlimit;
oradebug setinst all
oradebug -g def hanganalyze 3

Sometimes it is not possible to even login with sqlplus “/ as sysdba ” at the time of hang, In that situation we can use below command to enter with 11g onwards

sqlplus -prelim / as sysdba


Leave a Reply