oradebug的使用

最新推荐文章于 2024-08-16 15:23:46 发布

edwzhang

最新推荐文章于 2024-08-16 15:23:46 发布

阅读量3.8k

点赞数

分类专栏： ORACLE 文章标签： events session oracle system db2 command

本文链接：https://blog.csdn.net/edwzhang/article/details/7675515

版权

ORACLE 专栏收录该内容

161 篇文章 1 订阅

订阅专栏

这两天看书，有oradebug的使用。本地验证一下。

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

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> <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             <Inst-List | def | all>   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      <SGA dump dir> Dump & map SGA as COW; dirname in double quotes
MAPCOWSGA      <SGA dump dir>         Map SGA as COW; dirname in double 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
IPC                                      Dump ipc information
UNLIMIT                                  Unlimit the size of the trace file
PROCSTAT                                 Dump process statistics
CALL           <func> [arg1] ... [argn] Invoke function with arguments
SQL>

这个看上去不清楚。从网上摘录吧。

原址如下：

http://blog.csdn.net/tianlesoftware/article/details/6525628

Oracle oradebug 命令使用说明

在之前的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	<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	<text>	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	<Inst-List \| def \| all>	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	<SGA dump dir>	Dump & map SGA as COW; dirname in double quotes
MAPCOWSGA	<SGA dump dir>	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 可列出来。

具体使用，可以参考：

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, 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有说明，参考：

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

============

还有其他文档，一并收录啊。

原址：http://psoug.org/reference/oradebug.html

Oracle ORADEBUG

Version 11.1

GENERAL

Background Processes List

SELECT b.name, p.pid
FROM gv$bgprocess b, gv$process p
WHERE b.paddr = p.addr
ORDER BY 1;

Dispatcher Processes List

SELECT d.name, p.pid
FROM gv$dispatcher d, gv$process p
WHERE d.paddr = p.addr;

Job Queue Process List

SELECT s.paddr, s.sid, j.job
FROM gv$session s, dba_jobs_running j
WHERE s.sid = j.sid;

SELECT pid
FROM gv$process
WHERE addr = '17';

Parallel Execution Slave Processes List

SELECT pid, server_name, status
FROM gv$px_process;

Shared Server Processes List

SELECT s.name, p.pid
FROM gv$shared_server s, gv$process p
WHERE s.paddr = p.addr;

SGA Variables List

SELECT ksmfsnam
FROM x$ksmfsv
WHERE ksmfsnam LIKE '%\_' ESCAPE '\';

Switches

Switch	Format	Description
-G	<Inst-List \| def \| all>	Parallel oradebug command prefix
-R	<Inst-List \| def \| all>	Parallel oradebug prefix return output

CALL

Invoke function with arguments

oradebug call <func> [arg1] ... [argn]

SQL> oradebug call ?

CLOSE_TRACE

Close trace file

oradebug close_trace

SQL> oradebug close_trace

CORE

Dump core without crashing process

oradebug core

SQL> oradebug core

CURRENT_SQL

Get current SQL

oradebug current_sql

SYS	UWCLASS
conn / as sysdba	conn uwclass/uwclass
GRANT execute ON dbms_support TO uwclass;
	SELECT pid FROM gv$process WHERE addr = ( SELECT paddr FROM gv$session WHERE sid = sys.dbms_support.mysid);
oradebug setorapid 26
	SELECT t.tablespace_name FROM all_tables t, all_indexes i WHERE t.tablespace_name = i.tablespace_name;
oradebug core

DELETE

Delete a watchpoint

oradebug delete <local|global|target> watchpoint <id>

SQL> oradebug delete ?

DIRECT_ACCESS

Fixed table access

oradebug direct_access <set/enable/disable command | select query>

TBD

DMPCOWSGA

Dump & map SGA as COW

oradebug dmpcowsga <SGA dump dir>

SQL> oradebug dmpcowsga "c: emp"

DUMP

Invoke named dump

oradebug dump <dump_name> <lvl> [addr]

-- dump_name values can be obtained from oradebug dumplist

SQL> oradebug setmypid
SQL> oradebug dump library_cache 4

Invoke ASH dump

SQL> oradebug setmypid
SQL> oradebug dump ashdump 10

