[Oracle] uses bbed to restore update data

You can use bbed to retrieve not only deleted data but also updated data. Of course, the process is more complex than restoring deleted data.

The experiment process is as follows:

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

Aaam0waabaaaoucaab1 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:, 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

Use bbed for viewing below

BBED> set dba 1,60290

DBA 0x0040eb82 (4254594, 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>

Use bbed to retrieve Historical Values

Based on the dump value, the start point of the first record is calculated as 02c1 02064241 444c5939.

Based on this value, offset-3 returns the value of offset 8175.

8175-92 = 8083 1f93

BBED> p kdbr

Sb2 kdbr [0] @ 110 8064

Sb2 kdbr [1] @ 112 8074

-- Modify the row directory pointer position

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

<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 = 1, 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 -- transaction error prompt

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 Blocks upt: 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 c102041541444c59 390206d5 a3

<32 bytes per line>

BBED> x/rnc

Rowdata [19] @ 8175

-----------

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

Lock @ 8176: 0x00 -- the transaction lock ID before the update is 0, and the updated transaction lock ID is 2

Cols @ 8177: 2

Col 0 [2] @ 8178: 1

Col 1 [6] @ 8181: BADLY9

-- Change the transaction lock ID to 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>

-- Change the identifier of the transaction lock after the update to 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 = 1, 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 -- the system prompts that the block space usage is incorrect.

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 Blocks upt: 0

Total Blocks Influx: 0

Change all fsc of all itl to 0, and change the values of kdbh. kdbhavsp and kdbh. kdbhtosp to equal. Their values are equal to the dtl-used displayed in dbv verification. This case is 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 @ 367938

Ub1 ktbbhflg @ 38 0x03 (KTBFONFL)

Ub1 ktbbhfsl @ 39 0x00

Ub4 ktbbhfnx @ 400x00000000

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 kxidserver Load balancer @ 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 Zero x 2001 (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

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

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

02000200 791f321f 831e481e 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

<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

<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 = 1, 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 Blocks upt: 0

Total Blocks Influx: 0

In this case, the data block is verified

Now go back to the repository for verification:

SYS @ ORCL> alter system flush buffer_cache;

System altered.

SYS @ ORCL> select * from bbed_test;

X Y

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

1 BADLY9

2 JP

We can see that the data before UPDATE has been restored.

For the experiment process, refer to the following articles:

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