Using BBED to recover the value before UPDATE modification
Please indicate the source of the reprint: http://blog.csdn.net/guoyjoe/article/details/30615151
The experimental process is as follows:
1. Create the table guo_test1
Gyj@PROD > create table guoyJoe_t1 (id int,name varchar2 (10)); Table created. Gyj@PROD > insert into guoyJoe_t1 values (1 row created). Gyj@PROD > insert into guoyJoe_t1 values (1 row created). Gyj@PROD > COMMIT; Commit complete.
2. Find out the DBA of this line of records
Gyj@PROD > select name,dbms_rowid.rowid_relative_fno (rowid) file#,dbms_rowid.rowid_block_number (rowid) block#,dbms_rowid.rowid_row_number (rowid) row# from guoyJoe_t1 NAME FILE# BLOCK# ROW#-guoyJoe 5 20877 0 tom 5 20877 1
3. Brush the data cache
Gyj@PROD > alter system flush buffer_cache; System altered.
4. Use BBED to find this row of records (value before modification: guoyJoe)
BBED > BBED > set file 5 block 20877 FILE# 5 BLOCK# 20877
-- observe the wardrobe
BBED > p kdbr
Sb2 kdbr [0] @ 118 8074
Sb2 kdbr [1] @ 120 8064
BBED > p * kdbr [0]
Rowdata [10]
-
Ub1 rowdata [10] @ 8174 0x2c
-- observing affairs
BBBED > x / rnc
Rowdata [10] @ 8174
-
Flag@8174: 0x2c (KDRHFL, KDRHFF, KDRHFH)
Lock@8175: 0x01
Cols@8176: 2
Col 0 [2] @ 8177: 1
Col 1 [7] @ 8180: guoyJoe
-observation space
BBED > p kdbhavsp
Sb2 kdbhavsp @ 110 8042
BBED > p kdbhtosp
Sb2 kdbhtosp @ 112 8042
5. A UPDATE statement occurs and the original value guoyJoe is changed to oracledba.
Gyj@PROD > update guoyJoe_t1 set name='oracledba' where name='guoyJoe'; 1 row updated. Gyj@PROD > commit; Commit complete
6. Brush the data cache
Gyj@PROD > alter system flush buffer_cache; System altered.
7. Use BBED to find the modified record (on the modified value: oracledba)
BBED > set file 5 block 20877 FILE# 5 BLOCK# 20877
-- observe the wardrobe
BBED > p kdbr
Sb2 kdbr [0] @ 118 8048
Sb2 kdbr [1] @ 120 8064
BBED > p * kdbr [0]
Rowdata [0]
-
Ub1 rowdata [0] @ 8148 0x2c
-- observing affairs
BBED > x / rnc
Rowdata [0] @ 8148
-
Flag@8148: 0x2c (KDRHFL, KDRHFF, KDRHFH)
Lock@8149: 0x01
Cols@8150: 2
Col 0 [2] @ 8151: 1
Col 1 [9] @ 8154: oracledba
-observation space
BBED > p kdbhavsp
Sb2 kdbhavsp @ 110 8040
BBED > p kdbhtosp
Sb2 kdbhtosp @ 112 8040
8. Compare the data before and after revision
BBED > d / v offset 8174 count 16 File: / u01/app/oracle/oradata/PROD/tp01.dbf (5) Block: 20877 Offsets: 8174 to 8189 Dba:0x0140518d-2c000202 c1020767 756f794a 6f650106 l,.. á.. guoyJoe.. BBED > d / v offset 8148 count 16 File: / u01/app/oracle/oradata/PROD/tp01.dbf (5) Block: 20877 Offsets: 8148 to 8163 Dba:0x0140518d-2c010202 c102096f 7261636c 65646261 l,.. oracledba
9. Start recovery
(1) modify row directory pointer position (@ 118)
-- before revision
BBED > p kdbr
Sb2 kdbr [0] @ 118 8074
-after revision
BBED > p kdbr
Sb2 kdbr [0] @ 118 8048
Gyj@PROD > select to_char ('8074') from dual
TO_CHAR ('8074'
-
1f8a
Gyj@PROD > select to_char ('8048) from dual
TO_CHAR ('8048'
-
1f70
BBED > d / v offset 118 count 16
File: / u01/app/oracle/oradata/PROD/tp01.dbf (5)
Block: 20877 Offsets: 118 to 133 Dba:0x0140518d
701f801f 00000000 00000000 00000000 l p.
BBED > modify / x 8a offset 118
Warning: contents of previous BIFILE will be lost. Proceed? (Ybig N) Y
File: / u01/app/oracle/oradata/PROD/tp01.dbf (5)
Block: 20877 Offsets: 118 to 133 Dba:0x0140518d
8a1f801f 00000000 00000000 00000000
BBED > sum apply
Check value for File 5, Block 20877:
Current = 0x8a00, required = 0x8a00
BBED > verify
DBVERIFY-Verification starting
FILE = / u01/app/oracle/oradata/PROD/tp01.dbf
BLOCK = 20877
Block Checking: DBA = 20992397, Block Type = KTB-managed data block
Data header at 0x7f76ff66e264
Kdbchk: xaction header lock count mismatch-transaction lock error
Trans=1 ilk=1 nlo=0
Block 20877 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
(2) next, modify the transaction lock
-change the transaction lock mark of the previous update value from 00 to 01
BBBED > x / rnc
Rowdata [10] @ 8174
-
Flag@8174: 0x2c (KDRHFL, KDRHFF, KDRHFH)
Lock@8175: 0x01
Cols@8176: 2
BBED > d / v offset 8175 count 16
File: / u01/app/oracle/oradata/PROD/tp01.dbf (5)
Block: 20877 Offsets: 8175 to 8190 Dba:0x0140518d
000202c1 02076775 6f794a6f 6501066f l... á.. guoyJoe..o
BBED > modify / x 01 offset 8175
File: / u01/app/oracle/oradata/PROD/tp01.dbf (5)
Block: 20861 Offsets: 8175 to 8190 Dba:0x0140517d
010202c1 02076775 6f794a6f 650106ed
-- change the transaction lock ID 01 of the updated value to 00
BBED > x / rnc
Rowdata [0] @ 8148
-
Flag@8148: 0x2c (KDRHFL, KDRHFF, KDRHFH)
Lock@8149: 0x01
Cols@8150: 2
BBED > d / v offset 8149 count 16
File: / u01/app/oracle/oradata/PROD/tp01.dbf (5)
Block: 20877 Offsets: 8149 to 8164 Dba:0x0140518d
010202c1 02096f72 61636c65 6462612cl... á.. oracledba
BBED > modify / x 00 offset 8149
File: / u01/app/oracle/oradata/PROD/tp01.dbf (5)
Block: 20861 Offsets: 8159 to 8174 Dba:0x0140517d
000202c1 02096f72 61636c65 6462612c
BBED > sum apply
Check value for File 5, Block 20877:
Current = 0x8a00, required = 0x8a00
BBED > verify
DBVERIFY-Verification starting
FILE = / u01/app/oracle/oradata/PROD/tp01.dbf
BLOCK = 20877
Block Checking: DBA = 20992397, Block Type = KTB-managed data block
Data header at 0x7cb464
Kdbchk: the amount of space used is not equal to block size
Used=46 fsc=0 avsp=8040 dtl=8088
-prompt for incorrect use of data block space (dtl-used=kdbhavsp=kdbhtosp)
8088-468042 is related to avsp=8040 2, which means I need to recover to 8042
(3) modify the space to change the space to 6a1f, mainly dtl-used=kdbhavsp
Gyj@PROD > select to_char ('8042') from dual
TO_CHAR ('8042'
-
1f6a
BBED > modify / x 6a1f offset 110
File: / u01/app/oracle/oradata/PROD/tp01.dbf (5)
Block: 20877 Offsets: 110 to 125 Dba:0x0140518d
6a1f681f 00000200 8a1f801f 00000000
BBED > modify / x 6a1f offset 112
File: / u01/app/oracle/oradata/PROD/tp01.dbf (5)
Block: 20877 Offsets: 112 to 127 Dba:0x0140518d
6a1f0000 02008a1f 801f0000 00000000
BBED > sum apply
Check value for File 5, Block 20877:
Current = 0x8a00, required = 0x8a00
BBED > verify
DBVERIFY-Verification starting
FILE = / u01/app/oracle/oradata/PROD/tp01.dbf
BLOCK = 20877
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
10. Restore successfully (oracledba--- > guoyJoe)
Gyj@PROD > alter system flush buffer_cache; System altered. Gyj@PROD > select name,dbms_rowid.rowid_relative_fno (rowid) file#,dbms_rowid.rowid_block_number (rowid) block#,dbms_rowid.rowid_row_number (rowid) row# from guoyJoe_t1 where id=1 NAME FILE# BLOCK# ROW#-guoyJoe 5 20877 0 tom 5 20877 1
In fact, it is not difficult to restore the value before UPDATE with BBED, mainly to understand the internal principle and block format.
The summary is as follows:
(1) modify the header
(2) modify transaction
(3) modify the space
