oracle工具 + 生產力：oradebug

    1 oradebug介紹

 

               oradebug主要是給oracle技術服務人員使用的，儘管很早便有，但oracle官網很少有記載。他是個sql*plus命令列工具，有sysdba的許可權就可以登入，無需特別設定。他可以被用於：

                           1）追蹤進程，或者是你的，或者是外部的

                           2）確定進程往哪個trc檔案寫

                           3）轉儲：資料檔案頭、內部oracle結構等

                           4）掛起進程，暫時的

                           5）確定執行個體使用了哪些共用記憶體塊和訊號量

                           6）找出RAC執行個體使用了哪些互聯地址和協議

                           7）修改SGA中的資料結構

 

    2 oradebug使用步驟

 

                  1）啟動sql*plus並以sysdba身份登入

                  2）串連到一個進程

                  3）設定一個事件或者進行診斷轉儲

                  4）檢索trc檔案名稱

                  5）與串連到的進程斷開

 

    3 oradebug命令介紹

sys@ORCL> oradebug helpHELP           [command]                 Describe one or all commandsSETMYPID                                 Debug current processSETOSPID       <ospid>                   Set OS pid of process to debugSETORAPID      <orapid> ['force']        Set Oracle pid of process to debugSHORT_STACK                              Dump abridged OS stackDUMP           <dump_name> <lvl> [addr]  Invoke named dumpDUMPSGA        [bytes]                   Dump fixed SGADUMPLIST                                 Print a list of available dumpsEVENT          <text>                    Set trace event in processSESSION_EVENT  <text>                    Set trace event in sessionDUMPVAR        <p|s|uga> <name> [level]  Print/dump a fixed PGA/SGA/UGA variableDUMPTYPE       <address> <type> <count>  Print/dump an address with type infoSETVAR         <p|s|uga> <name> <value>  Modify a fixed PGA/SGA/UGA variablePEEK           <addr> <len> [level]      Print/Dump memoryPOKE           <addr> <len> <value>      Modify memoryWAKEUP         <orapid>                  Wake up Oracle processSUSPEND                                  Suspend executionRESUME                                   Resume executionFLUSH                                    Flush pending writes to trace fileCLOSE_TRACE                              Close trace fileTRACEFILE_NAME                           Get name of trace fileLKDEBUG                                  Invoke global enqueue service debuggerNSDBX                                    Invoke CGS name-service debugger-G             <Inst-List | def | all>   Parallel oradebug command prefix-R             <Inst-List | def | all>   Parallel oradebug prefix (return outputSETINST        <instance# .. | all>      Set instance list in double quotesSGATOFILE      <SGA dump dir>         Dump SGA to file; dirname in double quotesDMPCOWSGA      <SGA dump dir> Dump & map SGA as COW; dirname in double quotesMAPCOWSGA      <SGA dump dir>         Map SGA as COW; dirname in double quotesHANGANALYZE    [level] [syslevel]        Analyze system hangFFBEGIN                                  Flash Freeze the InstanceFFDEREGISTER                             FF deregister instance from clusterFFTERMINST                               Call exit and terminate instanceFFRESUMEINST                             Resume the flash frozen instanceFFSTATUS                                 Flash freeze status of instanceSKDSTTPCS      <ifname>  <ofname>        Helps translate PCs to namesWATCH          <address> <len> <self|exist|all|target>  Watch a region of memoryDELETE         <local|global|target> watchpoint <id>    Delete a watchpointSHOW           <local|global|target> watchpoints        Show  watchpointsCORE                                     Dump core without crashing processIPC                                      Dump ipc informationUNLIMIT                                  Unlimit the size of the trace filePROCSTAT                                 Dump process statisticsCALL           <func> [arg1] ... [argn]  Invoke function with arguments

 

    4 串連到一個進程

 

                    在oradebug命令執行之前，你必須加入一個目標進程。這個目標進程有如下3種情況：

命令	目的
oradebug   setmypid	串連到為你的sql*plus提供服務的進程
oradebug  setorapid   pid	串連到一個外部服務進程，且pid=v$process.pid
oradebug  setospid  spid	串連到一個外部服務進程，且spid=v$process.pid

 

                      1)
setmypid

sys@ORCL> oradebug setmypidStatement processed.sys@ORCL> oradebug unlimitStatement processed.sys@ORCL> oradebug event 10046 trace name context forever,level 8 Statement processed.sys@ORCL> select * from hr.departments where department_id=10;DEPARTMENT_ID DEPARTMENT_NAME                MANAGER_ID LOCATION_ID------------- ------------------------------ ---------- -----------           10 Administration                        200        1700sys@ORCL> oradebug tracefile_name/u01/app/oracle/admin/orcl/udump/orcl_ora_5234.trcsys@ORCL> oradebug event 10046 trace name context offStatement processed.

 

                          2）setospid（spid操作）