DUMPLIST

Print a list of available dumps

oradebug dumplist

SQL> oradebug dumplist

Available Dumps
ADJUST_SCN	HANGDIAG_HEADER	MMAN_ALLOC_MEMORY
ALRT_TEST	HEAPDUMP	MMAN_CREATE_DEF_REQUEST
ARCHIVE_ERROR	HEAPDUMP_ADDR	MMAN_CREATE_IMM_REQUEST
ASHDUMP	HM_FW_TRACE	MMAN_IMM_REQUEST
ATSK_TEST	INSTANTIATIONSTATE	MMON_TEST
AWR_FLUSH_TABLE_OFF	IOERREMUL	MODIFIED_PARAMETERS
AWR_FLUSH_TABLE_ON	IOERREMULRNG	NEXT_SCN_WRAP
AWR_TEST	IR_FW_TRACE	OBJECT_CACHE
BC_SANITY_CHECK	JAVAINFO	OCR
BG_MESSAGES	KCBO_OBJ_CHECK_DUMP	OLAP_DUMP
BLK0_FMTCHG	KDLIDMP	OPEN_FILES
BUFFER	KRA_OPTIONS	PGA_DETAIL_CANCEL
BUFFERS	KRA_TRACE	PGA_DETAIL_DUMP
CALLSTACK	KRB_BSET_DAYS	PGA_DETAIL_GET
CHECK_ROREUSE_SANITY	KRB_CORRUPT_INTERVAL	PIN_BLOCKS
CONTEXTAREA	KRB_CORRUPT_REPEAT	PIN_RANDOM_BLOCKS
CONTROLF	KRB_CORRUPT_SIZE	POKE_ADDRESS
CROSSIC	KRB_CORRUPT_SPBAD_INTERVAL	POKE_LENGTH
CRS	KRB_CORRUPT_SPBAD_REPEAT	POKE_VALUE
CSS	KRB_CORRUPT_SPBITMAP_INTER	POKE_VALUE0
CURSOR_STATS	KRB_CORRUPT_SPBITMAP_REPEA	POOL_SIMULATOR
CURSORDUMP	KRB_CORRUPT_SPHEADER_INTER	PROCESSSTATE
CURSORTRACE	KRB_CORRUPT_SPHEADER_REPEA	REALFREEDUMP
DATA_ERR_OFF	KRB_FAIL_INPUT_FILENO	RECORD_CALLSTACK
DATA_ERR_ON	KRB_OPTIONS	RECOVERY
DBSCHEDULER	KRB_OVERWRITE_ACTION	REDOHDR
DROP_SEGMENTS	KRB_PIECE_FAIL	REDOLOGS
DUMP_ADV_SNAPSHOTS	KRB_SET_TIME_SWITCH	REFRESH_OS_STATS
DUMP_ALL_COMP_GRANULES	KRB_SIMULATE_NODE_AFFINITY	ROW_CACHE
DUMP_ALL_COMP_GRANULE_ADDR	KRB_TRACE	RULESETDUMP
DUMP_ALL_OBJSTATS	KRB_UNUSED_OPTION	RULESETDUMP_ADDR
DUMP_ALL_REQS	KRBMRSR_LIMIT	SAVEPOINTS
DUMP_PINNED_BUFFER_HISTORY	KRBMROR_LIMIT	SELFTESTASM
DUMP_TEMP	KRC_TRACE	SET_NBLOCKS
DUMP_TRANSFER_OPS	KSDTRADV_TEST	SET_TSN_P1
DUMPGLOBALDATA	KSFQP_LIMIT	SHARED_SERVER_STATE
ENQUEUES	KSKDUMPTRACE	SIMULATE_EOV
ERRORSTACK	KTPR_DEBUG	SYSTEMSTATE
EVENT_TSM_TEST	KSTDUMPALLPROCS	SYSTEMSTATE_GLOBAL
EXCEPTION_DUMP	KSTDUMPALLPROCS_CLUSTER	TEST_DB_ROBUSTNESS
FAILOVER	KSTDUMPCURPROC	TEST_GET_CALLER
FBHDR	KUPPLATCHTEST	TEST_SPACEBG
FBINC	KXFPCLEARSTATS	TEST_STACK_DUMP
FBTAIL	KXFPDUMPTRACE	TR_CRASH_AFTER_WRITE
FILE_HDRS	KXFPBLATCHTEST	TR_CORRUPT_ONE_SIDE
FLASHBACK_GEN	KXFXCURSORSTATE	TR_READ_ONE_SIDE
FLUSH_CACHE	KXFXSLAVESTATE	TR_SET_ALL_BLOCKS
FLUSH_JAVA_POOL	LATCHES	TR_SET_BLOCK
FULL_DUMPS	LDAP_KERNEL_DUMP	TR_SET_SIDE
GC_ELEMENTS	LDAP_USER_DUMP	TRACE_BUFFER_OFF
GES_STATE	LIBRARY_CACHE	TRACE_BUFFER_ON
GLOBAL_AREA	LOCKS	TREEDUMP
HANGANALYZE	LOGERROR	TR_RESET_NORMAL
HANGANALYZE_PROC	LOGHIST	UPDATE_BLOCK0_FORMAT
HANGANALYZE_GLOBAL	LONGF_CREATE	WORKAREATAB_DUMP

