Oracle ORADEBUG Utility

Oracle ORADEBUG Version 11.2.0.3

General Information

Credit: A few examples on this page, specifically PEEK and POKE, were copied from one of Julian Dyke's web pages which is no longer on the web a few others from Tanel Poder's excellent 2006 presentation for NoCOUG titled "Advanced Research Techniques in Oracle"

Related Queries

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 ksmget_sgamaxalloc

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 3

DIRECT_ACCESS

Fixed table access

oradebug direct_access <set/enable/disable command | select query>

SQL> oradebug direct_access enable trace
SQL> oradebug direct_access disable reply
SQL> oradebug direct_access set content_type = 'text/plain'
SQL> oradebug direct_access select * from x$ash

DMPCOWSGA

Dump & map SGA as COW

oradebug dmpcowsga <SGA dump dir>

SQL> oradebug dmpcowsga "c:\temp"

DOC

Displays internal documentation

oradebug doc [<event | component [<component_name>]>]

SQL> oradebug doc
SQL> oradebug doc event
SQL> oradebug doc component
SQL> oradebug doc component DIAG
SQL> oradebug doc component RDBMS

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

Invoke HANGANALYZE

SQL> oradebug setmypid
SQL> oradebug unlimit
SQL> oradebug -g all hanganalyze 3

SQL> oradebug setmypid
SQL> oradebug dump hanganalyze 4

DUMPLIST

Print a list of available dumps

oradebug dumplist

SQL> oradebug dumplist

Available Dumps
ADJUST_SCN	HANGDIAG_HEADER	MMAN_ALLOC_MEMORY
ALRT_TEST	HEAPDUMP <level>	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 [<bytes>]

SQL> oradebug dumpsga c:\oracle\diag\rdbms\orabase\orabase\trace\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 unlimit
SQL> oradebug EVENT 10046 TRACE NAME CONTEXT FOREVER, LEVEL 12

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 unlimit
SQL> oradebug tracefile_name
SQL> oradebug hanganalyze 4

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 [<command>]

SQL> oradebug help
SQL> oradebug help flush

IPC

Dump IPC information

oradebug ipc

SQL> oradebug setmypid
Statement processed.
SQL> oradebug ipc
Information written to trace file C:\ORACLE\diag\rdbms\orabase\orabase\trace\orabase_ora_10988.trc

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:\temp"

NSDBX

Invoke CGS name-service debugger

oradebug nsdbx

SQL> oradebug nsdbx

PEEK

Print/Dump memory

oradebug peek <addr> <len> [level]

SQL> oradebug peek oradebug peek 0x075731F8 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 v$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>

SQL> oradebug setorapname pmon

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:\temp"
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 global watchpoints

SKDSTTPCS

Helps translate PCs to names

oradebug skdsttpcs <ifname> <ofname>

TBD

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 0x29995F0C 12 self

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

Oradebug Used To Test ADR Functionality

-- To create an incident, along with a couple of trace files, use the following command from SQL*Plus. You must connect as SYS as SYSDBA

Syntax:

oradebug unit_test dbke_test dde_flow_kge_soft <error argument> <arg1> <arg2> <...>

-- for example
oradebug unit_test dbke_test dde_flow_kge_soft dbketest_soft_assert p1
oradebug unit_test dbke_test dde_flow_kge_soft dbketest_soft_assert p1 p2
oradebug unit_test dbke_test dde_flow_kge_soft another_error
oradebug unit_test dbke_test dde_flow_kge_soft another_error p1 p2 p3 p4

/* You can then view, edit, and package the incident(s) with the Enterprise Manager Support Workbench (doc'd in Ch 8 of the Database Adminstrator's Guide) or with the ADRCI utility (doc'd in the Utilities Guide). */

Take a System State Dump

oradebug setmypid
oradebug unlimit
oradebug -g all dump systemstate 266

oradebug setmypid
oradebug unlimit
oradebug dump systemstate 267