Please see my other blog for Oracle EBusiness Suite Posts - EBMentors

Search This Blog

Note: All the posts are based on practical approach avoiding lengthy theory. All have been tested on some development servers. Please don’t test any post on production servers until you are sure.

Tuesday, September 27, 2011

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. 

No comments: