tbstat:实时监控数据库统计状态的小工具
用perl写了一个简单的工具，用于实时查看数据库的统计状态信息，展现信息主要来源于Oracle数据字典中v$systat和v$system_event。写这个工具的初衷，是因为目前我们对于数据库的监控，更多的是分钟级别抽样的数据来绘制的图形，粒度相对还比较粗，有一些比较深的问题，需要更加细粒度的数据。而如果把监控的粒度做到秒级，则收集的数据量就会非常大，因此需要一个平衡，平时采用分钟级别的抽样数据已经足够用于预警，而秒级的则用于某个具体问题的分析。
当前tbstat功能还比较简单，类似于iostat/vmstat等os工具，tbstat可以通过指定抽样间隔和抽样次数，来循环抓取Oracle的统计状态信息。tbstat支持三个参数 -i 表示间隔时间 -c 表示循环次数 -n 表示需要查看的统计信息的名字(使用前后%的like来查询)
- tbstat -i 2 -c 10 表示间隔时间2s，循环次数10次，展示经过人工筛选的36项统计信息
- tbstat -i 2 -c 10 -n parse 表示间隔时间2s，循环次数10次，展示所有名字包含parse的统计信息
- tbstat -i 2 -c 10 -n all 表示间隔时间2s，循环次数10次，展示所有不为零的统计信息
也可以使用简化的参数输入方法，第一位表示间隔时间，第二位表示循环次数，第三位表示统计信息名。直接敲入tbstat，则默认的参数为间隔时间10s，次数无限，经过挑选过滤的一些常用的v$sysstat中的统计信息。如果name参数传入的值是event，则展示v$system_event中的等待事件的信息。
$tbstat 1 0
--------------------------------------------------------------------------
-- tbstat v0.3.3 --- a tool for oracle system statistics and event.
-- Powered by NinGoo.net
--------------------------------------------------------------------------
CPU used by this session: 40 CR blocks created: 5
DBWR checkpoint buffers written: 569 DBWR undo block writes: 64
bytes received via SQL*Net from client: 314297 bytes sent via SQL*Net to client: 2761660
cleanouts only - consistent read gets: 4 consistent gets: 48855
db block changes: 2122 db block gets: 3714
enqueue requests: 900 enqueue waits: 7
execute count: 3145 free buffer requested: 1402
index crx upgrade (positioned): 3 index fast full scans (full): 0
leaf node 90-10 splits: 0 leaf node splits: 0
logons cumulative: 1 parse count (failures): 0
parse count (hard): 0 physical reads: 1546
physical reads cache prefetch: 0 physical writes: 603
redo size: 618436 redo synch time: 16
redo synch writes: 181 redo write time: 15
redo writes: 174 rollbacks only - consistent read gets: 0
sorts (disk): 0 sorts (memory): 259
table scans (long tables): 0 table scans (short tables): 9
transaction rollbacks: 0 user commits: 182
$tbstat 1 0 event
-------------------------------------------------------------------------------
-- tbstat v0.3.3 --- a tool for oracle system statistics and event.
-- Powered by NinGoo.net
-------------------------------------------------------------------------------
Event Name: waits time Event Name: waits time
--------------------------------------------------------------------------------------------
LGWR wait for redo copy: 1 0.01 SQL*Net more data from client: 151 19.95
SQL*Net more data to client: 1218 0.01 buffer busy waits: 2 0.01
control file parallel write: 1 0.51 control file sequential read: 1 0.26
cursor: pin S: 0 0.00 cursor: pin S wait on X: 0 0.00
db file parallel read: 0 0.00 db file parallel write: 0 0.00
db file scattered read: 0 0.00 db file sequential read: 2040 3.43
direct path read: 269 0.71 direct path read temp: 0 0.00
direct path write: 23 0.26 direct path write temp: 0 0.00
enq: CF - contention: 0 0.00 enq: HW - contention: 7 9.00
enq: SQ - contention: 0 0.00 enq: TX - allocate ITL entry: 0 0.00
enq: TX - index contention: 0 0.00 enq: TX - row lock contention: 0 0.00
latch free: 0 0.00 latch: cache buffers chains: 0 0.00
latch: library cache: 0 0.00 latch: redo writing: 0 0.00
latch: session allocation: 0 0.00 library cache lock: 0 0.00
log buffer space: 0 0.00 log file parallel write: 145 0.60
log file sequential read: 145 0.53 log file switch completion: 0 0.00
log file sync: 147 0.78 os thread startup: 0 0.00
read by other session: 0 0.00 row cache lock: 0 0.00
undo segment extension: 0 0.00
如果输入的name是精确匹配到只有一条统计信息的，会在后面打印出间隔时间内排名前10的sid的值。利用此功能，可以很方便的抓到造成某些统计信息异常的会话和SQL语句，会话和SQL信息是通过关联v$session来获取的。因此需要注意，如果统计信息对应的事件持续时间很短，从v$session里抓取到的sql可能并不是造成统计信息升高的罪魁祸首，但是sid一般来说还是准确的，因为应用采用的大多是连接池来连接数据库的，因此还是可以更具sid和machine信息来看看造成异常的是哪个具体的应用。
例如，全表扫描一般会导致physical reads cache prefetch等待事件，因此可以通过查看该事件对应的top sid来获得具体的语句，当然，不是所有的physical reads cache prefetch都是全表扫描导致的，因此对于获得的结果，还需要DBA根据具体情况做进一步分析：
$tbstat 1 0 'physical reads cache prefetch'
-------------------------------------------------------------------------------
-- tbstat v0.3.3 --- a tool for oracle system statistics and event.
-- Powered by NinGoo.net
-------------------------------------------------------------------------------
physical reads cache prefetch: 526
sid value % machine sql_id
---------- ----------- ----- ------------------- --------------
2928 302 69.7 test11 79db58a3dg921
4902 67 15.5 test71 79db58a3dg921
4821 64 14.8 test33 3afdq50xt03ch
4544 0 0.0 test54 3afdq50xt03ch
1801 0 0.0 test06 79db58a3dg921
2830 0 0.0 test12 79db58a3dg921
898 0 0.0 test09 4n7675hwwcndc
1031 0 0.0 test16 79db58a3dg921
463 0 0.0 test04 3afdq50xt03ch
1364 0 0.0 test08 cq749u66x06uj
1408 0 0.0 test27 39rbqj3ck76w3
722 0 0.0 test37 26hdkf07336uf
当然，tbstat只是一个用于抽取统计状态的小工具而已，如果要用于故障诊断，则还是要求DBA对于v$systat和v$system_event中各种统计和事件非常的熟悉。tbstat使用了DBD::Oracle以sysdba身份来连接数据库，因此需要为Perl安装DBI和DBD::Oracle模块，并且在数据库服务器本机上执行。如果你对于这个工具有兴趣，可以在这里下载源代码，使用过程中，如果有什么建议和需求，欢迎告诉我。
使用BBED帮助理解Oracle数据块结构
BBED是Oracle提供的块编辑器，借助BBED，可以帮助我们更好的理解Oracle的Block的结构。当然，反过来说，也只有更加理解块的结构，才能更好的利用BBED完成某些特殊情况下的灾难恢复。
Oracle Data Block的结构简图如下，其中从Data header到Row Data部分合称Data Layer:
--------------------- - Cache Layer - --------------------- - Transaction Layer - --------------------- - Data Header - --------------------- - Table Directory - --------------------- - Row Directory - --------------------- - Free Space - --------------------- - Row Data - --------------------- - Tailchk - ---------------------
通过bbed的map命令，可以看到数据块内部的一些数据结构名：
BBED> map File: /u01/oracle/oradata/dbmon/system.dbf (1) Block: 31729 Dba:0x00407bf1 ------------------------------------------------------------ KTB Data Block (Table/Cluster) struct kcbh, 20 bytes @0 struct ktbbh, 72 bytes @20 struct kdbh, 14 bytes @92 struct kdbt[1], 4 bytes @106 sb2 kdbr[336] @110 ub1 freespace[821] @782 ub1 rowdata[6585] @1603 ub4 tailchk @8188
Cache Layer：Block的第一部分，长度为20字节，内部数据结构名为kcbh，包括
type_kcbh:块类型(table/index,rollback segment,temporary segment等)
frmt_kcbh:块格式(v6,v7,v8)
rdba_kcbh:块地址DBA
bas_kcbh/wrp_kcbh:SCN
seq_kcbh:块的序列号
flg_kcbh:块的标志
BBED> p kcbh struct kcbh, 20 bytes @0 ub1 type_kcbh @0 0x06 ub1 frmt_kcbh @1 0xa2 ub1 spare1_kcbh @2 0x00 ub1 spare2_kcbh @3 0x00 ub4 rdba_kcbh @4 0x00407bf1 ub4 bas_kcbh @8 0xd6449de8 ub2 wrp_kcbh @12 0x0595 ub1 seq_kcbh @14 0x03 ub1 flg_kcbh @15 0x04 (KCBHFCKV) ub2 chkval_kcbh @16 0x9130 ub2 spare3_kcbh @18 0x0000
Transaction Layer：内部结构名kcbbh。分成两部分，第一部分为固定长度，长度为24字节，包含事务相关的一些基本信息。第二部分为可变长度，包含itl，长度根据itl条目的个数变化，每个itl长度为24字节，内部结构名ktbbhitl。
BBED> p ktbbh
struct ktbbh, 72 bytes @20
ub1 ktbbhtyp @20 0x01 (KDDBTDATA)
union ktbbhsid, 4 bytes @24
ub4 ktbbhsg1 @24 0x000050fc
ub4 ktbbhod1 @24 0x000050fc
struct ktbbhcsc, 8 bytes @28
ub4 kscnbas @28 0xd6449de7
ub2 kscnwrp @32 0x0595
b2 ktbbhict @36 2
ub1 ktbbhflg @38 0x02 (NONE)
ub1 ktbbhfsl @39 0x00
ub4 ktbbhfnx @40 0x00000000
struct ktbbhitl[0], 24 bytes @44
struct ktbitxid, 8 bytes @44
ub2 kxidusn @44 0x0008
ub2 kxidslt @46 0x000c
ub4 kxidsqn @48 0x0000e991
struct ktbituba, 8 bytes @52
ub4 kubadba @52 0x0080222e
ub2 kubaseq @56 0x03a4
ub1 kubarec @58 0x22
ub2 ktbitflg @60 0x8000 (KTBFCOM)
union _ktbitun, 2 bytes @62
b2 _ktbitfsc @62 1429
ub2 _ktbitwrp @62 0x0595
ub4 ktbitbas @64 0xd6449de6
struct ktbbhitl[1], 24 bytes @68
struct ktbitxid, 8 bytes @68
ub2 kxidusn @68 0x0008
ub2 kxidslt @70 0x0015
ub4 kxidsqn @72 0x0000e992
struct ktbituba, 8 bytes @76
ub4 kubadba @76 0x0080222e
ub2 kubaseq @80 0x03a4
ub1 kubarec @82 0x23
ub2 ktbitflg @84 0x0001 (NONE)
union _ktbitun, 2 bytes @86
b2 _ktbitfsc @86 0
ub2 _ktbitwrp @86 0x0000
ub4 ktbitbas @88 0x00000000
Data Layer：包括Data Header，Table Directory，Row Directory，Free Space和Row Data。其中
Data Header：长度14字节，内部数据结构名kdbh
BBED> p kdbh struct kdbh, 14 bytes @92 ub1 kdbhflag @92 0x00 (NONE) b1 kdbhntab @93 1 b2 kdbhnrow @94 336 sb2 kdbhfrre @96 -1 sb2 kdbhfsbo @98 690 sb2 kdbhfseo @100 1511 b2 kdbhavsp @102 821 b2 kdbhtosp @104 821
Table Directory: 一般table只有一个条目，cluster则有一个或多个条目。每个条目长4字节，内部数据结构名kdbt。
BBED> p kdbt struct kdbt[0], 4 bytes @106 b2 kdbtoffs @106 0 b2 kdbtnrow @108 336
Row Directory：数目由块中数据的行数决定，每个条目长2字节，内部数据结构名kdbr
BBED> p kdbr sb2 kdbr[0] @110 7998 sb2 kdbr[1] @112 8017 ... sb2 kdbr[335] @780 1511
Free Space：表示数据块中可用空间，内部数据结构名freespace
Row Data：表示实际的数据，内部数据结构名rowdata
Tailchk:保存在块结尾用于校验的数据，长度4个字节，内部结构名tailchk。
BBED>p tailchk ub4 tailchk @8188 0x9de80603
注意到tailchk=bas_kcbh低2字节(9de8)+type_kcbh(06)+seq_kcbh(03)
安装使用BBED
在一次故障处理中，使用到了BBED(Oracle Block Brower and EDitor Tool)，这是Oracle一款内部工具，可以直接修改Oracle数据文件块的内容，在一些极端恢复场景下比较有用。该工具不受Oracle支持，所以默认是没有生成可执行文件的，在使用前需要重新连接。
在9i/10g中连接生成bbed:
cd $ORACLE_HOME/rdbms/lib make -f ins_rdbms.mk $ORACLE_HOME/rdbms/lib/bbed
以上生成的bbed可执行文件在$ORACLE_HOME/rdbms/lib目录，可以复制到其他位置或者其他同Oracle版本的机器上运行。也可通过以下命令将bbed生成到$ORACLE_HOME/bin目录
make -f ins_rdbms.mk BBED=$ORACLE_HOME/bin/bbed $ORACLE_HOME/bin/bbed
在11g中生成bbed，需要先从10g中复制如下文件到相应目录，然后再执行上述连接命令:
$ORACLE_HOME/rdbms/lib/ssbbded.o $ORACLE_HOME/rdbms/lib/sbbdpt.o $ORACLE_HOME/rdbms/mesg/bbedus.msb
BBED设置了口令保护，密码为blockedit。BBED的命令行参数：
$bbed help=yes PASSWORD - Required parameter FILENAME - Database file name BLOCKSIZE - Database block size LISTFILE - List file name MODE - [browse/edit] SPOOL - Spool to logfile [no/yes] CMDFILE - BBED command file name LOGFILE - BBED log file name PARFILE - Parameter file name BIFILE - BBED before-image file name REVERT - Rollback changes from BIFILE [no/yes] SILENT - Hide banner [no/yes] HELP - Show all valid parameters [no/yes]
一般将一些常用选项写入到一个parfile中：
blocksize=8192 listfile=filelist.txt mode=edit
其中filelist.txt列出了需要使用BBED编辑的数据文件列表，格式为
文件编号 文件名字 文件大小 1 /u01/oracle/oradata/NinGoo/system.dbf 2097160192 2 /u01/oracle/oradata/NinGoo/test.dbf 2097160192
这里的文件编号不一定要和数据库里的file_id相同，当然最好能设置相同，以免在处理过程中搞混淆了。
$bbed parfile=bbed.par Password: BBED: Release 2.0.0.0.0 - Limited Production on Sun Nov 15 00:26:14 2009 Copyright (c) 1982, 2007, Oracle. All rights reserved. ************* !!! For Oracle Internal Use only !!! *************** BBED> help HELP [| ALL ] BBED> help all SET DBA [ dba | file#, block# ] SET FILENAME 'filename' SET FILE file# SET BLOCK [+/-]block# SET OFFSET [ [+/-]byte offset | symbol | *symbol ] SET BLOCKSIZE bytes SET LIST[FILE] 'filename' SET WIDTH character_count SET COUNT bytes_to_display SET IBASE [ HEX | OCT | DEC ] SET OBASE [ HEX | OCT | DEC ] SET MODE [ BROWSE | EDIT ] SET SPOOL [ Y | N ] SHOW [ | ALL ] INFO MAP[/v] [ DBA | FILENAME | FILE | BLOCK ] DUMP[/v] [ DBA | FILENAME | FILE | BLOCK | OFFSET | COUNT ] PRINT[/x|d|u|o|c] [ DBA | FILE | FILENAME | BLOCK | OFFSET | symbol | *symbol ] EXAMINE[/Nuf] [ DBA | FILE | FILENAME | BLOCK | OFFSET | symbol | *symbol ] : N - a number which specifies a repeat count. u - a letter which specifies a unit size: b - b1, ub1 (byte) h - b2, ub2 (half-word) w - b4, ub4(word) r - Oracle table/index row f - a letter which specifies a display format: x - hexadecimal d - decimal u - unsigned decimal o - octal c - character (native) n - Oracle number t - Oracle date i - Oracle rowid FIND[/x|d|u|o|c] numeric/character string [ TOP | CURR ] COPY [ DBA | FILE | FILENAME | BLOCK ] TO [ DBA | FILE | FILENAME | BLOCK ] MODIFY[/x|d|u|o|c] numeric/character string [ DBA | FILE | FILENAME | BLOCK | OFFSET | symbol | *symbol ] ASSIGN[/x|d|u|o] = : [ DBA | FILE | FILENAME | BLOCK | OFFSET | symbol | *symbol ] : [ value | ] SUM [ DBA | FILE | FILENAME | BLOCK ] [ APPLY ] PUSH [ DBA | FILE | FILENAME | BLOCK | OFFSET ] POP [ALL] REVERT [ DBA | FILE | FILENAME | BLOCK ] UNDO HELP [ | ALL ] VERIFY [ DBA | FILE | FILENAME | BLOCK ] CORRUPT [ DBA | FILE | FILENAME | BLOCK ]
关于bbed的具体用法，这里就不罗嗦了，有兴趣的可以自行学习，下面这篇参考文档非常的详尽：
http://orafaq.com/papers/dissassembling_the_data_block.pdf
记一次redo损坏导致ora-600[4000]的恢复
据说今天是光棍节，逢年过节，必有大事。快下班的时候，一位朋友碰到了一个大问题，数据库服务器异常断电重启以后，数据库无法启动，报ora-600[4000]错误，尝试了使用隐藏参数，还是无法打开。
ORA-00704: bootstrap process failure ORA-00704: bootstrap process failure ORA-00600: internal error code, arguments: [4000], [46], [], [], [], [], [], []
从ora-600内部错误的类型可以看到，4000是trasaction undo相关的内部错误。搜索一下可以发现，logzgh同学已经两次碰到过该问题了(第一次，第二次)。看来在异常宕机的情况下，这个问题出现的机率还是比较高的。既然是有先例的，恢复起来应该是没有问题的，这个案例和logzgh的第一个案例基本一样，trace文件里显示也是obj$上有异常事务:
ORA-00600: internal error code, arguments: [4000], [46], [], [], [], [], [], [] Current SQL statement for this session: select ctime, mtime, stime from obj$ where obj# = :1
将trace文件往下翻，可以看到出现问题的block的dump信息，在block的itl中果然有一条活动事务存在。用bbed找到对应块，设置offset到61，将itl的flag从20改为80，重新计算block的checksum并apply。然后尝试重新启动数据库，这次ora-600[4000]错误没有了，报的是ora-600[2662]错误，数据库无法open。2662就比较容易了，网上相关的处理案例已经一大堆了，将数据库启动到mount状态，设置10015事件来调整scn即可
alter session set events '10015 trace name adjust_scn level 1';
如果数据库已经处于open状态，则可以使用如下语句来调整SCN:
alter session set events 'IMMEDIATE trace name ADJUST_SCN level x';
再次重新打开库，报ora-600[2256]，继续使用10015事件，这次level设置为(ora-600错误的第三个参数+1)*4
alter session set event '10015 trace name adjust_scn level 20';
重启，报ora-600[4097]错误，好了，现在基本上恢复已经接近尾声了，使用下面几个隐藏参数，可以正常将数据库打开了。接下来就是告诉朋友做全库export/重建库/import了。
_allow_resetlogs_corruption=true _allow_terminal_recovery_corruption=true _corrupted_rollback_segments=(_SYSSMU1$, _SYSSMU2$, _SYSSMU3$, _SYSSMU4$, _SYSSMU5$, _SYSSMU6$, _SYSSMU7$, _SYSSMU8$, _SYSSMU9$, _SYSSMU10$)
年关将近，事故高发时期，阿弥陀佛，多求个平安吧。话说这个案例还没来得及些写呢，晚上回家的路上又接到说有人truncate了一张表。。。
