Build and repair logical and physical damaged blocks

Oracle data block damages can be divided into physical and logical damages. For details, refer to blog. csdn. netyidian815articledetails39990803. physical and logical damages occur in different scenarios, so the processing methods are different. In many cases, the same corrupt block processing method may have different results for physical and logical damages.

Oracle data block damage can be divided into physical damage and logical damage, according to The http://blog.csdn.net/yidian815/article/details/39990803 physical damage and logical damage occurs in different scenarios, so the way to deal with it is different. In many cases, the same corrupt block processing method may have different results for physical and logical damages.

Oracle data block damages can be divided into physical and logical damages. For more information, see

Http://blog.csdn.net/yidian815/article/details/39990803

Physical and logical damages occur in different scenarios, so the processing methods are different. In many cases, the same corrupt block processing method may have different results for physical and logical damages. It is easy to simulate physical damages, but it is not common to simulate logical damages.

Physical damage:

To simulate physical damage, you only need to manually edit the physical block (through UTRAEDIT \ BBED, etc.), such

SQL> select object_name,dbms_rowid.rowid_block_number(rowid) from t1 where rownum < 9;OBJECT_NAME       DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)------------------------------ ------------------------------------ICOL$ 20I_USER1  20CON$ 20UNDO$ 20C_COBJ#  20I_OBJ# 20PROXY_ROLE_DATA$ 20I_IND1 208 rows selected.BBED> set file 5FILE#          5BBED> set block 20BLOCK#         20BBED> finr /c PROXYBBED-00200: invalid keyword (finr)BBED> find /c PROXY File: /home/app/oraten/oradata/oraten/tbs101.dbf (5) Block: 20               Offsets: 4223 to 4734           Dba:0x01400014------------------------------------------------------------------------ 50524f58 595f524f 4c455f44 41544124 5f32ff02 c11c02c1 1c05494e 44455807  786e0414 09191d07 786e0414 09191d13 32303130 2d30342d 32303a30 383a3234  3a323805 56414c49 44014e01 4e014e2c 000c0749 5f555345 5223ff02 c10c02c1  0c05494e 44455807 786e0414 09191d07 786e0414 09191d13 32303130 2d30342d  32303a30 383a3234 3a323805 56414c49 44014e01 4e014e2c 000c0649 5f434f4c  31ff02c1 2e02c12e 05494e44 45580778 6e041409 191d0778 6e041409 191d1332  3031302d 30342d32 303a3038 3a32343a 32380556 414c4944 014e014e 014e2c00  0c07495f 43434f4c 32ff02c1 3802c138 05494e44 45580778 6e041409 191d0778  6e041409 191d1332 3031302d 30342d32 303a3038 3a32343a 32380556 414c4944  014e014e 014e2c00 0c07495f 43444546 33ff02c1 3502c135 05494e44 45580778  6e041409 191d0778 6e041409 191d1332 3031302d 30342d32 303a3038 3a32343a  32380556 414c4944 014e014e 014e2c00 0c07495f 49434f4c 31ff02c1 2902c129  05494e44 45580778 6e041409 191d0778 6e041409 191d1332 3031302d 30342d32  303a3038 3a32343a 32380556 414c4944 014e014e 014e2c00 0c07495f 43444546  31ff02c1 3302c133 05494e44 45580778 6e041409 191d0778 6e041409 191d1332  3031302d 30342d32 303a3038 3a32343a 32380556 414c4944 014e014e 014e2c00  <32 bytes per line>BBED> dump /v File: /home/app/oraten/oradata/oraten/tbs101.dbf (5) Block: 20      Offsets: 4223 to 4734  Dba:0x01400014------------------------------------------------------- 50524f58 595f524f 4c455f44 41544124 l PROXY_ROLE_DATA$ 5f32ff02 c11c02c1 1c05494e 44455807 l _2........INDEX. 786e0414 09191d07 786e0414 09191d13 l xn......xn...... 32303130 2d30342d 32303a30 383a3234 l 2010-04-20:08:24 3a323805 56414c49 44014e01 4e014e2c l :28.VALID.N.N.N, 000c0749 5f555345 5223ff02 c10c02c1 l ...I_USER#...... 0c05494e 44455807 786e0414 09191d07 l ..INDEX.xn...... 786e0414 09191d13 32303130 2d30342d l xn......2010-04- 32303a30 383a3234 3a323805 56414c49 l 20:08:24:28.VALI 44014e01 4e014e2c 000c0649 5f434f4c l D.N.N.N,...I_COL 31ff02c1 2e02c12e 05494e44 45580778 l 1........INDEX.x 6e041409 191d0778 6e041409 191d1332 l n......xn......2 3031302d 30342d32 303a3038 3a32343a l 010-04-20:08:24: 32380556 414c4944 014e014e 014e2c00 l 28.VALID.N.N.N,. 0c07495f 43434f4c 32ff02c1 3802c138 l ..I_CCOL2...8..8 05494e44 45580778 6e041409 191d0778 l .INDEX.xn......x 6e041409 191d1332 3031302d 30342d32 l n......2010-04-2 303a3038 3a32343a 32380556 414c4944 l 0:08:24:28.VALID 014e014e 014e2c00 0c07495f 43444546 l .N.N.N,...I_CDEF 33ff02c1 3502c135 05494e44 45580778 l 3...5..5.INDEX.x 6e041409 191d0778 6e041409 191d1332 l n......xn......2 3031302d 30342d32 303a3038 3a32343a l 010-04-20:08:24: 32380556 414c4944 014e014e 014e2c00 l 28.VALID.N.N.N,. 0c07495f 49434f4c 31ff02c1 2902c129 l ..I_ICOL1...)..) 05494e44 45580778 6e041409 191d0778 l .INDEX.xn......x 6e041409 191d1332 3031302d 30342d32 l n......2010-04-2 303a3038 3a32343a 32380556 414c4944 l 0:08:24:28.VALID 014e014e 014e2c00 0c07495f 43444546 l .N.N.N,...I_CDEF 31ff02c1 3302c133 05494e44 45580778 l 1...3..3.INDEX.x 6e041409 191d0778 6e041409 191d1332 l n......xn......2 3031302d 30342d32 303a3038 3a32343a l 010-04-20:08:24: 32380556 414c4944 014e014e 014e2c00 l 28.VALID.N.N.N,. <16 bytes per line>BBED> modify /c abcdeWarning: contents of previous BIFILE will be lost. Proceed? (Y/N) y File: /home/app/oraten/oradata/oraten/tbs101.dbf (5) Block: 20               Offsets: 4223 to 4734           Dba:0x01400014------------------------------------------------------------------------ 61626364 655f524f 4c455f44 41544124 5f32ff02 c11c02c1 1c05494e 44455807  786e0414 09191d07 786e0414 09191d13 32303130 2d30342d 32303a30 383a3234  3a323805 56414c49 44014e01 4e014e2c 000c0749 5f555345 5223ff02 c10c02c1  0c05494e 44455807 786e0414 09191d07 786e0414 09191d13 32303130 2d30342d  32303a30 383a3234 3a323805 56414c49 44014e01 4e014e2c 000c0649 5f434f4c  31ff02c1 2e02c12e 05494e44 45580778 6e041409 191d0778 6e041409 191d1332  3031302d 30342d32 303a3038 3a32343a 32380556 414c4944 014e014e 014e2c00  0c07495f 43434f4c 32ff02c1 3802c138 05494e44 45580778 6e041409 191d0778  6e041409 191d1332 3031302d 30342d32 303a3038 3a32343a 32380556 414c4944  014e014e 014e2c00 0c07495f 43444546 33ff02c1 3502c135 05494e44 45580778  6e041409 191d0778 6e041409 191d1332 3031302d 30342d32 303a3038 3a32343a  32380556 414c4944 014e014e 014e2c00 0c07495f 49434f4c 31ff02c1 2902c129  05494e44 45580778 6e041409 191d0778 6e041409 191d1332 3031302d 30342d32  303a3038 3a32343a 32380556 414c4944 014e014e 014e2c00 0c07495f 43444546  31ff02c1 3302c133 05494e44 45580778 6e041409 191d0778 6e041409 191d1332  3031302d 30342d32 303a3038 3a32343a 32380556 414c4944 014e014e 014e2c00  <32 bytes per line>SQL> alter system flush buffer_cache;System altered.SQL> select object_name,dbms_rowid.rowid_block_number(rowid) from t1 where rownum < 9;select object_name,dbms_rowid.rowid_block_number(rowid) from t1 where rownum < 9                                                             *ERROR at line 1:ORA-01578: ORACLE data block corrupted (file # 5, block # 20)ORA-01110: data file 5: '/home/app/oraten/oradata/oraten/tbs101.dbf'

