HOWTO (Oracle DB) - Oradebug to trace session, hanganalyze and statedump

ORADEBUG Session Trace

The following steps are required to trace a user session with oradebug:

Obtain the SPID from v$process

SQL> select username, spid from v$process;

Start the debug session with the SPID of the process that needs traced

SQL> oradebug setospid 2280

SQL> oradebug unlimit

·         Select the appropriate trace level. There are four different options when specifying a tracing level.

·         Level 1 – provides “base set” tracing information. Bind variables are displayed as variables (:b1).

·         Level 4 – provides Level 1 data and the actual data values of bind variables.

·         Level 8 – provides Level 1 data and information on wait events when the elapsed time is greater than the CPU time.

·         Level 12 – combines levels 1, 4 and 8 tracing information. A Level 12 trace contains base set, bind variable values and wait events.

The oradebug command below will enable the maximum tracing possible:

SQL> oradebug event 10046 trace name context forever, level 12

Turn tracing off

SQL> oradebug event 10046 trace name context off

Obtain the trace file name. The oradebug facility provides an easy way to obtain the file name

SQL> oradebug tracefile_name

c:\oracle9i\admin\ORCL92\udump\mooracle_ora_2280.trc

Format the trace file with tkprof (as described in the earlier section on tkprof)

The result will be a trace file that contains more trace file information. Viewing wait events and bind variable values can be critical to diagnosing performance issues.

System state dump

In RAC system this need to be executed on all the instance

# Logon to sqlplus as sysdba

SQL> oradebug setmypid

SQL> oradebug unlimit

SQL> oradebug dump systemstate 10

... Wait at least 1 min

SQL> oradebug dump systemstate 10

... Wait at lease 1 min

SQL> oradebug dump systemstate 10

Hanganalyze dump

Syntax hangaanalyze for single instance

# Logon to sqlplus as sysdba

SQL> oradebug setmypid;

SQL> oradebug unlimit;

SQL> oradebug hanganalyze 3;

Syntax for hangaanalyze for RAC

# Logon to sqlplus as sysdba

SQL> oradebug setmypid;

SQL> oradebug unlimit;

SQL> oradebug setinst all

SQL> oradebug -g def hanganalyze 3

Connecting to SGA in case sqlplus does not work

$ sqlplus -prelim '/as sysdba

SQL> oradbug ...