在之前的HangAnalyze 中有使用oradebug命令，在这篇文章里，我们主要是重点看一下这个oradebug命令：


       Oracle HANGANALYZE 功能诊断 DB hanging


       http://blog.csdn.net/tianlesoftware/archive/2011/04/13/6321961.aspx


 


一.  Oradebug 命令 帮助文档


 


SYS@dave2(db2)> oradebug help



1.1 TRACEFILE_NAME command


This command prints the name of the current trace file e.g.


SQL>oradebug tracefile_name


For example


    /export/home/admin/SS92003/udump/ss92003_ora_14917.trc


This command does not work on Windows 2000 (Oracle 9.2)


 


1.2 UNLIMIT command


       In Oracle 8.1.5 and below the maximum size of the trace file is restricted by default. This means that large dumps (LIBRARY_CACHE, BUFFERS) may fail.


 


To remove the limitation on the size of the trace file use


SQL>oradebug unlimit


       In Oracle 8.1.6 and above the maximum size of the trace file defaults to UNLIMITED


 


1.3 FLUSH command


To flush the current contents of the trace buffer to the trace file use


SQL>oradebug flush


 


1.4 CLOSE_TRACE command


To close the current trace file use


SQL>oradebug close_trace


 


 


二.  追踪进程


如果是系统的进程ID，可以使用oradebug setospid id.


如果是根据Oracle ID，可以使用oradebug setorapid id 来追踪。


 


2.1 查询进程ID


可以查询Linux系统的pid或是oracle自己的pid：


SYS@dave2(db2)> 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


---------- ---------- ---------- ------------


SYS               159       1702 27028


 


查询spid


SYS@dave2(db2)> select pid,spid,username from v$process;


 


       PID SPID         USERNAME


---------- ------------ ----------


        18 27028        oracle


 


v$process 下的pid 是Oracle 的ID。spid 是系统的ID。


 


 


2.2 设定追踪


SYS@dave2(db2)> oradebug setospid 27028  -- 根据系统ID


Oracle pid: 18, Unix process pid: 27028, p_w_picpath: oracledave2@db2


 


或者使用，他们是一样的：


SYS@dave2(db2)> oradebug setorapid 18   --根据Oracle ID


Unix process pid: 27028, p_w_picpath: oracledave2@db2


 


2.3  dump 相关文件信息


       指定为SID 之后，就可以使用dump 将相关的信息，这些dump 内容很多。 可以使用dumplist 把所有的dump 可列出来。


 


具体使用，可以参考：


       http://psoug.org/reference/oradebug.html


 


SYS@dave2(db2)> oradebug dumplist


EVENTS


TRACE_BUFFER_ON


TRACE_BUFFER_OFF


HANGANALYZE


LATCHES


PROCESSSTATE


SYSTEMSTATE


INSTANTIATIONSTATE


REFRESH_OS_STATS


CROSSIC


CONTEXTAREA


HEAPDUMP


HEAPDUMP_ADDR


POKE_ADDRESS


POKE_LENGTH


POKE_VALUE


POKE_VALUE0


GLOBAL_AREA


MEMORY_LOG


REALFREEDUMP


FLUSH_JAVA_POOL


POOL_SIMULATOR


PGA_DETAIL_GET


PGA_DETAIL_DUMP


PGA_DETAIL_CANCEL


MODIFIED_PARAMETERS


EVENT_TSM_TEST


ERRORSTACK


CALLSTACK


HANGANALYZE_PROC


TEST_STACK_DUMP


TEST_GET_CALLER


RECORD_CALLSTACK


EXCEPTION_DUMP


BG_MESSAGES


ENQUEUES


KSTDUMPCURPROC


KSTDUMPALLPROCS


SIMULATE_EOV


KSFQP_LIMIT


KSKDUMPTRACE


DBSCHEDULER


LDAP_USER_DUMP


LDAP_KERNEL_DUMP


DUMP_ALL_OBJSTATS


DUMPGLOBALDATA


