oradebug

Oracle ORADEBUGVersion 11.1 GENERALBackground Processes ListSELECT b.name, p.pid
FROM gv$bgprocess b, gv$process p
WHERE b.paddr = p.addr
ORDER BY 1;Dispatcher Processes ListSELECT d.name, p.pid
FROM gv$dispatcher d, gv$process p
WHERE d.paddr = p.addr;Job Queue Process ListSELECT 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 ListSELECT pid, server_name, status
FROM gv$px_process;Shared Server Processes ListSELECT s.name, p.pid
FROM gv$shared_server s, gv$process p
WHERE s.paddr = p.addr;SGA Variables ListSELECT ksmfsnam
FROM x$ksmfsv
WHERE ksmfsnam LIKE '%\_' ESCAPE '\';SwitchesSwitchFormatDescription-G<Inst-List | def | all>Parallel oradebug command prefix-R<Inst-List | def | all>Parallel oradebug prefix return output CALLInvoke function with argumentsoradebug call <func> [arg1] ... [argn]SQL> oradebug call ? CLOSE_TRACEClose trace fileoradebug close_traceSQL> oradebug close_trace COREDump core without crashing processoradebug coreSQL> oradebug core CURRENT_SQL
Get current SQLoradebug current_sqlSYSUWCLASSconn / as sysdbaconn uwclass/uwclassGRANT 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  DELETEDelete a watchpointoradebug delete <local|global|target> watchpoint <id>SQL> oradebug delete ? DIRECT_ACCESSFixed table accessoradebug direct_access <set/enable/disable command | select query>TBD DMPCOWSGADump & map SGA as COWoradebug dmpcowsga <SGA dump dir>SQL> oradebug dmpcowsga "c: emp" DUMPInvoke named dumporadebug dump <dump_name> <lvl> [addr]-- dump_name values can be obtained from oradebug dumplist

SQL> oradebug setmypid
SQL> oradebug dump library_cache 4Invoke ASH dumpSQL> oradebug setmypid
SQL> oradebug dump ashdump 10 DUMPLIST
Print a list of available dumpsoradebug dumplistSQL> oradebug dumplist
 

Available Dumps

