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，改为CCCCCselect * 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