Database Hangs: What to collect for support

 我们经常会遇到数据库hang的情况，很多工程师都无从下手，我把oracle的一篇文章给大家共享出来，大家如果遇到这种问题，可以按照这个文章的思路去解决。

Applies to:

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

Goal

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

Solution

If the Database Appears to 'hang' Pleaseprovide the following information:

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

A. 2 Hanganalyze and 2 Systemstatedumps.

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 ofprocess.
  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 byexecuting the following:

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

Gather a second hang analyze having waitedat least 1 minute to give time to identify process state changes. In SQL1execute the following:

     SQL1> oradebug hanganalyze 3In SQL2 execute the following to collect a second systemstate dump: 

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

If you are usingsystemstate level 266 and it is taking much longer than expected to generatethe 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 tothe following article which describes how to collect systemstates in thatsituation:


B. Provide snapshots of database performance

Use statspack, or AWR (if licensed to use it).
Take snapshots avery 15 minutes to an hour interval, depending on the time ofthe hang.  When the database hangs, the snapshots can be used to determinewhat load on the database in the build up to the problem.

C. Provide an up to date RDA.

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

See the following note on metalink: