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

Last updated on JUNE 13, 2023

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]
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Information in this document applies to any platform.

Purpose

NOTE: In the images and/or the document content below, the user information and data used represents fictitious data from the Oracle sample schema(s) or Public Documentation delivered with an Oracle database product. Any similarity to actual persons, living or dead, is purely coincidental and not intended in any manner.

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