[20160526]bbed修改数据记录(不等长).txt

[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久,修改前的信息还保存在块中.
  所以才重新整理这篇文档.