玉面飞龙的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

For Oracle 9.2.0.1 or higher

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,

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.