How to generate Hang Analyze Report

 Sometimes database hangs due to many reasons and higher managements look for for RCA. It is important to have all trace file to find the issue for hanging the database or fine the RCA.

Get a hang analyze report by running below commands 

SQL> sqlplus / as sysdba 
SQL> oradebug setmypid 
SQL> oradebug unlimit
SQL> oradebug hanganalyze 3
# Wait at least 2-3 minutes to give time to identify process state changes.
SQL> oradebug hanganalyze 3
# Wait at least 2-3 minutes to give time to identify process state changes.
SQL> oradebug hanganalyze 3
SQL> oradebug tracefile_name   

SQL> select name, value from v$diag_info where name='Default Trace File'; 

In case database can't be connected  at all then please do  to the following commands 

connect / as sysdba 
oradebug setmypid 
oradebug unlimit
oradebug dump systemstate 266 -g; 
oradebug dump systemstate 266 -g; 
oradebug dump systemstate 266 -g; 

To generate HANGANALYZE for RAC:

SQL> oradebug setmypid 
SQL> oradebug setinst all
SQL> oradebug -g def hanganalyze 3
# Wait at least 2-3 minutes to give time to identify process state changes.
SQL> oradebug -g def hanganalyze 3
# Wait at least 2-1 minutes to give time to identify process state changes.
SQL> oradebug -g def hanganalyze 3

It is not recommended to run HANGANALYZE at levels higher than 3 because it may generate a huge number of trace files for complex systems. Please check with Oracle Support.

SQL> oradebug setmypid 
SQL> oradebug setinst all
SQL> oradebug -g def hanganalyze 2
# Wait at least 1-2 minutes to give time to identify process state changes.
SQL> oradebug -g def hanganalyze 2
# Wait at least 1-2 minutes to give time to identify process state changes.
SQL> oradebug -g def hanganalyze 2

For reference Oracle Support Documents:
  • Troubleshooting Database Hang Issues (Doc ID 1378583.1)
  • How to Collect Diagnostics for Database Hanging Issues (Doc ID 452358.1)
  • Troubleshooting Database Contention With V$Wait_Chains (Doc ID 1428210.1)
  • Event: Hanganalyze – Reference Note (Doc ID 130874.1)
  • Important Customer information about using Numeric Events (Doc ID 75713.1)


Comments

Popular posts from this blog

How to drop index and before dropping it how to get the DDL.

How to fix Oracle SQL Developer connection issue "Got minus one from a read call"

How to troubleshoot Long Running Concurrent Request in EBS 12.2

PRVG-11250 : The check "RPM Package Manager database" was not performed because

Verifying Daemon “Avahi-Daemon” Not Configured And Running …FAILED (PRVG-1360)

How to write to a CSV file using Oracle SQL*Plus

ORA-00257:archiver error, connect internal only until freed

CPU Patch Analysis

Few Important steps of Oracle Database Clone

Linux OL7/RHEL7: PRVE-0421 : No entry exists in /etc/fstab for mounting /dev/shm