oradebug使用（理论篇）

oradebugm命令介绍###

TRACEFILE_NAME command#####

显示当前trace文件名

SQL>oradebug tracefile_name
For example
    /export/home/admin/SS92003/udump/ss92003_ora_14917.trc

unlimit命令#####

取消trace文件大小限制

SQL>oradebug unlimit

FLUSH命令#####

将当前trace buffer中的内容刷新到trace文件

SQL>oradebug flush

CLOSE_TRACE命令#####

关闭trace文件

SQL>oradebug close_trace

oradebug支持的命令#####

可以使用sys用户权限运行oradebug help显示所有oradebug的操作。在11.1.0.7 for linux 64bit数据库上的显示如下：

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
SETORAPNAME    <orapname>                Set Oracle process name to debug
SHORT_STACK                              Get abridged OS stack
CURRENT_SQL                              Get current SQL
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
DIRECT_ACCESS  <set/enable/disable command | select query> Fixed table access
CORE                                     Dump core without crashing process
IPC                                      Dump ipc information
UNLIMIT                                  Unlimit the size of the trace file
PROCSTAT                                 Dump process statistics
CALL           [-t count] <func> [arg1]...[argn]  Invoke function with arguments

oradebug用来诊断系统进程###

如果是操作系统的进程ID，可以使用oradebug setospid id.

如果是根据Oracle ID，可以使用oradebug setorapid id 来追踪。

查询进程id####

可以查询Linux 操作系统的process id或是oracle自己的pid：

• 查询os process id：

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

*　查询oracle spid

SYS@dave2(db2)> select pid,spid,username from v$process;
       PID SPID         USERNAME

---------- ------------ ----------
        18 27028        oracle

开启trace####

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

设定dump相关内容####

指定为SID 之后，就可以使用dump 将相关的信息，这些dump 内容很多。 可以使用dumplist 把所有的dump 可列出来。

具体使用，参考：http://psoug.org/reference/oradebug.html

SQL> oradebug dumplist
TRACE_BUFFER_ON
TRACE_BUFFER_OFF
LATCHES
PROCESSSTATE
SYSTEMSTATE
INSTANTIATIONSTATE
REFRESH_OS_STATS
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
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
HANGANALYZE_GLOBAL
GES_STATE
OCR
CSS
CRS
SYSTEMSTATE_GLOBAL
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
BUFFER
PIN_BLOCKS
BC_SANITY_CHECK
PIN_RANDOM_BLOCKS
SET_NBLOCKS
CHECK_ROREUSE_SANITY
DUMP_PINNED_BUFFER_HISTORY
KCBO_OBJ_CHECK_DUMP
REDOLOGS
LOGHIST
ARCHIVE_ERROR
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
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
CURSORDUMP
CURSORTRACE
CURSOR_STATS
SHARED_SERVER_STATE
LISTENER_REGISTRATION
JAVAINFO
KXFPCLEARSTATS
KXFPDUMPTRACE
KXFPBLATCHTEST
KXFXSLAVESTATE
KXFXCURSORSTATE
WORKAREATAB_DUMP
KUPPLATCHTEST
OBJECT_CACHE
SAVEPOINTS
RULESETDUMP
RULESETDUMP_ADDR
FAILOVER
OLAP_DUMP
SELFTESTASM
ASMDISK_ERR_ON
ASMDISK_READ_ERR_ON
ASMDISK_ERR_OFF
IOERREMUL
IOERREMULRNG
ALRT_TEST
AWR_TEST
AWR_FLUSH_TABLE_ON
AWR_FLUSH_TABLE_OFF
ASHDUMP
MMON_TEST
ATSK_TEST
HM_FW_TRACE
IR_FW_TRACE
KSDTRADV_TEST

获得被监控进程的状态#####

如果为了获取全面一点的信息，可以使用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

监控oracle进程#####

把系统进程替换成oracle服务器会话的进程即可。
例子如下:

SQL> oradebug setospid 3260  --进程的spid

SQL> oradebug event 10046 trace name context forever,level 4

-- 取消追踪使用

SQL> oradebug event 10046 trace name context off

已处理的语句

oradebug的其他用途###

参考：http://www.juliandyke.com/Diagnostics/Tools/ORADEBUG/Introduction.html

dump命令####

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

EVENT command####

oradebug可以用来开启event。

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

SQL>oradebug event 10046 trace name context off

SESSION_EVENT command（当前session）####

oradebug可以用来开启oracle session event

SQL>oradebug session_event 10046 trace name context forever, level 12
SQL>oradebug session_event 10046 trace name context off

SESSION_EVENT command（其他session）####

• 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,'');

然后可以使用oradebug setospid命令指定进程的号码，进而获取到trace文件名称等信息。

DUMP SGA####

To dump the fixed SGA use

SQL>oradebug dumpsga

DUMP VAR####

To dump an SGA variable use

SQL>oradebug dumpvar sga variable_name

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

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

例如：

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

IPC####

To dump information about operating system shared memory and semaphores configuration use the command：

SQL>ORADEBUG IPC

此命令在windows平台下无法工作。

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