联系：手机/微信(+86 17813235971) QQ(107644445)
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
模拟数据块更新
SQL> create table t_xifenfei(id number,name varchar2(10));
Table created.
SQL> insert into t_xifenfei values(1,'XFF');
1 row created.
SQL> insert into t_xifenfei values(2,'CHF');
1 row created.
SQL> commit;
Commit complete.
SQL> alter system checkpoint;
System altered.
SQL> select id,rowid,
2 dbms_rowid.rowid_relative_fno(rowid)rel_fno,
3 dbms_rowid.rowid_block_number(rowid)blockno,
4 dbms_rowid.rowid_row_number(rowid) rowno
5 from t_xifenfei;
ID ROWID REL_FNO BLOCKNO ROWNO
---------- ------------------ ---------- ---------- ----------
1 AAASc+AAEAAAACvAAA 4 175 0
2 AAASc+AAEAAAACvAAB 4 175 1
SQL> select dump(1,'16') from dual;
DUMP(1,'16')
-----------------
Typ=2 Len=2: c1,2
SQL> select dump(2,'16') from dual;
DUMP(2,'16')
-----------------
Typ=2 Len=2: c1,3
SQL> select dump('XFF','16') FROM DUAL;
DUMP('XFF','16')
----------------------
Typ=96 Len=3: 58,46,46
SQL> SELECT DUMP('CHF','16') FROM DUAL;
DUMP('CHF','16')
----------------------
Typ=96 Len=3: 43,48,46
SQL> update t_xifenfei set name='XIFENFEI' where id=1;
1 row updated.
SQL> commit;
Commit complete.
SQL> select dump('XIFENFEI','16') from dual;
DUMP('XIFENFEI','16')
-------------------------------------
Typ=96 Len=8: 58,49,46,45,4e,46,45,49
SQL> alter system checkpoint;
System altered.
SQL> select * from t_xifenfei;
ID NAME
---------- ----------
1 XIFENFEI
2 CHF
这里我们对数据库进行了一次更新操作,并且dump出来对应值,为了方便定位到相应记录
bbed查看相关值
[oracle@xifenfei ~]$ bbed listfile=listfile mode=edit password=blockedit
BBED: Release 2.0.0.0.0 - Limited Production on Wed Aug 8 20:50:47 2012
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
************* !!! For Oracle Internal Use only !!! ***************
BBED> set file 4 block 175
FILE# 4
BLOCK# 175
BBED> map
File: /u01/oracle/oradata/ora11g/users01.dbf (4)
Block: 175 Dba:0x010000af
------------------------------------------------------------
KTB Data Block (Table/Cluster)
struct kcbh, 20 bytes @0
struct ktbbh, 72 bytes @20
struct kdbh, 14 bytes @100
struct kdbt[1], 4 bytes @114
sb2 kdbr[2] @118
ub1 freespace[8031] @122
ub1 rowdata[35] @8153
ub4 tailchk @8188
BBED> p kdbr
sb2 kdbr[0] @118 8053
sb2 kdbr[1] @120 8068
BBED> p *kdbr[1]
rowdata[15]
-----------
ub1 rowdata[15] @8168 0x2c
BBED> x /rnc
rowdata[15] @8168
-----------
flag@8168: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8169: 0x00
cols@8170: 2
col 0[2] @8171: 2
col 1[3] @8174: CHF
BBED> p *kdbr[0]
rowdata[0]
----------
ub1 rowdata[0] @8153 0x2c
BBED> x /rnc
rowdata[0] @8153
----------
flag@8153: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8154: 0x02
cols@8155: 2
col 0[2] @8156: 1
col 1[8] @8159: XIFENFEI
BBED> set count 64
COUNT 64
<32 bytes per line>
BBED> d /v
File: /u01/oracle/oradata/ora11g/users01.dbf (4)
Block: 175 Offsets: 8153 to 8191 Dba:0x010000af
-------------------------------------------------------
2c020202 c1020858 4946454e 4645492c l ,......XIFENFEI,
000202c1 03034348 462c0002 02c10203 l ......CHF,......
58464602 068de8 l XFF....
<16 bytes per line>
使用bbed找回历史值
--准备工作,通过dump出来的值,推算出来第一条记录的起点02c10203584646,
--在这个值的基础上offset-3得到offset值为8078
BBED> p kdbr
sb2 kdbr[0] @118 8053
sb2 kdbr[1] @120 8068
--修改row directory指针位置
BBED> m /x 8e1f
File: /u01/oracle/oradata/ora11g/users01.dbf (4)
Block: 175 Offsets: 118 to 181 Dba:0x010000af
------------------------------------------------------------------------
8e1f841f 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
<32 bytes per line>
BBED> p kdbr
sb2 kdbr[0] @118 8078
sb2 kdbr[1] @120 8068
BBED> sum apply
Check value for File 4, Block 175:
current = 0xdff8, required = 0xdff8
BBED> verify
DBVERIFY - Verification starting
FILE = /u01/oracle/oradata/ora11g/users01.dbf
BLOCK = 175
Block Checking: DBA = 16777391, Block Type = KTB-managed data block
data header at 0xb53cd264
kdbchk: xaction header lock count mismatch
trans=2 ilk=1 nlo=0 --提示事务锁错误
Block 175 failed with check code 6108
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 *kdbr[0]
rowdata[25]
-----------
ub1 rowdata[25] @8178 0x2c
BBED> d
File: /u01/oracle/oradata/ora11g/users01.dbf (4)
Block: 175 Offsets: 8178 to 8191 Dba:0x010000af
------------------------------------------------------------------------
2c000202 c1020358 46460206 8de8
<32 bytes per line>
BBED> x /rnc
rowdata[25] @8178
-----------
flag@8178: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8179: 0x00 --被更新前的记录事务锁标识为0,而更新后的事务锁标识为2
cols@8180: 2
col 0[2] @8181: 1
col 1[3] @8184: XFF
--修改事务锁标识为2
BBED> m /x 02 offset 8179
File: /u01/oracle/oradata/ora11g/users01.dbf (4)
Block: 175 Offsets: 8179 to 8191 Dba:0x010000af
------------------------------------------------------------------------
020202c1 02035846 4602068d e8
<32 bytes per line>
BBED> set offset 8153
OFFSET 8153
BBED> d
File: /u01/oracle/oradata/ora11g/users01.dbf (4)
Block: 175 Offsets: 8153 to 8191 Dba:0x010000af
------------------------------------------------------------------------
2c020202 c1020858 4946454e 4645492c 000202c1 03034348 462c0202 02c10203
58464602 068de8
<32 bytes per line>
--把更新后值的事务锁标识改为0
BBED> m /x 00 offset +1
File: /u01/oracle/oradata/ora11g/users01.dbf (4)
Block: 175 Offsets: 8154 to 8191 Dba:0x010000af
------------------------------------------------------------------------
000202c1 02085849 46454e46 45492c00 0202c103 03434846 2c020202 c1020358
46460206 8de8
<32 bytes per line>
BBED> sum apply
Check value for File 4, Block 175:
current = 0xddfa, required = 0xddfa
BBED> verify
DBVERIFY - Verification starting
FILE = /u01/oracle/oradata/ora11g/users01.dbf
BLOCK = 175
Block Checking: DBA = 16777391, Block Type = KTB-managed data block
data header at 0xb53cd264
kdbchk: the amount of space used is not equal to block size
used=42 fsc=0 avsp=8041 dtl=8088 -->提示块的空间使用不正确
Block 175 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 ktbbh
struct ktbbh, 72 bytes @20
ub1 ktbbhtyp @20 0x01 (KDDBTDATA)
union ktbbhsid, 4 bytes @24
ub4 ktbbhsg1 @24 0x0001273e
ub4 ktbbhod1 @24 0x0001273e
struct ktbbhcsc, 8 bytes @28
ub4 kscnbas @28 0x0000e88a
ub2 kscnwrp @32 0x0002
sb2 ktbbhict @36 2
ub1 ktbbhflg @38 0x32 (NONE)
ub1 ktbbhfsl @39 0x00
ub4 ktbbhfnx @40 0x010000a8
struct ktbbhitl[0], 24 bytes @44
struct ktbitxid, 8 bytes @44
ub2 kxidusn @44 0x0006
ub2 kxidslt @46 0x001e
ub4 kxidsqn @48 0x000002c6
struct ktbituba, 8 bytes @52
ub4 kubadba @52 0x00c000d9
ub2 kubaseq @56 0x0086
ub1 kubarec @58 0x2a
ub2 ktbitflg @60 0x8000 (KTBFCOM)
union _ktbitun, 2 bytes @62
sb2 _ktbitfsc @62 2
ub2 _ktbitwrp @62 0x0002
ub4 ktbitbas @64 0x0000e550
struct ktbbhitl[1], 24 bytes @68
struct ktbitxid, 8 bytes @68
ub2 kxidusn @68 0x0006
ub2 kxidslt @70 0x0008
ub4 kxidsqn @72 0x000002c7
struct ktbituba, 8 bytes @76
ub4 kubadba @76 0x00c000da
ub2 kubaseq @80 0x0086
ub1 kubarec @82 0x12
ub2 ktbitflg @84 0x2001 (KTBFUPB)
union _ktbitun, 2 bytes @86
sb2 _ktbitfsc @86 0
ub2 _ktbitwrp @86 0x0000
ub4 ktbitbas @88 0x0000e88d
--所有的_ktbitfsc修改为0
BBED> m /x 00 offset 62
File: /u01/oracle/oradata/ora11g/users01.dbf (4)
Block: 175 Offsets: 62 to 125 Dba:0x010000af
------------------------------------------------------------------------
000050e5 00000600 0800c702 0000da00 c0008600 12000120 00008de8 00000000
00000000 00000001 0200ffff 1600751f 691f691f 00000200 8e1f841f 00000000
<32 bytes per line>
BBED> sum apply
Check value for File 4, Block 175:
current = 0xddf8, required = 0xddf8
BBED> verify
DBVERIFY - Verification starting
FILE = /u01/oracle/oradata/ora11g/users01.dbf
BLOCK = 175
Block Checking: DBA = 16777391, Block Type = KTB-managed data block
data header at 0xb53cd264
kdbchk: the amount of space used is not equal to block size
used=42 fsc=0 avsp=8041 dtl=8088
Block 175 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 2
sb2 kdbhfrre @104 -1
sb2 kdbhfsbo @106 22
sb2 kdbhfseo @108 8053
sb2 kdbhavsp @110 8045
sb2 kdbhtosp @112 8045
--修改kdbhtosp和kdbhavsp值
BBED> m /x 6e1f offset 112
File: /u01/oracle/oradata/ora11g/users01.dbf (4)
Block: 175 Offsets: 112 to 175 Dba:0x010000af
------------------------------------------------------------------------
6e1f0000 02008e1f 841f0000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
<32 bytes per line>
BBED> m /x 6e1f offset 110
File: /u01/oracle/oradata/ora11g/users01.dbf (4)
Block: 175 Offsets: 110 to 173 Dba:0x010000af
------------------------------------------------------------------------
6e1f6e1f 00000200 8e1f841f 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
<32 bytes per line>
BBED> sum apply
Check value for File 4, Block 175:
current = 0xddf8, required = 0xddf8
--数据块验证通过
BBED> verify
DBVERIFY - Verification starting
FILE = /u01/oracle/oradata/ora11g/users01.dbf
BLOCK = 175
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> shutdown abort
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 313860096 bytes
Fixed Size 1344652 bytes
Variable Size 251661172 bytes
Database Buffers 54525952 bytes
Redo Buffers 6328320 bytes
Database mounted.
Database opened.
--找回更新前值
SQL> select * from chf.t_xifenfei;
ID NAME
---------- ----------
1 XFF
2 CHF
飞总，好文章，精彩!