CODE
Oracle Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
PSOUG Home Code Snippets Oracle Reference Oracle Functions PSOUG Forum Oracle Blogs Bookmark and Share
 
 Search the Reference Library pages:  

Free
Oracle Magazine
Subscriptions
and Oracle White Papers

Oracle ORADEBUG
Version 11.1
 
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
 
Related Topics
Dumping Oracle
Trace & TKPROF
 
 
*****
Credit: A few examples on this page, specifically PEEK and POKE, were copied from Julian Dyke's web site: Click Here
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us [79 users online]    © 2010 psoug.org