The repair of physical damage is also very simple (The last modified records are not 7th.):

BBED> sum applyCheck value for File 5, Block 20:current = 0xb5e0, required = 0xb5e0SQL> alter system flush buffer_cache;System altered.SQL> select object_name,dbms_rowid.rowid_block_number(rowid) from t1 where object_name like '%abcde%';OBJECT_NAME       DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)------------------------------ ------------------------------------I_abcde_ROLE_DATA$_2 20

Simulation of logical damages is indeed troublesome, because only oracle knows the internal logical structure of data blocks. Today we try to simulate a logic error. We specify that oracle data is stored in data blocks in row units, and the first byte of each row has a special meaning. each bit of this byte represents a different meaning, as shown in figure

If we manually modify the storage content of this byte through bbed, oracle may not be able to identify this record well, resulting in a logical error.

BBED> set file 5FILE#          5BBED> set block 20BLOCK#         20BBED> map /v File: /home/app/oraten/oradata/oraten/tbs101.dbf (5) Block: 20                                    Dba:0x01400014------------------------------------------------------------ KTB Data Block (Table/Cluster) struct kcbh, 20 bytes                      @0           ub1 type_kcbh                           @0           ub1 frmt_kcbh                           @1           ub1 spare1_kcbh                         @2           ub1 spare2_kcbh                         @3           ub4 rdba_kcbh                           @4           ub4 bas_kcbh                            @8           ub2 wrp_kcbh                            @12          ub1 seq_kcbh                            @14          ub1 flg_kcbh                            @15          ub2 chkval_kcbh                         @16          ub2 spare3_kcbh                         @18       struct ktbbh, 96 bytes                     @20          ub1 ktbbhtyp                            @20          union ktbbhsid, 4 bytes                 @24          struct ktbbhcsc, 8 bytes                @28          b2 ktbbhict                             @36          ub1 ktbbhflg                            @38          ub1 ktbbhfsl                            @39          ub4 ktbbhfnx                            @40          struct ktbbhitl[3], 72 bytes            @44       struct kdbh, 14 bytes                      @124         ub1 kdbhflag                            @124         b1 kdbhntab                             @125         b2 kdbhnrow                             @126         sb2 kdbhfrre                            @128         sb2 kdbhfsbo                            @130         sb2 kdbhfseo                            @132         b2 kdbhavsp                             @134         b2 kdbhtosp                             @136      struct kdbt[1], 4 bytes                    @138         b2 kdbtoffs                             @138         b2 kdbtnrow                             @140      sb2 kdbr[96]                               @142      ub1 freespace[868]                         @334      ub1 rowdata[6986]                          @1202     ub4 tailchk                                @8188    BBED> p *kdbr[1]rowdata[6844]-------------ub1 rowdata[6844]                           @8046     0x2cBBED> set offset 8046OFFSET         8046BBED> x /rccnncttcccccrowdata[6844]                               @8046    -------------flag@8046: 0x2c (KDRHFL, KDRHFF, KDRHFH)lock@8047: 0x00cols@8048:   12col    0[7] @8049: I_USER1col    1[0] @8057: *NULL*col    2[2] @8058: 44 col    3[2] @8061: 44 col    4[5] @8064: INDEXcol    5[7] @8070: 2010-04-20 08:24:28 col    6[7] @8078: 2010-04-20 08:24:28 col   7[19] @8086: 2010-04-20:08:24:28col    8[5] @8106: VALIDcol    9[1] @8112: Ncol   10[1] @8114: Ncol   11[1] @8116: NBBED> set offset 8046OFFSET         8046BBED> modify /x ff File: /home/app/oraten/oradata/oraten/tbs101.dbf (5) Block: 20               Offsets: 8046 to 8191           Dba:0x01400014------------------------------------------------------------------------ ff000c07 495f5553 455231ff 02c12d02 c12d0549 4e444558 07786e04 1409191d  07786e04 1409191d 13323031 302d3034 2d32303a 30383a32 343a3238 0556414c  4944014e 014e014e 2c000c05 49434f4c 24ff02c1 1502c103 05544142 4c450778  6e041409 191d0778 6e041409 211a1332 3031302d 30342d32 303a3038 3a32343a  32380556 414c4944 014e014e 014e0106 5af8  <32 bytes per line>BBED> x /rccnncttcccccrowdata[6844]                               @8046    -------------flag@8046: 0xff (KDRHFN, KDRHFP, KDRHFL, KDRHFF, KDRHFD, KDRHFH, KDRHFC, KDRHFK)lock@8047: 0x00cols@8048:    0ckix@8049:    7BBED> sum applyCheck value for File 5, Block 20:current = 0xb533, required = 0xb533

