Donald K. Burleson
Oracle Utilities Tips
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.
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
The last tracing utility to be explained in this chapter is tightly coupled
with the OEM product – Oracle Trace.
For more details on Oracle
utilities, see the book "Advanced
Oracle Utilities" by Bert Scalzo, Donald
K. Burleson, and Steve Callan.
You can buy it direct from the
publisher for 30% off directly from
Download your Oracle scripts now:
definitive Oracle Script collection for every Oracle professional DBA