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