Oracle 9i and Below: Exhaustive Troubleshooting Steps for Oracle Database Hanging Issues
(Doc ID 61552.1)
Last updated on JUNE 15, 2021
Oracle Database - Personal Edition - Version 126.96.36.199 to 188.8.131.52 [Release 7.1.4 to 9.2] Oracle Database - Standard Edition - Version 184.108.40.206 to 220.127.116.11 [Release 7.0 to 9.2] Oracle Database - Enterprise Edition - Version 18.104.22.168 to 22.214.171.124 [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.
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.
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
To view full details, sign in with your My Oracle Support account.
Don't have a My Oracle Support account? Click to get started!