How to connect hanged database and do hang analyze!

December 26, 2019  

sqlplus -prelim / as sysdba
(or)
sqlplus /nolog
set _prelim on
conn / as sysdba

Example:

$sqlplus -prelim /nolog

SQL> connect / as sysdba
Prelim connection established
SQL> oradebug setmypid
Statement processed.
SQL> oradebug hanganalyze 12
Hang Analysis in /u01/app/oracle/admin/orcl/udump/orcl_ora_5564.trc
SQL> oradebug dump systemstate 10
Statement processed.
SQL> oradebug tracefile_name
/u01/app/oracle/admin/orcl/udump/orcl_ora_5598.trc

In a RAC environment:

SQL> oradebug setinst all
SQL> oradebug -g def hanganalyze 12

SQL> oradebug setmypid
SQL> oradebug unlimit
SQL> oradebug -g all dump systemstate 266
We can generate the hanganalyze and systemstate dumps in a normal SQL*Plus session too:

For the HangAnalyze:

SQL>alter session set events 'immediate trace name hanganalyze level 12';

To get the SystemState:

SQL> alter session set events 'immediate trace name SYSTEMSTATE level 10';