Oracleblockcorrupt Bad blocks

Oracleblockcorrupt Bad blocks

In general, oracle's Bad blocks can be divided into two scenarios: physical damage and logical damage. The physical damage is caused by storage and other reasons. As a result, oracle finds that the checksum of the block is inconsistent when processing the data block. Most of the logical damages are caused by oracle bugs or memory errors. By checking the checksum of data blocks, no problems are found, but these blocks are logically damaged.

Oracle uses two parameters to control the detection of physical and logical damages:

SQL> show parameter db_block_checkNAME     TYPE VALUE------------------------------------ ----------- ------------------------------db_block_checking     string FALSEdb_block_checksum     string TRUE

Db_block_checking checks the logical integrity and Correctness When the block changes. This parameter can avoid corruption of data blocks in the memory. The check of the block will affect the system performance from 1% to 10%. Depends on the settings of the db_block_checking parameter. Frequent DML results in more overhead for block checks. We recommend that you set it to full when the system load permits. This parameter is always in the "open" status for the SYSTEM tablespace, regardless of whether the parameter is set to OFF. The following is a reference for setting this parameter. FALSE and TRUE are used for compatibility with earlier versions.

Property Description

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

Parameter type String

SyntaxDB_BLOCK_CHECKING = {FALSE | OFF | LOW | MEDIUM | TRUE | FULL} --> OFF (= FALSE), FULL (= TRUE)

Defaultvalue FALSE

Modifiable ALTER SYSTEM

Basic No

Note that db_block_checking works only when data blocks are modified. For example

SQL> show parameter db_block_checkNAME TYPE VALUE types ------------- ---------------------------- db_block_checking string FALSEdb_block_checksum string TRUESQL> alter system flush buffer_cache; System altered. SQL> select * from scott. s2; // No problem during query T1 T2 ---------- -------------------- 51809 lllll51888 SC51574 PK_DEPT51573 DEPT1575EMP, 551576 PK_EMP51578 SALGRADE7 rows selected. SQL> update scott. s2 set t2 = 'aaa' where t1 = '000000'; // No problem with the modification: 1 row updated. SQL> commit; Commit complete. SQL> alter system flush buffer_cache; System altered. SQL> alter system set db_block_checking = true; System altered. SQL> select * from scott. s2; // query is no problem T1 T2 ---------- -------------------- 51809 aaaa51888 SC51574 PK_DEPT51573 DEPT1575EMP, 551576 PK_EMP51578 SALGRADE7 rows selected. SQL> update scott. s2 set t2 = 'bbbbb' where t1 = '000000'; // The update scott logic check is triggered during the modification. s2 set t2 = 'bbbbb' where t1 = '000000' * 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], [6113], [], [], [], [] SQL> select * from scott. s2; // after a logical error is found, oracle marks the block as a bad block. In this case, the data block cannot access select * from scott. s2 * ERROR at line 1: ORA-01578: ORACLE data block was upted (file #5, block #20) ORA-01110: data file 5: '/home/app/oraten/oradata/oraten/tbs201.dbf'

When db_block_checksum is used for writing data blocks of DBWn and direct loader to a disk, a check value is calculated based on all bytes in the block and written into the block header. When this parameter is set to typical and full, the verification comparison is re-calculated during the read and write operations. If they are different, the block is damaged. If it is set to FULL mode, the check value will be recalculated and written after the update/delete application statement-level change occurs. At the same time, before writing a log block, the verification value is also generated and written to the block header. This parameter mainly prevents errors in I/O hardware and I/O subsystems. If it is set to OFF, it is only valid for the system tablespace. The following is a reference for setting this parameter. FALSE and TRUE are used for compatibility with earlier versions.

Property Description

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

Parameter type String

Syntax DB_BLOCK_CHECKSUM = {OFF | FALSE | TYPICAL | TRUE | FULL} --> OFF (= FALSE), FULL (= TRUE)