HANGANALYZE_GLOBAL


GES_STATE


OCR


CSS


CRS


CREATE_DUMMY_REQUEST


MMAN_ALLOC_MEMORY


MMAN_CREATE_REQUEST


MMAN_CREATE_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


BUFFER


PIN_BLOCKS


BC_SANITY_CHECK


PIN_RANDOM_BLOCKS


SET_NBLOCKS


CHECK_ROREUSE_SANITY


DUMP_PINNED_BUFFER_HISTORY


REDOLOGS


LOGHIST


ARCHIVE_ERROR


REDOHDR


LOGERROR


OPEN_FILES


DATA_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_PIECE_FAIL


KRB_OPTIONS


KRB_FAIL_INPUT_FILENO


KRB_SIMULATE_NODE_AFFINITY


KRB_TRACE


KRB_BSET_DAYS


KRB_SET_TIME_SWITCH


KRBMRSR_LIMIT


KRBMROR_LIMIT


KRC_TRACE


KRA_OPTIONS


KRA_TRACE


FBTAIL


FBINC


FBHDR


FLASHBACK_GEN


DROP_SEGMENTS


KTPR_DEBUG


TREEDUMP


LONGF_CREATE


ROW_CACHE


LIBRARY_CACHE


CURSORDUMP


CURSORTRACE


CURSOR_STATS


SHARED_SERVER_STATE


JAVAINFO


KXFPCLEARSTATS


KXFPDUMPTRACE


KXFPBLATCHTEST


KXFXSLAVESTATE


KXFXCURSORSTATE


WORKAREATAB_DUMP


KUPPLATCHTEST


OBJECT_CACHE


SAVEPOINTS


RULESETDUMP


RULESETDUMP_ADDR


OLAP_DUMP


SELFTESTASM


IOERREMUL


ALRT_TEST


AWR_TEST


AWR_FLUSH_TABLE_ON


AWR_FLUSH_TABLE_OFF


ASHDUMP


MMON_TEST


SYS@dave2(db2)>


 


       在这些dump选项中，大部分都有2，4，6，8，10，12等几个跟踪级别。在使用的时候要根据具体的情况来选择级别，不同级别的影响不一样。


 


 


2.3.1 获得系统状态


如果为了获取全面一点的信息，可以使用Level 10。


 


 


SYS@dave2(db2)> oradebug setospid 27028


Oracle pid: 18, Unix process pid: 27028, p_w_picpath: oracledave2@db2


SYS@dave2(db2)> oradebug unlimit


Statement processed.


SYS@dave2(db2)> oradebug dump systemstate 10


Statement processed.


SYS@dave2(db2)> oradebug TRACEFILE_NAME


/u01/app/oracle/admin/dave2/udump/dave2_ora_27028.trc


 


SYS@dave2(db2)> oradebug close_trace


Statement processed.


 


[oracle@db2 ~]$ tail -50 /u01/app/oracle/admin/dave2/udump/dave2_ora_27028.trc


              last process to post me: none


              last post sent: 0 0 0


              last post sent-location: No post


              last process posted by me: none


    (latch info) wait_event=0 bits=0


    Process Group: DEFAULT, pseudo proc: 0x2e24c604


    O/S info: user: , term: , ospid:


    OSD pid info: Unix process pid: 0, p_w_picpath: PSEUDO


Dump of memory from 0x2E207970 to 0x2E207AF4


2E207970 00000000 00000000 00000000 00000000  [................]


        Repeat 23 times


2E207AF0 00000000                             [....]           


NO DETACHED BRANCHES.


NO DETACHED NETWORK CONNECTIONS.


CLEANUP STATE OBJECTS:


----------------------------------------


SO: 0x2e03465c, type: 1, owner: (nil), flag: INIT/-/-/0x00


(cleanup state object) description: instance enqueue anchor state


