oradebug 简介！

最新推荐文章于 2023-10-30 16:32:22 发布

事后诸葛亮

最新推荐文章于 2023-10-30 16:32:22 发布

阅读量962

点赞数

分类专栏： trouble shooting 文章标签： oracle sql image unix linux command

本文链接：https://blog.csdn.net/zq9017197/article/details/7105890

版权

trouble shooting 专栏收录该内容

26 篇文章 1 订阅

订阅专栏

1、命令简介

SQL> oradebug help
HELP           [command]                 描述一个或所有的命令
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                                 打印可用的转储列表
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                                  暂停执行
RESUME                                   继续执行
FLUSH                                    Flush pending writes to trace file
CLOSE_TRACE                              关闭跟踪文件
TRACEFILE_NAME                           获取跟踪文件路径名
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                                  取消跟踪文件的大小限制
PROCSTAT                                 Dump process statistics
CALL           [-t count] <func> [arg1]...[argn]  Invoke function with arguments

2、简单使用

查询一个用户的id信息：

SQL> select sid from v$mystat where rownum = 1;

       SID
----------
        43

SQL> select PADDR from v$session where sid = 43;

PADDR
--------
2E3CC354

SQL> select PID,SPID from v$process where ADDR = '2E3CC354';

       PID SPID
---------- ------------------------
        33 6852

SQL> show user
USER 为 "U1"

设定追踪：

SQL> oradebug setospid 6852
Oracle pid: 33, Unix process pid: 6852, image: oracle@linux (TNS V1-V3)

或者

SQL> oradebug setorapid 33
Oracle pid: 33, Unix process pid: 6852, image: oracle@linux (TNS V1-V3)

可以使用 dumplist 把所有可以的 dump内容出来：

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
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
HANGANALYZE_GLOBAL
GES_STATE
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
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
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
IOERREMUL
IOERREMULRNG
ALRT_TEST
AWR_TEST
AWR_FLUSH_TABLE_ON
AWR_FLUSH_TABLE_OFF
ASHDUMP
MMON_TEST
ATSK_TEST
HM_FW_TRACE
HM_FDG_VERS
IR_FW_TRACE
KSDTRADV_TEST

在这些dump选项中，大部分都有2，4，6，8，10，12等几个跟踪级别。在使用的时候要根据具体的情况来选择级别，不同级别的影响不一样。

3、获得系统状态

SQL> oradebug setospid 6852
Oracle pid: 33, Unix process pid: 6852, image: oracle@linux (TNS V1-V3)
SQL> oradebug flush
已处理的语句
SQL> oradebug unlimit
已处理的语句
SQL> oradebug tracefile_name
/u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_6852.trc
SQL> oradebug dump systemstate 10
已处理的语句
SQL> oradebug close_trace
已处理的语句

4、获得某个进程状态

-- 注意，这里必须是Oracle 的进程

SQL> oradebug setospid 6852
Oracle pid: 33, Unix process pid: 6852, image: oracle@linux (TNS V1-V3)

SQL> oradebug unlimit
已处理的语句
SQL> oradebug tracefile_name
/u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_6852.trc
SQL> oradebug dump processstate 10
已处理的语句
SQL> oradebug close_trace
已处理的语句

5、获得进程的错误信息状态

SQL> oradebug setospid 6852
Oracle pid: 33, Unix process pid: 6852, image: oracle@linux (TNS V1-V3)

SQL> oradebug unlimit
已处理的语句

SQL> oradebug tracefile_name/u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_6852.trc
SQL> oradebug dump errorstack 3
已处理的语句
SQL> oradebug close_trace
已处理的语句

6、利用oradebug追踪10046事件

SQL> oradebug setospid 6852
Oracle pid: 33, Unix process pid: 6852, image: oracle@linux (TNS V1-V3)
SQL> oradebug unlimit
已处理的语句
SQL> oradebug tracefile_name
/u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_6852.trc
SQL> oradebug event 10046 trace name context forever,level 4
已处理的语句
SQL> oradebug event 10046 trace name context off
已处理的语句
SQL> oradebug close_trace
已处理的语句

7、例如要追踪能造成ORA-0094/952错误的会话

SQL> oradebug event 942 trace name errorstack level 3
SQL> oradebug event 952 trace name errorstack level 3

8、暂停、启动进程

SQL> oradebug setospid 6852
Oracle pid: 33, Unix process pid: 6852, image: oracle@linux (TNS V1-V3)
SQL> oradebug SUSPEND
已处理的语句
SQL> oradebug RESUME
已处理的语句
SQL> oradebug close_trace
已处理的语句