Oracle 11g Data Block Layout
KCBH
SQL> conn lyj/lyj SQL> select ROWID,ID,NAME,DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) FILE#,DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) BLOCK# from t1; ROWID ID NAME FILE# BLOCK# ------------------ ---------- ---------- ---------- ---------- AAAW0oAAGAAAACDAAA 1 AAAAAA 6 131 AAAW0oAAGAAAACDAAB 2 BBBBB 6 131 # 进入BBED BBED> set file 6 block 131 FILE# 6 BLOCK# 131 BBED> map /v File: /u01/app/oracle/oradata/orcl/skip_arch01.dbf (6) Block: 131 Dba:0x01800083 ------------------------------------------------------------ KTB Data Block (Table/Cluster) struct kcbh, 20 bytes @0 ub1 type_kcbh @0 ub1 frmt_kcbh @1 ub1 spare1_kcbh @2 ub1 spare2_kcbh @3 ub4 rdba_kcbh @4 ub4 bas_kcbh @8 ub2 wrp_kcbh @12 ub1 seq_kcbh @14 ub1 flg_kcbh @15 ub2 chkval_kcbh @16 ub2 spare3_kcbh @18 BBED> p kcbh struct kcbh, 20 bytes @0 ub1 type_kcbh @0 0x06 # 06代表数据块 ub1 frmt_kcbh @1 0xa2 # 0xa2代表块的大小是8k ub1 spare1_kcbh @2 0x00 # 保留值 ub1 spare2_kcbh @3 0x00 # 保留值 ub4 rdba_kcbh @4 0x01800083 # RDBA相对数据块地址 6号文件的131号块（转换方法在下面） ub4 bas_kcbh @8 0x0037066e # 块头的SCN(低32位) ub2 wrp_kcbh @12 0x0000 # 块头的SCN(高16位) ub1 seq_kcbh @14 0x02 # seq最大值254 ub1 flg_kcbh @15 0x06 (KCBHFDLC, KCBHFCKV) ub2 chkval_kcbh @16 0x9200 # db_block_checksum ub2 spare3_kcbh @18 0x0000 # 保留值 |
rdba转换成文件号块号的方法
rdba：0x01800083 0000 0001 1000 0000 0000 0000 1000 0011 前10位文件号：0000 0001 10 - 6号文件 后22位块号： 00 0000 0000 0000 1000 0011 - 131号块 |
RDBA转换函数
# 下以函数用来从RDBA中转换file#和block#出来 CREATE OR REPLACE FUNCTION getbfno (p_dba IN VARCHAR2) RETURN VARCHAR2 IS l_str VARCHAR2 (255) DEFAULT NULL; l_fno VARCHAR2 (15); l_bno VARCHAR2 (15); BEGIN l_fno := DBMS_UTILITY.data_block_address_file (TO_NUMBER (LTRIM (p_dba, '0x'), 'xxxxxxxx' ) ); l_bno := DBMS_UTILITY.data_block_address_block (TO_NUMBER (LTRIM (p_dba, '0x'), 'xxxxxxxx' ) ); l_str := 'datafile# is:' || l_fno || CHR (10) || 'datablock is:' || l_bno || CHR (10) || 'dump command:alter system dump datafile ' || l_fno || ' block ' || l_bno || ';'; RETURN l_str; END; / SQL> select getbfno('0x01800083') BFNO from dual; BFNO -------------------------------------------------------------------------------- datafile# is:6 datablock is:131 dump command:alter system dump datafile 6 block 131; |
scn wrap和scn
在Oracle内部，SCN分为两部分存储，分别称之为scn wrap和scn base。
实际上SCN长度为48位，即它其实就是一个48位的整数。只不过可能是由于在早些年通常只能处理32位甚至是16位的数据，所以人为地分成了低32位（scnbase）和高16位（scn wrap）。
为什么不设计成64位，这个或许是觉得48位已经足够长了并且为了节省两个字节的空间：）。那么SCN这个48位长的整数，最大就是2^48（2的48次方, 281万亿，281474976710656），很大的一个数字了。 这里有一个重要的公式：SCN= (SCN_WRP * 4294967296) + SCN_BAS
KTBBH: Transaction Layer
# DUMP的结构 Block header dump: 0x01800083 Object id on Block? Y seg/obj: 0x16d28 csc: 0x00.367d20 itc: 2 flg: E typ: 1 - DATA brn: 0 bdba: 0x1800080 ver: 0x01 opc: 0 inc: 0 exflg: 0 Itl Xid Uba Flag Lck Scn/Fsc 0x01 0x0009.01a.00000954 0x00c06012.03cd.1d --U- 1 fsc 0x0000.00367d21 0x02 0x0009.00c.00000965 0x00c06012.03d1.37 --U- 1 fsc 0x0000.0037066e bdba: 0x01800083 data_block_dump,data header at 0x7f4671807a64 # BBED BBED> p ktbbh struct ktbbh, 72 bytes @20 ub1 ktbbhtyp @20 0x01 (KDDBTDATA) union ktbbhsid, 4 bytes @24 ub4 ktbbhsg1 @24 0x00016d28 ub4 ktbbhod1 @24 0x00016d28 struct ktbbhcsc, 8 bytes @28 ub4 kscnbas @28 0x00367d20 ub2 kscnwrp @32 0x0000 sb2 ktbbhict @36 -2046 ub1 ktbbhflg @38 0x32 (NONE) ub1 ktbbhfsl @39 0x00 ub4 ktbbhfnx @40 0x01800080 struct ktbbhitl[0], 24 bytes @44 struct ktbitxid, 8 bytes @44 ub2 kxidusn @44 0x0009 ub2 kxidslt @46 0x001a ub4 kxidsqn @48 0x00000954 struct ktbituba, 8 bytes @52 ub4 kubadba @52 0x00c06012 ub2 kubaseq @56 0x03cd ub1 kubarec @58 0x1d ub2 ktbitflg @60 0x2001 (KTBFUPB) union _ktbitun, 2 bytes @62 sb2 _ktbitfsc @62 0 ub2 _ktbitwrp @62 0x0000 ub4 ktbitbas @64 0x00367d21 struct ktbbhitl[1], 24 bytes @68 struct ktbitxid, 8 bytes @68 ub2 kxidusn @68 0x0009 ub2 kxidslt @70 0x000c ub4 kxidsqn @72 0x00000965 struct ktbituba, 8 bytes @76 ub4 kubadba @76 0x00c06012 ub2 kubaseq @80 0x03d1 ub1 kubarec @82 0x37 ub2 ktbitflg @84 0x2001 (KTBFUPB) union _ktbitun, 2 bytes @86 sb2 _ktbitfsc @86 0 ub2 _ktbitwrp @86 0x0000 ub4 ktbitbas @88 0x0037066e |
KDBH
# dump tsiz: 0x1f98 hsiz: 0x16 pbl: 0x7f4671807a64 76543210 flag=-------- ntab=1 nrow=2 frre=-1 fsbo=0x16 fseo=0x1f7f avsp=0x1f69 tosp=0x1f69 0xe:pti[0] nrow=2 offs=0 0x12:pri[0] offs=0x1f8b 0x14:pri[1] offs=0x1f7f block_row_dump: tab 0, row 0, @0x1f8b tl: 13 fb: --H-FL-- lb: 0x1 cc: 2 col 0: [ 2] c1 02 col 1: [ 6] 41 41 41 41 41 41 tab 0, row 1, @0x1f7f tl: 12 fb: --H-FL-- lb: 0x2 cc: 2 col 0: [ 2] c1 03 col 1: [ 5] 42 42 42 42 42 end_of_block_dump # bbed BBED> p kdbr sb2 kdbr[0] @118 8075 sb2 kdbr[1] @120 8063 BBED> p *kdbr[0] rowdata[12] ----------- ub1 rowdata[12] @8175 0x2c BBED> x/rncccccc rowdata[12] @8175 ----------- flag@8175: 0x2c (KDRHFL, KDRHFF, KDRHFH) lock@8176: 0x01 cols@8177: 2 col 0[2] @8178: 1 col 1[6] @8181: AAAAAA BBED> p *kdbr[1] rowdata[0] ---------- ub1 rowdata[0] @8163 0x2c BBED> x/rncccccc rowdata[0] @8163 ---------- flag@8163: 0x2c (KDRHFL, KDRHFF, KDRHFH) lock@8164: 0x02 cols@8165: 2 col 0[2] @8166: 2 col 1[5] @8169: BBBBB # 转换语句 SQL> select dump('AAAAAA',16) from dual; DUMP('AAAAAA',16) ------------------------------- Typ=96 Len=6: 41,41,41,41,41,41 |
Oracle ROWID格式解析
SQL> select ROWID,ID,NAME,DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) FILE#,DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) BLOCK# from t1; ROWID ID NAME FILE# BLOCK# ------------------ ---------- ---------- ---------- ---------- AAAW0oAAGAAAACDAAA 1 AAAAAA 6 131 AAAW0oAAGAAAACDAAB 2 BBBBB 6 131 |
数据行格式解析
# dump trace中数据行格式解析 tl: 13 fb: --H-FL-- lb: 0x1 cc: 2 col 0: [ 2] c1 02 col 1: [ 6] 41 41 41 41 41 41 fb: --H-FL-- 即是开始F，又是结束L，但表只有一行记录 lb: 0x1 1号事务槽 cc: 2 有两列 col 0: [ 2] c1 02: 第一列 col 1: [ 6] 41 41 41 41 41 41：第二列 # BBED中数据行解析 2c 010202c1 02064141 41414141 2c - 行的状态，行正常的话都是2c，如果是3c的话，表示该行被删除了 01 - lb，锁的字节标记，代表在哪个事务槽上面，01代表在第一个事务槽 02 - 代表有2列 02 - 第1个列有2个字节组成 c1 02 - 第1列的2个字节c1 02 代表第1列对应的数据为1 06 - 第2个列有6个字节组成 4141 41414141 - 第2个列6个字节4141 41414141 ，对应的数值为AAAAAA |
实例
insert一条记录，没有提交事务，会写入Datablock吗？
会写入，验证步骤如下：
conn lyj/lyj create table t2 (id number,name varchar2(10)); insert into t2 values (1, 'AAAAAA'); select ROWID,ID,NAME,DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) FILE#,DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) BLOCK# from t2; ROWID ID NAME FILE# BLOCK# ------------------ ---------- ---------- ---------- ---------- AAAXCgAAGAAAACNAAA 1 AAAAAA 6 141 # 打开另一个会话 alter system flush buffer_cache; alter system dump datafile 6 block 141; select VALUE from v$diag_info where NAME='Default Trace File'; VALUE -------------------------------------------------------------------------------- /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_23817.trc # 打开trace文件 #-------------------------------------------------------------------------------- Block header dump: 0x0180008d Object id on Block? Y seg/obj: 0x170a0 csc: 0x00.3ddad2 itc: 2 flg: E typ: 1 - DATA brn: 0 bdba: 0x1800088 ver: 0x01 opc: 0 inc: 0 exflg: 0 Itl Xid Uba Flag Lck Scn/Fsc， 0x01 0x0002.019.00000a13 0x00c011b6.034d.14 ---- 1 fsc 0x0000.00000000 # 第一个事务槽，flag:---- 代表没有提交 0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000 bdba: 0x0180008d data_block_dump,data header at 0x7f4560e76a64 ....... block_row_dump: tab 0, row 0, @0x1f8b tl: 13 fb: --H-FL-- lb: 0x1 cc: 2 # 0x1 查看上面第一个事务槽 col 0: [ 2] c1 02 # 1 col 1: [ 6] 41 41 41 41 41 41 # AAAAAA end_of_block_dump End dump data blocks tsn: 7 file#: 6 minblk 141 maxblk 141 #-------------------------------------------------------------------------------- FLAG: C=Committed; U=Commit Upper Bound; T=Active as CSC # BBED BBED> set file 6 block 141 BBED> map /v File: /u01/app/oracle/oradata/orcl/skip_arch01.dbf (6) Block: 141 Dba:0x0180008d ------------------------------------------------------------ KTB Data Block (Table/Cluster) struct kcbh, 20 bytes @0 ub1 type_kcbh @0 ub1 frmt_kcbh @1 ub1 spare1_kcbh @2 ub1 spare2_kcbh @3 ub4 rdba_kcbh @4 ub4 bas_kcbh @8 ub2 wrp_kcbh @12 ub1 seq_kcbh @14 ub1 flg_kcbh @15 ub2 chkval_kcbh @16 ub2 spare3_kcbh @18 struct ktbbh, 72 bytes @20 # 事务 ub1 ktbbhtyp @20 union ktbbhsid, 4 bytes @24 struct ktbbhcsc, 8 bytes @28 sb2 ktbbhict @36 ub1 ktbbhflg @38 ub1 ktbbhfsl @39 ub4 ktbbhfnx @40 struct ktbbhitl[2], 48 bytes @44 ..... BBED> p ktbbh struct ktbbh, 72 bytes @20 ub1 ktbbhtyp @20 0x01 (KDDBTDATA) union ktbbhsid, 4 bytes @24 ub4 ktbbhsg1 @24 0x000170a0 ub4 ktbbhod1 @24 0x000170a0 struct ktbbhcsc, 8 bytes @28 ub4 kscnbas @28 0x003ddad2 ub2 kscnwrp @32 0x0000 sb2 ktbbhict @36 2 ub1 ktbbhflg @38 0x32 (NONE) ub1 ktbbhfsl @39 0x00 ub4 ktbbhfnx @40 0x01800088 struct ktbbhitl[0], 24 bytes @44 # 第一个事务槽 struct ktbitxid, 8 bytes @44 ub2 kxidusn @44 0x0002 ub2 kxidslt @46 0x0019 ub4 kxidsqn @48 0x00000a13 struct ktbituba, 8 bytes @52 ub4 kubadba @52 0x00c011b6 ub2 kubaseq @56 0x034d ub1 kubarec @58 0x14 ub2 ktbitflg @60 0x0001 (NONE) # 00代表没提交，01代表在偏移量60的地方锁了1行记录 # 前两位是80代表提交，20代表快速提交， ..... BBED> set offset 0 count 8192 BBED> dump File: /u01/app/oracle/oradata/orcl/skip_arch01.dbf (6) Block: 141 Offsets: 0 to 8191 Dba:0x0180008d ------------------------------------------------------------------------ 06a20000 8d008001 d2da3d00 00000304 cc440000 01000000 a0700100 d2da3d00 00000000 02003200 88008001 02001900 130a0000 b611c000 4d031400 01000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00010100 ffff1400 8b1f771f 771f0000 01008b1f 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 ...... 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 0000002c 010202c1 02064141 41414141 0306d2da # 2c 010202c1 02064141 41414141 这个就是没提交insert的值 |
使用BBED手工修复DELETE数据
先看一下未删除前数据块内容
# 提交上面insert的数据 commit; alter system flush buffer_cache; # 退出重新登陆BBED BBED> set file 6 block 141 BBED> map /v File: /u01/app/oracle/oradata/orcl/skip_arch01.dbf (6) Block: 141 Dba:0x0180008d ------------------------------------------------------------ KTB Data Block (Table/Cluster) struct kcbh, 20 bytes @0 ub1 type_kcbh @0 ub1 frmt_kcbh @1 ub1 spare1_kcbh @2 ub1 spare2_kcbh @3 ub4 rdba_kcbh @4 ub4 bas_kcbh @8 ub2 wrp_kcbh @12 ub1 seq_kcbh @14 ub1 flg_kcbh @15 ub2 chkval_kcbh @16 ub2 spare3_kcbh @18 struct ktbbh, 72 bytes @20 ub1 ktbbhtyp @20 union ktbbhsid, 4 bytes @24 struct ktbbhcsc, 8 bytes @28 sb2 ktbbhict @36 ub1 ktbbhflg @38 ub1 ktbbhfsl @39 ub4 ktbbhfnx @40 struct ktbbhitl[2], 48 bytes @44 # ..... BBED> p ktbbhitl struct ktbbhitl[0], 24 bytes @44 struct ktbitxid, 8 bytes @44 ub2 kxidusn @44 0x0002 ub2 kxidslt @46 0x0019 ub4 kxidsqn @48 0x00000a13 struct ktbituba, 8 bytes @52 ub4 kubadba @52 0x00c011b6 ub2 kubaseq @56 0x034d ub1 kubarec @58 0x14 ub2 ktbitflg @60 0x8000 (KTBFCOM) # 80代表提交 00 没有锁行 union _ktbitun, 2 bytes @62 sb2 _ktbitfsc @62 0 ub2 _ktbitwrp @62 0x0000 ub4 ktbitbas @64 0x003de705 BBED> p kdbr sb2 kdbr[0] @118 8075 BBED> p *kdbr[0] rowdata[0] ---------- ub1 rowdata[0] @8175 0x2c # 绝对位置：上面相对位置+100 (ASSM) # ASSM +100 MSSM +92 详细算法见下面ASSM和MSSM下block结构的一点差异图 BBED> x/rnccccccc # r-读 n-数值型 c-字符型 rowdata[0] @8175 ---------- flag@8175: 0x2c (KDRHFL, KDRHFF, KDRHFH) # 2c 行的状态 lock@8176: 0x00 cols@8177: 2 col 0[2] @8178: 1 col 1[6] @8181: AAAAAA # dump trace #-------------------------------------------------------------------------------- Itl Xid Uba Flag Lck Scn/Fsc 0x01 0x0002.019.00000a13 0x00c011b6.034d.14 C--- 0 scn 0x0000.003de705 0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000 bdba: 0x0180008d data_block_dump,data header at 0x7ff1f6bf9a64 ...... block_row_dump: tab 0, row 0, @0x1f8b tl: 13 fb: --H-FL-- lb: 0x0 cc: 2 col 0: [ 2] c1 02 col 1: [ 6] 41 41 41 41 41 41 #-------------------------------------------------------------------------------- |
ASSM和MSSM下block结构的一点差异
删除记录并使用BBED恢复
# 删除记录，并确保写到文件中 delete from t2 where id=1; commit; alter system flush buffer_cache; # 重新登陆bbed查看file 6 block 141 BBED> set file 6 block 141 BBED> p *kdbr[0] rowdata[0] ---------- ub1 rowdata[0] @8175 0x3c BBED> x/rnccccccc rowdata[0] @8175 ---------- flag@8175: 0x3c (KDRHFL, KDRHFF, KDRHFD, KDRHFH) # 3c 行状态为删除 lock@8176: 0x02 cols@8177: 0 BBED> dump offset 8000 count 200 File: /u01/app/oracle/oradata/orcl/skip_arch01.dbf (6) Block: 141 Offsets: 8000 to 8191 Dba:0x0180008d ------------------------------------------------------------------------ 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 0000003c 020202c1 02064141 41414141 0106c071 3c 020202c1 02064141 41414141 3c - 删除 02 - 第2个事务槽 02 - 代表有2列 02 - 第1个列有2个字节组成 c1 02 - 第1列的2个字节c1 02 代表第1列对应的数据为1 06 - 第2个列有6个字节组成 4141 41414141 - 第2个列6个字节4141 41414141 ，对应的数值为AAAAAA # 使用BBED恢复 modify /x 2c offset 8175 modify /x 01 offset 8176 sum apply # 在SQLPLUS中已经能查到刚才被删除的数据 alter system flush buffer_cache; (可选) select * from t2; ID NAME ---------- ---------- 1 AAAAAA |
使用BBED手工修复UPDATE数据
conn lyj/lyj create table t3 (id number,name varchar(10)); insert into t3 values (1,'AAAAA'); insert into t3 values (2,'BBBBB'); commit; select id,name,DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) FILE#,DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) BLOCK# from t3; ID NAME FILE# BLOCK# ---------- ---------- ---------- ---------- 1 AAAAA 6 148 2 BBBBB 6 148 alter system flush buffer_cache; # 使用BBED dump块 BBED> set file 6 block 148 FILE# 6 BLOCK# 148 BBED> dump/v offset 8000 count 200 File: /u01/app/oracle/oradata/orcl/skip_arch01.dbf (6) Block: 148 Offsets: 8000 to 8191 Dba:0x01800094 ------------------------------------------------------- 00000000 00000000 00000000 00000000 l ................ 00000000 00000000 00000000 00000000 l ................ 00000000 00000000 00000000 00000000 l ................ 00000000 00000000 00000000 00000000 l ................ 00000000 00000000 00000000 00000000 l ................ 00000000 00000000 00000000 00000000 l ................ 00000000 00000000 00000000 00000000 l ................ 00000000 00000000 00000000 00000000 l ................ 00000000 00000000 00000000 00000000 l ................ 00000000 00000000 00000000 00000000 l ................ 00000000 2c010202 c1030542 42424242 l ....,......BBBBB 2c010202 c1020541 41414141 01065e76 l ,......AAAAA..^v 2c010202 c1020541 41414141 2c - 正常状态 01 - 第1个事务槽 02 - 代表有2列 02 - 第1个列有2个字节组成 c1 02 - 第1列的2个字节c1 02 代表第1列对应的数据为1 05 - 第2个列有5个字节组成 41 41414141 - 第2个列5个字节41 41414141 ，对应的数值为AAAAA 2c010202 c1030542 42424242 2c - 正常状态 01 - 第1个事务槽 02 - 代表有2列 02 - 第1个列有2个字节组成 c103 - 第1列的2个字节c1 03 代表第1列对应的数据为2 05 - 第2个列有5个字节组成 42 42424242 - 第2个列5个字节42 42424242 ，对应的数值为BBBBB # update字符等长时（直接替换） update t3 set name='CCCCC' where name='BBBBB'; commit; alter system flush buffer_cache; # 重进BBED BBED> set file 6 block 148 BBED> dump/v offset 8000 count 200 File: /u01/app/oracle/oradata/orcl/skip_arch01.dbf (6) Block: 148 Offsets: 8000 to 8191 Dba:0x01800094 ------------------------------------------------------- 00000000 00000000 00000000 00000000 l ................ 00000000 00000000 00000000 00000000 l ................ 00000000 00000000 00000000 00000000 l ................ 00000000 00000000 00000000 00000000 l ................ 00000000 00000000 00000000 00000000 l ................ 00000000 00000000 00000000 00000000 l ................ 00000000 00000000 00000000 00000000 l ................ 00000000 00000000 00000000 00000000 l ................ 00000000 00000000 00000000 00000000 l ................ 00000000 00000000 00000000 00000000 l ................ 00000000 2c020202 c1030543 43434343 l ....,......CCCCC # update前的事务槽是01，update后事务槽是02 2c000202 c1020541 41414141 02068782 l ,......AAAAA.... # update字符超过原有长时 update t3 set name='DDDDDD' where name='CCCCC'; commit; alter system flush buffer_cache; # 重进BBED BBED> set file 6 block 148 BBED> dump/v offset 8000 count 200 File: /u01/app/oracle/oradata/orcl/skip_arch01.dbf (6) Block: 148 Offsets: 8000 to 8191 Dba:0x01800094 ------------------------------------------------------- 00000000 00000000 00000000 00000000 l ................ 00000000 00000000 00000000 00000000 l ................ 00000000 00000000 00000000 00000000 l ................ 00000000 00000000 00000000 00000000 l ................ 00000000 00000000 00000000 00000000 l ................ 00000000 00000000 00000000 00000000 l ................ 00000000 00000000 00000000 00000000 l ................ 00000000 00000000 00000000 00000000 l ................ 00000000 00000000 00000000 00000000 l ................ 00000000 0000002c 010202c1 03064444 l .......,......DD # DDDDDD是新写到文件中，并不直接修改 44444444 2c000202 c1030543 43434343 l DDDD,......CCCCC # 原有的CCCCC还在文件中，状态也是c2 2c000202 c1020541 41414141 02064883 l ,......AAAAA..H. |
下面要使用BBED工具，手工将DDDDDD，改为CCCCC
select * from t3; ID NAME ---------- ---------- 1 AAAAA 2 DDDDDD BBED> set file 6 block 148 FILE# 6 BLOCK# 148 BBED> p kdbr sb2 kdbr[0] @118 8076 sb2 kdbr[1] @120 8051 # offset 120 是第二行记录 BBED> p *kdbr[1] rowdata[0] ---------- ub1 rowdata[0] @8151 0x2c # 8151-100=8051 BBED> x /rncccccc rowdata[0] @8151 ---------- flag@8151: 0x2c (KDRHFL, KDRHFF, KDRHFH) lock@8152: 0x01 cols@8153: 2 col 0[2] @8154: 2 col 1[6] @8157: DDDDDD # 变更指向位置 BBED> dump /v offset 8164 count 100 File: /u01/app/oracle/oradata/orcl/skip_arch01.dbf (6) Block: 148 Offsets: 8164 to 8191 Dba:0x01800094 ------------------------------------------------------- 2c000202 c1030543 43434343 2c000202 l ,......CCCCC,... c1020541 41414141 02064883 l ...AAAAA..H. 8164 - 100 = 8064 select to_char(8064,'xxxxxxxxxx') c from dual; C ----------- 1f80 # 801f BBED> dump /v offset 120 count 16 File: /u01/app/oracle/oradata/orcl/skip_arch01.dbf (6) Block: 148 Offsets: 120 to 135 Dba:0x01800094 ------------------------------------------------------- 731f0000 00000000 00000000 00000000 l s............... # 1f73 select to_number('1f73','xxxxxxxxxx') n from dual; N ---------- 8051 # 8051+100 DDDDDD所在位置的绝对偏移量 BBED> modify /x 80 offset 120 Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y File: /u01/app/oracle/oradata/orcl/skip_arch01.dbf (6) Block: 148 Offsets: 120 to 135 Dba:0x01800094 ------------------------------------------------------------------------ 801f0000 00000000 00000000 00000000 BBED> sum apply Check value for File 6, Block 148: current = 0xae1b, required = 0xae1b # 8152事务槽设置为00 BBED> dump /v offset 8152 File: /u01/app/oracle/oradata/orcl/skip_arch01.dbf (6) Block: 148 Offsets: 8152 to 8167 Dba:0x01800094 ------------------------------------------------------- 010202c1 03064444 44444444 2c000202 l ......DDDDDD,... BBED> modify /x 00 File: /u01/app/oracle/oradata/orcl/skip_arch01.dbf (6) Block: 148 Offsets: 8152 to 8167 Dba:0x01800094 ------------------------------------------------------------------------ 000202c1 03064444 44444444 2c000202 # 8165事务槽设置为02 BBED> dump /v offset 8165 File: /u01/app/oracle/oradata/orcl/skip_arch01.dbf (6) Block: 148 Offsets: 8165 to 8180 Dba:0x01800094 ------------------------------------------------------- 000202c1 03054343 4343432c 000202c1 l ......CCCCC,.... BBED> sum apply Check value for File 6, Block 148: current = 0xac1a, required = 0xac1a # 有效空间设置 BBED> modify /x 5c offset 110 File: /u01/app/oracle/oradata/orcl/skip_arch01.dbf (6) Block: 148 Offsets: 110 to 125 Dba:0x01800094 ------------------------------------------------------------------------ 5c1f691f 00000200 8c1f801f 00000000 <32 bytes per line> BBED> modify /x 5c offset 112 File: /u01/app/oracle/oradata/orcl/skip_arch01.dbf (6) Block: 148 Offsets: 112 to 127 Dba:0x01800094 ------------------------------------------------------------------------ 5c1f0000 02008c1f 801f0000 00000000 <32 bytes per line> BBED> sum apply Check value for File 6, Block 148: current = 0xac1a, required = 0xac1a # 校验 BBED> verify DBVERIFY - Verification starting FILE = /u01/app/oracle/oradata/orcl/skip_arch01.dbf BLOCK = 148 Block Checking: DBA = 25165972, Block Type = KTB-managed data block data header at 0x21c4a64 kdbchk: row locked by non-existent transaction table=0 slot=1 lockid=2 ktbbhitc=2 Block 148 failed with check code 6101 DBVERIFY - Verification complete Total Blocks Examined : 1 Total Blocks Processed (Data) : 1 Total Blocks Failing (Data) : 1 Total Blocks Processed (Index): 0 Total Blocks Failing (Index): 0 Total Blocks Empty : 0 Total Blocks Marked Corrupt : 0 Total Blocks Influx : 0 Message 531 not found; product=RDBMS; facility=BBED # 验证 SQL> alter system flush buffer_cache; System altered. SQL> select * from t3; ID NAME ---------- ---------- 1 AAAAA 2 CCCCC |
使用BBED恢复UPDATE的数据2
根据以下景场操作，使用BBED恢复UPDATE的数据，把BBBBBB恢复成AAAAA（即把6个B恢复5个A）。
create table t1 (id int,name varchar2(10)); insert into t1 values(1,'AAAAA'); commit; update t1 set name='BBBBBB' where name='AAAAA'; #误操作 commit; |
恢复步骤
alter system flush buffer_cache; select id,name,DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) FILE#,DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) BLOCK# from t1; ID NAME FILE# BLOCK# ---------- ---------- ---------- ---------- 1 BBBBBB 6 135 # 查看dump trace (可选步骤) alter system dump datafile 6 block 135; select * from v$diag_info; #----------------------------------------------------------------------------- Block header dump: 0x01800087 Object id on Block? Y seg/obj: 0x1723e csc: 0x00.40b170 itc: 2 flg: E typ: 1 - DATA brn: 0 bdba: 0x1800080 ver: 0x01 opc: 0 inc: 0 exflg: 0 Itl Xid Uba Flag Lck Scn/Fsc 0x01 0x000a.000.0000097d 0x00c00866.0370.22 C--- 0 scn 0x0000.0040b16f 0x02 0x0005.000.00000acd 0x00c00ee9.03f6.14 --U- 1 fsc 0x0000.0040b173 #----------------------------------------------------------------------------- # 登入BBED BBED> set file 6 block 135 FILE# 6 BLOCK# 131 BBED> map /v File: /u01/app/oracle/oradata/orcl/skip_arch01.dbf (6) Block: 135 Dba:0x01800087 ------------------------------------------------------------ KTB Data Block (Table/Cluster) struct kcbh, 20 bytes @0 ub1 type_kcbh @0 ub1 frmt_kcbh @1 ub1 spare1_kcbh @2 ub1 spare2_kcbh @3 ub4 rdba_kcbh @4 ub4 bas_kcbh @8 ub2 wrp_kcbh @12 ub1 seq_kcbh @14 ub1 flg_kcbh @15 ub2 chkval_kcbh @16 ub2 spare3_kcbh @18 struct ktbbh, 72 bytes @20 ub1 ktbbhtyp @20 union ktbbhsid, 4 bytes @24 struct ktbbhcsc, 8 bytes @28 sb2 ktbbhict @36 ub1 ktbbhflg @38 ub1 ktbbhfsl @39 ub4 ktbbhfnx @40 struct ktbbhitl[2], 48 bytes @44 struct kdbh, 14 bytes @100 ub1 kdbhflag @100 sb1 kdbhntab @101 sb2 kdbhnrow @102 sb2 kdbhfrre @104 sb2 kdbhfsbo @106 sb2 kdbhfseo @108 sb2 kdbhavsp @110 sb2 kdbhtosp @112 struct kdbt[1], 4 bytes @114 sb2 kdbtoffs @114 sb2 kdbtnrow @116 sb2 kdbr[1] @118 ub1 freespace[8043] @120 ub1 rowdata[25] @8163 ub4 tailchk @8188 BBED> p kdbr sb2 kdbr[0] @118 8063 BBED> p *kdbr[0] rowdata[0] ---------- ub1 rowdata[0] @8163 0x2c BBED> x/rnccccccc rowdata[0] @8163 ---------- flag@8163: 0x2c (KDRHFL, KDRHFF, KDRHFH) lock@8164: 0x02 cols@8165: 2 col 0[2] @8166: 1 col 1[6] @8169: BBBBBB BBED> dump /v offset 8163 File: /u01/app/oracle/oradata/orcl/skip_arch01.dbf (6) Block: 135 Offsets: 8163 to 8191 Dba:0x01800087 ------------------------------------------------------- 2c020202 c1020642 42424242 422c0002 l ,......BBBBBB,.. 02c10205 41414141 41020673 b1 l ....AAAAA..s <16 bytes per line> BBED> dump /v offset 8176 # 8163+13 File: /u01/app/oracle/oradata/orcl/skip_arch01.dbf (6) Block: 131 Offsets: 8176 to 8191 Dba:0x01800083 ------------------------------------------------------- 2c000202 c1020541 41414141 02065593 l ,......AAAAA..U. <16 bytes per line> SQL> select to_char(8076,'xxxxxxxx') from dual; # 8176-100 TO_CHAR(8 --------- 1f8c # Little Endian：8c1f BBED> dump offset 118 count 16 File: /u01/app/oracle/oradata/orcl/skip_arch01.dbf (6) Block: 131 Offsets: 118 to 133 Dba:0x01800083 ------------------------------------------------------------------------ 7f1f0000 00000000 00000000 00000000 <32 bytes per line> # 修改 BBED> modify /x 8c offset 118 Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y File: /u01/app/oracle/oradata/orcl/skip_arch01.dbf (6) Block: 135 Offsets: 118 to 133 Dba:0x01800087 ------------------------------------------------------------------------ 8c1f0000 00000000 00000000 00000000 <32 bytes per line> # 修改对应的事务槽 BBED> modify /x 00 offset 8164 File: /u01/app/oracle/oradata/orcl/skip_arch01.dbf (6) Block: 135 Offsets: 8164 to 8179 Dba:0x01800087 ------------------------------------------------------------------------ 000202c1 02064242 42424242 2c000202 <32 bytes per line> BBED> modify /x 02 offset 8177 File: /u01/app/oracle/oradata/orcl/skip_arch01.dbf (6) Block: 135 Offsets: 8177 to 8191 Dba:0x01800087 ------------------------------------------------------------------------ 020202c1 02054141 41414102 0673b1 <32 bytes per line> BBED> sum apply Check value for File 6, Block 135: current = 0xd252, required = 0xd252 # 有效空间设置 ## 检验 BBED> verify DBVERIFY - Verification starting FILE = /u01/app/oracle/oradata/orcl/skip_arch01.dbf BLOCK = 135 Block Checking: DBA = 25165959, Block Type = KTB-managed data block data header at 0x7fcea7922264 kdbchk: the amount of space used is not equal to block size # 使用的空间大小不等于块大小 used=32 fsc=0 avsp=8055 dtl=8088 # 提示数据块的空间使用不正确（dtl-used=kdbhavsp=kdbhtosp） # 8088-32=8056 与 avsp=8055相差1,也就是说我要恢复到8056 Block 135 failed with check code 6110 DBVERIFY - Verification complete Total Blocks Examined : 1 Total Blocks Processed (Data) : 1 Total Blocks Failing (Data) : 1 # 有错误 Total Blocks Processed (Index): 0 Total Blocks Failing (Index): 0 Total Blocks Empty : 0 Total Blocks Marked Corrupt : 0 Total Blocks Influx : 0 Message 531 not found; product=RDBMS; facility=BBED BBED> p kdbh struct kdbh, 14 bytes @100 ub1 kdbhflag @100 0x00 (NONE) sb1 kdbhntab @101 1 sb2 kdbhnrow @102 1 sb2 kdbhfrre @104 -1 sb2 kdbhfsbo @106 20 sb2 kdbhfseo @108 8063 sb2 kdbhavsp @110 8055 sb2 kdbhtosp @112 8055 # 修改空间，把空间修改为781f,以dtl-used=kdbhavsp为主，恢复到8088-32=8086 （算法见上面注释） SQL> select to_char(8056,'xxxxxxxx') from dual; TO_CHAR(8 --------- 1f78 # 781f BBED> modify /x 78 offset 110 File: /u01/app/oracle/oradata/orcl/skip_arch01.dbf (6) Block: 131 Offsets: 110 to 125 Dba:0x01800083 ------------------------------------------------------------------------ 5c1f771f 00000100 8c1f0000 00000000 <32 bytes per line> BBED> modify /x 78 offset 112 File: /u01/app/oracle/oradata/orcl/skip_arch01.dbf (6) Block: 131 Offsets: 112 to 127 Dba:0x01800083 ------------------------------------------------------------------------ 5c1f0000 01008c1f 00000000 00000000 <32 bytes per line> BBED> sum apply Check value for File 6, Block 135: current = 0xd252, required = 0xd252 # 校验 BBED> verify DBVERIFY - Verification starting FILE = /u01/app/oracle/oradata/orcl/skip_arch01.dbf BLOCK = 135 DBVERIFY - Verification complete Total Blocks Examined : 1 Total Blocks Processed (Data) : 1 Total Blocks Failing (Data) : 0 Total Blocks Processed (Index): 0 Total Blocks Failing (Index): 0 Total Blocks Empty : 0 Total Blocks Marked Corrupt : 0 Total Blocks Influx : 0 Message 531 not found; product=RDBMS; facility=BBED # 恢复成功 SQL> alter system flush buffer_cache; System altered. SQL> select * from t1; ID NAME ---------- ---------- 1 AAAAA |
使用BBED手工提交delete操作的事务
根据以下景场操作，使用BBED手工提交delete操作的事务
sqlplus lyj/lyj drop table t2 purge; create table t2 (id int,name varchar2(10)); insert into t2 values(1,'AAAAA'); commit; alter system flush buffer_cache; select id,name,DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) FILE#,DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) BLOCK# from t2; ID NAME FILE# BLOCK# ---------- ---------- ---------- ---------- 1 AAAAA 6 143 会话一： delete from t2 where name='AAAAA'; #不允许用commit命令提交 alter system flush buffer_cache; 会话二： select * from t2; #如果查不到name='AAAAA'这条记录，说明BBED手工提交事务成功！ |
操作步骤：
# 可选查看dump数据块内容 alter system dump datafile 6 block 143; select * from v$diag_info; #----------------------------------------------------------------------------- Block header dump: 0x0180008f Object id on Block? Y seg/obj: 0x17240 csc: 0x00.40bc99 itc: 2 flg: E typ: 1 - DATA brn: 0 bdba: 0x1800088 ver: 0x01 opc: 0 inc: 0 exflg: 0 Itl Xid Uba Flag Lck Scn/Fsc 0x01 0x0002.003.00000a73 0x00c00201.037a.02 C--- 0 scn 0x0000.0040bc86 0x02 0x0006.01c.00000ae5 0x00c00dee.040c.04 ---- 1 fsc 0x000a.00000000 bdba: 0x0180008f data_block_dump,data header at 0x7f7743224a64 tsiz: 0x1f98 hsiz: 0x14 pbl: 0x7f7743224a64 76543210 flag=-------- ntab=1 nrow=1 frre=-1 fsbo=0x14 fseo=0x1f8c avsp=0x1f78 tosp=0x1f84 0xe:pti[0] nrow=1 offs=0 0x12:pri[0] offs=0x1f8c block_row_dump: tab 0, row 0, @0x1f8c tl: 2 fb: --HDFL-- lb: 0x2 end_of_block_dump #----------------------------------------------------------------------------- # 重新登陆BBED BBED> set file 6 block 143 FILE# 6 BLOCK# 143 BBED> p kdbr sb2 kdbr[0] @118 8076 BBED> p *kdbr[0] rowdata[0] ---------- ub1 rowdata[0] @8176 0x3c # 3c-数据已是删除状态 BBED> x/rncccc rowdata[0] @8176 ---------- flag@8176: 0x3c (KDRHFL, KDRHFF, KDRHFD, KDRHFH) lock@8177: 0x02 cols@8178: 0 # 更改更备槽状态为0 BBED> modify /x 00 offset 8177 File: /u01/app/oracle/oradata/orcl/skip_arch01.dbf (6) Block: 143 Offsets: 8177 to 8191 Dba:0x0180008f ------------------------------------------------------------------------ 000202c1 02054141 41414101 0699bc <32 bytes per line> BBED> sum apply Check value for File 6, Block 143: current = 0x65d1, required = 0x65d1 # 更改提交状态 BBED> p ktbbh struct ktbbh, 72 bytes @20 ub1 ktbbhtyp @20 0x01 (KDDBTDATA) union ktbbhsid, 4 bytes @24 ub4 ktbbhsg1 @24 0x00017240 ub4 ktbbhod1 @24 0x00017240 struct ktbbhcsc, 8 bytes @28 ub4 kscnbas @28 0x0040bc99 ub2 kscnwrp @32 0x0000 sb2 ktbbhict @36 2 ub1 ktbbhflg @38 0x32 (NONE) ub1 ktbbhfsl @39 0x00 ub4 ktbbhfnx @40 0x01800088 struct ktbbhitl[0], 24 bytes @44 struct ktbitxid, 8 bytes @44 ub2 kxidusn @44 0x0002 ub2 kxidslt @46 0x0003 ub4 kxidsqn @48 0x00000a73 struct ktbituba, 8 bytes @52 ub4 kubadba @52 0x00c00201 ub2 kubaseq @56 0x037a ub1 kubarec @58 0x02 ub2 ktbitflg @60 0x8000 (KTBFCOM) union _ktbitun, 2 bytes @62 sb2 _ktbitfsc @62 0 ub2 _ktbitwrp @62 0x0000 ub4 ktbitbas @64 0x0040bc86 struct ktbbhitl[1], 24 bytes @68 struct ktbitxid, 8 bytes @68 ub2 kxidusn @68 0x0006 ub2 kxidslt @70 0x001c ub4 kxidsqn @72 0x00000ae5 struct ktbituba, 8 bytes @76 ub4 kubadba @76 0x00c00dee ub2 kubaseq @80 0x040c ub1 kubarec @82 0x04 ub2 ktbitflg @84 0x0001 (NONE) # 状态是非提交，需要修改成8000 (0080) union _ktbitun, 2 bytes @86 sb2 _ktbitfsc @86 10 # 需要将这里修改为0 ub2 _ktbitwrp @86 0x000a ub4 ktbitbas @88 0x00000000 BBED> modify /x 0080 offset 84 Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y File: /u01/app/oracle/oradata/orcl/skip_arch01.dbf (6) Block: 143 Offsets: 84 to 595 Dba:0x0180008f ------------------------------------------------------------------------ 00800a00 00000000 00000000 00000000 00010100 ffff1400 8c1f781f 841f0000 01008c1f 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 <32 bytes per line> BBED> modify /x 00 offset 86 File: /u01/app/oracle/oradata/orcl/skip_arch01.dbf (6) Block: 143 Offsets: 86 to 597 Dba:0x0180008f ------------------------------------------------------------------------ 00000000 00000000 00000000 00000001 0100ffff 14008c1f 781f841f 00000100 8c1f0000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 <32 bytes per line> BBED> sum apply Check value for File 6, Block 143: current = 0x67db, required = 0x67db # 校验 BBED> verify DBVERIFY - Verification starting FILE = /u01/app/oracle/oradata/orcl/skip_arch01.dbf BLOCK = 143 Block Checking: DBA = 25165967, Block Type = KTB-managed data block data header at 0x1ac7864 kdbchk: the amount of space used is not equal to block size used=22 fsc=0 avsp=8056 dtl=8088 # 8088-22=8066 Block 143 failed with check code 6110 DBVERIFY - Verification complete Total Blocks Examined : 1 Total Blocks Processed (Data) : 1 Total Blocks Failing (Data) : 1 Total Blocks Processed (Index): 0 Total Blocks Failing (Index): 0 Total Blocks Empty : 0 Total Blocks Marked Corrupt : 0 Total Blocks Influx : 0 Message 531 not found; product=RDBMS; facility=BBED BBED> p kdbh struct kdbh, 14 bytes @100 ub1 kdbhflag @100 0x00 (NONE) sb1 kdbhntab @101 1 sb2 kdbhnrow @102 1 sb2 kdbhfrre @104 -1 sb2 kdbhfsbo @106 20 sb2 kdbhfseo @108 8076 sb2 kdbhavsp @110 8056 # 需要修改 sb2 kdbhtosp @112 8068 # 需要修改（这个其实证明不用修改） BBED> dump /v offset 110 count 16 File: /u01/app/oracle/oradata/orcl/skip_arch01.dbf (6) Block: 143 Offsets: 110 to 125 Dba:0x0180008f ------------------------------------------------------- 781f841f 00000100 8c1f0000 00000000 l x............... <16 bytes per line> BBED> dump /v offset 112 count 16 File: /u01/app/oracle/oradata/orcl/skip_arch01.dbf (6) Block: 143 Offsets: 112 to 127 Dba:0x0180008f ------------------------------------------------------- 841f0000 01008c1f 00000000 00000000 l ................ <16 bytes per line> SQL> select to_char(8066,'xxxxxxxx') from dual; TO_CHAR(8 --------- 1f82 # 821f BBED> modify /x 82 offset 110 File: /u01/app/oracle/oradata/orcl/skip_arch01.dbf (6) Block: 143 Offsets: 110 to 125 Dba:0x0180008f ------------------------------------------------------------------------ 821f841f 00000100 8c1f0000 00000000 <32 bytes per line> BBED> modify /x 82 offset 112 File: /u01/app/oracle/oradata/orcl/skip_arch01.dbf (6) Block: 143 Offsets: 112 to 127 Dba:0x0180008f ------------------------------------------------------------------------ 821f0000 01008c1f 00000000 00000000 <32 bytes per line> BBED> sum apply Check value for File 6, Block 143: current = 0x652d, required = 0x652d BBED> verify DBVERIFY - Verification starting FILE = /u01/app/oracle/oradata/orcl/skip_arch01.dbf BLOCK = 143 Block Checking: DBA = 25165967, Block Type = KTB-managed data block data header at 0x1dafa64 kdbchk: space available on commit is incorrect tosp=8066 fsc=0 stb=2 avsp=8066 # 这里提示stb=2，那说明这个avsp 实际上应该是8066+2=8068才对 Block 143 failed with check code 6111 DBVERIFY - Verification complete Total Blocks Examined : 1 Total Blocks Processed (Data) : 1 Total Blocks Failing (Data) : 1 # 仍有问题 Total Blocks Processed (Index): 0 Total Blocks Failing (Index): 0 Total Blocks Empty : 0 Total Blocks Marked Corrupt : 0 Total Blocks Influx : 0 Message 531 not found; product=RDBMS; facility=BBED SQL> select to_char(8068,'xxxxxxxx') from dual; TO_CHAR(8 --------- 1f84 BBED> sum apply Check value for File 6, Block 143: current = 0x652b, required = 0x652b # 检验 - 终于对了 BBED> verify DBVERIFY - Verification starting FILE = /u01/app/oracle/oradata/orcl/skip_arch01.dbf BLOCK = 143 DBVERIFY - Verification complete Total Blocks Examined : 1 Total Blocks Processed (Data) : 1 Total Blocks Failing (Data) : 0 Total Blocks Processed (Index): 0 Total Blocks Failing (Index): 0 Total Blocks Empty : 0 Total Blocks Marked Corrupt : 0 Total Blocks Influx : 0 Message 531 not found; product=RDBMS; facility=BBED 会话二： SQL> select * from t2; ID NAME ---------- ---------- 1 AAAAA SQL> alter system flush buffer_cache; System altered. SQL> select * from t2; no rows selected |
为什么ASSM要比MSSM多了8个byte？请给出实验步骤证明
因为在ASSM下，ORACLE改变了block内部table directory和row directory的位置，oracle把它们顺延了8个字节
SQL> select * from v$type_size where component in ('KCB','KTB'); COMPONEN TYPE DESCRIPTION TYPE_SIZE -------- -------- -------------------------------- ---------- KCB KCBH BLOCK COMMON HEADER 20 KTB KTBIT TRANSACTION VARIABLE HEADER 24 KTB KTBBH TRANSACTION FIXED HEADER 48 KTB KTBBH_BS TRANSACTION BLOCK BITMAP SEGMENT 8 # 创建MSSM表空间 create tablespace mssm datafile '/u01/app/oracle/oradata/orcl/mssm01.dbf' size 10m autoextend on next 1m segment space management manual; select TABLESPACE_NAME,SEGMENT_SPACE_MANAGEMENT from dba_tablespaces; TABLESPACE_NAME SEGMEN ------------------------------ ------ SYSTEM MANUAL SYSAUX AUTO UNDOTBS1 MANUAL TEMP MANUAL USERS AUTO DSI AUTO SKIP_ARCH AUTO TS_16 AUTO MSSM MANUAL create table lyj.t4(id number,name varchar2(10)) tablespace mssm; insert into lyj.t4 values(1,'TTTTT'); commit; select id,name,DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) FILE#,DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) BLOCK# from lyj.t4; ID NAME FILE# BLOCK# ---------- ---------- ---------- ---------- 1 TTTTT 8 129 # 登入BBED echo "8 /u01/app/oracle/oradata/orcl/mssm01.dbf 10485760" >> filelist.txt bbed BBED> info File# Name Size(blks) ----- ---- ---------- 1 /u01/app/oracle/oradata/orcl/system01.dbf 97281 2 /u01/app/oracle/oradata/orcl/sysaux01.dbf 96001 3 /u01/app/oracle/oradata/orcl/undotbs01.dbf 21121 4 /u01/app/oracle/oradata/orcl/users01.dbf 641 5 /u01/app/oracle/oradata/orcl/dsi01.dbf 64001 6 /u01/app/oracle/oradata/orcl/skip_arch01.dbf 6400 7 /u01/app/oracle/oradata/orcl/ts16_01.dbf 6400 8 /u01/app/oracle/oradata/orcl/mssm01.dbf 1280 BBED> set file 8 block 129 FILE# 8 BLOCK# 129 BBED> map /v File: /u01/app/oracle/oradata/orcl/mssm01.dbf (8) Block: 129 Dba:0x02000081 ------------------------------------------------------------ KTB Data Block (Table/Cluster) struct kcbh, 20 bytes @0 ub1 type_kcbh @0 ub1 frmt_kcbh @1 ub1 spare1_kcbh @2 ub1 spare2_kcbh @3 ub4 rdba_kcbh @4 ub4 bas_kcbh @8 ub2 wrp_kcbh @12 ub1 seq_kcbh @14 ub1 flg_kcbh @15 ub2 chkval_kcbh @16 ub2 spare3_kcbh @18 struct ktbbh, 72 bytes @20 ub1 ktbbhtyp @20 union ktbbhsid, 4 bytes @24 struct ktbbhcsc, 8 bytes @28 sb2 ktbbhict @36 ub1 ktbbhflg @38 ub1 ktbbhfsl @39 ub4 ktbbhfnx @40 struct ktbbhitl[2], 48 bytes @44 struct kdbh, 14 bytes @92 # 从上面的例子可以看出，ASSM kdbh是100，比MSSM多了8个偏移量 ub1 kdbhflag @92 sb1 kdbhntab @93 sb2 kdbhnrow @94 sb2 kdbhfrre @96 sb2 kdbhfsbo @98 sb2 kdbhfseo @100 sb2 kdbhavsp @102 sb2 kdbhtosp @104 struct kdbt[1], 4 bytes @106 sb2 kdbtoffs @106 sb2 kdbtnrow @108 sb2 kdbr[1] @110 ub1 freespace[8064] @112 ub1 rowdata[12] @8176 ub4 tailchk @8188 |