玉面飞龙的BLOG

This creates a large trace file in the user_dump_dest (30M or more is not unusual).

Note: the init.ora parameter MAX_DUMP_FILE_SIZE controls the maximum trace file size. Using Oradebug and setting unlimit will allow a complete dump which we will need.

Do this step for sure if the entire database is frozen or nearly frozen and if this condition came on suddenly and there are no archive errors in the alert log. Please note: As systemstate dumps are instance specific, they tend to be inconclusive with hanging problems involving Oracle Parallel Server (OPS) unless you get them from each node. You will need 3 system state dumps from each node for OPS.

NOTE: Do systemstate dump 3 times in a row, so we can determine whether
the processes are hung or active.

For Oracle 8.0.5.x to 8.1.7.x
[coolcode lang=”sql” linenum=”no”]
$ svrmgrl
svrmgr> connect internal

svrmgr>ALTER SESSION SET EVENTS ‘IMMEDIATE TRACE NAME HANGANALYZE LEVEL 3′;
wait 90 seconds
svrmgr>ALTER SESSION SET EVENTS ‘IMMEDIATE TRACE NAME HANGANALYZE LEVEL 3′;

EXIT … then reconnect

svrmgr>ALTER SESSION SET MAX_DUMP_FILE_SIZE=UNLIMITED;
svrmgr>ALTER SESSION SET EVENTS ‘IMMEDIATE TRACE NAME SYSTEMSTATE LEVEL 10′;
wait 90 seconds
svrmgr>ALTER SESSION SET EVENTS ‘IMMEDIATE TRACE NAME SYSTEMSTATE LEVEL 10′;
wait 90 seconds
svrmgr>ALTER SESSION SET EVENTS ‘IMMEDIATE TRACE NAME SYSTEMSTATE LEVEL 10′;

[/coolcode]

For Oracle 9.2.0.1 or higher
[coolcode lang=”sql” linenum=”no”]
$ sqlplus /nolog
connect / as sysdba
oradebug setmypid
oradebug unlimit
oradebug hanganalyze 3
wait 90 seconds
oradebug hanganalyze 3
oradebug dump systemstate 10
wait 90 seconds
oradebug dump systemstate 10
wait 90 seconds
oradebug dump systemstate 10
[/coolcode]

Update for 10g:

In some cases, no connections are allowed on the instance (in some ORA-20 situations for example).

As of 10.1.x, there is a new option with SQL*Plus to allow access to an instance to
generate traces.

sqlplus -prelim / as sysdba

For example,
[coolcode lang=”sql” linenum=”no”]
export ORACLE_SID=PROD
sqlplus -prelim / as sysdba
oradebug setmypid
oradebug unlimit;
oradebug dump systemstate 10
[/coolcode]

Usually level 3 is enough for HANGANALYZE, higher level will cause system performance degration. And trace file of HANGANALYZE is quite self-explaination and understandable.

And for SYSTEMSTATE dump, it’s usually used for diagnose by Oracle support with a TAR.