How to Collect Diagnostics for Database Hanging Issues

If you are encountering a database hanging situation, you need to take system
state dumps/hanganalyze so that Oracle Support can begin to diagnose the cause of the problem.
Whenever you take such dumps for a hang it is important to take at least 3 of
them a few minutes apart, on all instances of your database.  That way there is
evidence showing whether a resource is still being held from one time to the
next.

2 Hanganalyze and 2 Systemstate dumps with at least 1 minute difference.

Hanganalyze will confirm if the db is really hung or just slow.
Systemstate dump shows what each process on the database is doing.
Open two command prompts to run the statements.

Note: Beware of taking systemstates on very large systems with large numbers of process.
           Systemstates can be very slow and the trace file can be very large.

D:\oracle\product\10.2.0\admin\HOMEDEV\udump>sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Tue Sep 27 12:25:35 2011

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


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> oradebug setmypid
Statement processed.
SQL> oradebug unlimit;
Statement processed.
SQL> oradebug hanganalyze 3
Hang Analysis in d:\oracle\product\10.2.0\admin\homedev\udump\homedev_ora_40032.trc

 C:\Documents and Settings\inam>sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Tue Sep 27 12:26:06 2011

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


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> oradebug setmypid
Statement processed.
SQL>    oradebug unlimit;
Statement processed.SQL> oradebug dump systemstate 266
Statement processed.

If you are using systemstate level 266 and it is taking much longer than expected to generate the dump file, then end this systemstate dump and try level 258.