ADJUST_SCNHANGDIAG_HEADERMMAN_ALLOC_MEMORYALRT_TESTHEAPDUMPMMAN_CREATE_DEF_REQUESTARCHIVE_ERRORHEAPDUMP_ADDRMMAN_CREATE_IMM_REQUESTASHDUMPHM_FW_TRACEMMAN_IMM_REQUESTATSK_TESTINSTANTIATIONSTATEMMON_TESTAWR_FLUSH_TABLE_OFFIOERREMULMODIFIED_PARAMETERSAWR_FLUSH_TABLE_ONIOERREMULRNGNEXT_SCN_WRAPAWR_TESTIR_FW_TRACEOBJECT_CACHEBC_SANITY_CHECKJAVAINFOOCRBG_MESSAGESKCBO_OBJ_CHECK_DUMPOLAP_DUMPBLK0_FMTCHGKDLIDMPOPEN_FILESBUFFERKRA_OPTIONSPGA_DETAIL_CANCELBUFFERSKRA_TRACEPGA_DETAIL_DUMPCALLSTACKKRB_BSET_DAYSPGA_DETAIL_GETCHECK_ROREUSE_SANITYKRB_CORRUPT_INTERVALPIN_BLOCKSCONTEXTAREAKRB_CORRUPT_REPEATPIN_RANDOM_BLOCKSCONTROLFKRB_CORRUPT_SIZEPOKE_ADDRESSCROSSICKRB_CORRUPT_SPBAD_INTERVALPOKE_LENGTHCRSKRB_CORRUPT_SPBAD_REPEATPOKE_VALUECSSKRB_CORRUPT_SPBITMAP_INTERPOKE_VALUE0CURSOR_STATSKRB_CORRUPT_SPBITMAP_REPEAPOOL_SIMULATORCURSORDUMPKRB_CORRUPT_SPHEADER_INTERPROCESSSTATECURSORTRACEKRB_CORRUPT_SPHEADER_REPEAREALFREEDUMPDATA_ERR_OFFKRB_FAIL_INPUT_FILENORECORD_CALLSTACKDATA_ERR_ONKRB_OPTIONSRECOVERYDBSCHEDULERKRB_OVERWRITE_ACTIONREDOHDRDROP_SEGMENTSKRB_PIECE_FAILREDOLOGSDUMP_ADV_SNAPSHOTSKRB_SET_TIME_SWITCHREFRESH_OS_STATSDUMP_ALL_COMP_GRANULESKRB_SIMULATE_NODE_AFFINITYROW_CACHEDUMP_ALL_COMP_GRANULE_ADDRKRB_TRACERULESETDUMPDUMP_ALL_OBJSTATSKRB_UNUSED_OPTIONRULESETDUMP_ADDRDUMP_ALL_REQSKRBMRSR_LIMITSAVEPOINTSDUMP_PINNED_BUFFER_HISTORYKRBMROR_LIMITSELFTESTASMDUMP_TEMPKRC_TRACESET_NBLOCKSDUMP_TRANSFER_OPSKSDTRADV_TESTSET_TSN_P1DUMPGLOBALDATAKSFQP_LIMITSHARED_SERVER_STATEENQUEUESKSKDUMPTRACESIMULATE_EOVERRORSTACKKTPR_DEBUGSYSTEMSTATE EVENT_TSM_TESTKSTDUMPALLPROCSSYSTEMSTATE_GLOBALEXCEPTION_DUMPKSTDUMPALLPROCS_CLUSTERTEST_DB_ROBUSTNESSFAILOVERKSTDUMPCURPROCTEST_GET_CALLERFBHDRKUPPLATCHTESTTEST_SPACEBGFBINCKXFPCLEARSTATSTEST_STACK_DUMPFBTAILKXFPDUMPTRACETR_CRASH_AFTER_WRITE FILE_HDRSKXFPBLATCHTESTTR_CORRUPT_ONE_SIDEFLASHBACK_GENKXFXCURSORSTATETR_READ_ONE_SIDE FLUSH_CACHEKXFXSLAVESTATETR_SET_ALL_BLOCKSFLUSH_JAVA_POOLLATCHESTR_SET_BLOCK FULL_DUMPSLDAP_KERNEL_DUMPTR_SET_SIDEGC_ELEMENTSLDAP_USER_DUMPTRACE_BUFFER_OFF GES_STATELIBRARY_CACHETRACE_BUFFER_ONGLOBAL_AREALOCKSTREEDUMP HANGANALYZELOGERRORTR_RESET_NORMALHANGANALYZE_PROCLOGHISTUPDATE_BLOCK0_FORMATHANGANALYZE_GLOBALLONGF_CREATEWORKAREATAB_DUMP DUMPSGADump fixed SGAoradebug dumpsga

oradebug dumpsga <bytes>SQL> oradebug dumpsga
c:\oracle\product\admin\orabase\udump\orabase_ora_2120.trc DUMPTYPEPrint/dump an address with type infooradebug dumptype <address> <type> <count>TBD DUMPVARPrint/dump a fixed PGA/SGA/UGA variableoradebug dumpvar <pga|sga|uga> <name> [level]SQL> oradebug setmypid
SQL> oradebug dumpvar SGA kcbnbh EVENTSet trace event in processoradebug EVENT <event> 
TRACE NAME CONTEXT FOREVER, LEVEL <level>SQL> oradebug setmypid

SQL> oradebug EVENT 10046 TRACE NAME CONTEXT FOREVER, LEVEL 12

