How to Capture Oracle Systemstate Dumps with ORADEBUG for Hang Troubleshooting

Background

A customer’s Oracle test database became completely unresponsive, preventing any login or query; the trace log kept filling up. After a forced restart the issue was resolved, but the author realized that collecting a systemstate dump beforehand would have provided valuable diagnostic data.

Preparation

Collect an AWR report before restarting.

Back up trace logs to another location before cleaning them.

When sqlplus cannot connect, use ORADEBUG.

Connecting with sqlplus -prelim

When the database is extremely slow or hung, the normal sqlplus / as sysdba login may fail. Using sqlplus -prelim / as sysdba allows a connection in a hung state, though only limited operations are possible; the most useful actions are running ORADEBUG commands or shutting down the database.

Non‑RAC Systemstate Dump Commands

For a single‑instance (non‑RAC) database, execute the following in sqlplus:

SQL> oradebug setmypid

SQL> oradebug unlimit;

SQL> oradebug dump systemstate 266;  -- wait 1‑2 minutes

SQL> oradebug tracefile_name;  -- shows the generated dump file name

Generating a Hang Analyze Dump

SQL> oradebug dump hanganalyze 3;  -- wait 1‑2 minutes

SQL> oradebug tracefile_name;

This produces a hanganalyze file that visualizes wait relationships between sessions.

RAC Considerations

In RAC environments, each instance creates its own systemstate dump in the diagnostic trace directory. Starting with Oracle 11.2.0.3, the command rac with fixes can be used because relevant bugs are fixed in that release. Be aware of Bug 11800959, which causes huge global cache dumps (levels ≥10) to hang or crash RAC instances in versions ≤11.2.0.2; level 258 is a safer alternative.

Systemstate Dump Level Meaning

2   – dump (excluding lock element)

10  – dump

11  – dump + global cache of RAC

256 – short stack (function call stack)

258 – 256 + 2  (short stack + dump without lock element)

266 – 256 + 10 (short stack + dump)

267 – 256 + 11 (short stack + dump + global cache of RAC)

Recommendations

For most situations, level 266 is recommended because it provides a short stack useful for analyzing what each process is doing. However, generating a short stack can be time‑consuming when many processes exist (e.g., 2000 processes may take over 30 minutes). In such cases, use level 10 or level 258. Avoid levels 11 and 267 unless you specifically need global cache information, as they produce large trace files.