Required Support Diagnostics for Hanging Databases [ID 452358.1]

Required Support Diagnostics for Hanging Databases [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"
If there are problems making this connection then in 10gR2 and above, the sqlplus "preliminary connection" can be used :
sqlplus -prelim " / as sysdba"


See:
 Note:986640.1 How To Connect Using A Sqlplus Preliminary Connection


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.


As an alternative to the manual collection method notes above, it is also possible to use the HANGFG script. as described in the following note to collect the information:


Note:362094.1  HANGFG User Guide


Additionally, this script. can collect information with lower impact on the target database.


On RAC Systems, hanganalyze, systemstates and some other RAC information can be collected using the 'racdiag.sql' script, see:


Note:135714.1 Script. to Collect RAC Diagnostic Information (racdiag.sql)


 
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 My Oracle Support.
Please List and describe the uploaded files below. If none, please indicate as such.


Of primary importance are AWR (or statspack) reports immediately before, during and after the hang. 
Support can use the snapshots to determine the nature of the load on the database in the build up to, during and after the problem which can provide vital diagnostic information and  may prove invaluable in resolving the issue. 


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 My Oracle Support:


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










References


NOTE:135714.1 - Script. to Collect RAC Diagnostic Information (racdiag.sql)
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
NOTE:362094.1 - HANGFG User Guide
NOTE:781198.1 - Required Support Diagnostics for Database Performance Issues
NOTE:94224.1 - FAQ- Statspack Complete Reference
NOTE:986640.1 - How To Connect Using A Sqlplus Preliminary Connection