【Oracle】使用bbed恢复update的数据

首页 > 代码库 > 【Oracle】使用bbed恢复update的数据

【Oracle】使用bbed恢复update的数据

2024-07-09 03:04:20 224人阅读

使用bbed不仅仅可以找回已经delete的数据还可以恢复update的数据，当然过程要比恢复delete的数据复杂一些。

实验过程如下：

SYS@ORCL>create table bbed_test(x int,y varchar2(20));

Table created.

SYS@ORCL>insert into bbed_test values(1,‘BADLY9‘);

1 row created.

SYS@ORCL>insert into bbed_test values(2,‘JP‘);

1 row created.

SYS@ORCL>commit;

Commit complete.

SYS@ORCL>select rowid, dbms_rowid.rowid_relative_fno(rowid)rel_fno,

2 dbms_rowid.rowid_block_number(rowid)blockno,

3 dbms_rowid.rowid_row_number(rowid) rowno

4 from bbed_test;

ROWID REL_FNO BLOCKNO ROWNO

------------------ ---------- ---------- ----------

AAAM0WAABAAAOuCAAA 1 60290 0

AAAM0WAABAAAOuCAAB 1 60290 1

SYS@ORCL>update bbed_test set y=‘JP1‘ where x=1;

1 row updated.

SYS@ORCL>commit;

Commit complete.

SYS@ORCL>select dump(1,‘16‘) from dual;

DUMP(1,‘16‘)

-----------------

Typ=2 Len=2: c1,2

SYS@ORCL>select dump(2,‘16‘) from dual;

DUMP(2,‘16‘)

-----------------

Typ=2 Len=2: c1,3

SYS@ORCL>select dump(‘BADLY9‘,16) from dual;

DUMP(‘BADLY9‘,16)

-------------------------------

Typ=96 Len=6: 42,41,44,4c,59,39

SYS@ORCL>select dump(‘JP1‘,‘16‘) from dual;

DUMP(‘JP1‘,‘16‘)

----------------------

Typ=96 Len=3: 4a,50,31

SYS@ORCL>select * from bbed_test;

X Y

---------- --------------------

1 JP1

2 JP

下面使用bbed进行查看

BBED> set dba 1,60290

DBA 0x0040eb82 (4254594 1,60290)

BBED> map

File: /u01/app/oracle/oradata/ORCL/system01.dbf (1)

Block: 60290 Dba:0x0040eb82

------------------------------------------------------------

KTB Data Block (Table/Cluster)

struct kcbh, 20 bytes @0

struct ktbbh, 72 bytes @20

struct kdbh, 14 bytes @92

struct kdbt[1], 4 bytes @106

sb2 kdbr[2] @110

ub1 freespace[8042] @114

ub1 rowdata[32] @8156

ub4 tailchk @8188

BBED> p kdbr

sb2 kdbr[0] @110 8064

sb2 kdbr[1] @112 8074

BBED> p *kdbr[1]

rowdata[10]

-----------

ub1 rowdata[10] @8166 0x2c

8166-8074=92

BBED> x /rnc

rowdata[10] @8166

-----------

flag@8166: 0x2c (KDRHFL, KDRHFF, KDRHFH)

lock@8167: 0x00

cols@8168: 2

col 0[2] @8169: 2

col 1[2] @8172: JP

BBED> p *kdbr[0]

rowdata[0]

----------

ub1 rowdata[0] @8156 0x2c

BBED> x /rnc

rowdata[0] @8156

----------

flag@8156: 0x2c (KDRHFL, KDRHFF, KDRHFH)

lock@8157: 0x02

cols@8158: 2

col 0[2] @8159: 1

col 1[3] @8162: JP1

BBED> set count 64

COUNT 64

BBED> d /v

File: /u01/app/oracle/oradata/ORCL/system01.dbf (1)

Block: 60290 Offsets: 8156 to 8191 Dba:0x0040eb82

-------------------------------------------------------

2c020202 c102034a 50312c00 0202c103 l ,...?..JP1,...?.

024a502c 000202c1 02064241 444c5939 l .JP,...?..BADLY9

