在之前的HangAnalyze 中有使用oradebug指令，在這篇文章裡，我們主要是重點看一下這個oradebug指令：
Oracle HANGANALYZE 功能診斷 DB hanging
一. Oradebug 指令 幫助文檔
SYS@dave2(db2)> oradebug help
Command
Arguments
Description
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
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
Set trace event in session
DUMPVAR
<p|s|uga> <name> [level]
Print/dump a fixed PGA/SGA/UGA variable
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
LKDEBUG
Invoke global enqueue service debugger
NSDBX
Invoke CGS name-service debugger
-G
<Inst-List | def | all>
Parallel oradebug command prefix
-R
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
Dump & map SGA as COW; dirname in double quotes
MAPCOWSGA
Map SGA as COW; dirname in double quotes
HANGANALYZE
[level]
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
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, image: oracledave2@db2
或者使用，他們是一樣的：
SYS@dave2(db2)> oradebug setorapid 18 --根據Oracle ID
Unix process pid: 27028, image: oracledave2@db2
2.3 dump 相關檔案資訊
指定為SID 之後，就可以使用dump 将相關的資訊，這些dump 内容很多。 可以使用dumplist 把所有的dump 可列出來。
具體使用，可以參考：
SYS@dave2(db2)> oradebug dumplist
EVENTS
TRACE_BUFFER_ON
TRACE_BUFFER_OFF
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
SYS@dave2(db2)> oradebug unlimit
Statement processed.
SYS@dave2(db2)> oradebug dump systemstate 10
SYS@dave2(db2)> oradebug TRACEFILE_NAME
/u01/app/oracle/admin/dave2/udump/dave2_ora_27028.trc
[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, image: 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, image:
*** 2011-06-04 05:32:21.241
Received ORADEBUG command 'close_trace' from process Unix process pid: 27042, image:
[oracle@db2 ~]$
如果系統hung的時候，systemstate基本等同于hanganalyze，可以用于診斷system hung
關于hanganalyze 參考：
2.3.2 獲得某個程序狀态
SYS@dave2(db2)> oradebug setospid 27028
-- 注意，這裡必須是Oracle 的程序
SYS@dave2(db2)> oradebug dump processstate 10
SYS@dave2(db2)> oradebug TRACEFILE_NAME
SQL> oradebug setospid 3188
2.3.3 獲得程序的錯誤資訊狀态
SYS@dave2(db2)> oradebug dump errorstack 3;
三. 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有說明，參考：
<a href="http://www.eygle.com/digest/2008/04/oracle_internal_events_introdu.html">http://www.eygle.com/digest/2008/04/oracle_internal_events_introdu.html</a>
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程序号)
--Disable:
SQL>oradebug event event trace name context off
Session Events:
SQL>oradebug session_event event trace name context forever, level level
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：
<a href="http://www.juliandyke.com/Diagnostics/Tools/ORADEBUG/Introduction.html">http://www.juliandyke.com/Diagnostics/Tools/ORADEBUG/Introduction.html</a>
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
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
SQL>ORADEBUG PEEK 0x20005F0C 12
returns 12 bytes starting at location 0x20005f0c
5.7 POKE
To poke memory locations use
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
![龍珠訓練營task04[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)