Oradebug

The oradebug utility falls into the “hidden” classification of utilities due to the lack of available documentation. The utility is invoked directly from SQL*Plus beginning in version 8.1.5 and Server Manager in releases prior to that. The utility can trace a user session as well as perform many other, more global, database tracing functions.

oradebug requires the SYSDBA privilege to execute (connect internal on older Oracle versions). The list of oradebug options can be viewed by typing oradebug help at the SQL*Plus prompt:

SQL> oradebug help
HELP [command] Describe one or all commands
SETMYPID Debug current process
SETOSPID Set OS pid of process to debug
SETORAPID ['force'] Set Oracle pid of process to debug
DUMP [addr] Invoke named dump
DUMPSGA [bytes] Dump fixed SGA
DUMPLIST Print a list of available dumps
EVENT Set trace event in process
SESSION_EVENT Set trace event in session
DUMPVAR [level] Print/dump a fixed PGA/SGA/UGA variable
SETVAR Modify a fixed PGA/SGA/UGA variable
PEEK [level] Print/Dump memory
POKE Modify memory
WAKEUP Wake up Oracle process
SUSPEND Suspend execution
RESUME Resume execution
FLUSH Flush pending writes to trace file
CLOSE_TRACE Close trace file
TRACEFILE_NAME Get name of trace file
LKDEBUG Invoke global enqueue service debugger
NSDBX Invoke CGS name-service debugger
-G Parallel oradebug command prefix
-R Parallel oradebug prefix (return output
SETINST Set instance list in double quotes
SGATOFILE Dump SGA to file; dirname in double quotes
DMPCOWSGA Dump & map SGA as COW; dirname in double quotes
MAPCOWSGA Map SGA as COW; dirname in double quotes
HANGanalyze [level] Analyze system hang
FFBEGIN Flash Freeze the Instance
FFDEREGISTER FF deregister instance from cluster
FFTERMINST Call exit and terminate instance
FFRESUMEINST Resume the flash frozen instance
FFSTATUS Flash freeze status of instance
SKDSTTPCS Helps translate PCs to names
WATCH

Watch a region of memory
DELETE watchpoint Delete a watchpoint
SHOW watchpoints Show watchpoints
CORE Dump core without crashing process
UNLIMIT Unlimit the size of the trace file
PROCSTAT Dump process statistics
CALL [arg1] ... [argn] Invoke function with arguments


Identical to tkprof, oradebug depends on trace files to store its output. These files are in the same location as specified by the user_dump_dest initialization parameter. The trace files are named according to the SPID of the process where the oradebug command is executed – the same naming scheme as described earlier.

Some of the oradebug options apply to a particular session and therefore require a system process id (SPID) as obtained from v$process. Other options are more global in nature and can be executed without attaching to any session.


ORADEBUG Session Trace


The oradebug utility provides the ability to trace a particular user session. In addition, oradebug provides additional details that are not available through other tracing mechanisms.

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

1. Obtain the SPID from v$process.
SQL> select username, spid from v$process;

2. Start the debug session with the SPID of the process that needs traced.
SQL> oradebug setospid 2280

3. 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

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

1.Turn tracing off.
SQL> oradebug event 10046 trace name context off



2.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



3.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.


ORADEBUG Dumps


The oradebug utility provides many options for dumping database information to trace files. In order to know what data can be dumped, the dumplist command will return the available options.

• EVENTS
• TRACE_BUFFER_ON
• TRACE_BUFFER_OFF
• HANGanalyze
• LATCHES
• PROCESSSTATE
• SYSTEMSTATE
• INSTANTIATIONSTATE
• REFRESH_OS_STATS
• CROSSIC
• CONTEXTAREA
• HEAPDUMP
• HEAPDUMP_ADDR
• POKE_ADDRESS
• POKE_LENGTH
• POKE_VALUE
• POKE_VALUE0
• GLOBAL_AREA
• MEMORY_LOG
• REALFREEDUMP
• ERRORSTACK
• HANGANALYZE_PROC
• TEST_STACK_DUMP
• BG_MESSAGES
• ENQUEUES
• SIMULATE_EOV
• KSFQP_LIMIT
• KSKDUMPTRACE
• DBSCHEDULER
• GRANULELIST
• GRANULELISTCHK
• SCOREBOARD
• GES_STATE
• ADJUST_SCN
• NEXT_SCN_WRAP
• CONTROLF
• FULL_DUMPS
• BUFFERS
• RECOVERY
• SET_TSN_P1
• BUFFER
• PIN_BLOCKS
• BC_SANITY_CHECK
• FLUSH_CACHE
• LOGHIST
• ARCHIVE_ERROR
• REDOHDR
• LOGERROR
• OPEN_FILES
• DATA_ERR_ON
• DATA_ERR_OFF
• BLK0_FMTCHG
• TR_SET_BLOCK
• TR_SET_ALL_BLOCKS
• TR_SET_SIDE
• TR_CRASH_AFTER_WRITE
• TR_READ_ONE_SIDE
• TR_CORRUPT_ONE_SIDE
• TR_RESET_NORMAL
• TEST_DB_ROBUSTNESS
• LOCKS
• GC_ELEMENTS
• FILE_HDRS
• KRB_CORRUPT_INTERVAL
• KRB_CORRUPT_SIZE
• KRB_PIECE_FAIL
• KRB_OPTIONS
• KRB_SIMULATE_NODE_AFFINITY
• KRB_TRACE
• KRB_BSET_DAYS
• DROP_SEGMENTS
• TREEDUMP
• LONGF_CREATE
• ROW_CACHE
• LIBRARY_CACHE
• SHARED_SERVER_STATE
• KXFPCLEARSTATS
• KXFPDUMPTRACE
• KXFPBLATCHTEST
• KXFXSLAVESTATE
• KXFXCURSORSTATE
• WORKAREATAB_DUMP
• OBJECT_CACHE
• SAVEPOINTS
• OLAP_DUMP

One scenario in which a dump may aid in diagnosing the problem is when dealing with a system hang. If no errors exist in the alert log and the database appears to be hung, connect as SYSDBA and execute the following:

SQL> oradebug setmypid
SQL> oradebug unlimit
SQL> oradebug dump systemstate 10

This creates a very large trace file from the system dump. For this reason, the oradebug unlimit option should be used to override the maximum trace file size as specified in init.ora.

oradebug also has the capability to only produce trace output if a particular error is encountered. This command is especially useful when certain errors are encountered inconsistently. Rather than generate enormous trace files, the trace data will only be generated when the particular error occurs. The following command will monitor a particular session and only generate trace data when the ORA-00942 error is hit:

SQL> oradebug event 942 trace name errorstack level 3

oradebug can also be used to suspend and resume any user database connection. When suspended, the user session will simply hang until the resume command is processed. During the suspension period, v$session_wait will indicate that the session is waiting on the debugger.

Only a few of the oradebug options were discussed. oradebug is a powerful utility to be used only by expert DBAs when diagnosing serious database issues. oradebug can be used in tandem with tkprof to get more detailed information in trace files. In addition, the tool can be used to generate system or process state dumps which can be vital when diagnosing system hang scenarios.