GENERAL |
Background Processes List | SELECT b.name, p.pid FROM gv$bgprocess b, gv$process p WHERE b.paddr = p.addr ORDER BY 1; |
Dispatcher Processes List | SELECT d.name, p.pid FROM gv$dispatcher d, gv$process p WHERE d.paddr = p.addr; |
Job Queue Process List | SELECT s.paddr, s.sid, j.job FROM gv$session s, dba_jobs_running j WHERE s.sid = j.sid; SELECT pid FROM gv$process WHERE addr = '17'; |
Parallel Execution Slave Processes List | SELECT pid, server_name, status FROM gv$px_process; |
Shared Server Processes List | SELECT s.name, p.pid FROM gv$shared_server s, gv$process p WHERE s.paddr = p.addr; |
SGA Variables List | SELECT ksmfsnam FROM x$ksmfsv WHERE ksmfsnam LIKE '%\_' ESCAPE '\'; |
Switches |
Switch | Format | Description | -G | <Inst-List | def | all> | Parallel oradebug command prefix | -R | <Inst-List | def | all> | Parallel oradebug prefix return output | |
|
CALL |
Invoke function with arguments | oradebug call <func> [arg1] ... [argn] |
SQL> oradebug call ? |
|
CLOSE_TRACE |
Close trace file | oradebug close_trace |
SQL> oradebug close_trace |
|
CORE |
Dump core without crashing process | oradebug core |
SQL> oradebug core |
|
CURRENT_SQL |
Get current SQL | oradebug current_sql |
SYS | UWCLASS | conn / as sysdba | conn uwclass/uwclass | GRANT execute ON dbms_support TO uwclass; | | | SELECT pid FROM gv$process WHERE addr = ( SELECT paddr FROM gv$session WHERE sid = sys.dbms_support.mysid); | oradebug setorapid 26 | | | SELECT t.tablespace_name FROM all_tables t, all_indexes i WHERE t.tablespace_name = i.tablespace_name; | oradebug core | | |
|
DELETE |
Delete a watchpoint | oradebug delete <local|global|target> watchpoint <id> |
SQL> oradebug delete ? |
|
DIRECT_ACCESS |
Fixed table access | oradebug direct_access <set/enable/disable command | select query> |
TBD |
|
DMPCOWSGA |
Dump & map SGA as COW | oradebug dmpcowsga <SGA dump dir> |
SQL> oradebug dmpcowsga "c: emp" |
|
DUMP |
Invoke named dump | oradebug dump <dump_name> <lvl> [addr] |
-- dump_name values can be obtained from oradebug dumplist SQL> oradebug setmypid SQL> oradebug dump library_cache 4 |
Invoke ASH dump | SQL> oradebug setmypid SQL> oradebug dump ashdump 10 |
|
DUMPLIST |
Print a list of available dumps | oradebug dumplist |
SQL> oradebug dumplist
Available Dumps | ADJUST_SCN | HANGDIAG_HEADER | MMAN_ALLOC_MEMORY | ALRT_TEST | HEAPDUMP | MMAN_CREATE_DEF_REQUEST | ARCHIVE_ERROR | HEAPDUMP_ADDR | MMAN_CREATE_IMM_REQUEST | ASHDUMP | HM_FW_TRACE | MMAN_IMM_REQUEST | ATSK_TEST | INSTANTIATIONSTATE | MMON_TEST | AWR_FLUSH_TABLE_OFF | IOERREMUL | MODIFIED_PARAMETERS | AWR_FLUSH_TABLE_ON | IOERREMULRNG | NEXT_SCN_WRAP | AWR_TEST | IR_FW_TRACE | OBJECT_CACHE | BC_SANITY_CHECK | JAVAINFO | OCR | BG_MESSAGES | KCBO_OBJ_CHECK_DUMP | OLAP_DUMP | BLK0_FMTCHG | KDLIDMP | OPEN_FILES | BUFFER | KRA_OPTIONS | PGA_DETAIL_CANCEL | BUFFERS | KRA_TRACE | PGA_DETAIL_DUMP | CALLSTACK | KRB_BSET_DAYS | PGA_DETAIL_GET | CHECK_ROREUSE_SANITY | KRB_CORRUPT_INTERVAL | PIN_BLOCKS | CONTEXTAREA | KRB_CORRUPT_REPEAT | PIN_RANDOM_BLOCKS | CONTROLF | KRB_CORRUPT_SIZE | POKE_ADDRESS | CROSSIC | KRB_CORRUPT_SPBAD_INTERVAL | POKE_LENGTH | CRS | KRB_CORRUPT_SPBAD_REPEAT | POKE_VALUE | CSS | KRB_CORRUPT_SPBITMAP_INTER | POKE_VALUE0 | CURSOR_STATS | KRB_CORRUPT_SPBITMAP_REPEA | POOL_SIMULATOR | CURSORDUMP | KRB_CORRUPT_SPHEADER_INTER | PROCESSSTATE | CURSORTRACE | KRB_CORRUPT_SPHEADER_REPEA | REALFREEDUMP | DATA_ERR_OFF | KRB_FAIL_INPUT_FILENO | RECORD_CALLSTACK | DATA_ERR_ON | KRB_OPTIONS | RECOVERY | DBSCHEDULER | KRB_OVERWRITE_ACTION | REDOHDR | DROP_SEGMENTS | KRB_PIECE_FAIL | REDOLOGS | DUMP_ADV_SNAPSHOTS | KRB_SET_TIME_SWITCH | REFRESH_OS_STATS | DUMP_ALL_COMP_GRANULES | KRB_SIMULATE_NODE_AFFINITY | ROW_CACHE | DUMP_ALL_COMP_GRANULE_ADDR | KRB_TRACE | RULESETDUMP | DUMP_ALL_OBJSTATS | KRB_UNUSED_OPTION | RULESETDUMP_ADDR | DUMP_ALL_REQS | KRBMRSR_LIMIT | SAVEPOINTS | DUMP_PINNED_BUFFER_HISTORY | KRBMROR_LIMIT | SELFTESTASM | DUMP_TEMP | KRC_TRACE | SET_NBLOCKS | DUMP_TRANSFER_OPS | KSDTRADV_TEST | SET_TSN_P1 | DUMPGLOBALDATA | KSFQP_LIMIT | SHARED_SERVER_STATE | ENQUEUES | KSKDUMPTRACE | SIMULATE_EOV | ERRORSTACK | KTPR_DEBUG | SYSTEMSTATE | EVENT_TSM_TEST | KSTDUMPALLPROCS | SYSTEMSTATE_GLOBAL | EXCEPTION_DUMP | KSTDUMPALLPROCS_CLUSTER | TEST_DB_ROBUSTNESS | FAILOVER | KSTDUMPCURPROC | TEST_GET_CALLER | FBHDR | KUPPLATCHTEST | TEST_SPACEBG | FBINC | KXFPCLEARSTATS | TEST_STACK_DUMP | FBTAIL | KXFPDUMPTRACE | TR_CRASH_AFTER_WRITE | FILE_HDRS | KXFPBLATCHTEST | TR_CORRUPT_ONE_SIDE | FLASHBACK_GEN | KXFXCURSORSTATE | TR_READ_ONE_SIDE | FLUSH_CACHE | KXFXSLAVESTATE | TR_SET_ALL_BLOCKS | FLUSH_JAVA_POOL | LATCHES | TR_SET_BLOCK | FULL_DUMPS | LDAP_KERNEL_DUMP | TR_SET_SIDE | GC_ELEMENTS | LDAP_USER_DUMP | TRACE_BUFFER_OFF | GES_STATE | LIBRARY_CACHE | TRACE_BUFFER_ON | GLOBAL_AREA | LOCKS | TREEDUMP | HANGANALYZE | LOGERROR | TR_RESET_NORMAL | HANGANALYZE_PROC | LOGHIST | UPDATE_BLOCK0_FORMAT | HANGANALYZE_GLOBAL | LONGF_CREATE | WORKAREATAB_DUMP | |
|
DUMPSGA |
Dump fixed SGA | oradebug dumpsga oradebug dumpsga <bytes> |
SQL> oradebug dumpsga c:\oracle\product\admin\orabase\udump\orabase_ora_2120.trc |
|
DUMPTYPE |
Print/dump an address with type info | oradebug dumptype <address> <type> <count> |
TBD |
|
DUMPVAR |
Print/dump a fixed PGA/SGA/UGA variable | oradebug dumpvar <pga|sga|uga> <name> [level] |
SQL> oradebug setmypid SQL> oradebug dumpvar SGA kcbnbh |
|
EVENT |
Set trace event in process | oradebug EVENT <event> TRACE NAME CONTEXT FOREVER, LEVEL <level> |
SQL> oradebug setmypid SQL> oradebug EVENT 10046 TRACE NAME CONTEXT FOREVER, LEVEL 12 SQL> oradebug unlimit |
|
FFBEGIN |
Flash Freeze the Instance | oradebug ffbegin |
SQL> oradebug ffbegin |
|
FFDEREGISTER |
FF deregister instance from cluster | oradebug ffderegistger |
SQL> oradebug ffderegister |
|
FFRESUMEINST |
Resume the flash frozen instance | oradebug ffresumeinst |
SQL> oradebug ffresumeinst |
|
FFSTATUS |
Flash freeze status of instance | oradebug ffstatus |
SQL> oradebug ffstatus |
|
FFTERMINST |
Call exit and terminate instance | oradebug ffterminst |
SQL> oradebug ffterminst |
|
FLUSH |
Flush the current contents of the trace buffer to the trace file use | oradebug flush |
SQL> oradebug flush |
|
HANGANALYZE |
Analyze system hang for stand-alone | oradebug hanganalzye [level] [syslevel] |
SQL> oradebug setmypid; SQL> oradebug hanganalyze; SQL> oradebug flush; |
Analyze system hang for RAC | SQL> oradebug setmypid; SQL> oradebug -g def hanganalyze 1 SQL> oradebug flush; |
|
HELP |
Describe one or all commands | oradebug help oradebug help <command> |
SQL> oradebug help SQL> oradebug help flush |
|
LKDEBUG |
Invoke global enqueue service debugger | oradebug lkdebug |
SQL> oradebug lkdebug |
|
MAPCOWSGA |
Map SGA as COW | oradebug mapcowsga <SGA dump dir> |
SQL> oradebug mapcowsga "c: emp" |
|
NSDBX |
Invoke CGS name-service debugger | oradebug nsdbx |
SQL> oradebug nsdbx |
|
PEEK |
Print/Dump memory | oradebug peek <addr> <len> [level] |
SQL> oradebug peek 0x20005F0C 12 |
|
POKE |
Modify memory. Never perform this function on a production database! | oradebug poke <addr> <len> <value> |
SQL> oradebug poke 0x20005F0C 4 0x46495845 |
|
PROCSTAT |
Dump process statistics | oradebug procstat |
SQL> oradebug setmypid SQL> oradebug procstat |
|
RESUME |
Resume execution | oradebug resume |
SQL> oradebug resume |
|
SESSION_EVENT |
Set trace event in session | oradebug session_event <text> |
SQL> oradebug session_event 10053 TRACE NAME CONTEXT FOREVER, LEVEL 1 |
|
SETINST |
Set instance list | oradebug setinst <instance# .. | all> |
SQL> oradebug setinst "1" |
|
SETMYPID |
Sets the oradebug PID to the current process | oradebug setmypid |
SQL> oradebug setmypid |
|
SETORAPID |
Set PID of Oracle process to debug | oradebug setorapid <orapid> ['force'] |
SELECT pid FROM gv$process WHERE addr = ( SELECT paddr FROM gv$session WHERE sid = DBMS_SUPPORT.MYSID); or SELECT pid FROM gv$process WHERE addr = ( SELECT paddr FROM gv$session WHERE sid = (SELECT sid FROM gv$mystat WHERE ROWNUM = 1)); /* or one of the processes from the background, dispatcher, job queue, parallel, or shared server process lists */ SQL> oradebug setorapid 19 |
|
SETOSPID |
Set OS pid of process to debug The operating system process ID is the PID on Unix systems and the thread number for Windows systems | oradebug setospid <ospid> |
Do not use as it often fails. Use setorapid instead. |
|
SETORAPNAME |
Set Oracle process name to debug | oradebug setorapname <orapname> |
TBD |
|
SETVAR |
Modify a fixed PGA/SGA/UGA variable | oradebug setvar <pga|sga|uga> <name> <value> |
SQL> oradebug setvar SGA kcfdfk 200 |
|
SGATOFILE |
Dump SGA to file | oradebug sgatofile <SGA dump dir> |
SQL> oradebug ffbegin SQL> oradebug sgatofile "c: emp" SQL> oradebug ffresumeinst |
|
SHORT_STACK |
Get abridged OS stack | oradebug short_stack |
SQL> oradebug short_stack |
|
SHOW |
Show watchpoints | oradebug show <local|global|target> watchpoint <id> |
SQL> oradebug show? |
|
SKDSTTPCS |
Helps translate PCs to names | oradebug skdsttpcs <ifname> <ofname> |
SQL> oradebug skdsttpcs? |
|
SUSPEND |
Suspends the current process | oradebug suspend |
SQL> oradebug suspend |
|
TRACEFILE_NAME |
Get trace file name Will not return a value on Windows systems | oradebug tracefile_name |
SQL> oradebug tracefile_name |
|
UNLIMIT |
Unlimit the size of the trace file | oradebug unlimit |
SQL> oradebug unlimit |
|
WAKEUP |
Wake up Oracle process | oradebug wakeup <orapid> |
SELECT pid FROM gv$process WHERE addr = ( SELECT paddr FROM gv$bgprocess WHERE name = 'SMON'); SQL> oradebug wakeup 7 |
|
WATCH |
Watch a region of memory | oradebug watch <address> <len> <self|exist|all|target> |
SQL> oradebug watch? |
|
Demo |
Taking 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 gv$process WHERE addr IN ( SELECT paddr FROM gv$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 |