Defaultvalue TYPICAL

Modifiable alter session, ALTER SYSTEM

Basic No

For performance differences, when the two block parameters are set to true, more CPU resources are required to generate the check value and verify the memory block. At the same time, this operation may increase the hold time of redo copy latch and cause competition of this latch. Regardless of the values of db_block_checking and db_block_checksum, the SYSTEM tablespace performs checking and checksum. You can use the implicit parameter _ db_always_check_system_ts to set it to FALSE, but to ensure the data security of the SYSTEM tablespace, we do not recommend that you set this implicit parameter value to FALSE.

We already know that oracle divides Bad blocks into physical and logical damages, so how does oracle mark a data block after it finds physical or logical damage so that subsequent operations know that the block is damaged?

For physical damage, oracle will not perform any processing. During subsequent processing, oracle will re-calculate the checksum. As long as the checksum is found to be inconsistent, it is considered that the block is physically damaged and a 01578 error is thrown.

If the logic is damaged, oracle will throw errors such as ora 600 and modify the tag bit in the data block for the first time when it checks the data block for logic. When the data block is accessed for the next time, oracle detection flag. if it finds that the flag is logically damaged, A ora 01578 error is thrown. When DBMS_REPAIR is used to modify a bad block, physical damage is not processed. If the logic is damaged, the flag of the data block is modified.

There are two locations in the data block, indicating that the data block is logically damaged (physical damage is not indicated), as shown below:

BBED> map /v File: /home/app/oraten/oradata/oraten/tbs201.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          <strong>ub1 seq_kcbh                            @14  </strong>        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[7]                                @142      ub1 freespace[7905]                        @156      ub1 rowdata[127]                           @8061    <strong> ub4 tailchk                                @8188</strong> 

BBED> set offset 14OFFSET         14BBED> dump /v count 12 File: /home/app/oraten/oradata/oraten/tbs201.dbf (5) Block: 20      Offsets:   14 to   25  Dba:0x01400014------------------------------------------------------- <strong>ff</strong>0425fe 00000100 0000fcca          l ..%......... <16 bytes per line>BBED> set offset 8188OFFSET         8188BBED> dump /v count 12 File: /home/app/oraten/oradata/oraten/tbs201.dbf (5) Block: 20      Offsets: 8188 to 8191  Dba:0x01400014------------------------------------------------------- <strong>ff</strong>060000                            l ....

Here, ff indicates the logical damage block of the data block bit. We can manually modify the bit 01 or 02 to cancel the logical damage mark.

The detection results of tools such as dbv are as follows:

Total Blocks Examined         : 1Total Blocks Processed (Data) : 1Total Blocks Failing   (Data) : 0Total Blocks Processed (Index): 0Total Blocks Failing   (Index): 0Total Blocks Empty            : 0Total Blocks Marked Corrupt   : 0Total Blocks Influx           : 0

For unlabeled logic damages, dbv and other tools are detected as follows:

Total Blocks Examined         : 1Total Blocks Processed (Data) : 1Total Blocks Failing   (Data) : 1Total Blocks Processed (Index): 0Total Blocks Failing   (Index): 0Total Blocks Empty            : 0Total Blocks Marked Corrupt   : 0Total Blocks Influx           : 0

In case of physical damage, dbv detection results are as follows:

DBVERIFY - Verification completeTotal Blocks Examined         : 1Total Blocks Processed (Data) : 0Total Blocks Failing   (Data) : 0Total Blocks Processed (Index): 0Total Blocks Failing   (Index): 0Total Blocks Empty            : 0Total Blocks Marked Corrupt   : 1Total Blocks Influx           : 0

For the dbms_repair package, if it is physically damaged, the marked_upt column of REPAIR_TABLE is always true, and the fix method does not work. If the logic is corrupted, the marked_upt column of repair_table is false. After the fix method is corrected, marked_corrupt is changed to true, and the flag of the data block has been modified.