How to generate Hang Analyze Report when Database Hanged?
Sometimes database hangs due to multiple reasons and company higher managements always looks for RCA. This is very important to have all trace file to find the issue for hanging the database or fine the RCA. Otherwise Oracle Support will say sorry!!!
Get a hang analyze report by running below commands when you notice that the database is hanged.
SQL> sqlplus / as sysdba
SQL> oradebug setmypid
SQL> oradebug unlimit
SQL> oradebug hanganalyze 3
# Wait at least 1 minutes to give time to identify process state changes.
SQL> oradebug hanganalyze 3
# Wait at least 1 minutes to give time to identify process state changes.
SQL> oradebug hanganalyze 3
SQL> oradebug tracefile_name
You can get the trace file location by executing below mentioned query :
SQL> select name, value from v$diag_info where name='Default Trace File'; If you cannot connect at all to database then please refer to the following commands to collect systemstates in that situation:
connect / as sysdba
oradebug setmypid
oradebug unlimit
oradebug dump systemstate 266 -g;
oradebug dump systemstate 266 -g;
oradebug dump systemstate 266 -g; For RAC: To generate HANGANALYZE cluster wide:
SQL> oradebug setmypid
SQL> oradebug setinst all
SQL> oradebug -g def hanganalyze 3
# Wait at least 1 minutes to give time to identify process state changes.
SQL> oradebug -g def hanganalyze 3
# Wait at least 1 minutes to give time to identify process state changes.
SQL> oradebug -g def hanganalyze 3Note: This is not recommended to run HANGANALYZE at levels higher than 3 because it may generate a huge number of trace files for large systems. If you need to run, please discuss with Oracle Support Engineer.
SQL> oradebug setmypid
SQL> oradebug setinst all
SQL> oradebug -g def hanganalyze 2
# Wait at least 1 minutes to give time to identify process state changes.
SQL> oradebug -g def hanganalyze 2
# Wait at least 1 minutes to give time to identify process state changes.
SQL> oradebug -g def hanganalyze 2
