ORADEBUG is an undocumented debugging utility supplied with Oracle
For more general information see ORADEBUG introduction
ORADEBUG commands include:
The ORADEBUG HELP command lists the commands available within ORADEBUG
These vary by release and platform. Commands appearing in this help do not necessarily work for the release/platform on which the database is running
For example in Oracle 12.1.0.2 ORADEBUG HELP returns the following:
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
PDUMP [interval=<interval> Invoke named dump periodically
[ndumps=<count> <dump_name> <lvl> [addr]
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
SETTRACEFILEID <identifier name> Set tracefile identifier
LKDEBUG Invoke global enqueue service debugger
NSDBX Invoke CGS name-service debugger
-G <Inst-List | def | all | cluster > Nodes involved
-R <Inst-List | def | all | cluster > Nodes involved (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> <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
DIRECT_ACCESS <set/enable/disable command | select query> Fixed table access
IPC Dump ipc information
IPC_TRACE <module> <trace_flags> <trace_level>
Modify IPC trace flags
IPC_CHECKSUM <light/medium/full>
Enable/Disable IPC Checksumming
UNLIMIT Unlimit the size of the trace file
CALL [-t count] <func> [arg1]...[argn] Invoke function with arguments
CORE Dump core without crashing process
PROCSTAT Dump process statistics
Before using ORADEBUG commands, a process must be selected. Depending on the commands to be issued, this can either be the current process or another process
Once a process has been selected, this will be used as the ORADEBUG process until another process is selected
The SETMYPID command selects the current process as the ORADEBUG process
For example:
ORADEBUG SETMYPID
ORADEBUG SETMYPID can be used to select the current process to run systemwide commands such as dumps
Do not use ORADEBUG SETMYPID if you intend to use the ORADEBUG SUSPEND command
Before using ORADEBUG commands, a process must be selected. Depending on the commands to be issued, this can either be the current process or another process.
Once a process has been selected, this will be used as the ORADEBUG process until another process is selected.
The SETOSPID command selects the another process using the operating system PID as the ORADEBUG process
The syntax is:
ORADEBUG SETOSPID pid
where pid is the operating system process ID of the target process
For example:
ORADEBUG SETOSPID 34345
The operating system process ID is the PID on Unix systems and the thread number on Windows NT/2000 systems
On Unix the PID of interest may have been identified using a top or ps command.
Before using ORADEBUG commands, a process must be selected. Depending on the commands to be issued, this can either be the current process or another process.
Once a process has been selected, this will be used as the ORADEBUG process until another process is selected.
The SETORAPID command selects another process using the Oracle PID as the ORADEBUG process
The syntax is:
ORADEBUG SETORAPID pid
where pid is the Oracle process ID of the target process
For example:
ORADEBUG SETORAPID 9
The Oracle process id for a process can be found in V$PROCESS.PID
To obtain the Oracle process ID for a foreground process use:
SELECT pid FROM v$process WHERE addr = ( SELECT paddr FROM v$session WHERE sid = DBMS_SUPPORT.MYSID );
Alternatively, if the DBMS_SUPPORT package is not available use:
SELECT pid FROM v$process
WHERE addr =
(
SELECT paddr FROM v$session
WHERE sid =
(
SELECT sid FROM v$mystat WHERE ROWNUM = 1
)
);
To obtain the process ID for a background process e.g. SMON use:
SELECT pid FROM v$process WHERE addr = ( SELECT paddr FROM v$bgprocess WHERE name = 'SMON' );
To obtain the process ID for a dispatcher process e.g. D000 use:
SELECT pid FROM v$process WHERE addr = ( SELECT paddr FROM v$dispatcher WHERE name = 'D000' );
To obtain the process ID for a shared server process e.g. S000 use:
SELECT pid FROM v$process WHERE addr = ( SELECT paddr FROM v$shared_server WHERE name = 'S000' );
To obtain the process ID for a job queue process e.g. job 21 use:
SELECT pid FROM v$process
WHERE addr =
(
SELECT paddr FROM v$session
WHERE sid =
(
SELECT sid FROM dba_jobs_running WHERE job = 21
)
);
To obtain the process ID for a parallel execution slave e.g. P000 use:
SELECT pid FROM v$px_process WHERE server_name = 'P000';
Before using ORADEBUG commands, a process must be selected. Depending on the commands to be issued, this can either be the current process or another process.
Once a process has been selected, this will be used as the ORADEBUG process until another process is selected.
The SETORAPNAME command selects a background process using the Oracle name
The syntax is:
ORADEBUG SETORAPNAME <pname>
where pname is the Oracle process name of the background process.
For example to debug the PMON background process use:
ORADEBUG SETORAPNAME PMON
This command prints the currently executing statement (SQL or PL/SQL)
ORADEBUG CURRENT_SQL
This command prints the name of the current trace file e.g.:
ORADEBUG TRACEFILE_NAME
For example:
/export/home/admin/SS92003/udump/ss92003_ora_14917.trc
This command does not work on Windows 2000 (Oracle 9.2)
This command sets the TRACEFILE_IDENTIFIER parameter:
ORADEBUG SETTRACEFILEID <suffix>
For example to set the TRACEFILE_IDENTIFIER to TEST use:
ORADEBUG SETTRACEFILEID TEST
In Oracle 8.1.5 and below the maximum size of the trace file is restricted by default. This means that large dumps (LIBRARY_CACHE, BUFFERS) may fail.
To remove the limitation on the size of the trace file use:
ORADEBUG UNLIMIT
In Oracle 8.1.6 and above the maximum size of the trace file defaults to UNLIMITED
To flush the current contents of the trace buffer to the trace file use
ORADEBUG FLUSH
To close the current trace file use:
ORADEBUG CLOSE_TRACE
This command suspends the current process
First select a process using SETORAPID or SETOSPID
Do not use SETMYPID as the current ORADEBUG process will hang and cannot be resumed even from another ORADEBUG process
For example the command:
ORADEBUG SUSPEND
suspends the current process.
The command:
ORADEBUG RESUME
resumes the current process.
While the process is suspended ORADEBUG can be used to take dumps of the current process state e.g. global area, heap, subheaps etc.
This example demonstrates how to take a heap dump during a large (sorting) query
This example requires two sessions, session 1 logged on SYS AS SYSDBA and session 2 which executes the query.
In session 2 identify the PID using:
SELECT pid FROM v$process
WHERE addr IN
(
SELECT paddr FROM v$session
WHERE sid = dbms_support.mysid
);
In this example the PID was 12.
In session 1 set the Oracle PID using:
ORADEBUG SETORAPID 12
In session 2 start the query:
SELECT ... FROM t1 ORDER BY ....
In session 1 suspend session 2:
ORADEBUG SUSPEND
The query in session 2 will be suspended.
In session 1 run the heap dump:
ORADEBUG DUMP HEAPDUMP 1
The heapdump will show the memory structures allocated for the sort. At this point further dumps e.g. subheap dumps can be taken.
In session 1 resume session 2:
ORADEBUG RESUME
The query in session 2 will resume execution.
This command resumes the current process.
First select a process using SETORAPID or SETOSPID
Do not use SETMYPID as the current ORADEBUG process will hang and cannot be resumed even from another ORADEBUG process
For example the command:
ORADEBUG SUSPEND
suspends the current process.
The command:
ORADEBUG RESUME
resumes the current process.
While the process is suspended ORADEBUG can be used to take dumps of the current process state e.g. global area, heap, subheaps etc.
See SUSPEND for an example of use of the SUSPEND and RESUME commands.
To wake up a process use:
ORADEBUG WAKEUP pid
For example to wake up SMON, first obtain the PID using:
SELECT pid FROM v$process
WHERE addr =
(
SELECT paddr FROM v$bgprocess
WHERE name = 'SMON'
);
If the PID is 6 then send a wakeup call using:
ORADEBUG WAKEUP 6
To list the dumps available in ORADEBUG use the DUMPLIST command.
For example in Oracle 12.1.0.2, ORADEBUG DUMPLIST returns the following:
SQL> ORADEBUG DUMPLIST TRACE_BUFFER_ON TRACE_BUFFER_OFF LATCHES PROCESSSTATE SYSTEMSTATE INSTANTIATIONSTATE REFRESH_OS_STATS SESSION_STATS_FREELIST PDBSTATS SQLNET_SERVER_TRACE CONTEXTAREA HANGDIAG_HEADER HEAPDUMP HEAPDUMP_ADDR POKE_ADDRESS POKE_LENGTH POKE_VALUE POKE_VALUE0 GLOBAL_AREA REALFREEDUMP DUMP_SGA_METADATA FLUSH_JAVA_POOL POOL_SIMULATOR PGA_DETAIL_GET PGA_DETAIL_DUMP PGA_DETAIL_CANCEL PGA_SUMMARY MODIFIED_PARAMETERS CROSSIC EVENT_TSM_TEST ERRORSTACK CALLSTACK TEST_STACK_DUMP TEST_GET_CALLER RECORD_CALLSTACK EXCEPTION_DUMP BG_MESSAGES ENQUEUES KSTDUMPCURPROC KSTDUMPALLPROCS KSTDUMPALLPROCS_CLUSTER SIMULATE_EOV KSFQP_LIMIT KSKDUMPTRACE DBSCHEDULER LDAP_USER_DUMP LDAP_KERNEL_DUMP DUMP_ALL_OBJSTATS DUMPGLOBALDATA HANGANALYZE HANGANALYZE_PROC HNGDET_MEM_USAGE_DUMP DEAD_CLEANUP_STATE HANGANALYZE_GLOBAL CBDB_ENTRIES GES_STATE RACDUMP CGS OCR CSS CRS SYSTEMSTATE_GLOBAL GIPC MMAN_ALLOC_MEMORY MMAN_CREATE_DEF_REQUEST MMAN_CREATE_IMM_REQUEST MMAN_IMM_REQUEST DUMP_ALL_COMP_GRANULE_ADDRS DUMP_ALL_COMP_GRANULES DUMP_ALL_REQS DUMP_TRANSFER_OPS DUMP_ADV_SNAPSHOTS ADJUST_SCN NEXT_SCN_WRAP CONTROLF FLUSH_CACHE FULL_DUMPS BUFFERS RECOVERY SET_TSN_P1 GLOBAL_BUFFER_DUMP BUFFER PIN_BLOCKS BC_SANITY_CHECK PIN_RANDOM_BLOCKS SET_NBLOCKS CHECK_ROREUSE_SANITY DUMP_PINNED_BUFFER_HISTORY KCBO_OBJ_CHECK_DUMP KCB_WORKING_SET_DUMP KCBS_ADV_INT_DUMP KCBI_DUMP_FREELIST SET_AFN SET_ISTEMPFILE FLUSH_BUFFER FLUSH_OBJECT SCN_AUTO_ROLLOVER_TS_OVERRIDE REDOLOGS ARCHIVE_ERROR LOGHIST REDOHDR LOGERROR OPEN_FILES DATA_ERR_ON DATA_READ_ERR_ON DATA_ERR_OFF BLK0_FMTCHG UPDATE_BLOCK0_FORMAT 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_CORRUPT_REPEAT KRB_CORRUPT_OFFSET KRB_PIECE_FAIL KRB_OPTIONS KRB_FAIL_INPUT_FILENO KRB_SIMULATE_NODE_AFFINITY KRB_BSET_DAYS KRB_SET_TIME_SWITCH KRB_OVERWRITE_ACTION KRB_CORRUPT_SPHEADER_INTERVAL KRB_CORRUPT_SPHEADER_REPEAT KRB_CORRUPT_SPBITMAP_INTERVAL KRB_CORRUPT_SPBITMAP_REPEAT KRB_CORRUPT_SPBAD_INTERVAL KRB_CORRUPT_SPBAD_SIGNAL KRB_UNUSED_OPTION KRBMRSR_LIMIT KRBMROR_LIMIT KBRS_TRACE KRDRSBF KRA_OPTIONS FBTAIL FBINC FBHDR FLASHBACK_GEN KTPR_DEBUG IMDB_PINNED_BUFFER_HISTORY DUMP_TEMP DROP_SEGMENTS TEST_SPACEBG TREEDUMP LONGF_CREATE KDLIDMP ROW_CACHE LIBRARY_CACHE LIBRARY_CACHE_OBJECT CURSORDUMP CURSORTRACE CURSOR_STATS XS_SESSION_STATE SHARED_SERVER_STATE LISTENER_REGISTRATION JAVAINFO KXFPCLEARSTATS KXFPDUMPTRACE KXFPBLATCHTEST KXFXSLAVESTATE KXFXCURSORSTATE KXFRHASHMAP WORKAREATAB_DUMP KUPPLATCHTEST OBJECT_CACHE SAVEPOINTS RULESETDUMP RULESETDUMP_ADDR FAILOVER OLAP_DUMP ASMDISK_ERR_ON ASMDISK_READ_ERR_ON ASMDISK_ERR_OFF IOERREMUL IOERREMULRNG SELFTESTASM ALRT_TEST AWR_TEST AWR_FLUSH_TABLE_ON AWR_FLUSH_TABLE_OFF ASHDUMP ASHDUMPSECONDS MMON_TEST ATSK_TEST HM_FW_TRACE HM_FDG_VERS IR_FW_TRACE KSDTRADV_TEST GWM_TRACE GWM_TEST
To perform a dump use:
ORADEBUG DUMP <dumpname> <level> [address]
The address parameter is optional.
For example for a level 4 dump of the library cache use:
ORADEBUG SETMYPID ORADEBUG DUMP LIBRARY_CACHE 4
To set an event in a process use:
ORADEBUG EVENT event TRACE NAME CONTEXT FOREVER, LEVEL level
For example to set event 10046, level 12 in Oracle process 8 use:
ORADEBUG SETORAPID 8 ORADEBUG EVENT 10046 TRACE NAME CONTEXT FOREVER, LEVEL 12
To perform a dump periodically use:
ORADEBUG PDUMP [interval=<interval>] [ndumps=<count> <dumpname> <level> [address]
The interval, ndumps and address parameters are optional.
To set an event in a session use:
ORADEBUG SESSION_EVENT event TRACE NAME CONTEXT FOREVER, LEVEL level
For example:
ORADEBUG SESSION_EVENT 10046 TRACE NAME CONTEXT FOREVER, LEVEL 12
To dump the fixed SGA use:
ORADEBUG DUMPSGA
To dump an SGA variable use:
ORADEBUG DUMPVAR SGA variable_name
For example:
ORADEBUG DUMPVAR SGA kcbnhb
which returns the number of hash buckets in the buffer cache.
The names of SGA variables can be found in X$KSMFSV.KSMFSNAM. Variables in this view are suffixed with an underscore. For example:
kcbnhb_
To peek memory locations use:
ORADEBUG PEEK address length
where address can be decimal or hexadecimal and length is in bytes.
For example:
ORADEBUG PEEK 0x20005F0C 12
returns 12 bytes starting at location 0x20005f0c.
To poke memory locations use:
ORADEBUG POKE address length value
where address and value can be decimal or hexadecimal and length is in bytes.
For Example:
ORADEBUG POKE 0x20005F0C 4 0x46495845 ORADEBUG POKE 0x20005F10 4 0x44205349 ORADEBUG POKE 0x20005F14 2 0x5A45
WARNING Do not use the POKE command on a production system
To dump information about operating system shared memory and semaphores configuration use the command:
ORADEBUG IPC
This command does not work on Windows NT or Windows 2000 (Oracle 9.2).
On Solaris, similar information can be obtained using the operating system command:
ipcs -b
To dump operating system process statistics use ORADEBUG PROCSTAT.
For example to dump operating system process statistics for the PMON background process use:
ORADEBUG SETORAPNAME PMON ORADEBUG PROCSTAT
Sample output:
----- Dump of Process Statistics ----- User time used = 65 System time used = 30 Maximum resident set size = 28452 Integral shared text size = 0 Integral unshared data size = 0 Integral unshared stack size = 0 Page reclaims = 7480 Page faults = 7 Swaps = 0 Block input operations = 1472 Block output operations = 32 Socket messages sent = 0 Socket messages received = 0 Signals received = 0 Voluntary context switches = 2555 Involuntary context switches = 387
In some versions it is possible to dump the entire SGA to a file.
Freeze the instance using:
ORADEBUG FFBEGIN
Dump the SGA to a file using:
ORADEBUG SGATOFILE directory
Unfreeze the instance using:
ORADEBUG FFRESUMEINST
The above commands work in Oracle 9.0.1 and 9.2.0 on Solaris, but fail in both versions in Windows 2000.