Required Support Diagnostics for Hanging Databases

最新推荐文章于 2025-08-08 12:35:56 发布

aicon

最新推荐文章于 2025-08-08 12:35:56 发布

阅读量548

点赞数

分类专栏： Oracle EBS Oracle 文章标签： diagnostics database sql performance upload date

Oracle 同时被 2 个专栏收录

523 篇文章

订阅专栏

Oracle EBS

416 篇文章

订阅专栏

本文档提供了一套详细的步骤来收集Oracle数据库挂起时所需的信息，包括如何进行两次挂起分析、系统状态转储及数据库性能快照的获取方法，并指导如何使用SQL*Plus执行相关命令。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

Doc 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"

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.

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

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 metalink:

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

References

Note 121779.1 - Taking Systemstate Dumps when You cannot Connect to Oracle
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