latch: 0x2000502c


  ----------------------------------------


  SO: 0x2e3b9bc0, type: 5, owner: 0x2e03465c, flag: INIT/-/-/0x00


  (enqueue) TA-00000006-00000001        DID: 0001-000F-0000000D


  lv: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00  flag: 0x2


  res: 2d8362f4, mode: X, prv: 2d8362fc, own: 0, sess: 0


----------------------------------------


SO: 0x2e0346a0, type: 1, owner: (nil), flag: INIT/-/-/0x00


(cleanup state object) description: switchable channel handle anch


latch: 0x200059cc


  ----------------------------------------


  SO: 0x2d87ac7c, type: 11, owner: 0x2e0346a0, flag: INIT/-/-/0x00


  (broadcast handle) flag: (c2) ACTIVE SUBSCRIBER, owner: (nil),


                     event: 1, last message event: 1,


                     last message waited event: 1, messages read: 0


                     channel: (0x2d8827f0) KPON channel


                              scope: 2, event: 1, last mesage event: 0,


                              publishers/subscribers: 0/1,


                              messages published: 0


----------------------------------------


SO: 0x2e0346e4, type: 1, owner: (nil), flag: INIT/-/-/0x00


(cleanup state object) description: TT shared object cleanup SO


latch: 0x2000dc98


----------------------------------------


SO: 0x2e034728, type: 1, owner: (nil), flag: INIT/-/-/0x00


(cleanup state object) description: SS shared object cleanup SO


latch: 0x2000dfa4


END OF SYSTEM STATE


*** 2011-06-04 05:28:17.743


Received ORADEBUG command 'TRACEFILE_NAME' from process Unix process pid: 27042, p_w_picpath:


*** 2011-06-04 05:32:21.241


Received ORADEBUG command 'close_trace' from process Unix process pid: 27042, p_w_picpath:


[oracle@db2 ~]$


 


 


       如果系统hung的时候，systemstate基本等同于hanganalyze，可以用于诊断system hung


 


关于hanganalyze 参考：


       Oracle HANGANALYZE 功能诊断 DB hanging


       http://blog.csdn.net/tianlesoftware/archive/2011/04/13/6321961.aspx


 


 


2.3.2  获得某个进程状态


SYS@dave2(db2)> oradebug setospid 27028


Oracle pid: 18, Unix process pid: 27028, p_w_picpath: oracledave2@db2


-- 注意，这里必须是Oracle 的进程


SYS@dave2(db2)> oradebug dump processstate 10


Statement processed.


SYS@dave2(db2)> oradebug TRACEFILE_NAME


/u01/app/oracle/admin/dave2/udump/dave2_ora_27028.trc


 


SQL> oradebug setospid 3188




2.3.3 获得进程的错误信息状态


SYS@dave2(db2)> oradebug dump errorstack 3;


Statement processed.


SYS@dave2(db2)> oradebug TRACEFILE_NAME


/u01/app/oracle/admin/dave2/udump/dave2_ora_27028.trc


 


 


三.  Trace  SQL


3.1 Trace a session SQL


3.1.1 使用DBMS_SYSTEM包


SQL>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


 


--开启对该进程的trace，记录在trace文件中:


执行SQL> execute dbms_system.set_sql_trace_in_session(143,6,true);


 


--关闭追踪


执行SQL> execute dbms_system.set_sql_trace_in_session(143,6,false);


 


3.1.2使用oradebug


SQL> oradebug setospid 3260  --进程的spid


SQL> oradebug event 10046 trace name context forever,level 4




-- 取消追踪使用


SQL> oradebug event 10046 trace name context off


已处理的语句


 


3.1.3 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




 


四.  Events 事件


 


关于Events,  eygle 的blog有说明，参考：


http://www.eygle.com/digest/2008/04/oracle_internal_events_introdu.html


 


Events可以在Instance一级Enabled,主要是在init.ora文件中做操作:


        event='event trace name context forever, level level';


 


 一次可以Enable多个事件,可以用以下两种方式:


（1） 用一个冒号隔开


       event = "10248 trace name context forever, level 10:10249 trace name context forever, level 10"


