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 sysdbaTo generate it:
oradebug setmypid;
oradebug unlimit;
oradebug dump systemstate 266
oradebug tracefile_nameFor all cluster:
oradebug setmypid;
oradebug unlimit;
oradebug -g all dump systemstate 266
oradebug tracefile_nameAn 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.trcAlso See: