Oracle oradebug 命令 使用说明

原创 2011年06月04日 00:38:00

 

       在之前的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 IDspid 是系统的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选项中,大部分都有24681012等几个跟踪级别。在使用的时候要根据具体的情况来选择级别,不同级别的影响不一样。

 

 

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"

       #一些版本的Oracleevent要一样的大小写

 

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

--ProcessEnable:

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包来实现EnableDisable

先从V$session视图中获得SIDSerial#

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

 

 

 

 

 

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

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   1 oradebug介绍                oradebug主要是给oracle支持人员使用的,尽管很早便有,但oracl...
  • haiross
  • haiross
  • 2014年04月16日 16:54
  • 3842

11.未公开的Oracle数据库秘密笔记——ORADEBUG使用

欢迎转载,转载请标明出处: 11.未公开的Oracle数据库秘密笔记——ORADEBUG使用 ORADEBUG是诊断性能和挂起问题的有效工具。能够合适正确的IP地址,帮助实时应用集群实例间的通信。11...
  • notbaron
  • notbaron
  • 2015年12月20日 22:27
  • 30371

使用oradebug增进scn

http://blog.itpub.net/29154652/viewspace-774532/ 使用oradebug增进scn,网上案例很多,这里自己测试了下,发布上来。 首先来看下命令...

oracle实用工具:oradebug

1 oradebug介绍                  oradebug主要是给oracle支持人员使用的,尽管很早便有,但oracle官网很少有记载。他是个sql*plus命令行工具,有sy...

oracle 之oradebug(抛砖引玉)

oracle 之oradebug(抛砖引玉)     今天是2013-07-15,今天上午在看书的时候,发现如下问题: SYS@orcl#oradebug setmypid 已处理的语句 SY...

oradebug的使用

这两天看书,有oradebug的使用。本地验证一下。   Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0....

oradebug的简单介绍及应用

FROM:http://duqiangatom.blog.163.com/blog/static/11502518201061392857916/自ORACLE7起,ORADEBUG工具就开始被DBA...

Oracle errorstack 工具 说明

        Errorstack 和 oradebug 命令类似,都是帮助我们诊断错误信息。有关oradebug 参考我的Blog:       Oracle oradebug 命令 使用说明  ...

oradebug 推进SCN

1 linux 32位 [oracle@lx01 ~]$ uname -a Linux lx01 2.6.18-194.el5 #1 SMP Tue Mar 16 21:52:43 EDT 2010 ...

解决Oracle临时表空间占满的问题

 正常来说,在完成Select语句、create index等一些使用TEMP表空间的排序操作后,Oracle是会自动释放掉临时段a的。但有些有侯我们则会遇到临时段没有被释放,TEMP表空间几乎满的状...
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:Oracle oradebug 命令 使用说明
举报原因:
原因补充:

(最多只允许输入30个字)