0206d5a3 l ..?

<16 bytes per line>

使用bbed找回历史值

通过dump出来的值，推算出来第一条记录的起点02c1 02064241 444c5939

在这个值的基础上offset-3得到offset的值为8175

8175-92=8083 1f93

BBED> p kdbr

sb2 kdbr[0] @110 8064

sb2 kdbr[1] @112 8074

--修改row directory指针位置

BBED> m /x 931f

File: /u01/app/oracle/oradata/ORCL/system01.dbf (1)

Block: 60290 Offsets: 110 to 173 Dba:0x0040eb82

------------------------------------------------------------------------

931f8a1f 02000000 02000200 791f321f 831e481e 00000000 00000000 00000000

00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

<32 bytes per line>

BBED> p kdbr

sb2 kdbr[0] @110 8083

sb2 kdbr[1] @112 8074

BBED> sum apply

Check value for File 1, Block 60290:

current = 0x3e20, required = 0x3e20

BBED> verify

DBVERIFY - Verification starting

FILE = /u01/app/oracle/oradata/ORCL/system01.dbf

BLOCK = 60290

Block Checking: DBA = 4254594, Block Type = KTB-managed data block

data header at 0x79a25c

kdbchk: xaction header lock count mismatch

trans=2 ilk=1 nlo=0 --提示事务错误

Block 60290 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

BBED> p *kdbr[0]

rowdata[19]

-----------

ub1 rowdata[19] @8175 0x2c

BBED> d

File: /u01/app/oracle/oradata/ORCL/system01.dbf (1)

Block: 60290 Offsets: 8175 to 8191 Dba:0x0040eb82

------------------------------------------------------------------------

2c000202 c1020642 41444c59 390206d5 a3

<32 bytes per line>

BBED> x /rnc

rowdata[19] @8175

-----------

flag@8175: 0x2c (KDRHFL, KDRHFF, KDRHFH)

lock@8176: 0x00 --被更新前的记录事务锁标识为0，而更新后的事务锁标识为2

cols@8177: 2

col 0[2] @8178: 1

col 1[6] @8181: BADLY9

--修改事务锁标识为2

BBED> m /x 02 offset +1

File: /u01/app/oracle/oradata/ORCL/system01.dbf (1)

Block: 60290 Offsets: 8176 to 8191 Dba:0x0040eb82

------------------------------------------------------------------------

020202c1 02064241 444c5939 0206d5a3

<32 bytes per line>

--把更新后值得事务锁标识改为0

BBED> set offset 8156

OFFSET 8156

BBED> m /x 00 offset +1

File: /u01/app/oracle/oradata/ORCL/system01.dbf (1)

Block: 60290 Offsets: 8157 to 8191 Dba:0x0040eb82

------------------------------------------------------------------------

000202c1 02034a50 312c0002 02c10302 4a502c02 0202c102 06424144 4c593902

06d5a3

<32 bytes per line>

BBED> sum apply

Check value for File 1, Block 60290:

current = 0x3c22, required = 0x3c22

BBED> verify

DBVERIFY - Verification starting

FILE = /u01/app/oracle/oradata/ORCL/system01.dbf

BLOCK = 60290

Block Checking: DBA = 4254594, Block Type = KTB-managed data block

data header at 0x28225c

kdbchk: the amount of space used is not equal to block size

used=44 fsc=3 avsp=8052 dtl=8096 --提示块的空间使用不正确

Block 60290 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

此时要将所有itl的fsc全部改为0，然后将kdbh.kdbhavsp和kdbh.kdbhtosp的值改成相等，它们的值都等于dbv校验中显示的dtl－used。本次案例为8096-44 741f

BBED> p ktbbh

struct ktbbh, 72 bytes @20

ub1 ktbbhtyp @20 0x01 (KDDBTDATA)

union ktbbhsid, 4 bytes @24

ub4 ktbbhsg1 @24 0x0000cd16

ub4 ktbbhod1 @24 0x0000cd16

struct ktbbhcsc, 8 bytes @28

ub4 kscnbas @28 0x0007a3d3

ub2 kscnwrp @32 0x0000

