Problem Diagnosis - Expert Oracle RAC Performance Diagnostics and Tuning - page 626

Step 8
After the patch has been accepted, the next step is to rerun the SQL statement. This time, it should not give the critical
error. An “explain plan” for this statement should show that the SQL patch was used.
Step 9
After the official patch from Oracle to fix an error is received, the patch that was applied using the previous steps
should be dropped using DBMS_SQLDIAG.DROP_SQL_PATCH with the patch name. The patch name can be obtained from
the explain plan section or by querying the view DBA_SQL_PATCHES .
ORADEBUG Utility
ORADEBUG is an undocumented but powerful utility and helps identify and troubleshoot a wealth of information
regarding the database, its components, and interfaces. This utility comes in handy to step deep into the internal
structures of Oracle and understand the actual problem. One of the great benefits of using this utility is to get a
jump start into investigating problems encountered with the performance or failure of instances in the cluster. The
information gathered using this is also useful when calling Oracle Support. The information puts the problem closer
to resolution. The ORADEBUG utility is invoked from an SQL*Plus session by simply connecting to it and executing
the specific ORADEBUG command. For example, the following code is to get help on the various commands available
through ORADEBUG :
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
SETORAPNAME <orapname> Set Oracle process name to debug
SHORT_STACK Get abridged OS stack
CURRENT_SQL Get current SQL
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
DUMPTYPE <address> <type> <count> Print/dump an address with type info
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