Oracle bbed Practical Example-----Modify the data content, restore the delete rows

Bbed can be modified in the DB open state, but it is recommended to shutdown DB before any modifications are made. This avoids the checkpoint process rewriting bbed's modifications to the block. Also avoid Oracle reading block or declaring block as corrupt before the bbed modification is complete.

I. Example: Modifying Data content

1.1 Connecting Bbed

[Email protected] ~]$ bbed parfile=/u01/app/oracle/bbed/bbed.parpassword:bbed:release 2.0.0.0.0-limited Production o n Thu Jan 17:27:30 2015Copyright (c) 1982, 2005, Oracle.  

1.2 Viewing what you want to modify

Sql> Conn tv/tvconnected.sql> SELECT * from t01 where name= ' zz '; No. rows selectedsql> select * from t01 where name = ' ww ';        ID NAME------------------------------------------         1 wwsql> Note: The bbed modification is only a replacement for the original location content

1.3 The information corresponding to block is as follows

Sql> Select  2     rowid,  3     dbms_rowid.rowid_relative_fno (ROWID) rel_fno,--When the number of database files is less than 1024, rel_ The FNO and file IDs are the same  4     dbms_rowid.rowid_block_number (ROWID) Blockno,  5     dbms_rowid.rowid_row_number ( ROWID) Rowno  6 from  t01  7  where name= ' ww '; ROWID                 rel_fno    blockno      ROWNO------------------------------------------------AAAMFRAAFAAAAY0AFM          5       3252        

1.4 Setting the current file and data block

Bbed> set DBA 5,3252 offset 0        dba             0x01400cb4 (20974772 5,3252)        offset          0 or bbed> set file 5        file#           5bbed> Set block 3252        block#          3252bbed>

1.5 Find the keyword WW, determine its offset in the block

Bbed> find/c ww File:/u01/app/oracle/oradata/orcl/datafile/test_01.dbf (5) block:3252 offsets:5530 to 6 041 dba:0x01400cb4------------------------------------------------------------------------77772c00 02018002 797 92C00 02018002 79792c00 02018002 79792c00 02018002 79792c00 02018002 79792c00 02018002 79792c00 02018002 79792c00 0201800 2 79792c00 02018002 79792c00 02018002 79792c00 02018002 79792c00 02018002 79792c00 02018002 79792c00 02018002 79792c00 0 2018002 79792c00 02018002 79792c00 02018002 79792c00 02018002 79792c00 02018002 79792c00 02018002 79792c00 02018002 7979   2C00 02018002 79792c00 02018002 79792c00 02018002 79792c00 02018002 79792c00 02018002 79792c00 02018002 79792c00 02018002 79792C00 02018002 79792c00 02018002 79792c00 02018002 79792c00 02018002 79792c00 02018002 79792c00 02018002 79792c00 02 018002 79792c00 02018002 79792c00 02018002 79792c00 02018002 79792c00 02018002 79792c00 02018002 79792c00 02018002 79792 C00 02018002 79792c00 02018002 79792c00 02018002 79792c00 02018002 79792c00 02018002 79792c00 02018002 79792c00 02018002 79792c00 02018002 79792  C00 02018002 79792c00 02018002 79792c00 02018002 79792c00 02018002 79792c00 02018002 79792c00 02018002 79792c00 02018002 79792C00 02018002 79792c00 02018002 79792c00 02018002 79792c00 02018002 79792c00 02018002 79792c00 02018002 79792c00 020   18002 79792c00 02018002< +bytes per line>bbed>

Contents of the 1.6 Dump data block

bbed> dump/v dba 5,3252 offset 5530 Count of File:/U01/APP/ORACLE/ORADATA/ORCL/DATAFILE/TEST_01.DBF (5) Block:3 252 offsets:5530 to 5657 dba:0x01400cb4-------------------------------------------------------77772c00 02018002 7979 2C00 02018002 l ww,..... yy,..... 79792C00 02018002 79792c00 02018002 l yy,..... yy,..... 79792C00 02018002 79792c00 02018002 l yy,..... yy,..... 79792C00 02018002 79792c00 02018002 l yy,..... yy,..... 79792C00 02018002 79792c00 02018002 l yy,..... yy,..... 79792C00 02018002 79792c00 02018002 l yy,..... yy,..... 79792C00 02018002 79792c00 02018002 l yy,..... yy,..... 79792C00 02018002 79792c00 02018002 l yy,..... yy,.....< -bytes per line>bbed> Note the offsets:5530 to 5657 here, which refers to an address in this line. Where the offset of the first W is 5530 that is the dump out of the first 77 of the second W offset is 5531 that is the dump out of the second

