[20160526]bbed修改数据记录(不等长).txt
--以前做的测试,有点乱,当时没有很好的理解快速提交.而且做的很乱,链接如下:
http://blog.itpub.net/267265/viewspace-1193074/
--今天重复测试看看:
1.环境:
SCOTT@book> @ &r/ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
--首先等长的修改很简单,因为是原地替换,直接修改就ok了,再做sum apply就ok了.这个不再练习的范围.
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
create table t (id int,name varchar2(100));
insert into t values(1,'AAAAA');
insert into t values(2,'BBBBB');
insert into t values(3,'CCCCC');
insert into t values(4,'DDDDD');
insert into t values(5,'EEEEE');
commit;
alter system checkpoint;
SCOTT@book> select ora_rowscn,rowid,t.* from t;
ORA_ROWSCN ROWID ID NAME
------------ ------------------ ------------ --------------------
13237811650 AAAW7EAAEAAAT/7AAA 1 AAAAA
13237811650 AAAW7EAAEAAAT/7AAB 2 BBBBB
13237811650 AAAW7EAAEAAAT/7AAC 3 CCCCC
13237811650 AAAW7EAAEAAAT/7AAD 4 DDDDD
13237811650 AAAW7EAAEAAAT/7AAE 5 EEEEE
SCOTT@book> @ &r/rowid AAAW7EAAEAAAT/7AAA
OBJECT FILE BLOCK ROW DBA TEXT
------------ ------------ ------------ ------------ -------------------- ----------------------------------------
93892 4 81915 0 4,81915 alter system dump datafile 4 block 81915
2.修改id=1,3,5:
SCOTT@book> update t set name=name||id where id in (1,3,5);
3 rows updated.
SCOTT@book> commit ;
Commit complete.
SCOTT@book> select ora_rowscn,rowid,t.* from t;
ORA_ROWSCN ROWID ID NAME
------------ ------------------ ------------ --------------------
13237811703 AAAW7EAAEAAAT/7AAA 1 AAAAA1
13237811703 AAAW7EAAEAAAT/7AAB 2 BBBBB
13237811703 AAAW7EAAEAAAT/7AAC 3 CCCCC3
13237811703 AAAW7EAAEAAAT/7AAD 4 DDDDD
13237811703 AAAW7EAAEAAAT/7AAE 5 EEEEE5
SCOTT@book> alter system checkpoint;
System altered.
SCOTT@book> alter system flush buffer_cache;
System altered.
--OK现在使用bbed是否可以恢复原样.
3.使用bbed恢复:
BBED> set dba 4,81915
DBA 0x01013ffb (16859131 4,81915)
BBED> p kdbr
sb2 kdbr[0] @118 8015
sb2 kdbr[1] @120 8064
sb2 kdbr[2] @122 8002
sb2 kdbr[3] @124 8040
sb2 kdbr[4] @126 7989
--最小的offset是kdbr[4].
BBED> p *kdbr[4]
rowdata[0]
----------
ub1 rowdata[0] @8089 0x2c
BBED> x /3rnc rowdata
rowdata[0] @8089
----------
flag@8089: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8090: 0x02
cols@8091: 2
col 0[2] @8092: 5
col 1[6] @8095: EEEEE5
rowdata[13] @8102
-----------
flag@8102: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8103: 0x02
cols@8104: 2
col 0[2] @8105: 3
col 1[6] @8108: CCCCC3
rowdata[26] @8115
-----------
flag@8115: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8116: 0x02
cols@8117: 2
col 0[2] @8118: 1
col 1[6] @8121: AAAAA1
--//从这里可以确定修改3条记录.lock=0x02,对应第2个ITL槽.
--使用如下可以输出全部记录.
BBED> p *kdbr[4]
BBED> x /8rnc rowdata
...
rowdata[39] @8128
-----------
flag@8128: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8129: 0x00
cols@8130: 2
col 0[2] @8131: 5
col 1[5] @8134: EEEEE
....
rowdata[63] @8152
-----------
flag@8152: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8153: 0x00
cols@8154: 2
col 0[2] @8155: 3
col 1[5] @8158: CCCCC
...
rowdata[87] @8176
-----------
flag@8176: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8177: 0x00
cols@8178: 2
col 0[2] @8179: 1
col 1[5] @8182: AAAAA
--可以确定偏移在8128,8152,8176. kdbr记录的是相对偏移要再减去100. 应该输入:8028,8052,8076
--排序kdbr,前面3个就是的地址要替换为原来的.
7989=>kdbr[4]=>8028
8002=>kdbr[2]=>8052
8015=>kdbr[0]=>8076
8040
8064
assign kdbr[4] = 8028
assign kdbr[2] = 8052
assign kdbr[0] = 8076
--执行如上命令:
--然后检查信息是否正确.
BBED> p *kdbr[4]
rowdata[39]
-----------
ub1 rowdata[39] @8128 0x2c
BBED> x /rnc
rowdata[39] @8128
-----------
flag@8128: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8129: 0x00
cols@8130: 2
col 0[2] @8131: 5
col 1[5] @8134: EEEEE
BBED> sum apply
Check value for File 4, Block 81915:
current = 0x51d4, required = 0x51d4
BBED> verify
DBVERIFY - Verification starting
FILE = /mnt/ramdisk/book/users01.dbf
BLOCK = 81915
Block Checking: DBA = 16859131, Block Type = KTB-managed data block
data header at 0x7f8163e23264
kdbchk: xaction header lock count mismatch
trans=2 ilk=3 nlo=0
Block 81915 failed with check code 6108
--修改行记录的lock=0x20.注意在行记录的位置+1. 8128,8152,8176=>8129,8153,8177.
modify /x 0x02 offset 8129
modify /x 0x02 offset 8153
modify /x 0x02 offset 8177
BBED> p *kdbr[4]
rowdata[39]
-----------
ub1 rowdata[39] @8128 0x2c
BBED> x /rnc
rowdata[39] @8128
-----------
flag@8128: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8129: 0x02
cols@8130: 2
col 0[2] @8131: 5
col 1[5] @8134: EEEEE
BBED> sum apply
Check value for File 4, Block 81915:
current = 0x53d4, required = 0x53d4
BBED> verify
DBVERIFY - Verification starting
FILE = /mnt/ramdisk/book/users01.dbf
BLOCK = 81915
Block Checking: DBA = 16859131, Block Type = KTB-managed data block
data header at 0xbe0464
kdbchk: the amount of space used is not equal to block size
used=88 fsc=0 avsp=7997 dtl=8088
Block 81915 failed with check code 6110
--依旧不行.
4.要修改一致kdbh.kdbhtosp=kdbh.kdbhavsp
BBED> p kdbh.kdbhavsp
sb2 kdbhavsp @110 7997
BBED> p kdbh.kdbhtosp
sb2 kdbhtosp @112 7997
--要修改一致kdbh.kdbhtosp=kdbh.kdbhavsp,已经一致可以不修改.
BBED> assign kdbh.kdbhtosp=kdbh.kdbhavsp
sb2 kdbhtosp @112 7997
--// dtl-used = 8088-88=8000
assign kdbh.kdbhtosp=8000
assign kdbh.kdbhavsp=8000
BBED> sum apply
Check value for File 4, Block 81915:
current = 0x53d4, required = 0x53d4
BBED> verify
DBVERIFY - Verification starting
FILE = /mnt/ramdisk/book/users01.dbf
BLOCK = 81915
5.修改对应ITL信息:
--//我看了其他文档要修改对应
assign ktbbhitl[1]._ktbitun,_ktbitfsc=0
--//不过我这里已经是0,不需要修改,上面也不报错.而且这个信息对应上面的fsc=0(used=88 fsc=0 avsp=7997 dtl=8088)
BBED> p ktbbhitl[1]
struct ktbbhitl[1], 24 bytes @68
struct ktbitxid, 8 bytes @68
ub2 kxidusn @68 0x000a
ub2 kxidslt @70 0x001e
ub4 kxidsqn @72 0x00009b75
struct ktbituba, 8 bytes @76
ub4 kubadba @76 0x00c001b5
ub2 kubaseq @80 0x1def
ub1 kubarec @82 0x17
ub2 ktbitflg @84 0x2003 (KTBFUPB)
union _ktbitun, 2 bytes @86
sb2 _ktbitfsc @86 0
ub2 _ktbitwrp @86 0x0000
ub4 ktbitbas @88 0x1508f9f7
6.检验修改成果:
SCOTT@book> select ora_rowscn,rowid,t.* from t;
ORA_ROWSCN ROWID ID NAME
------------ ------------------ ------------ --------------------
13237811703 AAAW7EAAEAAAT/7AAA 1 AAAAA
13237811703 AAAW7EAAEAAAT/7AAB 2 BBBBB
13237811703 AAAW7EAAEAAAT/7AAC 3 CCCCC
13237811703 AAAW7EAAEAAAT/7AAD 4 DDDDD
13237811703 AAAW7EAAEAAAT/7AAE 5 EEEEE
--OK结果正确.
总结:
1.这样操作比我原来的修改简单一些,我以前的修改太多.
2.首先修改行目录指向正确的偏移.
3.修改相应记录的lock等于原来的itl槽.
4.使用verify检查根据提示,我这里fsc=0,我那另外的情况来说明:
--修改前:
BBED> p /d kdbh
struct kdbh, 14 bytes @100
ub1 kdbhflag @100 0 (NONE)
sb1 kdbhntab @101 1
sb2 kdbhnrow @102 1
sb2 kdbhfrre @104 -1
sb2 kdbhfsbo @106 20
sb2 kdbhfseo @108 6841
sb2 kdbhavsp @110 7608
sb2 kdbhtosp @112 7647
BBED> p ktbbhitl[0]
struct ktbbhitl[0], 24 bytes @44
struct ktbitxid, 8 bytes @44
ub2 kxidusn @44 0x000a
ub2 kxidslt @46 0x002e
ub4 kxidsqn @48 0x00001ac8
struct ktbituba, 8 bytes @52
ub4 kubadba @52 0x00800860
ub2 kubaseq @56 0x2398
ub1 kubarec @58 0x1c
ub2 ktbitflg @60 0x2001 (KTBFUPB)
union _ktbitun, 2 bytes @62
sb2 _ktbitfsc @62 39
ub2 _ktbitwrp @62 0x0027
ub4 ktbitbas @64 0x8fc1230f
Block header dump: 0x0180239c
Object id on Block? Y
seg/obj: 0x1da20 csc: 0x03.8fc12309 itc: 2 flg: E typ: 1 - DATA
brn: 0 bdba: 0x1802399 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x000a.02e.00001ac8 0x00800860.2398.1c --U- 1 fsc 0x0027.8fc1230f
0x02 0x0001.005.00001873 0x0080000f.1e28.0d C--- 0 scn 0x0003.690c955a
--修改后出现:
Block Checking: DBA = 25174940, Block Type = KTB-managed data block
data header at 0x22a9c64
kdbchk: the amount of space used is not equal to block size
used=480 fsc=39 avsp=7608 dtl=8088
Block 9116 failed with check code 6110
--实际上修改前满足 dtl-used+fsc= kdbh.kdbhtosp
-- 8088-480+39=7647
--fsc来源于ITL槽.
--// dtl-used 8088-480=7608
assign kdbh.kdbhavsp=7608
assign kdbh.kdbhtosp=7608
--然后设置(根据修改记录lock指向的itl槽)
assign ktbbhitl[0]._ktbitun._ktbitfsc=0
BBED> sum apply
Check value for File 21, Block 9116:
current = 0x16db, required = 0x16db
BBED> verify
DBVERIFY - Verification starting
FILE = /home/oracle/emp.aaa
BLOCK = 9116
5.写这些主要是前面修复一个没有加where的update语句,好在表本身记录不多,没有打开归档,已经过去N久,修改前的信息还保存在块中.
所以才重新整理这篇文档.
