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 |
