Oradebug
From Utiliwiki
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.
Options
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 <ospid> Set OS pid of process to debug SETORAPID <orapid> ['force'] Set Oracle pid of process to debug DUMP <dump_name> <lvl> [addr] Invoke named dump DUMPSGA [bytes] Dump fixed SGA DUMPLIST Print a list of available dumps EVENT <text> Set trace event in process SESSION_EVENT <text> Set trace event in session DUMPVAR <p|s|uga> <name> [level] Print/dump a fixed PGA/SGA/UGA variable SETVAR <p|s|uga> <name> <value> Modify a fixed PGA/SGA/UGA variable PEEK <addr> <len> [level] Print/Dump memory POKE <addr> <len> <value> Modify memory WAKEUP <orapid> 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 <Inst-List | def | all> Parallel oradebug command prefix -R <Inst-List | def | all> Parallel oradebug prefix (return output SETINST <instance# .. | all> Set instance list in double quotes SGATOFILE <SGA dump dir> Dump SGA to file; dirname in double quotes DMPCOWSGA <SGA dump dir> Dump & map SGA as COW; dirname in double quotes MAPCOWSGA <SGA dump dir> 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 <ifname> <ofname> Helps translate PCs to names WATCH <address> <len> <self|exist|all|target> Watch a region of memory DELETE <local|global|target> watchpoint <id> Delete a watchpoint SHOW <local|global|target> watchpoints Show watchpoints CORE Dump core without crashing process UNLIMIT Unlimit the size of the trace file PROCSTAT Dump process statistics CALL <func> [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 (: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
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.