In this case, query and edit t1 through sqlpus.

SQL> alter system flush buffer_cache;System altered.SQL> select count(*) from t1;  COUNT(*)----------     22944SQL> select sum(length(object_name)) from t1;SUM(LENGTH(OBJECT_NAME))------------------------  531915SQL> update t1 set object_name = rownum;update t1 set object_name = rownum       *ERROR at line 1:ORA-00607: Internal error occurred while making a change to a data blockORA-00600: internal error code, arguments: [kddummy_blkchk], [5], [20], [6251],[], [], [], []SQL> rollback;Rollback complete.SQL> update t1 set object_name = rownum;update t1 set object_name = rownum       *ERROR at line 1:ORA-01578: ORACLE data block corrupted (file # 5, block # 20)ORA-01110: data file 5: '/home/app/oraten/oradata/oraten/tbs101.dbf'

It can be seen that oracle believes that a logic error has occurred. Check the mark bit of block20 and find that the mark of the logic corruption (14-byte Modified ff, tailchk contains ff) has been marked.

BBED> set file 5FILE#          5BBED> set block 20BLOCK#         20BBED> set offset 14OFFSET         14BBED> dump /v count 12 File: /home/app/oraten/oradata/oraten/tbs101.dbf (5) Block: 20      Offsets:   14 to   25  Dba:0x01400014------------------------------------------------------- ff0438b5 00000100 00001ecb          l ..8......... <16 bytes per line>BBED> set offset 8188OFFSET         8188BBED> dump /v  File: /home/app/oraten/oradata/oraten/tbs101.dbf (5) Block: 20      Offsets: 8188 to 8191  Dba:0x01400014------------------------------------------------------- ff060000                            l .... <16 bytes per line>

However, the repair of logical Corruption usually ignores the damaged block by using the dbms_repair package.

SQL> exec dbms_repair.skip_corrupt_blocks('SYS','T1');PL/SQL procedure successfully completed.SQL> update t1 set object_name=rownum;22849 rows updated.SQL> commit;Commit complete.