Generate & Analyze System State Dump

Steps to Generate the System State Dump 

• System State Dump on particular Session
• Get the ospid of the session using the below sql,

               SELECT S.SID||'/'||S.SERIAL# "SID/SERIAL", S.USERNAME,S.OSUSER,P.SPID "OS PID"

          FROM V$SESSION S,V$PROCESS P

          WHERE

              S.PADDR=P.ADDR

              AND S.SID=<SID>;

$ sqlplus "/as sysdba"
oradebug setospid <process ID>
oradebug unlimit
oradebug dump systemstate <LEVEL>

-- Do repeat the last step for atleast 3 times  in an interval of 5-10 minutes.
exit

LEVEL:

1              Very basic process information only

2              process + session state objects

10            Most common level - includes state object trees for all processes

Level+256 Adding 256 to the level will try to dump short stack info for each process.

• System State Dump when not able to login to Database,

$ sqlplus -prelim "/as sysdba"

oradebug setmypid
oradebug unlimit
oradebug dump systemstate <LEVEL>

-- Do repeat the last step for atleast 3 times  in an interval of 5-10 minutes.

exit

The Trace Files will get generated in USER_DUMP_DEST .

For analyzing the Trace you can use the aas.awk in the below link,

http://dba.5341.com/msg/34515.html  ( http://www.speakeasy.net/~jwilton/ass.awk )