DUMPSGA

Dump fixed SGA

oradebug dumpsga

oradebug dumpsga <bytes>

SQL> oradebug dumpsga
c:\oracle\product\admin\orabase\udump\orabase_ora_2120.trc

DUMPTYPE

Print/dump an address with type info

oradebug dumptype <address> <type> <count>

TBD

DUMPVAR

Print/dump a fixed PGA/SGA/UGA variable

oradebug dumpvar <pga|sga|uga> <name> [level]

SQL> oradebug setmypid
SQL> oradebug dumpvar SGA kcbnbh

EVENT

Set trace event in process

oradebug EVENT <event>
TRACE NAME CONTEXT FOREVER, LEVEL <level>

SQL> oradebug setmypid

SQL> oradebug EVENT 10046 TRACE NAME CONTEXT FOREVER, LEVEL 12

SQL> oradebug unlimit

FFBEGIN

Flash Freeze the Instance

oradebug ffbegin

SQL> oradebug ffbegin

FFDEREGISTER

FF deregister instance from cluster

oradebug ffderegistger

SQL> oradebug ffderegister

FFRESUMEINST

Resume the flash frozen instance

oradebug ffresumeinst

SQL> oradebug ffresumeinst

FFSTATUS

Flash freeze status of instance

oradebug ffstatus

SQL> oradebug ffstatus

FFTERMINST

Call exit and terminate instance

oradebug ffterminst

SQL> oradebug ffterminst

FLUSH

Flush the current contents of the trace buffer to the trace file use

oradebug flush

SQL> oradebug flush

HANGANALYZE

Analyze system hang for stand-alone

oradebug hanganalzye [level] [syslevel]

SQL> oradebug setmypid;
SQL> oradebug hanganalyze;
SQL> oradebug flush;

Analyze system hang for RAC

SQL> oradebug setmypid;
SQL> oradebug -g def hanganalyze 1
SQL> oradebug flush;

HELP

Describe one or all commands

oradebug help

oradebug help <command>

SQL> oradebug help
SQL> oradebug help flush

LKDEBUG

Invoke global enqueue service debugger

oradebug lkdebug

SQL> oradebug lkdebug

MAPCOWSGA

Map SGA as COW

oradebug mapcowsga <SGA dump dir>

SQL> oradebug mapcowsga "c: emp"

NSDBX

Invoke CGS name-service debugger

oradebug nsdbx

SQL> oradebug nsdbx

PEEK

Print/Dump memory

oradebug peek <addr> <len> [level]

SQL> oradebug peek 0x20005F0C 12

POKE

Modify memory. Never perform this function on a production database!

oradebug poke <addr> <len> <value>

SQL> oradebug poke 0x20005F0C 4 0x46495845

PROCSTAT

Dump process statistics

oradebug procstat

SQL> oradebug setmypid

SQL> oradebug procstat

RESUME

Resume execution

oradebug resume

SQL> oradebug resume

SESSION_EVENT

Set trace event in session

oradebug session_event <text>

