Appendix C: Oracle Dumps and Traces

All of us have encountered ORA-0600 or ORA-7445 errors in our databases, and we all know how difficult it is to identify the root cause of such errors. Almost all the time you must contact Oracle Support to determine the root cause of these errors. Invariably, Oracle Support asks for additional trace files or various data or memory dumps, depending on the situation.

Dumping and tracing are integral parts of any advanced debugging processes. As a single piece of software, Oracle RDBMS is one of the most complex software packages, and debugging complex problems requires such traces and dumps so you can understand exactly what went wrong.

This appendix introduces you to the utilities and procedures used in producing such trace files and dumps. However, detailed analysis of the generated trace files is beyond the scope of this book.

Oradebug : The Ultimate Utility for Traces and Dumps

Oradebug is one of the most widely used utilities to set the debug events and produce dumps. Unfortunately, very little documentation is readily available on how to use it correctly. However, it does have a friendly help facility that you can use. Oradebug requires that you connect as a SYS user to use its commands. It has an extensive list of commands that you can use to simply trace a process, or it can be used to see the contents of the memory structures Oracle uses. Some of these commands can also change the contents. For this reason, extreme care must be taken when using oradebug commands.

Note ‚  

Incorrect use of oradebug commands can result in data corruption, rendering your database unsupported by Oracle.

The following is an output of the oradebug help command from Oracle Database 10 g Release 1. Most of these are self-explanatory.

 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        <psuga> <name> [level]  Print/dump a fixed PGA/SGA/UGA variable 
 SETVAR         <psuga> <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] [syslevel]        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> <selfexistalltarget>  Watch a region of memory 
 DELETE         <localglobaltarget> watchpoint <id>    Delete a watchpoint 
 SHOW           <localglobaltarget> watchpoints        Show  watchpoints 
 CORE                                     Dump core without crashing process 
 IPC                                      Dump ipc information 
 UNLIMIT                                  Unlimit the size of the trace file 
 PROCSTAT                                 Dump process statistics 
 CALL           <func> [arg1] ... [argn]  Invoke function with arguments 
 SQL> 

The most commonly used commands are setmypid, setospid, setorapid, dump, event, session_event, ipc, wakeup, and tracefile_name.

The dumplist command will list all available dumps, which will list contents of various internal Oracle structures. Each of these named dumps can be initiated with the use of dump command. The following example lists a few of the named dumps available in Oracle Database 10 g Release 1. We will discuss a couple of named dumps and their application in the subsequent sections:

 SQL> oradebug dumplist 
 EVENTS 
 TRACE_BUFFER_ON 
 TRACE_BUFFER_OFF 
 HANGANALYZE 
 LATCHES 
 PROCESSSTATE 
 SYSTEMSTATE 
 INSTANTIATIONSTATE 
 REFRESH_OS_STATS 
 CROSSIC 
 CONTEXTAREA 
 HEAPDUMP 

In addition to the oradebug utility, the ALTER SYSTEM and ALTER SESSION commands can be used to produce dumps and trace files.