自ORACLE7起，ORADEBUG工具就开始被DBA使用了。它可以tracing any session / dump SGA and other memory str ture / wakeup oracle process / suspend and resume processing ….
一、 概要
首先看看help
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
SHORT_STACK Dump abridged OS stack
DUMP <dump_name> <lvl> [addr] Invoke named dump
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> <val> Modify a fixed PGA/SGA/UGA variable
PEEK <addr> <len> [level] Print/Dump memory
POKE <addr> <len> <val> 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
LKDEBUG Invoke global enq 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 do le quotes
SGATOFILE <SGA dump dir> Dump SGA to file; dirname in do le quotes
DMPCOWSGA <SGA dump dir> Dump & map SGA as COW; dirname in do le quotes
MAPCOWSGA <SGA dump dir> Map SGA as COW; dirname in do le 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
CORE Dump core without crashing process
UNLIMIT Unlimit the size of the trace file
PROCSTAT Dump process statistics
CALL <func> [arg1] ... [argn] Invoke function with arguments
SQL>
首先确认要追踪哪个进程？可以查询spid或是oracle自己的pid
SQL> select a.username,a.sid ,a.serial#,b.spid
from v$session a,v$process b
where a.paddr=b.addr
/
------查询spid
以本次实验为例
USERNAME SID SERIAL# SPID
------------------------------ ---------- ---------- ------------
SCOTT 155 1524 2204
SQL> select pid,spid,username from v$process;
PID SPID USERNAME
---------- ------------ ---------------
23 2204 Administrator
设定追踪
SQL> oradebug setospid 2204
Oracle pid: 15, Windows thread id: 2204, image: ORACLE.EXE (SHAD)
或设定 SQL> oradebug setorapid 23
SQL> oradebug unlimit
已处理的语句
本次有个疑问: 为什么显示是ORACLE.EXE?windows进程与线程的问题？
…
oracle 3180 8 24 482 223056 0:00:50.212 2:15:12.204
2204 9 89 Wait:UserReq 0:00:00.020 0:00:00.010 0:08:37.113
sid设定之后，可以用来dump的东西可用oradebug dumplist 列出。在这些项中绝大部分，都有2，4，6，8，10，12等几个跟踪级别。
A. 获得系统状态
用于获得系统状态
SQL> oradebug dump systemstate 10
已处理的语句
（注：有趣的事件发生了，系统生成trc用打开，然后将文件内容删除，此时的设定生成了一个bak文件。仔细观察，发现oradebug指向的trc出口也发生了变化，指向了bak文件）
如果系统hung的时候，systemstate基本等同于hanganalyze，可以用于诊断system hung
SQL> oradebug hanganalyze 12
Hang Analysis in d:\oracle\prod t\10.2.0\admin\orcl\ ump\orcl_ora_3576.trc
(注：trc文件怎么看还不会…)
B. 获得某个进程状态
SQL> oradebug setospid 3188
Oracle pid: 12, Windows thread id: 3188, image: ORACLE.EXE (MMNL)
SQL> oradebug setospid 1192
ORA-01858: 在要求输入数字处找到非数字字符
(注：测试发现该进程必须是ORACLE进程/线程)
SQL> oradebug dump processstate 10
已处理的语句
C. 也可以获得进程的错误信息状态
SQL> oradebug dump errorstack 3
已处理的语句
(注：本处没有产生新的trace，难道是需要出错的时候才有信息？)
D. 定位现在在使用哪个trace
SQL> oradebug TRACEFILE_NAME
d:\oracle\prod t\10.2.0\admin\orcl\ ump\orcl_ora_3648.trc
二、 高级一点的应用
1．Trace a session SQL
1) 如果是只想抓取用户sql语句的话（level 1），使用DBMS_SYSTEM包
select a.username,a.sid ,a.serial#,b.spid
from v$session a,v$process b
where a.paddr=b.addr
/
USERNAME SID SERIAL# SPID
------------------------------ ---------- ---------- -------------------- ---------- ---------- -------
SCOTT 143 6 3260
执行SQL> execute dbms_system.set_sql_trace_in_session(143,6,tr); 开启对该进程的trace，记录在trace文件中。
执行SQL> execute dbms_system.set_sql_trace_in_session(143,6,false); 关闭追踪
2) 如果想进行更高级别的抓取，level 4，要使用oradebug
首先按照前面得到进程的spid
SQL> oradebug setospid 3260
Oracle pid: 22, Windows thread id: 3260, image: oracle.exe (SHAD)
SQL> oradebug event 10046 trace name context forever,level 4
已处理的语句
(注：可以同时追踪多个进程,setospid …. , Oradebug …10046…)
抓取后取消追踪使用
SQL> oradebug event 10046 trace name context off
已处理的语句
2. Tracing errors use oradebug
例如要追踪能造成ORA-0094/952错误的会话，则
SQL> oradebug event 942 trace name errorstack level 3
已处理的语句
SQL> oradebug event 952 trace name errorstack level 3
已处理的语句
3. Trace ORA-04030
ORA-04030是由于某些进程请求的内存不断增大最后导致溢出的错误
SQL > oradebug setodpid <pid>
SQL > oradebug unlimit
SQL > oradebug dump heapdump 5 ?this dump PGA and UGA heaps
4. waking up PMON to release DDL locks
首先确认PMON进程是who
SQL> select pid,spid from v$process p,v$bgprocess b
where b.paddr=p.addr
and name='PMON';
PID SPID
---------- ------------
2 3608
SQL> oradebug wakeup 2
已处理的语句
5. 暂停和启动进程(suspending and resuming a process)
SQL> oradebug setospid ***
SQL> oradebug suspend
Or
SQL> oradebug setospid ***
SQL> oradebug resume
6. DUMP很多东西
oradebug可以dump很多内容，例如latch / library_cache / locks / controlfile 等等
具体可参照 oradebug dumplist 的信息
例子：
oradebug dump controlf 10
oradebug dump file_hdrs 10