Oracle System State Dump

Using Oracle System State Dump. When the database encounters a serious performance problem or hang, sqlplus cannot connect to the server. In this case, if you want to obtain the current status of the database for later diagnosis, then we need to use systemstate dump to know what the process is doing, what it is waiting for, who is the resource owner, and who is blocking others. In case of the above problems, it is very helpful to analyze the cause of the problem by collecting systemstate dump in time. 

There are two ways to connect to sqlplus using a preliminary connection.

sqlplus -prelim / as sysdba
sqlplus /nolog
set _prelim on
connect / as sysdba

To generate it:

oradebug setmypid;
oradebug unlimit;
oradebug dump systemstate 266
oradebug tracefile_name

For all cluster:

oradebug setmypid;
oradebug unlimit;
oradebug -g all dump systemstate 266
oradebug tracefile_name

An example of execution:

SQL> oradebug setmypid;
Statement processed.
SQL> oradebug unlimit;
Statement processed.
SQL> oradebug dump systemstate 266
Statement processed.
SQL> oradebug tracefile_name
/db/oracle/diag/rdbms/grepora/GREPORA/trace/GREPORA_ora_18256.trc

Also See:

ORAchk