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 )

ORA-28575: unable to open RPC connection to external procedure agent

One of the common Error with EXTPROC connection is ORA-28575 .
The Below article will suit for the server having 9i/10g or 9i/10g/11g installed .
In this case you will be having extproc for each Version running in different port and listener.


LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (PROTOCOL_STACK = (PRESENTATION = TTC)(SESSION = NS))
      (ADDRESS = (PROTOCOL = TCP)(HOST = <<HOST>>)(PORT = 1524))
      (ADDRESS = (PROTOCOL = IPC)(KEY = extproc_key_1))
    )
  )
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (PROGRAM = extproc)
      (SID_NAME = extproc1)
      (ORACLE_HOME = <<ORACLE9i_HOME>>)
      (ENVS='EXTPROC_DLLS=ANY')
    ) )
LISTENER_10g =

  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (PROTOCOL_STACK = (PRESENTATION = TTC)(SESSION = NS))
      (ADDRESS = (PROTOCOL = TCP) (HOST = <<HOST>>) (PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC) (KEY = extproc_key_2))
    )
  )
SID_LIST_LISTENER_10g =
  (SID_LIST =
    (SID_DESC =
      (PROGRAM = extproc)
      (SID_NAME = extproc2)
      (ORACLE_HOME = <<ORACLE_10g_HOME>>)
      (ENVS='EXTPROC_DLLS=ANY')
    )
  )

And while connecting to the extproc you will get the bleow error,

ORA-28575: unable to open RPC connection to external procedure agent

One of the solution to this error is ,

• Stop the listener
• Set the TNS_ADMIN pointing to the correct EXTPROC
• Start the listener

Testing EXTPROC : Metalink 47484.1