SQL> oradebug session_event 10053 TRACE NAME CONTEXT FOREVER, LEVEL 1

SETINST

Set instance list

oradebug setinst <instance# .. | all>

SQL> oradebug setinst "1"

SETMYPID

Sets the oradebug PID to the current process

oradebug setmypid

SQL> oradebug setmypid

SETORAPID

Set PID of Oracle process to debug

oradebug setorapid <orapid> ['force']

SELECT pid
FROM gv$process
WHERE addr = (
SELECT paddr
FROM gv$session
WHERE sid = DBMS_SUPPORT.MYSID);

or

SELECT pid
FROM gv$process
WHERE addr = (
SELECT paddr
FROM gv$session
WHERE sid = (SELECT sid FROM gv$mystat WHERE ROWNUM = 1));

/*
or one of the processes from the background, dispatcher, job queue, parallel, or shared server process lists
*/

SQL> oradebug setorapid 19

SETOSPID

Set OS pid of process to debug

The operating system process ID is the PID on Unix systems and the thread number for Windows systems

oradebug setospid <ospid>

Do not use as it often fails. Use setorapid instead.

SETORAPNAME

Set Oracle process name to debug

oradebug setorapname <orapname>

TBD

SETVAR

Modify a fixed PGA/SGA/UGA variable

oradebug setvar <pga|sga|uga> <name> <value>

SQL> oradebug setvar SGA kcfdfk 200

SGATOFILE

Dump SGA to file

oradebug sgatofile <SGA dump dir>

SQL> oradebug ffbegin
SQL> oradebug sgatofile "c: emp"
SQL> oradebug ffresumeinst

SHORT_STACK

Get abridged OS stack

oradebug short_stack

SQL> oradebug short_stack

SHOW

Show watchpoints

oradebug show <local|global|target> watchpoint <id>

SQL> oradebug show?

SKDSTTPCS

Helps translate PCs to names

oradebug skdsttpcs <ifname> <ofname>

SQL> oradebug skdsttpcs?

SUSPEND

Suspends the current process

oradebug suspend

SQL> oradebug suspend

TRACEFILE_NAME

Get trace file name

Will not return a value on Windows systems

oradebug tracefile_name

SQL> oradebug tracefile_name

UNLIMIT

Unlimit the size of the trace file

oradebug unlimit

SQL> oradebug unlimit

WAKEUP

Wake up Oracle process

oradebug wakeup <orapid>

SELECT pid
FROM gv$process
WHERE addr = (
SELECT paddr
FROM gv$bgprocess
WHERE name = 'SMON');

SQL> oradebug wakeup 7

WATCH

Watch a region of memory

oradebug watch <address> <len> <self|exist|all|target>

SQL> oradebug watch?

Demo

Taking a heap dump during a large (sorting) query

-- This example requires two sessions, session 1 logged on SYS AS SYSDBA and session 2 which
-- executes the query. In session 2 identify the PID using

SELECT pid
FROM gv$process
WHERE addr IN (
SELECT paddr
FROM gv$session
WHERE sid = dbms_support.mysid);

In this example the PID was 12

In session 1 set the Oracle PID using

ORADEBUG SETORAPID 12

In session 2 start the query

SELECT ... FROM t1 ORDER BY ....

In session 1 suspend session 2

ORADEBUG SUSPEND

The query in session 2 will be suspended

In session 1 run the heap dump

ORADEBUG DUMP HEAPDUMP 1

The heapdump will show the memory structures allocated for the sort. At this point further dumps e.g. subheap dumps can be taken.

In session 1 resume session 2

ORADEBUG RESUME

The query in session 2 will resume execution

再有：

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

Oracle Internal Events Introduction

Introduction:(简介)

有四种类型的Events:

Immediate dumps

Conditional dumps

Trace dumps

Events that change database behaviour

每一个事件都有一个号跟Oracle的错误信息是一样的.如10046和ORA-10046

每一个事件都有一个Level,可以是以下:

范围1到10

位标 0x01 0x02 0x04 0x08 0x10

标识 0=off,1=on

ID号对象ID(object id),内存地址(memory address)