1.7 Modify block to convert WW to XZ

bbed> modify/c xz dba 5,3252 offset 5530warning:contents of previous bifile would be lost. Proceed?           (y/n) Y File:/u01/app/oracle/oradata/orcl/datafile/test_01.dbf (5) block:3252 offsets:5530 to 5657 DBA:0X01400CB4------------------------------------------------------------------------787a2c00 02018002 79792c00 02018002 79792c00 02018002 79792c00 02018002 79792c00 02018002 79792c00 02018002 79792c00 02018002 79792c00 02018002 797 92C00 02018002 79792c00 02018002 79792c00 02018002 79792c00 02018002 79792c00 02018002 79792c00 02018002 79792c00 0201800 2 79792c00 02018002< +bytes per line>--dump modified data bbed> dump/v DBA 5,3252 offset 5530 Count of File:/u01/app/oracle/oradata/orcl/datafile/test_01. DBF (5) block:3252 offsets:5530 to 5657 dba:0x01400cb4-------------------------------------------------------787a2c XX 02018002 79792c00 02018002 l XZ,..... yy,..... 79792C00 02018002 79792c00 02018002 l yy,..... yy,..... 79792C00 02018002 79792c00 02018002 l yy,..... yy,..... 79792C00 02018002 79792c00 02018002 l yy,..... yy,..... 79792C00 02018002 79792c00 02018002 l yy,..... yy,..... 79792C00 02018002 79792c00 02018002 l yy,..... yy,..... 79792C00 02018002 79792c00 02018002 l yy,..... yy,..... 79792C00 02018002 79792c00 02018002 l yy,..... yy,.....< -bytes per line>bbed>

1.8 Application Changes

bbed> sum dba 5,3252 applycheck value for File 5, Block 3252:current = 0x5730, required = 0x5730bbed>

1.9 Back to the SQL window to refresh the cache and view the modified data

Sql> alter system flush Buffer_cache; System altered. Sql> SELECT * from tv.t01 where name= ' ww ', no rows selectedsql> select * from tv.t01 where name= ' XZ ';        ID NAME------------------------------------------         

Note: Because it was an experiment, the database was not closed when the data was modified

Two. Example: Restore rows for delete

When the row is deleted, the data is not actually removed, but the row is marked as delete, and the corresponding space is counted as free. Row's status exists in each row's row header. The row Header contains row Flag,lock Byte (ITL) and column Count. ROW Header Structure:

name length Comment Row Flag 1 bytes Row status flag Lock Byte (ITL) 1 bytes Corresponds to ITL's lck to indicate whether this line is locked or not. column Count 1 bytes With ITL's lck should indicate whether this line is locked

where row flag occupies 1 bytes and is saved with bitmask. Bitmask's explanation is as follows:

Cluster Key K 128 Cluster Table Member C 64 Head of Row piece H 32 Deleted D 16 First Data piece F 8 Last data piece L 4 1st Column continues from previous piece P 2 Last column continues in next piece N 1

2.1 Dump a block and look at a row Flag

Sql> conn/as sysdbaconnected.sql> alter system dump DATAFILE 5 block 3252; System altered. sql> oradebug setmypidstatement processed. Sql> Oradebug tracefile_name/u01/app/oracle/admin/orcl/udump/orcl_ora_6106.trcsql>!more/u01/app/oracle/ ADMIN/ORCL/UDUMP/ORCL_ORA_6106.TRC---Information about row is as follows Block_row_dump:tab 0, row 0, @0x1db0tl:8 fb:--h-fl--lb:0x0  cc: 2col  0: [1]  80col  1: [2]  79tab 0, row 1, @0x1db8tl:8 fb:--h-fl--lb:0x0  cc:2col  0: [1]
    80col  1: [2]  79tab 0, Row 2, @0x1dc0tl:8 fb:--h-fl--lb:0x0  cc:2col  0: [1]  80col  1: [2]  79tab 0, Row 3, @0x1dc8................tab 0, row 332, @0x152f          --No. 332 data tl:9 fb:--h-fl--lb:0x2
    cc:2col  0: [2]  C1 02col  1: [2]  7a              --The name value of the No. 332 data is 7a to ' XZ ' end_of_block_dump

