Oradebug 学习

 

说明

 oradebug主要是给oracle支持人员使用的，尽管很早便有，但oracle官网很少有记载。他是个sql*plus命令行工具，有sysdba的权限就可以登入，无需特别设置。他可以被用于：

1）追踪进程，或者是你的，或者是外部的

2）确定进程往哪个trc文件写

3）转储：数据文件头、内部oracle结构等

4）挂起进程，暂时的

5）确定实例使用了哪些共享内存块和信号量

6）找出RAC实例使用了哪些互联地址和协议

7）修改SGA中的数据结构

 

 

SQL> oradebughelp

HELP           [command]                 Describe one or all commands

SETMYPID                                 Debug currentprocess

SETOSPID       <ospid>                   Set OS pid of process todebug

SETORAPID      <orapid> ['force']        Set Oracle pid of process to debug

SETORAPNAME    <orapname>                Set Oracle process name todebug

SHORT_STACK                              Get abridged OSstack

CURRENT_SQL                              Get current SQL

DUMP           <dump_name> <lvl>[addr]  Invoke named dump

PDUMP          [interval=<interval>]     Invoke named dump periodically

               [ndumps=<count>]  <dump_name> <lvl> [addr]

DUMPSGA        [bytes]                   Dump fixed SGA

DUMPLIST                                 Print a listof 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/UGAvariable

DUMPTYPE       <address> <type><count>  Print/dump an address withtype info

SETVAR         <p|s|uga> <name><value>  Modify a fixed PGA/SGA/UGAvariable

PEEK           <addr> <len>[level]      Print/Dump memory

POKE           <addr> <len><value>      Modify memory

WAKEUP         <orapid>                  Wake up Oracle process

SUSPEND                                  Suspendexecution

RESUME                                   Resumeexecution

FLUSH                                    Flushpending writes to trace file

CLOSE_TRACE                              Close trace file

TRACEFILE_NAME                           Get name of tracefile

SETTRACEFILEID<identifier name>         Settracefile identifier

LKDEBUG                                  Invoke globalenqueue service debugger

NSDBX                                    Invoke CGSname-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 doublequotes

DMPCOWSGA      <SGA dump dir> Dump & map SGAas COW; dirname in double quotes

MAPCOWSGA      <SGA dump dir>         Map SGA as COW; dirname in doublequotes

HANGANALYZE    [level] [syslevel]        Analyze system hang

FFBEGIN                                  Flash Freezethe Instance

FFDEREGISTER                             FF deregisterinstance from cluster

FFTERMINST                               Call exit andterminate instance

FFRESUMEINST                             Resume the flashfrozen instance

FFSTATUS                                 Flash freezestatus of instance

SKDSTTPCS      <ifname>  <ofname>        Helps translate PCs to names

WATCH          <address> <len><self|exist|all|target>  Watch aregion of memory

DELETE         <local|global|target> watchpoint<id>    Delete a watchpoint

SHOW           <local|global|target>watchpoints        Show  watchpoints

DIRECT_ACCESS  <set/enable/disable command | selectquery> Fixed table access

IPC                                      Dump ipcinformation

UNLIMIT                                  Unlimit thesize of the trace file

CALL           [-t count] <func>[arg1]...[argn]  Invoke function witharguments

CORE                                     Dump corewithout crashing process

PROCSTAT                                 Dump processstatistics

 

 

 

 

使用方法举例

【获得 当前实例的trace文件】

SQL> oradebugsetmypid

Statement processed.

SQL>oradebug tracefile_name  ---获得文件名称

/u01/app/oracle/diag/rdbms/tune/tune/trace/tune_ora_6877.trc

 

【获得非当前进程的trace】

selectpid,spid,username from v$process;

v$process 下的pid 是Oracle 的ID。spid 是系统的ID。

 

selecta.username,a.sid ,a.serial#,b.spid  fromv$session a,v$process b where a.paddr=b.addr;

v$session下的sid是指session id，b.spid是指系统ID。

 

 

oradebug setospid  --跟踪系统进程

oradebug setorapid --跟踪ORACLE进程

SQL> oradebugsetospid 6150

Oracle pid: 44, Unixprocess pid: 6150, image: oracle@dgdemo1

SQL> oradebugtracefile_name

/u01/app/oracle/diag/rdbms/tune/tune/trace/tune_ora_6150.trc

SQL> oradebugsetorapid 44

Oracle pid: 44, Unixprocess pid: 6150, image: oracle@dgdemo1

SQL> oradebugtracefile_name

/u01/app/oracle/diag/rdbms/tune/tune/trace/tune_ora_6150.trc

可以看到 文件名称都是一样的

 

 

【用oradebug做session级10046或10053】

oradebugsetmypid  ---跟踪当前的进程

oradebug unlimit        ---取消对trace文件大小的限制

oradebug session_event 10046 trace name context forever ,level4  --开启10046事件跟踪

oradebug tracefile_name ---查看trace的路径

oradebug event 10046 trace name context off --关闭10046事件跟踪

oradebug session_event 10053 trace name context forever ,level4  ---开启10053事件跟踪

oradebug event 10053 trace name context off --关闭10053事件跟踪

 

 

SQL> oradebugsetmypid

Statement processed.

SQL>  oradebug unlimit

Statement processed.

SQL> oradebugsession_event 10046 trace name context forever ,level 4

Statement processed.

SQL> oradebugtracefile_name

/u01/app/oracle/diag/rdbms/tune/tune/trace/tune_ora_11602.trc

SQL>  !ls -lrt/u01/app/oracle/diag/rdbms/tune/tune/trace/tune_ora_11602.trc

-rw-r----- 1 oracle asmadmin 1629 12月 1516:56 /u01/app/oracle/diag/rdbms/tune/tune/trace/tune_ora_11602.trc

SQL> oradebugevent 10046 trace name context off

Statement processed.

SQL> !ls -lrt/u01/app/oracle/diag/rdbms/tune/tune/trace/tune_ora_11602.trc

-rw-r----- 1 oracle asmadmin 1829 12月 1516:57 /u01/app/oracle/diag/rdbms/tune/tune/trace/tune_ora_11602.trc

 

 

【用oradebug做oracle process级10046】

跟踪oracle ID 14，系统进程6150的10046是事件情况

SQL>  select a.username,a.sid,a.serial#,b.spid  from v$sessiona,v$process b where a.paddr=b.addr;

SOE                                    11          3 6150

 

SQL>  select pid,spid,username from v$process;

        44 6150                     oracle

 

oradebug setorapid44

oradebug unlimit

oradebug event 10046trace name context forever ,level 4

oradebug event 10046trace name context off

oradebugtracefile_name

 

SQL> oradebugsetorapid 44

Oracle pid: 44, Unixprocess pid: 6150, image: oracle@dgdemo1

SQL> oradebugevent 10046 trace name context forever ,level 4

Statement processed.

SQL> oradebugtracefile_name

/u01/app/oracle/diag/rdbms/tune/tune/trace/tune_ora_6150.trc

SQL> oradebugevent 10046 trace name context off

Statement processed.

SQL> !ls -lrt/u01/app/oracle/diag/rdbms/tune/tune/trace/tune_ora_6150.trc

-rw-r----- 1 oracle asmadmin 707546 12月 1517:19 /u01/app/oracle/diag/rdbms/tune/tune/trace/tune_ora_6150.trc

 

 

 

 

【radebug系统hang住原因分析】

oradebug setmypid

oradebug unlimit

oradebugsetinst all   --RAC环境

oradebug hanganalyze 3  -- 级别一般指定为3足够了

oradebug-g def dump systemstate 10  --RAC环境

oradebugtracefile_name

 

 

SQL> oradebugsetmypid

Statement processed.

SQL> oradebugunlimit

Statement processed.

SQL> oradebughanganalyze 3

Hang Analysis in/u01/app/oracle/diag/rdbms/tune/tune/trace/tune_ora_12457.trc

SQL> !ls -lrt/u01/app/oracle/diag/rdbms/tune/tune/trace/tune_ora_12457.trc

-rw-r----- 1 oracle asmadmin 5547 12月 1517:20 /u01/app/oracle/diag/rdbms/tune/tune/trace/tune_ora_12457.trc

 

SQL> !ls -lrt/u01/app/oracle/diag/rdbms/tune/tune/trace/tune_ora_12457.trc

-rw-r----- 1 oracle asmadmin 5547 12月 1517:20 /u01/app/oracle/diag/rdbms/tune/tune/trace/tune_ora_12457.trc

 

SQL> oradebugtracefile_name

/u01/app/oracle/diag/rdbms/tune/tune/trace/tune_ora_12457.trc

SQL> exit

 

 

 

 

【获取某进程的状态信息】  必须是Oracle 的进程

oradebug setospid6150

oradebug dumpprocessstate 10

oradebugtracefile_name

 

SQL> oradebugsetospid 6150

Oracle pid: 44, Unixprocess pid: 6150, image: oracle@dgdemo1

SQL> oradebugdump processstate 10

Statement processed.

SQL> oradebugtracefile_name

/u01/app/oracle/diag/rdbms/tune/tune/trace/tune_ora_6150.trc

 

SQL> !ls -lrt/u01/app/oracle/diag/rdbms/tune/tune/trace/tune_ora_6150.trc

-rw-r----- 1 oracle asmadmin 1629700 12月 15 17:22/u01/app/oracle/diag/rdbms/tune/tune/trace/tune_ora_6150.trc

 

SQL> !ls -lrt/u01/app/oracle/diag/rdbms/tune/tune/trace/tune_ora_6150.trc

-rw-r----- 1 oracle asmadmin 1629700 12月 15 17:22/u01/app/oracle/diag/rdbms/tune/tune/trace/tune_ora_6150.trc

 

SQL> exit

 

【获得系统状态】

oradebug setospid6150

oradebug unlimit

oradebug dump systemstate 10  -使用level10 可以获得较全的信息

oradebugTRACEFILE_NAME

oradebug close_trace

 

 

 

【获取进程错误信息状态】

oradebug setospid6150

oradebug dumperrorstack 3

 

SQL> oradebugsetospid 6150

Oracle pid: 44, Unixprocess pid: 6150, image: oracle@dgdemo1

SQL> oradebugdump errorstack 3

Statement processed.

SQL> oradebugtracefile_name

/u01/app/oracle/diag/rdbms/tune/tune/trace/tune_ora_6150.trc

 

SQL> !ls -lrt/u01/app/oracle/diag/rdbms/tune/tune/trace/tune_ora_6150.trc

-rw-r----- 1 oracle asmadmin 5403981 12月 15 17:23/u01/app/oracle/diag/rdbms/tune/tune/trace/tune_ora_6150.trc

 

SQL> !ls -lrt/u01/app/oracle/diag/rdbms/tune/tune/trace/tune_ora_6150.trc

-rw-r----- 1 oracle asmadmin 5403981 12月 15 17:23/u01/app/oracle/diag/rdbms/tune/tune/trace/tune_ora_6150.trc

 

SQL> exit

 

 

【追踪造成错误信息的原因，如ORA-04031】

oradebug event 4031trace name errorstack level 3

 

SQL> oradebugsetospid 6150

Oracle pid: 44, Unixprocess pid: 6150, image: oracle@dgdemo1

SQL> oradebugevent 4031 trace name errorstack level 3

Statement processed.

SQL> oradebugtracefile_name

/u01/app/oracle/diag/rdbms/tune/tune/trace/tune_ora_6150.trc

 

 

oradebug flush 将当前trace buffer中的条目 刷新到trace文件中

 

 

 

【dump 相关文件信息】

 

SQL> oradebugdumplist

TRACE_BUFFER_ON

TRACE_BUFFER_OFF

LATCHES

PROCESSSTATE

SYSTEMSTATE

INSTANTIATIONSTATE

REFRESH_OS_STATS

SQLNET_SERVER_TRACE

CROSSIC

CONTEXTAREA

HANGDIAG_HEADER

HEAPDUMP

HEAPDUMP_ADDR

POKE_ADDRESS

POKE_LENGTH

POKE_VALUE

POKE_VALUE0

GLOBAL_AREA

REALFREEDUMP

FLUSH_JAVA_POOL

POOL_SIMULATOR

PGA_DETAIL_GET

PGA_DETAIL_DUMP

PGA_DETAIL_CANCEL

PGA_SUMMARY

MODIFIED_PARAMETERS

EVENT_TSM_TEST

ERRORSTACK

CALLSTACK

TEST_STACK_DUMP

TEST_GET_CALLER

RECORD_CALLSTACK

EXCEPTION_DUMP

BG_MESSAGES

ENQUEUES

KSTDUMPCURPROC

KSTDUMPALLPROCS

KSTDUMPALLPROCS_CLUSTER

SIMULATE_EOV

KSFQP_LIMIT

KSKDUMPTRACE

DBSCHEDULER

LDAP_USER_DUMP

LDAP_KERNEL_DUMP

DUMP_ALL_OBJSTATS

DUMPGLOBALDATA

HANGANALYZE

HANGANALYZE_PROC

HNGDET_MEM_USAGE_DUMP

DEAD_CLEANUP_STATE

HANGANALYZE_GLOBAL

GES_STATE

CGS

OCR

CSS

CRS

SYSTEMSTATE_GLOBAL

GIPC

MMAN_ALLOC_MEMORY

MMAN_CREATE_DEF_REQUEST

MMAN_CREATE_IMM_REQUEST

MMAN_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

GLOBAL_BUFFER_DUMP

BUFFER

PIN_BLOCKS

BC_SANITY_CHECK

PIN_RANDOM_BLOCKS

SET_NBLOCKS

CHECK_ROREUSE_SANITY

DUMP_PINNED_BUFFER_HISTORY

KCBO_OBJ_CHECK_DUMP

KCB_WORKING_SET_DUMP

KCBS_ADV_INT_DUMP

KCBI_DUMP_FREELIST

SCN_AUTO_ROLLOVER_TS_OVERRIDE

REDOLOGS

ARCHIVE_ERROR

LOGHIST

REDOHDR

LOGERROR

OPEN_FILES

DATA_ERR_ON

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

KRB_PIECE_FAIL

KRB_OPTIONS

KRB_FAIL_INPUT_FILENO

KRB_SIMULATE_NODE_AFFINITY

KRB_TRACE

KRB_BSET_DAYS

KRB_SET_TIME_SWITCH

KRB_OVERWRITE_ACTION

KRB_CORRUPT_SPHEADER_INTERVAL

KRB_CORRUPT_SPHEADER_REPEAT

KRB_CORRUPT_SPBITMAP_INTERVAL

KRB_CORRUPT_SPBITMAP_REPEAT

KRB_CORRUPT_SPBAD_INTERVAL

KRB_CORRUPT_SPBAD_REPEAT

KRB_UNUSED_OPTION

KRBMRSR_LIMIT

KRBMROR_LIMIT

KRBABR_TRACE

KRDRSBF

KRC_TRACE

KRA_OPTIONS

KRA_TRACE

FBTAIL

FBINC

FBHDR

FLASHBACK_GEN

KTPR_DEBUG

DUMP_TEMP

DROP_SEGMENTS

TEST_SPACEBG

TREEDUMP

LONGF_CREATE

KDLIDMP

ROW_CACHE

LIBRARY_CACHE

LIBRARY_CACHE_OBJECT

CURSORDUMP

CURSORTRACE

CURSOR_STATS

XS_SESSION_STATE

SHARED_SERVER_STATE

LISTENER_REGISTRATION

JAVAINFO

KXFPCLEARSTATS

KXFPDUMPTRACE

KXFPBLATCHTEST

KXFXSLAVESTATE

KXFXCURSORSTATE

KXFRHASHMAP

WORKAREATAB_DUMP

KUPPLATCHTEST

OBJECT_CACHE

SAVEPOINTS

RULESETDUMP

RULESETDUMP_ADDR

FAILOVER

OLAP_DUMP

SELFTESTASM

ASMDISK_ERR_ON

ASMDISK_READ_ERR_ON

ASMDISK_ERR_OFF

ASM_EVENREAD

IOERREMUL

IOERREMULRNG

ALRT_TEST

AWR_TEST

AWR_FLUSH_TABLE_ON

AWR_FLUSH_TABLE_OFF

ASHDUMP

ASHDUMPSECONDS

MMON_TEST

ATSK_TEST

HM_FW_TRACE

HM_FDG_VERS

IR_FW_TRACE

KSDTRADV_TEST

 

具体使用方法

SQL>oradebug setmypid

Statementprocessed.

SQL>oradebug dump LOCKS 10

Statementprocessed.

SQL>oradebug tracefile_name

/u01/app/oracle/diag/rdbms/tune/tune/trace/tune_ora_12730.trc

 

具体 参考http://psoug.org/reference/oradebug.html

 

 

 

 

【Trace  SQL】

使用DBMS_SYSTEM包：

 select a.username,a.sid ,a.serial#,b.spid fromv$session a,v$process b  wherea.paddr=b.addr;

 SOE                                    11          3 oradebug setospid

 

execute dbms_system.set_sql_trace_in_session(11,3,true); 开启对进程6150的跟踪

 

execute dbms_system.set_sql_trace_in_session(11,3,false); 关闭

 

SQL> l

  1* select a.username,a.sid ,a.serial#,b.spidfrom v$session a,v$process b  wherea.paddr=b.addr

SQL> executedbms_system.set_sql_trace_in_session(11,3,true);

 

PL/SQL proceduresuccessfully completed.

 

SQL> executedbms_system.set_sql_trace_in_session(11,3,false);

 

PL/SQL proceduresuccessfully completed.

 

 

 

使用oradebug

oradebug setospid6150

oradebug event 10046trace name context forever,level 4

oradebug event 10046trace name context off

 

SQL> oradebugsetospid 6150

Oracle pid: 44, Unixprocess pid: 6150, image: oracle@dgdemo1

SQL> oradebugevent 10046 trace name context forever,level 4

Statement processed.

SQL> oradebugevent 10046 trace name context off

Statement processed.

 

 

 

参考

http://blog.youkuaiyun.com/tianlesoftware/article/details/6525628

http://blog.sina.com.cn/s/blog_61cd89f60102ee3j.html