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 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 o ption 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.