要注意的是,Events在每一个版本之间都有所改变.有一些存在的事件可能存在争议性或者已经不可用了,往往这些事件号会由新的事件所替代掉.也要注意在当前的版本中message file不一定可以反映出Events.

很多Events都会影响数据库的行为,一些测试Events极有可能导致数据库DOWN掉.所以,在没有Oracle Support的前提下,最好不要在PRO系统上做Events操作.DEV系统上如果要做Events最好先做个数据库的全备份.

Enabling Events(Enable事件)

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

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

(红色部分:event指事件号.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要一样的大小写

Events也可以在Instance一级用ALTER SYSTEM命令来Enable:

ALTER SYSTEM SET EVENTS 'event trace name context forever, level level';

　在Instance一级用以下Disable

ALTER SYSTEM SET EVENTS 'event trace name context off';

Events也可以在Session一级用ALTER SESSION命令来Enable:

ALTER SESSION SET EVENTS 'event trace name context forever, level level';

在Session一级用以下命令Disable:

ALTER SESSION SET EVENTS 'event trace name context off';

Events在其他的Session用ORADEBUG来Enable:

在一个Process中实现Enable:

ORADEBUG EVENT event TRACE NAME CONTEXT FOREVER, LEVEL level

在某个进程中Enable:

ORADEBUG SETORAPID 8(PID进程号)

ORADEBUG EVENT event TRACE NAME CONTEXT FOREVER, LEVEL level

以下命令Disable:

ORADEBUG EVENT event TRACE NAME CONTEXT OFF

在Session中实现Enable:

ORADEBUG SESSION_EVENT event TRACE NAME CONTEXT FOREVER, LEVELlevel

在Session中实现Disable:

ORADEBUG SESSION_EVENT event TRACE NAME CONTEXT OFF

Events也可以用DBMS_SYSTEM.SETEV包来实现Enable和Disable

(在做之前要先从V$session视图中获得SID和Serial#)

用以下方式:EXECUTE DBMS_SYSTEM.SET_EV(SID,Serial#,event,level, '')

如EXECUTE dbms_system.set_ev (9,29,10046,8,'');

要Disable则将level改为0,如: EXECUTE dbms_system.set_ev (9,29,10046,0,'');

Listing All Events:(列出所有可用的Events)

大部分的Events number的范围都在10000到10999.可以用以下命令Dump出所有的信息

SET SERVEROUTPUT ON

DECLARE

err_msg VARCHAR2(120);

BEGIN

dbms_output.enable (1000000);

FOR err_num IN 10000..10999

LOOP

err_msg := SQLERRM (-err_num);

IF err_msg NOT LIKE '%Message '||err_num||' not found%' THEN

dbms_output.put_line (err_msg);

END IF;

END LOOP;

END;

在UNIX系统下message文件在底下目录$ORACLE_HOME/rdbms/mesg/oraus.msg

在NT系统下message文件在底下目录$ORACLE_HOME/rdbms/mesg/oraus.msg

Listing Enabled Events(列出Enabled Events)

用以下命令列出在当前Session已经Enabled Events:

SET SERVEROUTPUT ON

DECLARE

l_level NUMBER;

BEGIN

FOR l_event IN 10000..10999

LOOP

dbms_system.read_ev (l_event,l_level);

IF l_level > 0 THEN

dbms_output.put_line ('Event '||TO_CHAR (l_event)||

' is set at level '||TO_CHAR (l_level));

END IF;

END LOOP;

END;

常用Events的参考:(红色的为最常用的而且对DBA比较有用的Events)

Event 10013 - Monitor Transaction Recovery------在Startup时跟踪事务恢复

ALTER SESSION SET EVENTS '10013 trace name context forever, level 1';

Event 10015 - Dump Undo Segment Headers----在事务恢复后做Dump回退段头信息

ALTER SESSION SET EVENTS '10015 trace name context forever, level 1';

Event 10032 - Dump Sort Statistics----Dump排序的统计信息,level 10是最详细的

ALTER SESSION SET EVENTS '10032 trace name context forever, level 10';

Event 10033 - Dump Sort Intermediate Run Statistics--level 10

ALTER SESSION SET EVENTS '10033 trace name context forever, level 10';

Event 10045 - Trace Free List Management Operations--跟踪Freelist

ALTER SESSION SET EVENTS '10045 trace name context forever, level 1';

Event 10046 - Enable SQL Statement Trace---跟踪SQL,有执行计划,邦定变量和等待的统计信息,level 12最详细

ALTER SESSION SET EVENTS '10046 trace name context forever, level 12';

级别level参考如下图:

Level	Action
1	Print SQL statements, execution plans and execution statistics
4	As level 1 plus bind variables
8	As level 1 plus wait statistics
12	As level 1 plus bind variables and wait statistics

Event 10053 - Dump Optimizer Decisions---在分析SQL语句时,Dump出优化器所做的选择,级别level 1最详细

ALTER SESSION SET EVENTS '10053 trace name context forever, level 1';

参考level:

Level	Action
1	Print statistics and computations
2	Print computations only

Event 10060 - Dump Predicates---(参考http://www.juliandyke.com/)

Event 10065 - Restrict Library Cache Dump Output for State Object Dumps-(参考http://www.juliandyke.com/)

Event 10079 - Dump SQL*Net Statistics---Dump SQL*NeT的统计信息

ALTER SESSION SET EVENTS '10079 trace name context forever, level 2';

Event 10081 - Trace High Water Mark Changes--跟踪HWM的改变

ALTER SESSION SET EVENTS '10081 trace name context forever, level 1';

Event 10104 - Dump Hash Join Statistics--Dump HASH JOIN的统计信息,用level 10

ALTER SESSION SET EVENTS '10104 trace name context forever, level 10';

Event 10128 - Dump Partition Pruning Information--Dump分区表信息

ALTER SESSION SET EVENTS '10128 trace name context forever, levellevel';

Level参考

Level	Action
0x0001	Dump pruning descriptor for each partitioned object
0x0002	Dump partition iterators
0x0004	Dump optimizer decisions about partition-wise joins
0x0008	Dump ROWID range scan pruning information

在9.0.1或者后面的版本,在level 2后还需要建立如下的表:

CREATE TABLE kkpap_pruning
　　 (
　　 partition_count NUMBER,
　　 iterator VARCHAR2(32),
　　 partition_level VARCHAR2(32),
　　 order_pt VARCHAR2(12),
　　 call_time VARCHAR2(12),
　　 part# NUMBER,
　　 subp# NUMBER,
　　 abs# NUMBER
　　 );

Event 10200 - Dump Consistent Reads---Dump出一致读的信息
　　 ALTER SESSION SET EVENTS '10200 trace name context forever, level 1';
　　Event 10201 - Dump Consistent Read Undo Application
　　 ALTER SESSION SET EVENTS '10201 trace name context forever, level 1';
　　Event 10220 - Dump Changes to Undo Header--Dump出Undo头信息的改变
　　 ALTER SESSION SET EVENTS '10220 trace name context forever, level 1';
　　Event 10221 - Dump Undo Changes--Dump Undo的改变
　　 ALTER SESSION SET EVENTS '10221 trace name context forever, level 7';
　　Event 10224 - Dump Index Block Splits / Deletes--Dump索引块的Split和Delete信息
　　 ALTER SESSION SET EVENTS '10224 trace name context forever, level 1';
　　Event 10225 - Dump Changes to Dictionary Managed Extents---Dump出在Row Cache中,字典管理的Extents的改变
　　 ALTER SESSION SET EVENTS '10225 trace name context forever, level 1';
　　Event 10231--设置在做全表扫描时跳过损坏的数据块（在做EXP时，如果有坏块，

设置这个可以使EXP导出时跳过坏块，使得部分数据能可以使用）
　　 ALTER SYSTEM SET EVENTS '10231 trace name context forever,level 10';
　　Event 10241 - Dump Remote SQL Execution--Dump远程SQL语句的执行信息
　　 ALTER SESSION SET EVENTS '10241 trace name context forever, level 1';
　　Event 10246 - Trace PMON Process---只能在init.ora中做,不能用ALTER SYSTEM做
　　 event = "10246 trace name context forever, level 1"
　　Event 10248 - Trace Dispatcher Processes---在init.ora中做(9iDump到udump目录中)
　　 event = "10248 trace name context forever, level 10"
　　Event 10249 - Trace Shared Server (MTS) Processes---在init.ora中做(9iDump到udump目录中)
　　 event = "10249 trace name context forever, level 10"
　　Event 10270 - Debug Shared Cursors
　　event = "10270 trace name context forever, level 10"
　　Event 10299 - Debug Prefetching---(参考http://www.juliandyke.com/)
　　 event = "10299 trace name context forever, level 1"
　　Event 10357 - Debug Direct Path---(参考http://www.juliandyke.com/)
　　 ALTER SESSION SET EVENTS '10357 trace name context forever, level 1';
　　Event 10390 - Dump Parallel Execution Slave Statistics--(参考http://www.juliandyke.com/)
　　 ALTER SESSION SET EVENTS '10390 trace name context forever, level level';
　　Event 10391-Dump Parallel Execution Granule Allocation---
　　 (参考http://www.juliandyke.com/)
　　 ALTER SESSION SET EVENTS '10391 trace name context forever, level level';
　　Event 10393 - Dump Parallel Execution Statistics--(参考http://www.juliandyke.com/)
　　 ALTER SESSION SET EVENTS '10393 trace name context forever, level 1';
　　Event 10500 - Trace SMON Process--在init.ora中做
　　 event = "10500 trace name context forever, level 1"
　　Event 10608 - Trace Bitmap Index Creation--跟踪二位图索引
　　 ALTER SESSION SET EVENTS '10608 trace name context forever, level 10';
　　Event 10704 - Trace Enqueues--跟踪队列
　　 ALTER SESSION SET EVENTS '10704 trace name context forever, level 1';
　　Event 10706 - Trace Global Enqueue Manipulation-(参考http://www.juliandyke.com/)
　　 ALTER SESSION SET EVENTS '10706 trace name context forever, level 1';
　　Event 10708 - Trace RAC Buffer Cache--跟踪RAC的Buffer Cache
　　 ALTER SESSION SET EVENTS '10708 trace name context forever, level 10';
　　Event 10710 - Trace Bitmap Index Access--(参考http://www.juliandyke.com/)
　　 ALTER SESSION SET EVENTS '10710 trace name context forever, level 1';
　　Event 10711 - Trace Bitmap Index Merge Operation--
　　 ALTER SESSION SET EVENTS '10711 trace name context forever, level 1';
　　Event 10712 - Trace Bitmap Index OR Operation--
　　 ALTER SESSION SET EVENTS '10712 trace name context forever, level 1';
　　Event 10713 - Trace Bitmap Index AND Operation--
　　 ALTER SESSION SET EVENTS '10713 trace name context forever, level 1';
　　Event 10714 - Trace Bitmap Index MINUS Operation--
　　 ALTER SESSION SET EVENTS '10714 trace name context forever, level 1';
　　Event 10715 - Trace Bitmap Index Conversion to ROWIDs Operation--
　　 ALTER SESSION SET EVENTS '10715 trace name context forever, level 1';
　　Event 10716 - Trace Bitmap Index Compress/Decompress--
　　 ALTER SESSION SET EVENTS '10716 trace name context forever, level 1';
　　Event 10717 - Trace Bitmap Index Compaction--
　　 ALTER SESSION SET EVENTS '10717 trace name context forever, level 1';
　　Event 10719 - Trace Bitmap Index DML--
　　 ALTER SESSION SET EVENTS '10719 trace name context forever, level 1';
　　Event 10730 - Trace Fine Grained Access Predicates--
　　 ALTER SESSION SET EVENTS '10730 trace name context forever, level 1';
　　Event 10731 - Trace CURSOR Statements--
　　 ALTER SESSION SET EVENTS '10731 trace name context forever, level level';
　　Levels are:
　　Level　　Action
　　1　　      Print parent query and subquery
　　2    　　   Print subquery only
　　Event 10928 - Trace PL/SQL Execution--
　　 ALTER SESSION SET EVENTS '10928 trace name context forever, level 1';
　　Event 10938 - Dump PL/SQL Execution Statistics--
　　 ALTER SESSION SET EVENTS '10938 trace name context forever, level 1';
　　一些其他的Events：
　　ALTER SESSION SET EVE