How to handle HANG Databases

How to handle HANG Databases

1. Generate systemstate dump:
SQL> sqlplus-prelim/as sysdba
SQL> alter session set max_dump_file_size = unlimited;
SQL> alter session set events 'immediate trace name systemstate level 10 ';
Wait for some 30 seconds
SQL> alter session set events 'immediate trace name systemstate level 10 ';
Wait for some 30 seconds
SQL> alter session set events 'immediate trace name systemstate level 10;

2. Start a new SESSION:
SQL> sqlplus-prelim/as sysdba
SQL> oradebug setmypid
SQL> oradebug unlimit;
SQL> oradebug dump hanganalyze 3
Wait for 30 seconds
SQL> oradebug dump hanganalyze 3
Wait for 30 seconds
SQL> oradebug dump hanganalyze 3

Analyze the generated file.

Appendix: system state dump when connection to the instance is not possible
There are several ways of getting more information when connection to an instance is not possible by sqlplus. It is useful to collect as much information about a hang before clearing the instance.

1) Using OS debuggers like dbx or gdb.
For example:
$ Ps-ef | grep $ Oracle_SID # Use PID of any User Process instead of BG Process
$ Gdb $ ORACLE_HOME/bin/oracle <PID_from_earlier_step>
Print ksudss (10)
The systemstate dump will be in the bdump or udump depending on the PID chosen
2.) Using the new 10.1 sqlplus-prelim option.
For example:
Export ORACLE_SID = PROD # Replace PROD with the SID you want to trace
Sqlplus-prelim/as sysdba
Oradebug setmypid
Oradebug unlimit;
Oradebug dump systemstate 10
3.) Finally if either option (1) or (2) is not doable, please remember to take stacks of all the Oracle Background process using pstack or gdb
For example:
Script stack. log
Pstack <pid_of_pmon>
Pstack <pid_of_smon>
4.) Finally you may also want to collect some truss, tusc or strace of sqlplus
Note: The recommended systemstate level for 10g is 266