oracle 之oradebug（抛砖引玉）
今天是2013-07-15，今天上午在看书的时候，发现如下问题：
SYS@orcl#oradebug setmypid
已处理的语句
SYS@orcl#oradebug dumpvar sga kcsgscn_
kcslf kcsgscn_ [060016AF8, 060016B28) = 005B6EFA 00000000 00000000 00000000 000000F2 00000000 00000000 00000000 00000000 00000000 600167D8 00000000
SYS@orcl#select current_scn from v$database;
SYS@orcl#oradebug setmypid
已处理的语句
SYS@orcl#oradebug dumpvar sga kcsgscn_
kcslf kcsgscn_ [060016AF8, 060016B28) = 005B6EFA 00000000 00000000 00000000 000000F2 00000000 00000000 00000000 00000000 00000000 600167D8 00000000
SYS@orcl#select current_scn from v$database;
CURRENT_SCN
-----------
5992206
-----------
5992206
SYS@orcl#select to_number('005B6EFA','xxxxxxxxxxxx') from dual;
TO_NUMBER('005B6EFA','XXXXXXXXXXXX')
------------------------------------
5992186
------------------------------------
5992186
SYS@orcl#
该语句是使用oradebug工具查看内存中oracle的scn是多少。
至此引起了我对oradebug的使用探索。就是这么一个不起眼的东西，背后居然拥有了如此Huge energy，在国外搜索了一番，找到了相关内容。在此学习一下，顺便记录一下笔记。
oradebug
从oracle 7 开始，oradebug就运行dba进行启动和停止一些进程的跟踪想，dumpsga和其他的内存结构，唤醒oracle的进程像SMON,PMON，在一个sid中暂停和恢复处理，调测请求序列服务，调测CGS名称服务，dump内部文件和ipc信息等等。该工具非常的强大，但是不幸，该工具只是oracle公司内部的工具，就像bbed一样，官网没有出过任何关于该工具的使用，但是该工具一直都被全球的dba进行挖掘并运用。
我们可以查看一下帮助信息：
SYS@orcl#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
SYS@orcl#
每个命令到在oradebug中使用的时候，都会在服务器端生成trace文件，在没有启动adr之前，该trace文件在user_dump_dest中存放，我们可以通过OEM或是sqlplus输入show parameter user_dump_dest进行查看，当然了我们也可以使用alter system进行修改文件位置。
eg：
SYS@orcl#show parameter user_dump_dest
我们可以查看一下帮助信息：
SYS@orcl#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
SYS@orcl#
每个命令到在oradebug中使用的时候，都会在服务器端生成trace文件，在没有启动adr之前，该trace文件在user_dump_dest中存放，我们可以通过OEM或是sqlplus输入show parameter user_dump_dest进行查看，当然了我们也可以使用alter system进行修改文件位置。
eg：
SYS@orcl#show parameter user_dump_dest
NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
user_dump_dest string /opt/oracle/diag/rdbms/orcl/or
cl/trace
SYS@orcl#
另外，每个跟踪文件的大小都是有一定的限制的，该限制在参数文件的max_dump_file_size参数进行限制：
eg：
SYS@orcl#show parameter max_dump_file
------------------------------------ ---------------------- ------------------------------
user_dump_dest string /opt/oracle/diag/rdbms/orcl/or
cl/trace
SYS@orcl#
另外，每个跟踪文件的大小都是有一定的限制的，该限制在参数文件的max_dump_file_size参数进行限制：
eg：
SYS@orcl#show parameter max_dump_file
NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
max_dump_file_size string unlimited
SYS@orcl#
为了使用oradebug，首先需要指定一个spid，你可以在一个特权模式下的用户对其他进程进行运行debug。如何获取spid，如下：
eg：
SYS@orcl#select s.sid,s.serial#,s.username,p.spid,p.program from v$session s,v$process p where p.addr=s.paddr
2 ;
------------------------------------ ---------------------- ------------------------------
max_dump_file_size string unlimited
SYS@orcl#
为了使用oradebug，首先需要指定一个spid，你可以在一个特权模式下的用户对其他进程进行运行debug。如何获取spid，如下：
eg：
SYS@orcl#select s.sid,s.serial#,s.username,p.spid,p.program from v$session s,v$process p where p.addr=s.paddr
2 ;
SID SERIAL# USERNAME SPID PROGRAM
---------- ---------- ------------------------- -------------------- ----------------------------------------
180 1 5265 oracle@oracle (PMON)
179 1 5267 oracle@oracle (VKTM)
178 1 5271 oracle@oracle (DIAG)
176 1 5273 oracle@oracle (DBRM)
177 1 5275 oracle@oracle (PSP0)
171 1 5283 oracle@oracle (DBW0)
174 1 5279 oracle@oracle (DIA0)
175 3 5281 oracle@oracle (MMAN)
173 1 5285 oracle@oracle (LGWR)
172 1 5287 oracle@oracle (CKPT)
170 1 5289 oracle@oracle (SMON)
---------- ---------- ------------------------- -------------------- ----------------------------------------
180 1 5265 oracle@oracle (PMON)
179 1 5267 oracle@oracle (VKTM)
178 1 5271 oracle@oracle (DIAG)
176 1 5273 oracle@oracle (DBRM)
177 1 5275 oracle@oracle (PSP0)
171 1 5283 oracle@oracle (DBW0)
174 1 5279 oracle@oracle (DIA0)
175 3 5281 oracle@oracle (MMAN)
173 1 5285 oracle@oracle (LGWR)
172 1 5287 oracle@oracle (CKPT)
170 1 5289 oracle@oracle (SMON)
SID SERIAL# USERNAME SPID PROGRAM
---------- ---------- ------------------------- -------------------- ----------------------------------------
167 1 5291 oracle@oracle (RECO)
169 1 5293 oracle@oracle (MMON)
168 1 5295 oracle@oracle (MMNL)
139 66 5577 oracle@oracle (J000)
165 7 5323 oracle@oracle (ARC0)
154 1 5334 oracle@oracle (FBDA)
161 2 5325 oracle@oracle (ARC1)
160 1 5327 oracle@oracle (ARC2)
159 1 5329 oracle@oracle (ARC3)
156 1 5331 oracle@oracle (CTWR)
152 1 5336 oracle@oracle (SMCO)
---------- ---------- ------------------------- -------------------- ----------------------------------------
167 1 5291 oracle@oracle (RECO)
169 1 5293 oracle@oracle (MMON)
168 1 5295 oracle@oracle (MMNL)
139 66 5577 oracle@oracle (J000)
165 7 5323 oracle@oracle (ARC0)
154 1 5334 oracle@oracle (FBDA)
161 2 5325 oracle@oracle (ARC1)
160 1 5327 oracle@oracle (ARC2)
159 1 5329 oracle@oracle (ARC3)
156 1 5331 oracle@oracle (CTWR)
152 1 5336 oracle@oracle (SMCO)
SID SERIAL# USERNAME SPID PROGRAM
---------- ---------- ------------------------- -------------------- ----------------------------------------
151 3 5453 oracle@oracle (W000)
162 8 5340 oracle@oracle (QMNC)
147 1 5342 oracle@oracle (q000)
146 1 5344 oracle@oracle (q001)
166 8 5365 oracle@oracle (CJQ0)
134 50 SYS 5495 oracle@oracle (TNS V1-V3)
---------- ---------- ------------------------- -------------------- ----------------------------------------
151 3 5453 oracle@oracle (W000)
162 8 5340 oracle@oracle (QMNC)
147 1 5342 oracle@oracle (q000)
146 1 5344 oracle@oracle (q001)
166 8 5365 oracle@oracle (CJQ0)
134 50 SYS 5495 oracle@oracle (TNS V1-V3)
已选择28行。
SYS@orcl#
然后在使用spid进行使用。
eg：
SYS@orcl#oradebug setospid 5495
Oracle pid: 37, Unix process pid: 5495, image: oracle@oracle (TNS V1-V3)
SYS@orcl#oradebug unlimit
已处理的语句
SYS@orcl#
使用unlimit，为了是使跟踪文件不受max_dump_file_size参数的影响，保证整个trace文件在一个文件中。
1）使用setmypid command进行监视当前会话信息。
一旦spid设置了，那么任何sid指定的oradebug就可以执行了。可选的comman如下：
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
1、使用dump命令。
dump可以进行所列举的所有信息的dump，可以使用oradebug dumplist命令查看一下，有哪些是可以dump的，另外dump的选项是有等级的分别是2,4,6，8，10,12，每个高等级就包含更加详细的内容。
eg：
SYS@orcl#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_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
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
JAVAINFO
KXFPCLEARSTATS
KXFPDUMPTRACE
KXFPBLATCHTEST
KXFXSLAVESTATE
KXFXCURSORSTATE
WORKAREATAB_DUMP
KUPPLATCHTEST
OBJECT_CACHE
SAVEPOINTS
RULESETDUMP
RULESETDUMP_ADDR
FAILOVER
OLAP_DUMP
SELFTESTASM
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
SYS@orcl#
然后在使用spid进行使用。
eg：
SYS@orcl#oradebug setospid 5495
Oracle pid: 37, Unix process pid: 5495, image: oracle@oracle (TNS V1-V3)
SYS@orcl#oradebug unlimit
已处理的语句
SYS@orcl#
使用unlimit，为了是使跟踪文件不受max_dump_file_size参数的影响，保证整个trace文件在一个文件中。
1）使用setmypid command进行监视当前会话信息。
一旦spid设置了，那么任何sid指定的oradebug就可以执行了。可选的comman如下：
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
1、使用dump命令。
dump可以进行所列举的所有信息的dump，可以使用oradebug dumplist命令查看一下，有哪些是可以dump的，另外dump的选项是有等级的分别是2,4,6，8，10,12，每个高等级就包含更加详细的内容。
eg：
SYS@orcl#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_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
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
JAVAINFO
KXFPCLEARSTATS
KXFPDUMPTRACE
KXFPBLATCHTEST
KXFXSLAVESTATE
KXFXCURSORSTATE
WORKAREATAB_DUMP
KUPPLATCHTEST
OBJECT_CACHE
SAVEPOINTS
RULESETDUMP
RULESETDUMP_ADDR
FAILOVER
OLAP_DUMP
SELFTESTASM
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
SYS@orcl#
2、不需要设置spid的comman。
有些命令不需要设置spid。这些涉及到系统的很多类型的dump如ipc；
eg：
SYS@orcl#oradebug setmypid
已处理的语句
SYS@orcl#oradebug ipc
Information written to trace file.
SYS@orcl#select * from v$diag_info where name like 'Default Trace File';
有些命令不需要设置spid。这些涉及到系统的很多类型的dump如ipc；
eg：
SYS@orcl#oradebug setmypid
已处理的语句
SYS@orcl#oradebug ipc
Information written to trace file.
SYS@orcl#select * from v$diag_info where name like 'Default Trace File';
INST_ID
----------
NAME
--------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
1
Default Trace File
/opt/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_5775.trc
----------
NAME
--------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
1
Default Trace File
/opt/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_5775.trc
SYS@orcl#exit
####################trace################
Processing Oradebug command 'ipc'
Dump of unix-generic skgm context
areaflags 000000b7
realmflags 0000000f
mapsize 00001000
protectsize 00001000
lcmsize 00001000
seglen 00200000
largestsize 00000000c4653600
smallestsize 0000000000400000
stacklimit 0x7fffa1a08cf0
stackdir -1
mode 660
magic acc01ade
Handle: 0x2b2b0899e120 `/opt/oracle/product/10.2/db_1orcl'
Dump of unix-generic realm handle `/opt/oracle/product/10.2/db_1orcl', flags = 00000000
Area #0 `Fixed Size' containing Subareas 0-0
Total size 000000000020bec0 Minimum Subarea size 00000000
Area Subarea Shmid Stable Addr Actual Addr
0 0 131074 0x00000060000000 0x00000060000000
Subarea size Segment size
000000000020c000 000000001f600000
Area #1 `Variable Size' containing Subareas 4-4
Total size 000000001ec00000 Minimum Subarea size 00400000
Area Subarea Shmid Stable Addr Actual Addr
1 4 131074 0x00000060800000 0x00000060800000
Subarea size Segment size
000000001ec00000 000000001f600000
Area #2 `Redo Buffers' containing Subareas 1-1
Total size 00000000003b6000 Minimum Subarea size 00000000
Area Subarea Shmid Stable Addr Actual Addr
2 1 131074 0x0000006020c000 0x0000006020c000
Subarea size Segment size
00000000003b6000 000000001f600000
Area #3 `Base Allocator Control' containing Subareas 3-3
Total size 0000000000002000 Minimum Subarea size 00000000
Area Subarea Shmid Stable Addr Actual Addr
3 3 131074 0x000000607fe000 0x000000607fe000
Subarea size Segment size
0000000000002000 000000001f600000
Area #4 `Slab Allocator Control' containing Subareas 2-2
Total size 000000000023c000 Minimum Subarea size 00000000
Area Subarea Shmid Stable Addr Actual Addr
4 2 131074 0x000000605c2000 0x000000605c2000
Subarea size Segment size
000000000023c000 000000001f600000
Area #5 `skgm overhead' containing Subareas 5-5
Total size 0000000000002000 Minimum Subarea size 00000000
Area Subarea Shmid Stable Addr Actual Addr
5 5 131074 0x0000007f400000 0x0000007f400000
Subarea size Segment size
0000000000002000 000000001f600000
Dump of Linux-specific skgm context
sharedmmu 00000001
shareddec 0
used region 0: start 0000000060000000 length 000000001f800000
Maximum processes: = 160
Number of semaphores per set: = 164
Semaphores key overhead per set: = 4
User Semaphores per set: = 160
Number of semaphore sets: = 1
Semaphore identifiers: = 1
Semaphore List=
131073
-------------- system semaphore information -------------
------ Shared Memory Segments --------
key shmid owner perms bytes nattch status
0x62b091ac 131074 oracle 660 526385152 36
------ Semaphore Arrays --------
key semid owner perms nsems
0x1497e4d8 131073 oracle 660 164
------ Message Queues --------
key msqid owner perms used-bytes messages
*** 2013-07-15 22:54:06.536
Oradebug command 'ipc' console output:
Information written to trace file.
另外刚刚在oradebug dumplist中显示的command也是不需要指定spid的。
二）使用oradebug 处理oracle hangs问题。
oradebug 对于系统hangs特别的有用，通常情况下oracle会阻止其他用户登录数据库，但是我们可以使用sys用户以及internal用户进行登录，然后进行问题消息收集并诊断，当然了我们在进行dump systemstat的时候会产生很大的trace file，为了保证trace file在一个文件中，我们通常需要设置trace file的最大大小限制。
eg：
SYS@conn#show parameter max_dump
Oradebug command 'ipc' console output:
Information written to trace file.
另外刚刚在oradebug dumplist中显示的command也是不需要指定spid的。
二）使用oradebug 处理oracle hangs问题。
oradebug 对于系统hangs特别的有用，通常情况下oracle会阻止其他用户登录数据库，但是我们可以使用sys用户以及internal用户进行登录，然后进行问题消息收集并诊断，当然了我们在进行dump systemstat的时候会产生很大的trace file，为了保证trace file在一个文件中，我们通常需要设置trace file的最大大小限制。
eg：
SYS@conn#show parameter max_dump
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
max_dump_file_size string 10K
SYS@conn#alter system set max_dump_file_size='20M';
------------------------------------ ----------- ------------------------------
max_dump_file_size string 10K
SYS@conn#alter system set max_dump_file_size='20M';
系统已更改。
SYS@conn#show parameter max_dump_file_size
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
max_dump_file_size string 20M
SYS@conn#
对于该参数值如下：
------------------------------------ ----------- ------------------------------
max_dump_file_size string 20M
SYS@conn#
对于该参数值如下：
MAX_DUMP_FILE_SIZE = { integer [K | M | G] | UNLIMITED } |
开始查看systemstat
eg：
SQL> oradebug setmypid
Statement processed.
SQL> oradebug unlimit
Statement processed.
SQL> oradebug dump systemstate 10
Statement processed.
SQL>
或者我们可以执行执行alter system set events 'immediate trace name systemstate level 10';
SYS@conn#oradebug setmypid
已处理的语句
SYS@conn#oradebug unlimit
已处理的语句
SYS@conn#oradebug dump systemstate 10
已处理的语句
SYS@conn#set linesize 200
SYS@conn#col value for a80
SYS@conn#r
SP2-0103: SQL 缓冲区中无可运行的程序。
SYS@conn#select * from v$diag_info;
INST_ID NAME VALUE
---------- ---------------------------------------------------------------- --------------------------------------------------------------------------------
1 Diag Enabled TRUE
1 ADR Base /opt/oracle
1 ADR Home /opt/oracle/diag/rdbms/orcl/ORCL
1 Diag Trace /opt/oracle/diag/rdbms/orcl/ORCL/trace
1 Diag Alert /opt/oracle/diag/rdbms/orcl/ORCL/alert
1 Diag Incident /opt/oracle/diag/rdbms/orcl/ORCL/incident
1 Diag Cdump /opt/oracle/diag/rdbms/orcl/ORCL/cdump
1 Health Monitor /opt/oracle/diag/rdbms/orcl/ORCL/hm
1 Default Trace File /opt/oracle/diag/rdbms/orcl/ORCL/trace/ORCL_ora_2010.trc
1 Active Problem Count 3
1 Active Incident Count 1013
---------- ---------------------------------------------------------------- --------------------------------------------------------------------------------
1 Diag Enabled TRUE
1 ADR Base /opt/oracle
1 ADR Home /opt/oracle/diag/rdbms/orcl/ORCL
1 Diag Trace /opt/oracle/diag/rdbms/orcl/ORCL/trace
1 Diag Alert /opt/oracle/diag/rdbms/orcl/ORCL/alert
1 Diag Incident /opt/oracle/diag/rdbms/orcl/ORCL/incident
1 Diag Cdump /opt/oracle/diag/rdbms/orcl/ORCL/cdump
1 Health Monitor /opt/oracle/diag/rdbms/orcl/ORCL/hm
1 Default Trace File /opt/oracle/diag/rdbms/orcl/ORCL/trace/ORCL_ora_2010.trc
1 Active Problem Count 3
1 Active Incident Count 1013
已选择11行。
SYS@conn#!vi /opt/oracle/diag/rdbms/orcl/ORCL/trace/ORCL_ora_2010.trc
查看如下内容：
60 0 cluster wait time
61 22534112 concurrency wait time
62 20786 application wait time
63 107853831 user I/O wait time
74 93 enqueue waits
177 6738 shared hash latch upgrades - no wait
178 0 shared hash latch upgrades - wait
199 53 redo log space wait time
251 0 transaction lock foreground wait time
293 0 commit wait/nowait requested
294 0 commit nowait requested
295 0 commit wait requested
296 0 commit wait/nowait performed
297 0 commit nowait performed
298 0 commit wait performed
418 0 DX/BB enqueue lock foreground wait time
529 (latch info) wait_event=0 bits=0
605 (latch info) wait_event=0 bits=0
630 last message waited event: 1, messages read: 0
655 last message waited event: 13, messages read: 1
828 Dumping Current Wait Stack:
829 0: waiting for 'pmon timer'
831 wait_id=8724 seq_num=8725 snap_id=1
832 wait times (usecs) - snap=976245 exc=976245 total=976245
833 wait times (usecs) - max=3000000
834 wait counts (exc) - calls=1 os=1
835 in_wait=1 iflags=0x7a8
836 Wait State:
838 Dumping Session Wait History:
839 0: waited for 'pmon timer'
841 wait_id=8723 seq_num=8724 snap_id=1
842 wait times (usecs) - snap=2999938 exc=2999938 total=2999938
843 wait times (usecs) - max=3000000
844 wait counts (exc) - calls=1 os=1
846 1: waited for 'pmon timer'
848 wait_id=8722 seq_num=8723 snap_id=1
849 wait times (usecs) - snap=2999699 exc=2999699 total=2999699
850 wait times (usecs) - max=3000000
851 wait counts (exc) - calls=1 os=1
853 2: waited for 'pmon timer'
855 wait_id=8721 seq_num=8722 snap_id=1
856 wait times (usecs) - snap=2999948 exc=2999948 total=2999948
857 wait times (usecs) - max=3000000
858 wait counts (exc) - calls=1 os=1
860 3: waited for 'pmon timer'
862 wait_id=8720 seq_num=8721 snap_id=1
863 wait times (usecs) - snap=3000104 exc=3000104 total=3000104
864 wait times (usecs) - max=3000000
865 wait counts (exc) - calls=1 os=1
867 4: waited for 'pmon timer'
869 wait_id=8719 seq_num=8720 snap_id=1
870 wait times (usecs) - snap=3000124 exc=3000124 total=3000124
871 wait times (usecs) - max=3000000
872 wait counts (exc) - calls=1 os=1
874 5: waited for 'pmon timer'
876 wait_id=8718 seq_num=8719 snap_id=1
877 wait times (usecs) - snap=2999370 exc=2999370 total=2999370
878 wait times (usecs) - max=3000000
879 wait counts (exc) - calls=1 os=1
881 6: waited for 'pmon timer'
883 wait_id=8717 seq_num=8718 snap_id=1
884 wait times (usecs) - snap=3000080 exc=3000080 total=3000080
885 wait times (usecs) - max=3000000
886 wait counts (exc) - calls=1 os=1
888 7: waited for 'pmon timer'
890 wait_id=8716 seq_num=8717 snap_id=1
891 wait times (usecs) - snap=3000109 exc=3000109 total=3000109
892 wait times (usecs) - max=3000000
893 wait counts (exc) - calls=1 os=1
895 8: waited for 'pmon timer'
897 wait_id=8715 seq_num=8716 snap_id=1
898 wait times (usecs) - snap=3000053 exc=3000053 total=3000053
899 wait times (usecs) - max=3000000
900 wait counts (exc) - calls=1 os=1
902 9: waited for 'pmon timer'
904 wait_id=8714 seq_num=8715 snap_id=1
905 wait times (usecs) - snap=2999553 exc=2999553 total=2999553
906 wait times (usecs) - max=3000000
907 wait counts (exc) - calls=1 os=1
923 cpu yields: 0 (cg: 0), waits: 0 (cg: 0), wait time: 0 (cg: 0) usec
924 io waits: 0 (cg: 0), wait time: 0 (cg: 0) usec
929 I/O credits waiting for:small=0 large=0
1000 last message waited event: 39, messages read: 1
1052 (latch info) wait_event=0 bits=0
1223 Dumping Current Wait Stack:
1224 0: waiting for 'VKTM Logical Idle Wait'
1226 wait_id=0 seq_num=1 snap_id=1
1227 wait times (usecs) - snap=23830723449 exc=23830723449 total=23830723449
1228 wait times (usecs) - max=infinite
1229 wait counts (exc) - calls=0 os=0
1230 in_wait=1 iflags=0x5a0
1231 Wait State:
1233 Dumping Session Wait History:
1248 cpu yields: 0 (cg: 0), waits: 0 (cg: 0), wait time: 0 (cg: 0) usec
1249 io waits: 0 (cg: 0), wait time: 0 (cg: 0) usec
1254 I/O credits waiting for:small=0 large=0
1335 last message waited event: 39, messages read: 1
1386 (latch info) wait_event=0 bits=0
1563 Dumping Current Wait Stack:
1564 0: waiting for 'DIAG idle wait'
1565 component=5, where=1, wait time(millisec)=3e8
1566 wait_id=23822 seq_num=23823 snap_id=1
1567 wait times (usecs) - snap=209180 exc=209180 total=209180
1568 wait times (usecs) - max=1000000
1569 wait counts (exc) - calls=1 os=1
1570 in_wait=1 iflags=0x7a8
1571 Wait State:
1573 Dumping Session Wait History:
1574 0: waited for 'DIAG idle wait'
1575 component=5, where=1, wait time(millisec)=3e8
1576 wait_id=23821 seq_num=23822 snap_id=1
1577 wait times (usecs) - snap=1000111 exc=1000111 total=1000111
1578 wait times (usecs) - max=1000000
1579 wait counts (exc) - calls=1 os=1
1581 1: waited for 'DIAG idle wait'
1582 component=5, where=1, wait time(millisec)=3e8
1583 wait_id=23820 seq_num=23821 snap_id=1
1584 wait times (usecs) - snap=1000089 exc=1000089 total=1000089
1585 wait times (usecs) - max=1000000
1586 wait counts (exc) - calls=1 os=1
1588 2: waited for 'DIAG idle wait'
1589 component=5, where=1, wait time(millisec)=3e8
1590 wait_id=23819 seq_num=23820 snap_id=1
1591 wait times (usecs) - snap=1000068 exc=1000068 total=1000068
1592 wait times (usecs) - max=1000000
1593 wait counts (exc) - calls=1 os=1
刚刚我模拟了一下，把数据库空间全部占满，然后进行oradebug分析。
下面我在模拟一下，归档日志满的情况。然后进行分析：
eg：
SYS@conn#show parameter db_recovery
查看如下内容：
60 0 cluster wait time
61 22534112 concurrency wait time
62 20786 application wait time
63 107853831 user I/O wait time
74 93 enqueue waits
177 6738 shared hash latch upgrades - no wait
178 0 shared hash latch upgrades - wait
199 53 redo log space wait time
251 0 transaction lock foreground wait time
293 0 commit wait/nowait requested
294 0 commit nowait requested
295 0 commit wait requested
296 0 commit wait/nowait performed
297 0 commit nowait performed
298 0 commit wait performed
418 0 DX/BB enqueue lock foreground wait time
529 (latch info) wait_event=0 bits=0
605 (latch info) wait_event=0 bits=0
630 last message waited event: 1, messages read: 0
655 last message waited event: 13, messages read: 1
828 Dumping Current Wait Stack:
829 0: waiting for 'pmon timer'
831 wait_id=8724 seq_num=8725 snap_id=1
832 wait times (usecs) - snap=976245 exc=976245 total=976245
833 wait times (usecs) - max=3000000
834 wait counts (exc) - calls=1 os=1
835 in_wait=1 iflags=0x7a8
836 Wait State:
838 Dumping Session Wait History:
839 0: waited for 'pmon timer'
841 wait_id=8723 seq_num=8724 snap_id=1
842 wait times (usecs) - snap=2999938 exc=2999938 total=2999938
843 wait times (usecs) - max=3000000
844 wait counts (exc) - calls=1 os=1
846 1: waited for 'pmon timer'
848 wait_id=8722 seq_num=8723 snap_id=1
849 wait times (usecs) - snap=2999699 exc=2999699 total=2999699
850 wait times (usecs) - max=3000000
851 wait counts (exc) - calls=1 os=1
853 2: waited for 'pmon timer'
855 wait_id=8721 seq_num=8722 snap_id=1
856 wait times (usecs) - snap=2999948 exc=2999948 total=2999948
857 wait times (usecs) - max=3000000
858 wait counts (exc) - calls=1 os=1
860 3: waited for 'pmon timer'
862 wait_id=8720 seq_num=8721 snap_id=1
863 wait times (usecs) - snap=3000104 exc=3000104 total=3000104
864 wait times (usecs) - max=3000000
865 wait counts (exc) - calls=1 os=1
867 4: waited for 'pmon timer'
869 wait_id=8719 seq_num=8720 snap_id=1
870 wait times (usecs) - snap=3000124 exc=3000124 total=3000124
871 wait times (usecs) - max=3000000
872 wait counts (exc) - calls=1 os=1
874 5: waited for 'pmon timer'
876 wait_id=8718 seq_num=8719 snap_id=1
877 wait times (usecs) - snap=2999370 exc=2999370 total=2999370
878 wait times (usecs) - max=3000000
879 wait counts (exc) - calls=1 os=1
881 6: waited for 'pmon timer'
883 wait_id=8717 seq_num=8718 snap_id=1
884 wait times (usecs) - snap=3000080 exc=3000080 total=3000080
885 wait times (usecs) - max=3000000
886 wait counts (exc) - calls=1 os=1
888 7: waited for 'pmon timer'
890 wait_id=8716 seq_num=8717 snap_id=1
891 wait times (usecs) - snap=3000109 exc=3000109 total=3000109
892 wait times (usecs) - max=3000000
893 wait counts (exc) - calls=1 os=1
895 8: waited for 'pmon timer'
897 wait_id=8715 seq_num=8716 snap_id=1
898 wait times (usecs) - snap=3000053 exc=3000053 total=3000053
899 wait times (usecs) - max=3000000
900 wait counts (exc) - calls=1 os=1
902 9: waited for 'pmon timer'
904 wait_id=8714 seq_num=8715 snap_id=1
905 wait times (usecs) - snap=2999553 exc=2999553 total=2999553
906 wait times (usecs) - max=3000000
907 wait counts (exc) - calls=1 os=1
923 cpu yields: 0 (cg: 0), waits: 0 (cg: 0), wait time: 0 (cg: 0) usec
924 io waits: 0 (cg: 0), wait time: 0 (cg: 0) usec
929 I/O credits waiting for:small=0 large=0
1000 last message waited event: 39, messages read: 1
1052 (latch info) wait_event=0 bits=0
1223 Dumping Current Wait Stack:
1224 0: waiting for 'VKTM Logical Idle Wait'
1226 wait_id=0 seq_num=1 snap_id=1
1227 wait times (usecs) - snap=23830723449 exc=23830723449 total=23830723449
1228 wait times (usecs) - max=infinite
1229 wait counts (exc) - calls=0 os=0
1230 in_wait=1 iflags=0x5a0
1231 Wait State:
1233 Dumping Session Wait History:
1248 cpu yields: 0 (cg: 0), waits: 0 (cg: 0), wait time: 0 (cg: 0) usec
1249 io waits: 0 (cg: 0), wait time: 0 (cg: 0) usec
1254 I/O credits waiting for:small=0 large=0
1335 last message waited event: 39, messages read: 1
1386 (latch info) wait_event=0 bits=0
1563 Dumping Current Wait Stack:
1564 0: waiting for 'DIAG idle wait'
1565 component=5, where=1, wait time(millisec)=3e8
1566 wait_id=23822 seq_num=23823 snap_id=1
1567 wait times (usecs) - snap=209180 exc=209180 total=209180
1568 wait times (usecs) - max=1000000
1569 wait counts (exc) - calls=1 os=1
1570 in_wait=1 iflags=0x7a8
1571 Wait State:
1573 Dumping Session Wait History:
1574 0: waited for 'DIAG idle wait'
1575 component=5, where=1, wait time(millisec)=3e8
1576 wait_id=23821 seq_num=23822 snap_id=1
1577 wait times (usecs) - snap=1000111 exc=1000111 total=1000111
1578 wait times (usecs) - max=1000000
1579 wait counts (exc) - calls=1 os=1
1581 1: waited for 'DIAG idle wait'
1582 component=5, where=1, wait time(millisec)=3e8
1583 wait_id=23820 seq_num=23821 snap_id=1
1584 wait times (usecs) - snap=1000089 exc=1000089 total=1000089
1585 wait times (usecs) - max=1000000
1586 wait counts (exc) - calls=1 os=1
1588 2: waited for 'DIAG idle wait'
1589 component=5, where=1, wait time(millisec)=3e8
1590 wait_id=23819 seq_num=23820 snap_id=1
1591 wait times (usecs) - snap=1000068 exc=1000068 total=1000068
1592 wait times (usecs) - max=1000000
1593 wait counts (exc) - calls=1 os=1
刚刚我模拟了一下，把数据库空间全部占满，然后进行oradebug分析。
下面我在模拟一下，归档日志满的情况。然后进行分析：
eg：
SYS@conn#show parameter db_recovery
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /opt/oracle/orabak/
db_recovery_file_dest_size big integer 1000M
SYS@conn#select * from v$flash_recovery_area_usage;
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /opt/oracle/orabak/
db_recovery_file_dest_size big integer 1000M
SYS@conn#select * from v$flash_recovery_area_usage;
FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
-------------------- ------------------ ------------------------- ---------------
CONTROL FILE 0 0 0
REDO LOG 0 0 0
ARCHIVED LOG 0 0 0
BACKUP PIECE 0 0 0
IMAGE COPY 0 0 0
FLASHBACK LOG 27.23 13.8 48
FOREIGN ARCHIVED LOG 0 0 0
-------------------- ------------------ ------------------------- ---------------
CONTROL FILE 0 0 0
REDO LOG 0 0 0
ARCHIVED LOG 0 0 0
BACKUP PIECE 0 0 0
IMAGE COPY 0 0 0
FLASHBACK LOG 27.23 13.8 48
FOREIGN ARCHIVED LOG 0 0 0
已选择7行。
SYS@conn#select * from v$recovery_file_dest;
NAME SPACE_LIMIT SPACE_USED SPACE_RECLAIMABLE NUMBER_OF_FILES
-------------------------------------------------- ----------- ---------- ----------------- ---------------
/opt/oracle/orabak/ 1048576000 285556736 144654336 48
-------------------------------------------------- ----------- ---------- ----------------- ---------------
/opt/oracle/orabak/ 1048576000 285556736 144654336 48
SYS@conn#select 285556736/1024/1024/1024 from dual;
285556736/1024/1024/1024
------------------------
.265945435
------------------------
.265945435
SYS@conn#select 285556736/1024/1024 from dual;
285556736/1024/1024
-------------------
272.328125
-------------------
272.328125
SYS@conn#show parameter db_recovery
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /opt/oracle/orabak/
db_recovery_file_dest_size big integer 1000M
SYS@conn#alter system set db_recovery_file_dest_size=280M;
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /opt/oracle/orabak/
db_recovery_file_dest_size big integer 1000M
SYS@conn#alter system set db_recovery_file_dest_size=280M;
系统已更改。
SYS@conn#show parameter db_recovery_file_dest_size
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest_size big integer 280M
这个时候查看一下alert日志如下：
RC0: Failed to archive thread 1 sequence 54 (19502)
ARCH: Archival stopped, error occurred. Will continue retrying
ORACLE Instance ORCL - Archival Error
ORA-16038: log 2 sequence# 54 cannot be archived
ORA-19502: write error on file "", block number (block size=)
ORA-00312: online log 2 thread 1: '/opt/oracle/oradata/ORCL/redo02.log'
ORA-00312: online log 2 thread 1: '/opt/oracle/oradata/ORCL/redo002.log'
Errors in file /opt/oracle/diag/rdbms/orcl/ORCL/trace/ORCL_arc0_2366.trc:
ORA-16038: log 2 sequence# 54 cannot be archived
ORA-19502: write error on file "", block number (block size=)
ORA-00312: online log 2 thread 1: '/opt/oracle/oradata/ORCL/redo02.log'
ORA-00312: online log 2 thread 1: '/opt/oracle/oradata/ORCL/redo002.log'
2013-05-12 10:26:52.044000 +08:00
ARC1: Evaluating archive log 2 thread 1 sequence 54
ARC1: Failed to archive thread 1 sequence 54 (4)
ARCH: Archival stopped, error occurred. Will continue retrying
ORACLE Instance ORCL - Archival Error
ORA-16014: log 2 sequence# 54 not archived, no available destinations
ORA-00312: online log 2 thread 1: '/opt/oracle/oradata/ORCL/redo02.log'
ORA-00312: online log 2 thread 1: '/opt/oracle/oradata/ORCL/redo002.log'
Errors in file /opt/oracle/diag/rdbms/orcl/ORCL/trace/ORCL_arc1_2368.trc:
ORA-16014: log 2 sequence# 54 not archived, no available destinations
ORA-00312: online log 2 thread 1: '/opt/oracle/oradata/ORCL/redo02.log'
ORA-00312: online log 2 thread 1: '/opt/oracle/oradata/ORCL/redo002.log'
2013-05-12 10:31:52.058000 +08:00
ARC2: Evaluating archive log 2 thread 1 sequence 54
ARC2: Beginning to archive thread 1 sequence 54 (16366732-16394654) (ORCL)
ARC2: Creating local archive destination LOG_ARCHIVE_DEST_1: '/opt/oracle/flash_recovery_area/ORCL/archivelog/1_54_814740574.dbf' (thread 1 sequence 54)
(ORCL)
2013-05-12 10:31:57.589000 +08:00
ARC2: Closing local archive destination LOG_ARCHIVE_DEST_1: '/opt/oracle/flash_recovery_area/ORCL/archivelog/1_54_814740574.dbf'
(ORCL)
查看dump的 systenm state可以发现如下内容。
Wait State:
auto_close=0 flags=0x21 boundary=(nil)/-1
Dumping Session Wait History:
0: waited for 'SQL*Net message from client'
driver id=62657100, #bytes=1, =0
wait_id=34 seq_num=35 snap_id=1
wait times (usecs) - snap=7810885 exc=7810885 total=7810885
wait times (usecs) - max=infinite
wait counts (exc) - calls=0 os=0
occurred after 10 microseconds of elapsed time
1: waited for 'SQL*Net message to client'
driver id=62657100, #bytes=1, =0
wait_id=33 seq_num=34 snap_id=1
wait times (usecs) - snap=4 exc=4 total=4
wait times (usecs) - max=infinite
wait counts (exc) - calls=0 os=0
occurred after 242 microseconds of elapsed time
2: waited for 'SQL*Net message from client'
driver id=62657100, #bytes=1, =0
wait_id=32 seq_num=33 snap_id=1
wait times (usecs) - snap=7195242 exc=7195242 total=7195242
wait times (usecs) - max=infinite
wait counts (exc) - calls=0 os=0
occurred after 8 microseconds of elapsed time
3: waited for 'SQL*Net message to client'
driver id=62657100, #bytes=1, =0
wait_id=31 seq_num=32 snap_id=1
wait times (usecs) - snap=3 exc=3 total=3
wait times (usecs) - max=infinite
wait counts (exc) - calls=0 os=0
occurred after 307 microseconds of elapsed time
4: waited for 'SQL*Net message from client'
driver id=62657100, #bytes=1, =0
wait_id=30 seq_num=31 snap_id=1
wait times (usecs) - snap=5702280 exc=5702280 total=5702280
wait times (usecs) - max=infinite
wait counts (exc) - calls=0 os=0
occurred after 16 microseconds of elapsed time
5: waited for 'SQL*Net message to client'
driver id=62657100, #bytes=1, =0
wait_id=29 seq_num=30 snap_id=1
wait times (usecs) - snap=6 exc=6 total=6
wait times (usecs) - max=infinite
wait counts (exc) - calls=0 os=0
occurred after 101 microseconds of elapsed time
6: waited for 'switch logfile command'
=0, =0, =0
wait_id=28 seq_num=29 snap_id=1
wait times (usecs) - snap=22023 exc=22023 total=22023
wait times (usecs) - max=5000000
wait counts (exc) - calls=1 os=1
occurred after 15 microseconds of elapsed time
7: waited for 'switch logfile command'
=0, =0, =0
wait_id=27 seq_num=28 snap_id=1
wait times (usecs) - snap=11589 exc=11589 total=11589
wait times (usecs) - max=5000000
wait counts (exc) - calls=1 os=1
occurred after 2312 microseconds of elapsed time
8: waited for 'SQL*Net message from client'
driver id=62657100, #bytes=1, =0
wait_id=26 seq_num=27 snap_id=1
wait times (usecs) - snap=6800643 exc=6800643 total=6800643
wait times (usecs) - max=infinite
wait counts (exc) - calls=0 os=0
occurred after 126 microseconds of elapsed time
9: waited for 'SQL*Net message to client'
driver id=62657100, #bytes=1, =0
wait_id=25 seq_num=26 snap_id=1
wait times (usecs) - snap=2 exc=2 total=2
wait times (usecs) - max=infinite
wait counts (exc) - calls=0 os=0
三）获得一个进程状态的dump
eg：
1* select spid,pid,program from v$process
------------------------------------ ----------- ------------------------------
db_recovery_file_dest_size big integer 280M
这个时候查看一下alert日志如下：
RC0: Failed to archive thread 1 sequence 54 (19502)
ARCH: Archival stopped, error occurred. Will continue retrying
ORACLE Instance ORCL - Archival Error
ORA-16038: log 2 sequence# 54 cannot be archived
ORA-19502: write error on file "", block number (block size=)
ORA-00312: online log 2 thread 1: '/opt/oracle/oradata/ORCL/redo02.log'
ORA-00312: online log 2 thread 1: '/opt/oracle/oradata/ORCL/redo002.log'
Errors in file /opt/oracle/diag/rdbms/orcl/ORCL/trace/ORCL_arc0_2366.trc:
ORA-16038: log 2 sequence# 54 cannot be archived
ORA-19502: write error on file "", block number (block size=)
ORA-00312: online log 2 thread 1: '/opt/oracle/oradata/ORCL/redo02.log'
ORA-00312: online log 2 thread 1: '/opt/oracle/oradata/ORCL/redo002.log'
2013-05-12 10:26:52.044000 +08:00
ARC1: Evaluating archive log 2 thread 1 sequence 54
ARC1: Failed to archive thread 1 sequence 54 (4)
ARCH: Archival stopped, error occurred. Will continue retrying
ORACLE Instance ORCL - Archival Error
ORA-16014: log 2 sequence# 54 not archived, no available destinations
ORA-00312: online log 2 thread 1: '/opt/oracle/oradata/ORCL/redo02.log'
ORA-00312: online log 2 thread 1: '/opt/oracle/oradata/ORCL/redo002.log'
Errors in file /opt/oracle/diag/rdbms/orcl/ORCL/trace/ORCL_arc1_2368.trc:
ORA-16014: log 2 sequence# 54 not archived, no available destinations
ORA-00312: online log 2 thread 1: '/opt/oracle/oradata/ORCL/redo02.log'
ORA-00312: online log 2 thread 1: '/opt/oracle/oradata/ORCL/redo002.log'
2013-05-12 10:31:52.058000 +08:00
ARC2: Evaluating archive log 2 thread 1 sequence 54
ARC2: Beginning to archive thread 1 sequence 54 (16366732-16394654) (ORCL)
ARC2: Creating local archive destination LOG_ARCHIVE_DEST_1: '/opt/oracle/flash_recovery_area/ORCL/archivelog/1_54_814740574.dbf' (thread 1 sequence 54)
(ORCL)
2013-05-12 10:31:57.589000 +08:00
ARC2: Closing local archive destination LOG_ARCHIVE_DEST_1: '/opt/oracle/flash_recovery_area/ORCL/archivelog/1_54_814740574.dbf'
(ORCL)
查看dump的 systenm state可以发现如下内容。
Wait State:
auto_close=0 flags=0x21 boundary=(nil)/-1
Dumping Session Wait History:
0: waited for 'SQL*Net message from client'
driver id=62657100, #bytes=1, =0
wait_id=34 seq_num=35 snap_id=1
wait times (usecs) - snap=7810885 exc=7810885 total=7810885
wait times (usecs) - max=infinite
wait counts (exc) - calls=0 os=0
occurred after 10 microseconds of elapsed time
1: waited for 'SQL*Net message to client'
driver id=62657100, #bytes=1, =0
wait_id=33 seq_num=34 snap_id=1
wait times (usecs) - snap=4 exc=4 total=4
wait times (usecs) - max=infinite
wait counts (exc) - calls=0 os=0
occurred after 242 microseconds of elapsed time
2: waited for 'SQL*Net message from client'
driver id=62657100, #bytes=1, =0
wait_id=32 seq_num=33 snap_id=1
wait times (usecs) - snap=7195242 exc=7195242 total=7195242
wait times (usecs) - max=infinite
wait counts (exc) - calls=0 os=0
occurred after 8 microseconds of elapsed time
3: waited for 'SQL*Net message to client'
driver id=62657100, #bytes=1, =0
wait_id=31 seq_num=32 snap_id=1
wait times (usecs) - snap=3 exc=3 total=3
wait times (usecs) - max=infinite
wait counts (exc) - calls=0 os=0
occurred after 307 microseconds of elapsed time
4: waited for 'SQL*Net message from client'
driver id=62657100, #bytes=1, =0
wait_id=30 seq_num=31 snap_id=1
wait times (usecs) - snap=5702280 exc=5702280 total=5702280
wait times (usecs) - max=infinite
wait counts (exc) - calls=0 os=0
occurred after 16 microseconds of elapsed time
5: waited for 'SQL*Net message to client'
driver id=62657100, #bytes=1, =0
wait_id=29 seq_num=30 snap_id=1
wait times (usecs) - snap=6 exc=6 total=6
wait times (usecs) - max=infinite
wait counts (exc) - calls=0 os=0
occurred after 101 microseconds of elapsed time
6: waited for 'switch logfile command'
=0, =0, =0
wait_id=28 seq_num=29 snap_id=1
wait times (usecs) - snap=22023 exc=22023 total=22023
wait times (usecs) - max=5000000
wait counts (exc) - calls=1 os=1
occurred after 15 microseconds of elapsed time
7: waited for 'switch logfile command'
=0, =0, =0
wait_id=27 seq_num=28 snap_id=1
wait times (usecs) - snap=11589 exc=11589 total=11589
wait times (usecs) - max=5000000
wait counts (exc) - calls=1 os=1
occurred after 2312 microseconds of elapsed time
8: waited for 'SQL*Net message from client'
driver id=62657100, #bytes=1, =0
wait_id=26 seq_num=27 snap_id=1
wait times (usecs) - snap=6800643 exc=6800643 total=6800643
wait times (usecs) - max=infinite
wait counts (exc) - calls=0 os=0
occurred after 126 microseconds of elapsed time
9: waited for 'SQL*Net message to client'
driver id=62657100, #bytes=1, =0
wait_id=25 seq_num=26 snap_id=1
wait times (usecs) - snap=2 exc=2 total=2
wait times (usecs) - max=infinite
wait counts (exc) - calls=0 os=0
三）获得一个进程状态的dump
eg：
1* select spid,pid,program from v$process
SPID PID PROGRAM
------------------------ ---------- --------------------------------------------------
1 PSEUDO
15284 2 oracle@gbh-oracle (PMON)
15286 3 oracle@gbh-oracle (VKTM)
15290 4 oracle@gbh-oracle (DIAG)
15292 5 oracle@gbh-oracle (DBRM)
15294 6 oracle@gbh-oracle (PSP0)
15300 7 oracle@gbh-oracle (MMAN)
15298 8 oracle@gbh-oracle (DIA0)
15302 9 oracle@gbh-oracle (DBW0)
15304 10 oracle@gbh-oracle (LGWR)
15306 11 oracle@gbh-oracle (CKPT)
------------------------ ---------- --------------------------------------------------
1 PSEUDO
15284 2 oracle@gbh-oracle (PMON)
15286 3 oracle@gbh-oracle (VKTM)
15290 4 oracle@gbh-oracle (DIAG)
15292 5 oracle@gbh-oracle (DBRM)
15294 6 oracle@gbh-oracle (PSP0)
15300 7 oracle@gbh-oracle (MMAN)
15298 8 oracle@gbh-oracle (DIA0)
15302 9 oracle@gbh-oracle (DBW0)
15304 10 oracle@gbh-oracle (LGWR)
15306 11 oracle@gbh-oracle (CKPT)
SPID PID PROGRAM
------------------------ ---------- --------------------------------------------------
15308 12 oracle@gbh-oracle (SMON)
15310 13 oracle@gbh-oracle (RECO)
15312 14 oracle@gbh-oracle (MMON)
15314 15 oracle@gbh-oracle (MMNL)
15316 16 oracle@gbh-oracle (D000)
15318 17 oracle@gbh-oracle (D001)
15320 18 oracle@gbh-oracle (D002)
15322 19 oracle@gbh-oracle (S000)
15324 20 oracle@gbh-oracle (S001)
15332 21 oracle@gbh-oracle (TNS V1-V3)
15331 22 oracle@gbh-oracle (RVWR)
------------------------ ---------- --------------------------------------------------
15308 12 oracle@gbh-oracle (SMON)
15310 13 oracle@gbh-oracle (RECO)
15312 14 oracle@gbh-oracle (MMON)
15314 15 oracle@gbh-oracle (MMNL)
15316 16 oracle@gbh-oracle (D000)
15318 17 oracle@gbh-oracle (D001)
15320 18 oracle@gbh-oracle (D002)
15322 19 oracle@gbh-oracle (S000)
15324 20 oracle@gbh-oracle (S001)
15332 21 oracle@gbh-oracle (TNS V1-V3)
15331 22 oracle@gbh-oracle (RVWR)
SPID PID PROGRAM
------------------------ ---------- --------------------------------------------------
15334 23 oracle@gbh-oracle (ARC0)
15336 24 oracle@gbh-oracle (ARC1)
15338 25 oracle@gbh-oracle (ARC2)
15340 26 oracle@gbh-oracle (ARC3)
15342 27 oracle@gbh-oracle (SMCO)
15346 28 oracle@gbh-oracle (FBDA)
15352 29 oracle@gbh-oracle (QMNC)
15379 30 oracle@gbh-oracle (W000)
15976 31 oracle@gbh-oracle (J000)
15375 32 oracle@gbh-oracle (CJQ0)
15401 33 oracle@gbh-oracle (q000)
------------------------ ---------- --------------------------------------------------
15334 23 oracle@gbh-oracle (ARC0)
15336 24 oracle@gbh-oracle (ARC1)
15338 25 oracle@gbh-oracle (ARC2)
15340 26 oracle@gbh-oracle (ARC3)
15342 27 oracle@gbh-oracle (SMCO)
15346 28 oracle@gbh-oracle (FBDA)
15352 29 oracle@gbh-oracle (QMNC)
15379 30 oracle@gbh-oracle (W000)
15976 31 oracle@gbh-oracle (J000)
15375 32 oracle@gbh-oracle (CJQ0)
15401 33 oracle@gbh-oracle (q000)
SPID PID PROGRAM
------------------------ ---------- --------------------------------------------------
15403 34 oracle@gbh-oracle (q001)
------------------------ ---------- --------------------------------------------------
15403 34 oracle@gbh-oracle (q001)
已选择34行。
SYS@conn#
SYS@conn#oradebug setospid 15334
Oracle pid: 23, Unix process pid: 15334, image: oracle@gbh-oracle (ARC0)
SYS@conn#oradebug unlimit
已处理的语句
SYS@conn#oradebug dump processstate 10
已处理的语句
SYS@conn#select * from v$diag_info where name like 'Default Trace File';
SYS@conn#oradebug setospid 15334
Oracle pid: 23, Unix process pid: 15334, image: oracle@gbh-oracle (ARC0)
SYS@conn#oradebug unlimit
已处理的语句
SYS@conn#oradebug dump processstate 10
已处理的语句
SYS@conn#select * from v$diag_info where name like 'Default Trace File';
INST_ID NAME
---------- ----------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 Default Trace File
/opt/oracle/diag/rdbms/orcl/ORCL/trace/ORCL_ora_15332.trc
四）getting a errorstacks dump；
我们可以获得process的errorstacks。
eg：
oradebug setospid （pid）
oradebug unlimit
oradebug dump errorstack 3；
五）使用oradebug来跟踪一个会话的sql语句
为什么药用oradebug进行sql trace呢？使用dbms_system这个包也是可以的，但是不如oradebug更加详细
，例如我们可以使用dbms_system.set_sql_trace_in_session(pid,serial,true)；但是只能是level1 ，如果需要获取更将详细的信息的话，那么就不行了，因此可以采用oradebug或是alter system set event 10046.
eg：
SYS@conn#select sid,serial#,paddr from v$session where username='SYS';
---------- ----------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 Default Trace File
/opt/oracle/diag/rdbms/orcl/ORCL/trace/ORCL_ora_15332.trc
四）getting a errorstacks dump；
我们可以获得process的errorstacks。
eg：
oradebug setospid （pid）
oradebug unlimit
oradebug dump errorstack 3；
五）使用oradebug来跟踪一个会话的sql语句
为什么药用oradebug进行sql trace呢？使用dbms_system这个包也是可以的，但是不如oradebug更加详细
，例如我们可以使用dbms_system.set_sql_trace_in_session(pid,serial,true)；但是只能是level1 ，如果需要获取更将详细的信息的话，那么就不行了，因此可以采用oradebug或是alter system set event 10046.
eg：
SYS@conn#select sid,serial#,paddr from v$session where username='SYS';
SID SERIAL# PADDR
---------- ---------- --------
188 122 28AE0B0C
---------- ---------- --------
188 122 28AE0B0C
SYS@conn#select pid,spid,program from v$process where addr='28AE0B0C';
PID SPID
---------- ------------------------
PROGRAM
------------------------------------------------
31 18573
oracle@gbh-oracle (TNS V1-V3)
---------- ------------------------
PROGRAM
------------------------------------------------
31 18573
oracle@gbh-oracle (TNS V1-V3)
SYS@conn#oradebug setospid 18573
Oracle pid: 31, Unix process pid: 18573, image: oracle@gbh-oracle (TNS V1-V3)
SYS@conn#oradebug event 10046 trace name context forever,level 4;
已处理的语句
SYS@conn#select * from scott.emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- -------------- ---------- ----------
DEPTNO
----------
2001 rock
50
---------- ---------- --------- ---------- -------------- ---------- ----------
DEPTNO
----------
2001 rock
50
2000 Rhys admin 7689 24-4月 -12 20001 300
20
20
7369 SMITH CLERK 7902 17-12月-80 2800
20
20
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- -------------- ---------- ----------
DEPTNO
----------
7566 JONES MANAGER 7839 02-4月 -81 2975
20
7788 SCOTT ANALYST 7566 19-4月 -87 3000
20
20
7876 ADAMS CLERK 7788 23-5月 -87 3100
20
20
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- -------------- ---------- ----------
DEPTNO
----------
7844 TURNER SALESMAN 7698 08-9月 -81 3500 0
30
7698 BLAKE MANAGER 7839 01-5月 -81 2850
30
30
7654 MARTIN SALESMAN 7698 28-9月 -81 3250 1400
30
30
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- -------------- ---------- ----------
DEPTNO
----------
7521 WARD SALESMAN 7698 22-2月 -81 3250 500
30
7499 ALLEN SALESMAN 7698 20-2月 -81 3600 300
30
30
2002 xiaohai 2000
已选择12行。
SYS@conn#select * from v$diag;
select * from v$diag
*
第 1 行出现错误:
ORA-00942: 表或视图不存在
select * from v$diag
*
第 1 行出现错误:
ORA-00942: 表或视图不存在
SYS@conn#select * from v$diag_info;
INST_ID NAME
---------- ----------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
1 Diag Enabled
TRUE
---------- ----------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
1 Diag Enabled
TRUE
1 ADR Base
/opt/oracle
/opt/oracle
1 ADR Home
/opt/oracle/diag/rdbms/orcl/ORCL
/opt/oracle/diag/rdbms/orcl/ORCL
INST_ID NAME
---------- ----------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
1 Diag Trace
/opt/oracle/diag/rdbms/orcl/ORCL/trace
1 Diag Alert
/opt/oracle/diag/rdbms/orcl/ORCL/alert
/opt/oracle/diag/rdbms/orcl/ORCL/alert
1 Diag Incident
/opt/oracle/diag/rdbms/orcl/ORCL/incident
/opt/oracle/diag/rdbms/orcl/ORCL/incident
INST_ID NAME
---------- ----------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
1 Diag Cdump
/opt/oracle/diag/rdbms/orcl/ORCL/cdump
1 Health Monitor
/opt/oracle/diag/rdbms/orcl/ORCL/hm
/opt/oracle/diag/rdbms/orcl/ORCL/hm
1 Default Trace File
/opt/oracle/diag/rdbms/orcl/ORCL/trace/ORCL_ora_18573.trc
/opt/oracle/diag/rdbms/orcl/ORCL/trace/ORCL_ora_18573.trc
INST_ID NAME
---------- ----------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
1 Active Problem Count
3
1 Active Incident Count
1013
可以看一下这个语句的查询过程：
*** 2013-07-30 17:20:08.067
Processing Oradebug command 'event 10046 trace name context forever,level 4'
1013
可以看一下这个语句的查询过程：
*** 2013-07-30 17:20:08.067
Processing Oradebug command 'event 10046 trace name context forever,level 4'
*** 2013-07-30 17:20:08.068
Oradebug command 'event 10046 trace name context forever,level 4' console output: <none>
Oradebug command 'event 10046 trace name context forever,level 4' console output: <none>
*** 2013-07-30 17:20:22.234
=====================
PARSING IN CURSOR #2 len=493 dep=1 uid=0 oct=3 lid=0 tim=1375176022234299 hv=2584065658 ad='250cdb8c' sqlid='1gu8t96d0bdmu'
select t.ts#,t.file#,t.block#,nvl(t.bobj#,0),nvl(t.tab#,0),t.intcols,nvl(t.clucols,0),t.audit$,t.flags,t.pctfree$,t.pctused$,t.initrans,t.maxtrans,t.rowcnt,t.blkcnt,t.empcnt,t.avgspc,t.chncnt,t.avgrln,t.analyzetime,t.samplesize,t.cols,t.property,nvl(t.degree,1),nvl(t.instances,1),t.avgspc_flb,t.flbcnt,t.kernelcols,nvl(t.trigflag, 0),nvl(t.spare1,0),nvl(t.spare2,0),t.spare4,t.spare6,ts.cachedblk,ts.cachehit,ts.logicalread from tab$ t, tab_stats$ ts where t.obj#= :1 and t.obj# = ts.obj# (+)
END OF STMT
PARSE #2:c=0,e=1940,p=0,cr=0,cu=0,mis=1,r=
。。。。。。。。。。。。。。。。。
当完成语句跟踪之后需要关闭该sql语句的跟踪功能。
oradebug event 10046 trace name context off;
当然了我们在完成sql语句跟踪会会生产一个跟踪文件，为了便于阅读，我可使用tkprof进行格式化，
eg：
oracle@gbh-oracle:/opt/oracle/diag/rdbms/orcl/ORCL/trace> ls -ltr
total 456
-rw-r----- 1 oracle oinstall 241 2013-07-30 17:21 ORCL_ora_18573.trm
-rw-r----- 1 oracle oinstall 404822 2013-07-30 17:21 ORCL_ora_18573.trc
-rw-r----- 1 oracle oinstall 61 2013-07-30 17:26 ORCL_ora_19092.trm
-rw-r----- 1 oracle oinstall 1125 2013-07-30 17:26 ORCL_ora_19092.trc
-rw-r----- 1 oracle oinstall 60 2013-07-30 17:30 ORCL_arc3_15340.trm
-rw-r----- 1 oracle oinstall 2104 2013-07-30 17:30 ORCL_arc3_15340.trc
-rw-r----- 1 oracle oinstall 60 2013-07-30 17:30 ORCL_arc1_15336.trm
-rw-r----- 1 oracle oinstall 2104 2013-07-30 17:30 ORCL_arc1_15336.trc
-rw-r----- 1 oracle oinstall 60 2013-07-30 17:30 ORCL_arc0_15334.trm
-rw-r----- 1 oracle oinstall 2104 2013-07-30 17:30 ORCL_arc0_15334.trc
-rw-r----- 1 oracle oinstall 188 2013-07-30 17:31 ORCL_arc2_15338.trm
-rw-r----- 1 oracle oinstall 16943 2013-07-30 17:31 ORCL_arc2_15338.trc
oracle@gbh-oracle:/opt/oracle/diag/rdbms/orcl/ORCL/trace> tkprof ORCL_ora_18573.trc
output = /opt/oracle/tkprof
=====================
PARSING IN CURSOR #2 len=493 dep=1 uid=0 oct=3 lid=0 tim=1375176022234299 hv=2584065658 ad='250cdb8c' sqlid='1gu8t96d0bdmu'
select t.ts#,t.file#,t.block#,nvl(t.bobj#,0),nvl(t.tab#,0),t.intcols,nvl(t.clucols,0),t.audit$,t.flags,t.pctfree$,t.pctused$,t.initrans,t.maxtrans,t.rowcnt,t.blkcnt,t.empcnt,t.avgspc,t.chncnt,t.avgrln,t.analyzetime,t.samplesize,t.cols,t.property,nvl(t.degree,1),nvl(t.instances,1),t.avgspc_flb,t.flbcnt,t.kernelcols,nvl(t.trigflag, 0),nvl(t.spare1,0),nvl(t.spare2,0),t.spare4,t.spare6,ts.cachedblk,ts.cachehit,ts.logicalread from tab$ t, tab_stats$ ts where t.obj#= :1 and t.obj# = ts.obj# (+)
END OF STMT
PARSE #2:c=0,e=1940,p=0,cr=0,cu=0,mis=1,r=
。。。。。。。。。。。。。。。。。
当完成语句跟踪之后需要关闭该sql语句的跟踪功能。
oradebug event 10046 trace name context off;
当然了我们在完成sql语句跟踪会会生产一个跟踪文件，为了便于阅读，我可使用tkprof进行格式化，
eg：
oracle@gbh-oracle:/opt/oracle/diag/rdbms/orcl/ORCL/trace> ls -ltr
total 456
-rw-r----- 1 oracle oinstall 241 2013-07-30 17:21 ORCL_ora_18573.trm
-rw-r----- 1 oracle oinstall 404822 2013-07-30 17:21 ORCL_ora_18573.trc
-rw-r----- 1 oracle oinstall 61 2013-07-30 17:26 ORCL_ora_19092.trm
-rw-r----- 1 oracle oinstall 1125 2013-07-30 17:26 ORCL_ora_19092.trc
-rw-r----- 1 oracle oinstall 60 2013-07-30 17:30 ORCL_arc3_15340.trm
-rw-r----- 1 oracle oinstall 2104 2013-07-30 17:30 ORCL_arc3_15340.trc
-rw-r----- 1 oracle oinstall 60 2013-07-30 17:30 ORCL_arc1_15336.trm
-rw-r----- 1 oracle oinstall 2104 2013-07-30 17:30 ORCL_arc1_15336.trc
-rw-r----- 1 oracle oinstall 60 2013-07-30 17:30 ORCL_arc0_15334.trm
-rw-r----- 1 oracle oinstall 2104 2013-07-30 17:30 ORCL_arc0_15334.trc
-rw-r----- 1 oracle oinstall 188 2013-07-30 17:31 ORCL_arc2_15338.trm
-rw-r----- 1 oracle oinstall 16943 2013-07-30 17:31 ORCL_arc2_15338.trc
oracle@gbh-oracle:/opt/oracle/diag/rdbms/orcl/ORCL/trace> tkprof ORCL_ora_18573.trc
output = /opt/oracle/tkprof
TKPROF: Release 11.1.0.6.0 - Production on 星期二 7月 30 17:34:59 2013
Copyright (c) 1982, 2007, Oracle. All rights reserved.
oracle@gbh-oracle:/opt/oracle/diag/rdbms/orcl/ORCL/trace> vi /opt/oracle/tkprof.prf
然后可以看到非常详细的内容，格式化的非常便于查看：
nvl(i.spare1,i.intcols),i.spare4,i.spare2,i.spare6,decode(i.pctthres$,null,
null,mod(trunc(i.pctthres$/256),256)),ist.cachedblk,ist.cachehit,
ist.logicalread
from
ind$ i, ind_stats$ ist, (select enabled, min(cols) unicols,
min(to_number(bitand(defer,1))) deferrable#,min(to_number(bitand(defer,4)))
valid# from cdef$ where obj#=:1 and enabled > 1 group by enabled) c where
i.obj#=c.enabled(+) and i.obj# = ist.obj#(+) and i.bo#=:1 order by i.obj#
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.00 0.00 0 0 0 0
Execute 4 0.02 0.01 0 0 0 0
Fetch 8 0.00 0.00 0 24 0 4
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 14 0.02 0.01 0 24 0 4
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 1)
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT ORDER BY (cr=7 pr=0 pw=0 time=0 us cost=7 size=372 card=2)
1 HASH JOIN OUTER (cr=7 pr=0 pw=0 time=0 us cost=6 size=372 card=2)
1 NESTED LOOPS OUTER (cr=4 pr=0 pw=0 time=0 us cost=2 size=286 card=2)
1 TABLE ACCESS CLUSTER IND$ (cr=3 pr=0 pw=0 time=0 us cost=2 size=182 card=2)
1 INDEX UNIQUE SCAN I_OBJ# (cr=2 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 3)
0 TABLE ACCESS BY INDEX ROWID IND_STATS$ (cr=1 pr=0 pw=0 time=0 us cost=0 size=52 card=1)
0 INDEX UNIQUE SCAN I_IND_STATS$_OBJ# (cr=1 pr=0 pw=0 time=0 us cost=0 size=0 card=1)(object id 419)
1 VIEW (cr=3 pr=0 pw=0 time=0 us cost=3 size=43 card=1)
1 SORT GROUP BY (cr=3 pr=0 pw=0 time=0 us cost=3 size=15 card=1)
1 TABLE ACCESS BY INDEX ROWID CDEF$ (cr=3 pr=0 pw=0 time=0 us cost=2 size=15 card=1)
2 INDEX RANGE SCAN I_CDEF2 (cr=2 pr=0 pw=0 time=2 us cost=1 size=0 card=3)(object id 54)
------- ---------------------------------------------------
1 SORT ORDER BY (cr=7 pr=0 pw=0 time=0 us cost=7 size=372 card=2)
1 HASH JOIN OUTER (cr=7 pr=0 pw=0 time=0 us cost=6 size=372 card=2)
1 NESTED LOOPS OUTER (cr=4 pr=0 pw=0 time=0 us cost=2 size=286 card=2)
1 TABLE ACCESS CLUSTER IND$ (cr=3 pr=0 pw=0 time=0 us cost=2 size=182 card=2)
1 INDEX UNIQUE SCAN I_OBJ# (cr=2 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 3)
0 TABLE ACCESS BY INDEX ROWID IND_STATS$ (cr=1 pr=0 pw=0 time=0 us cost=0 size=52 card=1)
0 INDEX UNIQUE SCAN I_IND_STATS$_OBJ# (cr=1 pr=0 pw=0 time=0 us cost=0 size=0 card=1)(object id 419)
1 VIEW (cr=3 pr=0 pw=0 time=0 us cost=3 size=43 card=1)
1 SORT GROUP BY (cr=3 pr=0 pw=0 time=0 us cost=3 size=15 card=1)
1 TABLE ACCESS BY INDEX ROWID CDEF$ (cr=3 pr=0 pw=0 time=0 us cost=2 size=15 card=1)
2 INDEX RANGE SCAN I_CDEF2 (cr=2 pr=0 pw=0 time=2 us cost=1 size=0 card=3)(object id 54)
********************************************************************************
"~/tkprof.prf" [converted] 948L, 45808C 141,1 13%
SQL ID : 7ng34ruy5awxq
select i.obj#,i.ts#,i.file#,i.block#,i.intcols,i.type#,i.flags,i.property,
i.pctfree$,i.initrans,i.maxtrans,i.blevel,i.leafcnt,i.distkey,i.lblkkey,
i.dblkkey,i.clufac,i.cols,i.analyzetime,i.samplesize,i.dataobj#,
nvl(i.degree,1),nvl(i.instances,1),i.rowcnt,mod(i.pctthres$,256),
i.indmethod#,i.trunccnt,nvl(c.unicols,0),nvl(c.deferrable#+c.valid#,0),
nvl(i.spare1,i.intcols),i.spare4,i.spare2,i.spare6,decode(i.pctthres$,null,
null,mod(trunc(i.pctthres$/256),256)),ist.cachedblk,ist.cachehit,
ist.logicalread
from
ind$ i, ind_stats$ ist, (select enabled, min(cols) unicols,
min(to_number(bitand(defer,1))) deferrable#,min(to_number(bitand(defer,4)))
valid# from cdef$ where obj#=:1 and enabled > 1 group by enabled) c where
i.obj#=c.enabled(+) and i.obj# = ist.obj#(+) and i.bo#=:1 order by i.obj#
"~/tkprof.prf" [converted] 948L, 45808C 141,1 13%
SQL ID : 7ng34ruy5awxq
select i.obj#,i.ts#,i.file#,i.block#,i.intcols,i.type#,i.flags,i.property,
i.pctfree$,i.initrans,i.maxtrans,i.blevel,i.leafcnt,i.distkey,i.lblkkey,
i.dblkkey,i.clufac,i.cols,i.analyzetime,i.samplesize,i.dataobj#,
nvl(i.degree,1),nvl(i.instances,1),i.rowcnt,mod(i.pctthres$,256),
i.indmethod#,i.trunccnt,nvl(c.unicols,0),nvl(c.deferrable#+c.valid#,0),
nvl(i.spare1,i.intcols),i.spare4,i.spare2,i.spare6,decode(i.pctthres$,null,
null,mod(trunc(i.pctthres$/256),256)),ist.cachedblk,ist.cachehit,
ist.logicalread
from
ind$ i, ind_stats$ ist, (select enabled, min(cols) unicols,
min(to_number(bitand(defer,1))) deferrable#,min(to_number(bitand(defer,4)))
valid# from cdef$ where obj#=:1 and enabled > 1 group by enabled) c where
i.obj#=c.enabled(+) and i.obj# = ist.obj#(+) and i.bo#=:1 order by i.obj#
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.00 0.00 0 0 0 0
Execute 4 0.02 0.01 0 0 0 0
Fetch 8 0.00 0.00 0 24 0 4
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 14 0.02 0.01 0 24 0 4
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 1)
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT ORDER BY (cr=7 pr=0 pw=0 time=0 us cost=7 size=372 card=2)
1 HASH JOIN OUTER (cr=7 pr=0 pw=0 time=0 us cost=6 size=372 card=2)
1 NESTED LOOPS OUTER (cr=4 pr=0 pw=0 time=0 us cost=2 size=286 card=2)
1 TABLE ACCESS CLUSTER IND$ (cr=3 pr=0 pw=0 time=0 us cost=2 size=182 card=2)
1 INDEX UNIQUE SCAN I_OBJ# (cr=2 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 3)
0 TABLE ACCESS BY INDEX ROWID IND_STATS$ (cr=1 pr=0 pw=0 time=0 us cost=0 size=52 card=1)
0 INDEX UNIQUE SCAN I_IND_STATS$_OBJ# (cr=1 pr=0 pw=0 time=0 us cost=0 size=0 card=1)(object id 419)
1 VIEW (cr=3 pr=0 pw=0 time=0 us cost=3 size=43 card=1)
1 SORT GROUP BY (cr=3 pr=0 pw=0 time=0 us cost=3 size=15 card=1)
1 TABLE ACCESS BY INDEX ROWID CDEF$ (cr=3 pr=0 pw=0 time=0 us cost=2 size=15 card=1)
2 INDEX RANGE SCAN I_CDEF2 (cr=2 pr=0 pw=0 time=2 us cost=1 size=0 card=3)(object id 54)
------- ---------------------------------------------------
1 SORT ORDER BY (cr=7 pr=0 pw=0 time=0 us cost=7 size=372 card=2)
1 HASH JOIN OUTER (cr=7 pr=0 pw=0 time=0 us cost=6 size=372 card=2)
1 NESTED LOOPS OUTER (cr=4 pr=0 pw=0 time=0 us cost=2 size=286 card=2)
1 TABLE ACCESS CLUSTER IND$ (cr=3 pr=0 pw=0 time=0 us cost=2 size=182 card=2)
1 INDEX UNIQUE SCAN I_OBJ# (cr=2 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 3)
0 TABLE ACCESS BY INDEX ROWID IND_STATS$ (cr=1 pr=0 pw=0 time=0 us cost=0 size=52 card=1)
0 INDEX UNIQUE SCAN I_IND_STATS$_OBJ# (cr=1 pr=0 pw=0 time=0 us cost=0 size=0 card=1)(object id 419)
1 VIEW (cr=3 pr=0 pw=0 time=0 us cost=3 size=43 card=1)
1 SORT GROUP BY (cr=3 pr=0 pw=0 time=0 us cost=3 size=15 card=1)
1 TABLE ACCESS BY INDEX ROWID CDEF$ (cr=3 pr=0 pw=0 time=0 us cost=2 size=15 card=1)
2 INDEX RANGE SCAN I_CDEF2 (cr=2 pr=0 pw=0 time=2 us cost=1 size=0 card=3)(object id 54)
********************************************************************************
。。。。。。。。。。。。。。。
六）使用oradebug 跟踪error
eg：
如果我们在日志中看到ora-1200这样的错误，我们可以指定一个pid然后进行跟踪此错误:
oradebug event 1200 trace name errorstack level 3;
然后我们使用tkprof工具进行格式化。就可以查看了。
。。。。。。。。。。。。。。。
六）使用oradebug 跟踪error
eg：
如果我们在日志中看到ora-1200这样的错误，我们可以指定一个pid然后进行跟踪此错误:
oradebug event 1200 trace name errorstack level 3;
然后我们使用tkprof工具进行格式化。就可以查看了。
309
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
