Debugging a hung database

sqlplus –prelim
connect / as sysdba # At this point you can use oradebug utilities to invistigate

1. SQL> oradebug hanganalyze 3 
2. Wait at least 2 minutes to give time to identify process state changes. 
3. SQL> oradebug hanganalyze 3 
4. Open a separate SQL session and immediately generate a system state dump. 
5. SQL> alter session set events 'immediate trace name SYSTEMSTATE level 10';

 

 

# sqlplus /nolog

SQL*Plus: Release 10.2.0.4.0 - Production on Wed Sep 26 04:32:18 2012

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

SQL>  set _prelim on

SQL>

conn / as sysdbaSQL>

Prelim connection established

SQL>  oradebug setmypid

Statement processed.

SQL>  oradebug unlimit

Statement processed.

SQL>  oradebug call ksmcpg

Function returned 10525E

SQL>  oradebug dump hanganalyze 3

Statement processed.

SQL> oradebug dump systemstate 266

Statement processed.

SQL>  oradebug dump hanganalyze 3

Statement processed.

SQL>  oradebug dump systemstate 266

Statement processed.

SQL>  oradebug tracefile_name /u1/tot1_ora_22439.trc

SQL> exit