Oracle Database 11g -- How to Generate Hanganalyze, Systemstate Dump, Errorstack Dump

Problem

You need to generate a hanganalyze, a systemstate dump or an errorstack dump

Solution

To generate a hanganalyze dump and systemstate dump manually use:

1) Connect to the database, if the connection hangs, use the preliminary connections:

$ sqlplus -prelim / as sysdba

Or

$ sqlplus /nolog
SQL> set _prelim on
SQL> connect / as sysdba

2) Generate a hanganalyze dump:

Notes:

• there is a known bug when oradebug unlimited does not take effect and the trace files get truncated, as a workaround set the max_dump_file_size parameter explicitly
• when preliminary connections used there is no sessions created and hence no process state objects available, in this case connect to an existing process like PMON or SMON for example
• for RAC database use "-g all" for oradebug
  

SQL> alter session set max_dump_file_size = unlimited;
SQL> oradebug { setmypid | setospid <SPID_of_PMON_or_SMON> }
SQL> oradebug unlimit
SQL> oradebug [-g all] hanganalyze 3
SQL> -- Wait one minute before getting the second hanganalyze
SQL> oradebug [-g all] hanganalyze 3
SQL> oradebug tracefile_name
SQL> exit

3) Generate a systemstate dump:

Consider the notes above as well

SQL> alter session set max_dump_file_size = unlimited;
SQL> oradebug { setmypid | setospid <SPID_of_PMON_or_SMON> }
SQL> oradebug unlimit
SQL> oradebug [-g all] dump systemstate { 267 | 266 | 258 }
SQL> oradebug [-g all] dump systemstate { 267 | 266 | 258 }
SQL> oradebug tracefile_name
SQL> exit

To automatically generate a systemstate dump upon and error:

Note: <error_code> is numerical part of ORA-xxxxx error code, for example for ORA-00053 the <error_code> will be just 54

SQL> ALTER SYSTEM SET events '<error_code> trace name systemstate level { 267 | 266 | 258 }';

To disable auto systemstate dump use:

SQL> ALTER SYSTEM SET events '<error_code> trace name context off';

To generate an errorstack dump for a session use:

SQL> alter session set max_dump_file_size = unlimited;
SQL> oradebug setospid <SPID>
SQL> oradebug unlimit
SQL> oradebug dump errorstack 3
SQL> oradebug dump errorstack 3
SQL> oradebug dump errorstack 3

To automatically generate an errorstack dump upon an error:

SQL> alter system set events '<error_code> trace name errorstack level 3';

To disable auto errorstack for an error:

SQL> alter system set events '<error_code> trace name errorstack off';

Note. <error_code> is the numerical part of the ORA-xxxxx code, for example for error ORA-12751, <error_code> will be 12751

Reference:

• How to Collect Systemstate Dumps When you Cannot Connect to Oracle (Doc ID 121779.1)
• How to Collect Diagnostics for Database Hanging Issues (Doc ID 452358.1)
• How to Collect Errorstacks for use in Diagnosing Performance Issues. (Doc ID 1364257.1)