SQL> oradebug unlimit FFBEGINFlash Freeze the Instanceoradebug ffbeginSQL> oradebug ffbegin FFDEREGISTERFF deregister instance from clusteroradebug ffderegistgerSQL> oradebug ffderegister FFRESUMEINSTResume the flash frozen instanceoradebug ffresumeinstSQL> oradebug ffresumeinst FFSTATUSFlash freeze status of instanceoradebug ffstatusSQL> oradebug ffstatus FFTERMINSTCall exit and terminate instanceoradebug ffterminstSQL> oradebug ffterminst FLUSHFlush the current contents of the trace buffer to the trace file useoradebug flushSQL> oradebug flush HANGANALYZE
Analyze system hang for stand-aloneoradebug hanganalzye [level] [syslevel]SQL> oradebug setmypid;
SQL> oradebug hanganalyze;
SQL> oradebug flush;Analyze system hang for RACSQL> oradebug setmypid;
SQL> oradebug -g def hanganalyze 1
SQL> oradebug flush; HELPDescribe one or all commands oradebug help

oradebug help <command>SQL> oradebug help
SQL> oradebug help flush LKDEBUGInvoke global enqueue service debuggeroradebug lkdebugSQL> oradebug lkdebug MAPCOWSGAMap SGA as COWoradebug mapcowsga <SGA dump dir>SQL> oradebug mapcowsga "c: emp" NSDBXInvoke CGS name-service debuggeroradebug nsdbxSQL> oradebug nsdbx PEEKPrint/Dump memoryoradebug peek <addr> <len> [level]SQL> oradebug peek 0x20005F0C 12 POKEModify memory. Never perform this function on a production database!oradebug poke <addr> <len> <value>SQL> oradebug poke 0x20005F0C 4 0x46495845 PROCSTATDump process statisticsoradebug procstatSQL> oradebug setmypid

SQL> oradebug procstat RESUMEResume executionoradebug resumeSQL> oradebug resume SESSION_EVENTSet trace event in sessionoradebug session_event <text>SQL> oradebug session_event 10053 TRACE NAME CONTEXT FOREVER, LEVEL 1 SETINSTSet instance listoradebug setinst <instance# .. | all>SQL> oradebug setinst "1" SETMYPIDSets the oradebug PID to the current processoradebug setmypidSQL> oradebug setmypid SETORAPID
Set PID of Oracle process to debugoradebug 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 SETOSPIDSet OS pid of process to debug

The operating system process ID is the PID on Unix systems and the thread number for Windows systemsoradebug setospid <ospid>Do not use as it often fails. Use setorapid instead. SETORAPNAMESet Oracle process name to debugoradebug setorapname <orapname>TBD SETVARModify a fixed PGA/SGA/UGA variableoradebug setvar <pga|sga|uga> <name> <value>SQL> oradebug setvar SGA kcfdfk 200 SGATOFILEDump SGA to fileoradebug sgatofile <SGA dump dir>SQL> oradebug ffbegin
SQL> oradebug sgatofile "c: emp"
SQL> oradebug ffresumeinst SHORT_STACKGet abridged OS stackoradebug short_stackSQL> oradebug short_stack SHOWShow watchpointsoradebug show <local|global|target> watchpoint <id>SQL> oradebug show? SKDSTTPCSHelps translate PCs to namesoradebug skdsttpcs <ifname> <ofname>SQL> oradebug skdsttpcs? SUSPENDSuspends the current processoradebug suspendSQL> oradebug suspend TRACEFILE_NAMEGet trace file name

Will not return a value on Windows systemsoradebug tracefile_nameSQL> oradebug tracefile_name UNLIMITUnlimit the size of the trace fileoradebug unlimitSQL> oradebug unlimit WAKEUPWake up Oracle processoradebug wakeup <orapid>SELECT pid
FROM gv$process
WHERE addr = (
  SELECT paddr
  FROM gv$bgprocess
  WHERE name = 'SMON');

SQL> oradebug wakeup 7 WATCHWatch a region of memoryoradebug 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 TopicsDumping OracleTrace & TKPROF