oradebug

refer:

 

http://www.oracleutilities.com/SQLPLus/oradebug.html

 

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 requiresthe SYSDBA privilege to execute (connectinternal 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]                 Describeone or all commands

SETMYPID                                 Debug currentprocess

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]                   Dumpfixed SGA

DUMPLIST                                 Print a listof 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                                   Resumeexecution

FLUSH                                    Flushpending writes to trace file

CLOSE_TRACE                              Close trace file

TRACEFILE_NAME                           Get name of tracefile

LKDEBUG                                  Invoke globalenqueue service debugger

NSDBX                                    Invoke CGSname-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>         DumpSGA to file; dirname in double quotes

DMPCOWSGA     <SGA dump dir> Dump & map SGA as COW; dirname in double quotes

MAPCOWSGA     <SGA dump dir>         MapSGA as COW; dirname in double quotes

HANGanalyze    [level]                   Analyze system hang

FFBEGIN                                  Flash Freezethe Instance

FFDEREGISTER                             FF deregisterinstance from cluster

FFTERMINST                               Call exit andterminate instance

FFRESUMEINST                             Resume the flashfrozen instance

FFSTATUS                                 Flash freeze status of instance

SKDSTTPCS     <ifname> <ofname>        Helpstranslate 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 corewithout crashing process

UNLIMIT                                  Unlimit thesize of the trace file

PROCSTAT                                 Dump processstatistics

CALL          <func> [arg1] ... [argn] Invoke function with arguments

 

Identical to tkprof, oradebug dependson trace files to store its output. These files are in the same location as specified by the user_dump_dest initializationparameter.  The trace files are namedaccording to the SPID of the process where the oradebug command is executed – the same naming scheme as describedearlier.

 

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

ORADEBUG Session Trace  

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

 

The following steps are required to trace a user session withoradebug: 

 

1.  Obtain the SPID from v$process.  

SQL> select username, spidfrom 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 “baseset” tracing information.  Bind variablesare displayed as variables (:b1).

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

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

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

The oradebug command below will enablethe maximum tracing possible:

 

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

 

1.  Turn tracing off.

SQL> oradebug event 10046trace name context off

 

2.   Obtain the trace file name.  The oradebugfacilityprovides 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 thatcontains more trace file information. Viewing wait events and bind variable values can be critical todiagnosing performance issues.  

ORADEBUG Dumps

The oradebug utilityprovides many options for dumping database information to trace files.  In order to know what data can be dumped, thedumplist command will return theavailable 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 theproblem 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 systemdump.  For this reason, the oradebug unlimit optionshould be used to override the maximum trace file size as specified ininit.ora.

 

oradebug alsohas the capability to only produce trace output if a particular error isencountered.  This command is especiallyuseful 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 aparticular session and only generate trace data when the ORA-00942 error ishit:

 

SQL> oradebug event 942 trace name errorstack level 3

 

oradebug canalso be used to suspend and resume any user database connection.  When suspended, the user session will simplyhang until the resume command is processed. During the suspension period, v$session_waitwill indicate that thesession is waiting on the debugger.  

 

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