在之前的HangAnalyze 中有使用oradebug命令，在這篇文章裡，我們主要是重點看一下這個oradebug命令：

Oracle HANGANALYZE 功能診斷 DB hanging

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

一. Oradebug 命令幫助文件

SYS@dave2(db2)> oradebug help

Command	Arguments	Description
HELP	[command]	Describe one or all commands
SETMYPID		Debug current process
SETOSPID		Set OS pid of process to debug
SETORAPID	['force']	Set Oracle pid of process to debug
DUMP	[addr]	Invoke named dump
DUMPSGA	[bytes]	Dump fixed SGA
DUMPLIST		Print a list of available dumps
EVENT		Set trace event in process
SESSION_EVENT		Set trace event in session
DUMPVAR	[level]	Print/dump a fixed PGA/SGA/UGA variable
SETVAR		Modify a fixed PGA/SGA/UGA variable
PEEK	[level]	Print/Dump memory
POKE		Modify memory
WAKEUP		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		Parallel oradebug command prefix
-R		Parallel oradebug prefix (return output)
SETINST		Set instance list in double quotes
SGATOFILE		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		Helps translate PCs to names
WATCH		Watch a region of memory
DELETE	watchpoint	Delete a watchpoint
SHOW	watchpoints	Show watchpoints
CORE		Dump core without crashing process
UNLIMIT		Unlimit the size of the trace file
PROCSTAT		Dump process statistics
CALL	[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

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, image: 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, 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 參考：

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, image: 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有說明，參考：

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：

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

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

Blog： http://blog.csdn.net/tianlesoftware

Email: dvd.dba@gmail.com

DBA1 群：62697716(滿); DBA2 群：62697977(滿) DBA3 群：62697850(滿)

DBA 超級群：63306533(滿); DBA4 群： 83829929 DBA5群： 142216823

DBA6 群：158654907 聊天群：40132017 聊天2群：69087192

--加群需要在備註說明Oracle表空間和資料檔案的關係，否則拒絕申請

Oracle oradebug 命令使用說明

相關文章

Oracle oradebug 命令 使用說明

相關文章

Oracle oradebug 命令使用說明