（2） 两个Events分开写


      event="10248 trace name context forever, level 10"


      event="10249 trace name context forever, level 10"


       #一些版本的Oracle，event要一样的大小写


 


instance级别event：


enable:


      SQL>alter system set events 'event trace name context forever, level level';


Disable：


     SQL>alter system set events 'event trace name context off';


 


Session 级别Event：


--Enable:


      SQL>alter session set events 'event trace name context forever, levellevel';


--Disable:


       SQL>alter session set events 'event trace name context off';


 


Oradebug  Events：


--Process中Enable:


SQL>oradebug event event trace name context forever, level level


 


--进程中Enable:


SQL>oradebug setorapid 8(pid进程号)


SQL>oradebug event event trace name context forever, level level


 


 --Disable:


SQL>oradebug event event trace name context off


 


Session Events:


--Enable:


 SQL>oradebug session_event event trace name context forever, level level


--Disable:


SQL>oradebug session_event event trace name context off


 


使用DBMS_SYSTEM.SETEV包来实现Enable和Disable


先从V$session视图中获得SID和Serial#


--Enable：


SQL>execute dbms_system.set_ev(sid,serial#,event,level, '')


SQL>execute dbms_system.set_ev (9,29,10046,8,'');


--Disable则将level改为0


SQL> execute dbms_system.set_ev (9,29,10046,0,'');


 


 


五.  Other Data


From：


http://www.juliandyke.com/Diagnostics/Tools/ORADEBUG/Introduction.html


 


5.1 DUMP command


To perform a dump use


SQL>oradebug dump dumpname level


For example for a level 4 dump of the library cache use


SQL>oradebug setmypid


SQL>oradebug dump library_cache 4


 


5.2 EVENT command


To set an event in a process use


SQL>oradebug event event trace name context forever, level level


For example to set event 10046, level 12 in Oracle process 8 use


SQL>oradebug setorapid 8


SQL>oradebug event 10046 trace name context forever, level 12


 


5.3 SESSION_EVENT command


To set an event in a session use


SQL>oradebug session_event event trace name context forever, level level


For example


SQL>oradebug session_event 10046 trace name context forever, level 12


 


5.4 DUMP SGA


To dump the fixed SGA use


SQL>oradebug dumpsga


 


5.5 DUMPVAR


To dump an SGA variable use


SQL>oradebug dumpvar sga variable_name


e.g.


SQL>oradebug dumpvar sga kcbnhb


       which returns the number of hash buckets in the buffer cache. The names of SGA variables can be found in X$KSMFSV.KSMFSNAM. Variables in this view are suffixed with an underscore e.g.  kcbnhb_


 


5.6 PEEK


To peek memory locations use


SQL>oradebug peek address length


where address can be decimal or hexadecimal and length is in bytes


For example


SQL>ORADEBUG PEEK 0x20005F0C 12


returns 12 bytes starting at location 0x20005f0c


 


5.7 POKE


To poke memory locations use


SQL>ORADEBUG POKE address length value


where address and value can be decimal or hexadecimal and length is in bytes


 


For Example


SQL>ORADEBUG POKE 0x20005F0C 4 0x46495845


SQL>ORADEBUG POKE 0x20005F10 4 0x44205349


SQL>ORADEBUG POKE 0x20005F14 2 0x5A45


 -- WARNING Do not use the POKE command on a production system


 


5.8 IPC


To dump information about operating system shared memory and semaphores configuration use the command


SQL>ORADEBUG IPC


This command does not work on Windows NT or Windows 2000 (Oracle 9.2)


On Solaris, similar information can be obtained using the operating system command


    ipcs -b


 


5.9 Dumping the SGA


In some versions it is possible to dump the entire SGA to a file


Freeze the instance using


SQL>oradebug ffbegin


 


Dump the SGA to a file using


SQL>oradebug sgatofile directory


 


Unfreeze the instance using


SQL>oradebug ffresumeinst