本文共 8008 字，大约阅读时间需要 26 分钟。
[20160526]bbed修改数据记录(不等长).txt
--以前做的测试,有点乱,当时没有很好的理解快速提交.而且做的很乱,链接如下:
--今天重复测试看看:
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 EEEEESCOTT@book> @ &r/rowid AAAW7EAAEAAAT/7AAA
OBJECT FILE BLOCK ROW DBA TEXT ------------ ------------ ------------ ------------ -------------------- ---------------------------------------- 93892 4 81915 0 4,81915 alter system dump datafile 4 block 819152.修改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 EEEEE5SCOTT@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 0x2cBBED> 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: EEEEE5rowdata[13] @8102
----------- flag@8102: 0x2c (KDRHFL, KDRHFF, KDRHFH) lock@8103: 0x02 cols@8104: 2col 0[2] @8105: 3
col 1[6] @8108: CCCCC3rowdata[26] @8115
----------- flag@8115: 0x2c (KDRHFL, KDRHFF, KDRHFH) lock@8116: 0x02 cols@8117: 2col 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: 2col 0[2] @8131: 5
col 1[5] @8134: EEEEE....
rowdata[63] @8152
----------- flag@8152: 0x2c (KDRHFL, KDRHFF, KDRHFH) lock@8153: 0x00 cols@8154: 2col 0[2] @8155: 3
col 1[5] @8158: CCCCC...
rowdata[87] @8176
----------- flag@8176: 0x2c (KDRHFL, KDRHFF, KDRHFH) lock@8177: 0x00 cols@8178: 2col 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 8064assign kdbr[4] = 8028
assign kdbr[2] = 8052 assign kdbr[0] = 8076--执行如上命令:
--然后检查信息是否正确. BBED> p *kdbr[4] rowdata[39] ----------- ub1 rowdata[39] @8128 0x2cBBED> 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: EEEEEBBED> sum apply
Check value for File 4, Block 81915: current = 0x51d4, required = 0x51d4BBED> verify
DBVERIFY - Verification starting FILE = /mnt/ramdisk/book/users01.dbf BLOCK = 81915Block 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 8177BBED> p *kdbr[4]
rowdata[39] ----------- ub1 rowdata[39] @8128 0x2cBBED> x /rnc
rowdata[39] @8128 ----------- flag@8128: 0x2c (KDRHFL, KDRHFF, KDRHFH) lock@8129: 0x02 cols@8130: 2col 0[2] @8131: 5
col 1[5] @8134: EEEEEBBED> sum apply
Check value for File 4, Block 81915: current = 0x53d4, required = 0x53d4BBED> verify
DBVERIFY - Verification starting FILE = /mnt/ramdisk/book/users01.dbf BLOCK = 81915Block 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 7997BBED> 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=8000BBED> sum apply
Check value for File 4, Block 81915: current = 0x53d4, required = 0x53d4BBED> verify
DBVERIFY - Verification starting FILE = /mnt/ramdisk/book/users01.dbf BLOCK = 819155.修改对应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 0x1508f9f76.检验修改成果:
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 7647BBED> 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 0x8fc1230fBlock 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: 0Itl 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=0BBED> sum apply
Check value for File 21, Block 9116: current = 0x16db, required = 0x16dbBBED> verify
DBVERIFY - Verification starting FILE = /home/oracle/emp.aaa BLOCK = 91165.写这些主要是前面修复一个没有加where的update语句,好在表本身记录不多,没有打开归档,已经过去N久,修改前的信息还保存在块中.
所以才重新整理这篇文档.转载地址：http://vrjmo.baihongyu.com/