My Oracle Support Banner

Oracle 9i and Below: Exhaustive Troubleshooting Steps for Oracle Database Hanging Issues (Doc ID 61552.1)

Last updated on AUGUST 04, 2018

Applies to:

Oracle Database - Personal Edition - Version 7.1.4.0 to 9.2.0.8 [Release 7.1.4 to 9.2]
Oracle Database - Standard Edition - Version 7.0.16.0 to 9.2.0.8 [Release 7.0 to 9.2]
Oracle Database - Enterprise Edition - Version 7.0.16.0 to 9.2.0.8 [Release 7.0 to 9.2]
Information in this document applies to any platform.

Purpose

This bulletin describes the methods and tools used in diagnosing database hanging issues and performance issue). It primarily refers to methods used in Oracle 9i and below.

For a more concise set of collectables see:

<Document 1594905.1> SRDC - How to Collect Standard Information for a Database Hang
<Document 452358.1> How to Collect Diagnostics for Database Hanging Issues


Generally a hang situation will need to be investigated by Oracle Support, but this article may provide some useful pointers.

Hang issues may be due to tuning problems, design problems or Oracle bugs such as latching issues.
We determine the cause by narrowing down the problem and getting as much information as possible WHILE THE DATABASE IS HANGING.

The following diagnostic steps are discussed.

1. Describe the Problems. Under all situations go through this sections and describe the problem.
2. Look for errors.
3. Do the requested queries.
4. Gather OS level data.
5. Get systemstate and hanganalyze dumps.
6. Get BSTAT-ESTAT OR STATPACK output.
7. Generate a core dump and run a debugger (contact support first).
8. If certain process get PROCESSSTATE dumps
9. Check List for diagnostics.

Note: It may not be necessary to shutdown the database to stop the hanging. But if you MUST shutdown before speaking to support, please attempt to get these diagnostics so that we can debug the problem.
Without these diagnostics it may be impossible for us to determine the cause of the problem.

Scope

Intended Audience: All Database Administrators

Note: This article was created in the Oracle 8i Timeframe and as such is generally applicable to versions up to and including Oracle 9.2 . Later versions may have more extensive or different diagnostics as outlined in:

<Note:452358.1> How to Collect Diagnostics for Database Hanging Issues

Details

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Purpose
Scope
Details
 1. Describe the problem
 2. Look for Errors
 3. Spool out relevant V$ VIEWS
 4. Gather OS-Level Info
 5. Get SYSTEMSTATE and HANGANALYZE Dumps
 6. Get BSTAT-ESTAT OR (for 8.1.6 or higher) STATSPACK output
 7. Generate a CORE DUMP And Run a DEBUGGER.
 8. If certain processes get PROCESSSTATE dump.
 9. Check List for diagnostics.
References

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.