b2 ktbbhict @36 7938

ub1 ktbbhflg @38 0x03 (KTBFONFL)

ub1 ktbbhfsl @39 0x00

ub4 ktbbhfnx @40 0x00000000

struct ktbbhitl[0], 24 bytes @44

struct ktbitxid, 8 bytes @44

ub2 kxidusn @44 0x0008

ub2 kxidslt @46 0x0009

ub4 kxidsqn @48 0x0000010e

struct ktbituba, 8 bytes @52

ub4 kubadba @52 0x00800270

ub2 kubaseq @56 0x00c4

ub1 kubarec @58 0x02

ub2 ktbitflg @60 0x8000 (KTBFCOM)

union _ktbitun, 2 bytes @62

b2 _ktbitfsc @62 0

ub2 _ktbitwrp @62 0x0000

ub4 ktbitbas @64 0x0007a37e

struct ktbbhitl[1], 24 bytes @68

struct ktbitxid, 8 bytes @68

ub2 kxidusn @68 0x0001

ub2 kxidslt @70 0x0024

ub4 kxidsqn @72 0x000000d5

struct ktbituba, 8 bytes @76

ub4 kubadba @76 0x008000d8

ub2 kubaseq @80 0x0082

ub1 kubarec @82 0x38

ub2 ktbitflg @84 0x2001 (KTBFUPB)

union _ktbitun, 2 bytes @86

b2 _ktbitfsc @86 3

ub2 _ktbitwrp @86 0x0003

ub4 ktbitbas @88 0x0007a3d5

BBED> m /x 00 offset 86

File: /u01/app/oracle/oradata/ORCL/system01.dbf (1)

Block: 60290 Offsets: 86 to 149 Dba:0x0040eb82

------------------------------------------------------------------------

0000d5a3 07000001 0200ffff 1600801f 741f771f 00000200 931f8a1f 02000000

02000200 791f321f 831e481e 00000000 00000000 00000000 00000000 00000000

<32 bytes per line>

BBED> sum apply

Check value for File 1, Block 60290:

current = 0x3c21, required = 0x3c21

BBED> p kdbh

struct kdbh, 14 bytes @92

ub1 kdbhflag @92 0x00 (NONE)

b1 kdbhntab @93 1

b2 kdbhnrow @94 2

sb2 kdbhfrre @96 -1

sb2 kdbhfsbo @98 22

sb2 kdbhfseo @100 8064

b2 kdbhavsp @102 8052

b2 kdbhtosp @104 8055

BBED> m /x 741f offset 102

File: /u01/app/oracle/oradata/ORCL/system01.dbf (1)

Block: 60290 Offsets: 102 to 165 Dba:0x0040eb82

------------------------------------------------------------------------

741f771f 00000200 931f8a1f 02000000 02000200 791f321f 831e481e 00000000

00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

<32 bytes per line>

BBED> m /x 741f offset 104

File: /u01/app/oracle/oradata/ORCL/system01.dbf (1)

Block: 60290 Offsets: 104 to 167 Dba:0x0040eb82

------------------------------------------------------------------------

741f0000 0200931f 8a1f0200 00000200 0200791f 321f831e 481e0000 00000000

00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

<32 bytes per line>

BBED> sum apply

Check value for File 1, Block 60290:

current = 0x3c22, required = 0x3c22

BBED> verify

DBVERIFY - Verification starting

FILE = /u01/app/oracle/oradata/ORCL/system01.dbf

BLOCK = 60290

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

此时数据块验证通过

现在回到库里进行验证：

SYS@ORCL>alter system flush buffer_cache;

System altered.

SYS@ORCL>select * from bbed_test;

X Y

---------- --------------------

1 BADLY9

2 JP

可以看到UPDATE之前的数据已经被恢复回来了。

实验过程参考飞总的文章：

http;//www.xifenfei.com/3704.html

声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉：投诉/举报工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。

联系
我们

首页 > 代码库 > 【Oracle】使用bbed恢复update的数据

【Oracle】使用bbed恢复update的数据

看完仍有疑问？有类似问题直接问程序猿