Note Here's FB:--H-FL--。 It has 8 options, each corresponding to the bitmask.

If a row is not deleted, then its flag is expressed as:--H-FL--. The letters here represent the first letter of the property, respectively. Its corresponding value: + 8 + 4 =44 or 0x2c

If a row is deleted, its flag is expressed as:--HDFL--. The deleted in bitmask is set to 16. Its corresponding value: + + + 8 + 4 = or 0x3c

2.2 Validation: Deleting data

Sql> Conn tv/tvconnected.sql> Delete from t01 where name = ' XZ '; 1 row deleted. Sql> commit; Commit complete. Sql>--dump data block View flag tag: tab 0, Row 332, @0x152f      --No. 332 data tl:2 fb:--hdfl--lb:0x1--flag to   :--hdfl--end_of_b Lock_dump

2.3 Now use bbed to retrieve the deleted row 1 content

1. No. 332 data still exists in the data block
2. Find the row header location of the No. 332 data
3. Change the flag value in the row header

1. Find the location of a row record

bbed> Set file 5        file#           5bbed> Set block 3252        block#          3252bbed> find/c xz File:/u01/app/oracle/oradata/orcl/datafile/ TEST_01.DBF (5) block:3252             offsets:5530 to 6041           dba:0 X01400CB4------------------------------------------------------------------------787a2c00 02018002 79792c00 02018002 79792c00 02018002 79792c00 02018002  79792c00 02018002 79792c00 02018002 79792c00 02018002 79792c00 02018002< C8/>79792C00 02018002 79792c00 02018002 79792c00 02018002 79792c00 02018002  <   Bytes perline>bbed> ' xz ' offset within block is 5530

2. View the row directory

bbed> print KDBRSB2 kdbr[0]                                 @118      7600sb2 kdbr[1]                                 @120      7608sb2 kdbr[2]                                 @122      7616 ... SB2 kdbr[332]                               @782      5423

3. View the starting position of the No. 332 record:

bbed> print *kdbr[332]            ---Don't forget ' * '        rowdata[0]----------ub1 rowdata[0]                              @5523     0x3cbbed> The No. 332 record has an offset of 5523 in the block, and the ' XZ ' offset within the block is 5530

4. Dump the No. 332 record

bbed> dump/v dba 5,3252 offset 5523 count File:/u01/app/oracle/oradata/orcl/datafile/test_01.dbf (5) block:32    offsets:5523 to 5554  <.... XZ,..... y 792c0002 01800279 792c0002 01800279 l y, ... yy,..... y <16 bytes perline>bbed>3c   5523  --row Flag01   5524  -- Lock BYTE02   5525  --Number of columns   5526  --column length C1   552702   5528  --Column length   552978   5530  --' x ' 7a   5531  

5. Change the value of row flag to change 3c to 2c

bbed> modify/x 2c offset 5523warning:contents of previous bifile would be lost. Proceed? (y/n) Y File:/u01/app/oracle/oradata/orcl/datafile/test_01.dbf (5) block:3252             offsets:5523 to 5554           Dba:0 X01400CB4------------------------------------------------------------------------2c010202 c1020278 7a2c0002 01800279 792c0002 01800279 792c0002 01800279  <bytes perline>

5. Verify the results

--Clear cache sql> alter system flush Buffer_cache; System altered. Sql> Conn tv/tvconnected.sql> SELECT * from t01 where name= ' XZ ';        The ID NAME------------------------------------------         1 xzsql>--delete data has been recovered.

Oracle bbed Practical Example-----Modify the data content, restore the delete rows