说明
oradebug主要是给oracle支持人员使用的，尽管很早便有，但oracle官网很少有记载。他是个sql*plus命令行工具，有sysdba的权限就可以登入，无需特别设置。他可以被用于：
1）追踪进程，或者是你的，或者是外部的
2）确定进程往哪个trc文件写
3）转储：数据文件头、内部oracle结构等
4）挂起进程，暂时的
5）确定实例使用了哪些共享内存块和信号量
6）找出RAC实例使用了哪些互联地址和协议
7）修改SGA中的数据结构
SQL> oradebug help
HELP [command] Describe one or all commands
SETMYPID Debug current process
SETOSPID <ospid> Set OS pid of process to debug
SETORAPID <orapid> ['force'] Set Oracle pid of process to debug
SETORAPNAME <orapname> Set Oracle process name to debug
SHORT_STACK Get abridged OS stack
CURRENT_SQL Get current SQL
DUMP <dump_name> <lvl> [addr] Invoke named dump
PDUMP [interval=<interval>] Invoke named dump periodically
[ndumps=<count>] <dump_name> <lvl> [addr]
DUMPSGA [bytes] Dump fixed SGA
DUMPLIST Print a list of available dumps
EVENT <text> Set trace event in process
SESSION_EVENT <text> Set trace event in session
DUMPVAR <p|s|uga> <name> [level] Print/dump a fixed PGA/SGA/UGA variable
DUMPTYPE <address> <type> <count> Print/dump an address with type info
SETVAR <p|s|uga> <name> <value> Modify a fixed PGA/SGA/UGA variable
PEEK <addr> <len> [level] Print/Dump memory
POKE <addr> <len> <value> Modify memory
WAKEUP <orapid> Wake up Oracle process
SUSPEND Suspend execution
RESUME Resume execution
FLUSH Flush pending writes to trace file
CLOSE_TRACE Close trace file
TRACEFILE_NAME Get name of trace file
SETTRACEFILEID <identifier name> Set tracefile identifier
LKDEBUG Invoke global enqueue service debugger
NSDBX Invoke CGS name-service debugger
-G <Inst-List | def | all> Parallel oradebug command prefix
-R <Inst-List | def | all> Parallel oradebug prefix (return output
SETINST <instance# .. | all> Set instance list in double quotes
SGATOFILE <SGA dump dir> Dump SGA to file; dirname in double quotes
DMPCOWSGA <SGA dump dir> Dump & map SGA as COW; dirname in double quotes
MAPCOWSGA <SGA dump dir> Map SGA as COW; dirname in double quotes
HANGANALYZE [level] [syslevel] Analyze system hang
FFBEGIN Flash Freeze the Instance
FFDEREGISTER FF deregister instance from cluster
FFTERMINST Call exit and terminate instance
FFRESUMEINST Resume the flash frozen instance
FFSTATUS Flash freeze status of instance
SKDSTTPCS <ifname> <ofname> Helps translate PCs to names
WATCH <address> <len> <self|exist|all|target> Watch a region of memory
DELETE <local|global|target> watchpoint <id> Delete a watchpoint
SHOW <local|global|target> watchpoints Show watchpoints
DIRECT_ACCESS <set/enable/disable command | select query> Fixed table access
IPC Dump ipc information
UNLIMIT Unlimit the size of the trace file
CALL [-t count] <func> [arg1]...[argn] Invoke function with arguments
CORE Dump core without crashing process
PROCSTAT Dump process statistics
使用方法举例
【获得 当前实例的trace文件】
SQL> oradebug setmypid
Statement processed.
SQL> oradebug tracefile_name ---获得文件名称
/u01/app/oracle/diag/rdbms/tune/tune/trace/tune_ora_6877.trc
【获得非当前进程的trace】
select pid,spid,username from v$process;
v$process 下的pid 是Oracle 的ID。spid 是系统的ID。
select a.username,a.sid ,a.serial#,b.spid from v$session a,v$process b where a.paddr=b.addr;
v$session下的sid是指session id，b.spid是指系统ID。
oradebug setospid --跟踪系统进程
oradebug setorapid --跟踪ORACLE进程
SQL> oradebug setospid 6150
Oracle pid: 44, Unix process pid: 6150, image: oracle@dgdemo1
SQL> oradebug tracefile_name
/u01/app/oracle/diag/rdbms/tune/tune/trace/tune_ora_6150.trc
SQL> oradebug setorapid 44
Oracle pid: 44, Unix process pid: 6150, image: oracle@dgdemo1
SQL> oradebug tracefile_name
/u01/app/oracle/diag/rdbms/tune/tune/trace/tune_ora_6150.trc
可以看到 文件名称都是一样的
【用oradebug做session级10046或10053】
oradebug setmypid ---跟踪当前的进程
oradebug unlimit ---取消对trace文件大小的限制
oradebug session_event 10046 trace name context forever ,level 4 --开启10046事件跟踪
oradebug tracefile_name ---查看trace的路径
oradebug event 10046 trace name context off --关闭10046事件跟踪
oradebug session_event 10053 trace name context forever ,level 4 ---开启10053事件跟踪
oradebug event 10053 trace name context off --关闭10053事件跟踪
SQL> oradebug setmypid
Statement processed.
SQL> oradebug unlimit
Statement processed.
SQL> oradebug session_event 10046 trace name context forever ,level 4
Statement processed.
SQL> oradebug tracefile_name
/u01/app/oracle/diag/rdbms/tune/tune/trace/tune_ora_11602.trc
SQL> !ls -lrt /u01/app/oracle/diag/rdbms/tune/tune/trace/tune_ora_11602.trc
-rw-r----- 1 oracle asmadmin 1629 12月 15 16:56 /u01/app/oracle/diag/rdbms/tune/tune/trace/tune_ora_11602.trc
SQL> oradebug event 10046 trace name context off
Statement processed.
SQL> !ls -lrt /u01/app/oracle/diag/rdbms/tune/tune/trace/tune_ora_11602.trc
-rw-r----- 1 oracle asmadmin 1829 12月 15 16:57 /u01/app/oracle/diag/rdbms/tune/tune/trace/tune_ora_11602.trc
【用oradebug做oracle process级10046】
跟踪oracle ID 14，系统进程6150的10046是事件情况
SQL> select a.username,a.sid ,a.serial#,b.spid from v$session a,v$process b where a.paddr=b.addr;
SOE 11 3 6150
SQL> select pid,spid,username from v$process;
44 6150 oracle
oradebug setorapid 44
oradebug unlimit
oradebug event 10046 trace name context forever ,level 4
oradebug event 10046 trace name context off
oradebug tracefile_name
SQL> oradebug setorapid 44
Oracle pid: 44, Unix process pid: 6150, image: oracle@dgdemo1
SQL> oradebug event 10046 trace name context forever ,level 4
Statement processed.
SQL> oradebug tracefile_name
/u01/app/oracle/diag/rdbms/tune/tune/trace/tune_ora_6150.trc
SQL> oradebug event 10046 trace name context off
Statement processed.
SQL> !ls -lrt /u01/app/oracle/diag/rdbms/tune/tune/trace/tune_ora_6150.trc
-rw-r----- 1 oracle asmadmin 707546 12月 15 17:19 /u01/app/oracle/diag/rdbms/tune/tune/trace/tune_ora_6150.trc
【radebug系统hang住原因分析】
oradebug setmypid
oradebug unlimit
oradebug setinst all --RAC环境
oradebug hanganalyze 3 -- 级别一般指定为3足够了
oradebug -g def dump systemstate 10 --RAC环境
oradebug tracefile_name
SQL> oradebug setmypid
Statement processed.
SQL> oradebug unlimit
Statement processed.
SQL> oradebug hanganalyze 3
Hang Analysis in /u01/app/oracle/diag/rdbms/tune/tune/trace/tune_ora_12457.trc
SQL> !ls -lrt /u01/app/oracle/diag/rdbms/tune/tune/trace/tune_ora_12457.trc
-rw-r----- 1 oracle asmadmin 5547 12月 15 17:20 /u01/app/oracle/diag/rdbms/tune/tune/trace/tune_ora_12457.trc
SQL> !ls -lrt /u01/app/oracle/diag/rdbms/tune/tune/trace/tune_ora_12457.trc
-rw-r----- 1 oracle asmadmin 5547 12月 15 17:20 /u01/app/oracle/diag/rdbms/tune/tune/trace/tune_ora_12457.trc
SQL> oradebug tracefile_name
/u01/app/oracle/diag/rdbms/tune/tune/trace/tune_ora_12457.trc
SQL> exit
【获取某进程的状态信息】 必须是Oracle 的进程
oradebug setospid 6150
oradebug dump processstate 10
oradebug tracefile_name
SQL> oradebug setospid 6150
Oracle pid: 44, Unix process pid: 6150, image: oracle@dgdemo1
SQL> oradebug dump processstate 10
Statement processed.
SQL> oradebug tracefile_name
/u01/app/oracle/diag/rdbms/tune/tune/trace/tune_ora_6150.trc
SQL> !ls -lrt /u01/app/oracle/diag/rdbms/tune/tune/trace/tune_ora_6150.trc
-rw-r----- 1 oracle asmadmin 1629700 12月 15 17:22 /u01/app/oracle/diag/rdbms/tune/tune/trace/tune_ora_6150.trc
SQL> !ls -lrt /u01/app/oracle/diag/rdbms/tune/tune/trace/tune_ora_6150.trc
-rw-r----- 1 oracle asmadmin 1629700 12月 15 17:22 /u01/app/oracle/diag/rdbms/tune/tune/trace/tune_ora_6150.trc
SQL> exit
【获得系统状态】
oradebug setospid 6150
oradebug unlimit
oradebug dump systemstate 10 -使用level10 可以获得较全的信息
oradebug TRACEFILE_NAME
oradebug close_trace
【获取进程错误信息状态】
oradebug setospid 6150
oradebug dump errorstack 3
SQL> oradebug setospid 6150
Oracle pid: 44, Unix process pid: 6150, image: oracle@dgdemo1
SQL> oradebug dump errorstack 3
Statement processed.
SQL> oradebug tracefile_name
/u01/app/oracle/diag/rdbms/tune/tune/trace/tune_ora_6150.trc
SQL> !ls -lrt /u01/app/oracle/diag/rdbms/tune/tune/trace/tune_ora_6150.trc
-rw-r----- 1 oracle asmadmin 5403981 12月 15 17:23 /u01/app/oracle/diag/rdbms/tune/tune/trace/tune_ora_6150.trc
SQL> !ls -lrt /u01/app/oracle/diag/rdbms/tune/tune/trace/tune_ora_6150.trc
-rw-r----- 1 oracle asmadmin 5403981 12月 15 17:23 /u01/app/oracle/diag/rdbms/tune/tune/trace/tune_ora_6150.trc
SQL> exit
【追踪造成错误信息的原因，如ORA-04031】
oradebug event 4031 trace name errorstack level 3
SQL> oradebug setospid 6150
Oracle pid: 44, Unix process pid: 6150, image: oracle@dgdemo1
SQL> oradebug event 4031 trace name errorstack level 3
Statement processed.
SQL> oradebug tracefile_name
/u01/app/oracle/diag/rdbms/tune/tune/trace/tune_ora_6150.trc
oradebug flush 将当前trace buffer中的条目 刷新到trace文件中
【dump 相关文件信息】
SQL> oradebug dumplist
TRACE_BUFFER_ON
TRACE_BUFFER_OFF
LATCHES
PROCESSSTATE
SYSTEMSTATE
INSTANTIATIONSTATE
REFRESH_OS_STATS
SQLNET_SERVER_TRACE
CROSSIC
CONTEXTAREA
HANGDIAG_HEADER
HEAPDUMP
HEAPDUMP_ADDR
POKE_ADDRESS
POKE_LENGTH
POKE_VALUE
POKE_VALUE0
GLOBAL_AREA
REALFREEDUMP
FLUSH_JAVA_POOL
POOL_SIMULATOR
PGA_DETAIL_GET
PGA_DETAIL_DUMP
PGA_DETAIL_CANCEL
PGA_SUMMARY
MODIFIED_PARAMETERS
EVENT_TSM_TEST
ERRORSTACK
CALLSTACK
TEST_STACK_DUMP
TEST_GET_CALLER
RECORD_CALLSTACK
EXCEPTION_DUMP
BG_MESSAGES
ENQUEUES
KSTDUMPCURPROC
KSTDUMPALLPROCS
KSTDUMPALLPROCS_CLUSTER
SIMULATE_EOV
KSFQP_LIMIT
KSKDUMPTRACE
DBSCHEDULER
LDAP_USER_DUMP
LDAP_KERNEL_DUMP
DUMP_ALL_OBJSTATS
DUMPGLOBALDATA
HANGANALYZE
HANGANALYZE_PROC
HNGDET_MEM_USAGE_DUMP
DEAD_CLEANUP_STATE
HANGANALYZE_GLOBAL
GES_STATE
CGS
OCR
CSS
CRS
SYSTEMSTATE_GLOBAL
GIPC
MMAN_ALLOC_MEMORY
MMAN_CREATE_DEF_REQUEST
MMAN_CREATE_IMM_REQUEST
MMAN_IMM_REQUEST
DUMP_ALL_COMP_GRANULE_ADDRS
DUMP_ALL_COMP_GRANULES
DUMP_ALL_REQS
DUMP_TRANSFER_OPS
DUMP_ADV_SNAPSHOTS
ADJUST_SCN
NEXT_SCN_WRAP
CONTROLF
FLUSH_CACHE
FULL_DUMPS
BUFFERS
RECOVERY
SET_TSN_P1
GLOBAL_BUFFER_DUMP
BUFFER
PIN_BLOCKS
BC_SANITY_CHECK
PIN_RANDOM_BLOCKS
SET_NBLOCKS
CHECK_ROREUSE_SANITY
DUMP_PINNED_BUFFER_HISTORY
KCBO_OBJ_CHECK_DUMP
KCB_WORKING_SET_DUMP
KCBS_ADV_INT_DUMP
KCBI_DUMP_FREELIST
SCN_AUTO_ROLLOVER_TS_OVERRIDE
REDOLOGS
ARCHIVE_ERROR
LOGHIST
REDOHDR
LOGERROR
OPEN_FILES
DATA_ERR_ON
DATA_READ_ERR_ON
DATA_ERR_OFF
BLK0_FMTCHG
UPDATE_BLOCK0_FORMAT
TR_SET_BLOCK
TR_SET_ALL_BLOCKS
TR_SET_SIDE
TR_CRASH_AFTER_WRITE
TR_READ_ONE_SIDE
TR_CORRUPT_ONE_SIDE
TR_RESET_NORMAL
TEST_DB_ROBUSTNESS
LOCKS
GC_ELEMENTS
FILE_HDRS
KRB_CORRUPT_INTERVAL
KRB_CORRUPT_SIZE
KRB_CORRUPT_REPEAT
KRB_CORRUPT_OFFSET
KRB_PIECE_FAIL
KRB_OPTIONS
KRB_FAIL_INPUT_FILENO
KRB_SIMULATE_NODE_AFFINITY
KRB_TRACE
KRB_BSET_DAYS
KRB_SET_TIME_SWITCH
KRB_OVERWRITE_ACTION
KRB_CORRUPT_SPHEADER_INTERVAL
KRB_CORRUPT_SPHEADER_REPEAT
KRB_CORRUPT_SPBITMAP_INTERVAL
KRB_CORRUPT_SPBITMAP_REPEAT
KRB_CORRUPT_SPBAD_INTERVAL
KRB_CORRUPT_SPBAD_REPEAT
KRB_UNUSED_OPTION
KRBMRSR_LIMIT
KRBMROR_LIMIT
KRBABR_TRACE
KRDRSBF
KRC_TRACE
KRA_OPTIONS
KRA_TRACE
FBTAIL
FBINC
FBHDR
FLASHBACK_GEN
KTPR_DEBUG
DUMP_TEMP
DROP_SEGMENTS
TEST_SPACEBG
TREEDUMP
LONGF_CREATE
KDLIDMP
ROW_CACHE
LIBRARY_CACHE
LIBRARY_CACHE_OBJECT
CURSORDUMP
CURSORTRACE
CURSOR_STATS
XS_SESSION_STATE
SHARED_SERVER_STATE
LISTENER_REGISTRATION
JAVAINFO
KXFPCLEARSTATS
KXFPDUMPTRACE
KXFPBLATCHTEST
KXFXSLAVESTATE
KXFXCURSORSTATE
KXFRHASHMAP
WORKAREATAB_DUMP
KUPPLATCHTEST
OBJECT_CACHE
SAVEPOINTS
RULESETDUMP
RULESETDUMP_ADDR
FAILOVER
OLAP_DUMP
SELFTESTASM
ASMDISK_ERR_ON
ASMDISK_READ_ERR_ON
ASMDISK_ERR_OFF
ASM_EVENREAD
IOERREMUL
IOERREMULRNG
ALRT_TEST
AWR_TEST
AWR_FLUSH_TABLE_ON
AWR_FLUSH_TABLE_OFF
ASHDUMP
ASHDUMPSECONDS
MMON_TEST
ATSK_TEST
HM_FW_TRACE
HM_FDG_VERS
IR_FW_TRACE
KSDTRADV_TEST
具体使用方法
SQL> oradebug setmypid
Statement processed.
SQL> oradebug dump LOCKS 10
Statement processed.
SQL> oradebug tracefile_name
/u01/app/oracle/diag/rdbms/tune/tune/trace/tune_ora_12730.trc
具体 参考http://psoug.org/reference/oradebug.html
【Trace SQL】
使用DBMS_SYSTEM包：
select a.username,a.sid ,a.serial#,b.spid from v$session a,v$process b where a.paddr=b.addr;
SOE 11 3 oradebug setospid
execute dbms_system.set_sql_trace_in_session(11,3,true); 开启对进程6150的跟踪
execute dbms_system.set_sql_trace_in_session(11,3,false); 关闭
SQL> l
1* select a.username,a.sid ,a.serial#,b.spid from v$session a,v$process b where a.paddr=b.addr
SQL> execute dbms_system.set_sql_trace_in_session(11,3,true);
PL/SQL procedure successfully completed.
SQL> execute dbms_system.set_sql_trace_in_session(11,3,false);
PL/SQL procedure successfully completed.
使用oradebug
oradebug setospid 6150
oradebug event 10046 trace name context forever,level 4
oradebug event 10046 trace name context off
SQL> oradebug setospid 6150
Oracle pid: 44, Unix process pid: 6150, image: oracle@dgdemo1
SQL> oradebug event 10046 trace name context forever,level 4
Statement processed.
SQL> oradebug event 10046 trace name context off
Statement processed.
参考