Required Support Diagnostics for Hanging Databases

Doc ID: 452358.1

 

In this Document
  Goal
  Solution
  References

---

 

 

Applies to:

Oracle Server - Enterprise Edition - Version: 9.0.1.0 to 11.1.0.6
Information in this document applies to any platform.

Goal

Provide information on what to collect for a situation where the database appears to hang.

Solution

If the Database Appears to 'hang' Please provide the following information:

A. 2 Hanganalyze and 2 Systemstate dumps.
B. Provide snapshots of database performance
C. Provide an up to date RDA.

A. 2 Hanganalyze and 2 Systemstate dumps.

Hanganalyze will confirm if the db is really hung or just slow.
Systemstate dump shows what each process on the database is doing.

* 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.

Using SQL*Plus connect as SYSDBA using the following command:

  sqlplus " / as sysdba"

Do this 2 times in 2 separate windows, creating 2 sqlplus sessions (SQL1 and SQL2)
In SQL1 gather the hanganalyze by executing the following:

     SQL1> oradebug setmypid  
     SQL1> oradebug unlimit;
     SQL1> oradebug hanganalyze 3

In SQL2 gather the systemstates by executing the following:

     SQL2> oradebug setmypid
     SQL2> oradebug unlimit;
     SQL2> oradebug dump systemstate 266

Gather a second hang analyze having waited at least 1 minute to give time to identify process state changes. In SQL1 execute the following:

     SQL1> oradebug hanganalyze 3

In SQL2 execute the following to collect a second systemstate dump:

     SQL2> oradebug setmypid
     SQL2> oradebug unlimit;
     SQL2> oradebug dump systemstate 266

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.

If connection to the system is not possible in any form, then please refer to the following article which describes how to collect systemstates in that situation:

Note 121779.1 Taking a SYSTEMSTATE dump when you cannot CONNECT to Oracle.

B. Provide snapshots of database performance

Please take and upload snapshots of database performance.
Please refer to the following article for details of what to collect:

Note 781198.1 Required Support Diagnostics for Database Performance Issues

Please collect the requested information and upload the files produced to metalink.
Please List and describe the uploaded files below. If none, please indicate as such:

C. Provide an up to date RDA.

An up to date current RDA provides a lot of additional information about the configuration of the database and performance metrics.

See the following note on metalink:

Note 314422.1 Remote Diagnostic Agent (RDA) 4 - Getting Started

 

References

Note 121779.1 - Taking Systemstate Dumps when You cannot Connect to Oracle
Note 175006.1 - Steps to generate HANGANALYZE trace files
Note 276103.1 - PERFORMANCE TUNING USING 10g ADVISORS AND MANAGEABILITY FEATURES
Note 314422.1 - Remote Diagnostic Agent (RDA) 4 - Getting Started