sys@ORCL> host toptop - 15:14:20 up 47 min,  2 users,  load average: 0.22, 0.20, 0.26Tasks: 171 total,   2 running, 168 sleeping,   0 stopped,   1 zombieCpu(s):  1.3%us,  1.3%sy,  0.0%ni, 97.4%id,  0.0%wa,  0.0%hi,  0.0%si,  0.0%stMem:    678912k total,   671192k used,     7720k free,    16672k buffersSwap:        0k total,        0k used,        0k free,   401584k cached  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND                                     5874 oracle    15   0  2444 1084  828 R  1.6  0.2   0:00.47 top                                         4781 root      15   0 53900  11m 6076 S  0.7  1.7   1:00.13 Xorg                                        5168 oracle    16   0  491m  17m  15m S  0.3  2.6   0:02.41 oracle                                     sys@ORCL> oradebug setospid 5168Oracle pid: 7, Unix process pid: 5168, image: oracle@localhost.localdomain (CKPT)

                       3）setorapid（pid操作）

sys@ORCL> select sid from v$mystat where rownum=1;                       SID----------       159sys@ORCL> select pid,spid from v$process p,v$session s where s.sid=159 and s.paddr=p.addr;       PID SPID---------- ------------        15 5234sys@ORCL> oradebug setorapid 15Statement processed.sys@ORCL> oradebug tracefile_name/u01/app/oracle/admin/orcl/udump/orcl_ora_5234.trcsys@ORCL> oradebug event 10046 trace name context offStatement processed.

                    注意，在OEM的top session也可以等價實現上述效果，即：確定哪些資料庫會話消耗了較多的資源

 

    5 oradebug IPC

                 查看共用資訊的記憶體的命令是ipcs [-m|-s|-q]。預設會列出共用記憶體、訊號量，隊列資訊，-m列出共用記憶體，-s列出共用訊號量，-q列出共用隊列

sys@ORCL> host ipcs -m | grep ora0xbe3bb918 5439503    oracle    640        419438592  20     sys@ORCL> oradebug setmypidStatement processed.sys@ORCL> oradebug ipcInformation written to trace file.sys@ORCL> oradebug tracefile_name/u01/app/oracle/admin/orcl/udump/orcl_ora_5234.trcsys@ORCL> oradebug event 10046 trace name context offStatement processed.

                  當一台電腦的資源有限時，失敗的執行個體佔用共用記憶體和訊號量會導致資源緊張，從而使得新的oracle執行個體無法啟動。

                  清除命令是ipcrm [-m|-s|-q] id。
                  -m 刪除共用記憶體，-s刪除共用訊號量，-q刪除共用隊列
                  如：ipcrm -m 5439503   

 

    6 oradebug SHORT_STACK

 

                 如果一個程式掛起，那麼程式調用棧就可以顯示它在調用路徑中的哪一步掛起

sys@ORCL> oradebug setospid 5180Oracle pid: 13, Unix process pid: 5180, image: oracle@localhost.localdomain (D000)sys@ORCL> oradebug short_stackksdxfstk()+19<-ksdxcb()+1321<-sspuser()+81<-__kernel_vsyscall()+2<-ntevpque()+112<-ntevque()+606<-nsevwait()+821<-ksnwait()+111<-ksliwat()+770<-kslwaitns()+29<-kskthbwt()+370<-kslwait()+57<-kmdmai()+1797<-kmmrdp()+432<-opirip()+1191<-opidrv()+864<-sou2o()+113<-opimai_real()+353<-main()+111<-__libc_start_main()+220sys@ORCL> oradebug event 10046 trace name context offStatement processed.

                       當前的調用步驟在輸出的頂部

 

    7 診斷轉儲

 

                可轉儲類型如下：

sys@ORCL> oradebug dumplistEVENTSTRACE_BUFFER_ONTRACE_BUFFER_OFFHANGANALYZELATCHESPROCESSSTATESYSTEMSTATEINSTANTIATIONSTATE.........

               下面舉例兩個：

                 1）controlf

sys@ORCL> oradebug setmypidStatement processed.sys@ORCL> oradebug dump controlf 10Statement processed.sys@ORCL> oradebug tracefile_name/u01/app/oracle/admin/orcl/udump/orcl_ora_5234.trc

                2）events

                    這不是真正的轉儲，events只是將啟用的事件寫入trc檔案。如果你不能確定一個會話、進程或者執行個體中哪個事件是活動的，下面有一種很好的方式去查詢：

sys@ORCL> alter session set events '10046 trace name context forever,level 8';Session altered.sys@ORCL> alter session set events '4031 trace name heapdump level 3';Session altered.sys@ORCL> oradebug setmypidStatement processed.sys@ORCL> oradebug dump events 1Statement processed.sys@ORCL> oradebug tracefile_name/u01/app/oracle/admin/orcl/udump/orcl_ora_5234.trcsys@ORCL> oradebug event 10046  trace name context offStatement processed.

                                    oradebug dump events的層級和範圍

層級	事件範圍	使用的命令
1	會話	alter session
2	進程	oradebug  event
4	執行個體	alter system

 

    8 小結

                   oradebug對於解決問題和診斷效能是必不可少的工具。本文只是講述了一些最常見的命令，還有更多的功能，比如RAC環境下調用全域佇列服務調試器的

    oradebug lkdebug命令。