DBA from Crisis to Confidence

When: Hang & deadlock situations.

数据挖掘论坛

Scope: Always perform cluster-wide ′hanganalyze′ if it is RAC database.

数据挖掘研究院

Procedure: Run at least two times with one to two minute intervals between ′hanganalyze′ commands, waiting for couple of minutes to allow time to identify process state changes.

数据挖掘论坛

dbhost:/home/oracle > sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Fri Sep 23 15:14:44 2005

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options

SQL> oradebug setmypid
Statement processed.
SQL> oradebug unlimit
Statement processed.
SQL> oradebug -g all hanganalyze 3
Hang Analysis in /usr/app/oracle/admin/myrac/bdump/myrac1_diag_7056.trc
... wait a minute or two here...
SQL> oradebug -g all hanganalyze 3
Hang Analysis in /usr/app/oracle/admin/myrac/bdump/myrac1_diag_7056.trc
SQL> exit
 
数据挖掘实验室
 

Make sure the following pattern is present in the trace file – in my demo case, it is two identical entries in ′myrac1_diag_7056.trc′ file since I have done ′hanganalyze′ twice:

*** 2005-09-23 10:50:46.051
====================
END OF HANG ANALYSIS

END OF HANG ANALYSIS
 数据挖掘交友 

The trace file for the DIAG background process on the local node contains the ′hanganalyze′ results – upload the file to Metalink. 数据挖掘论坛

Another easy method of Hanganalyze : 数据挖掘论坛

If you cannot connect to database/ASM instances on any cluster node with SQL*Plus, then you may want to try the ′-prelim′ option as follows. Note the difference in the SQL*Plus banner – it is a ′lightweight′ session, no SQL is allowed in the connected session: 数据挖掘研究院

dbhost:/home/oracle > sqlplus -prelim / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Fri Sep 23 15:20:38 2005

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

SQL> oradebug setmypid
Statement processed.
SQL> oradebug unlimit
Statement processed.
SQL> oradebug -g all hanganalyze 3
Hang Analysis in /usr/app/oracle/admin/myrac/bdump/myrac1_diag_7056.trc
...wait a minute or two here...
SQL> oradebug -g all hanganalyze 3
Hang Analysis in /usr/app/oracle/admin/myrac/bdump/myrac1_diag_7056.trc
SQL> exit