Oracle Block corrupt bad block

In general, Oracle's bad blocks can be divided into two scenarios: physical and logical corruption. Physical damage is caused by storage, which causes Oracle to find that the block's checksum is inconsistent when it processes data blocks. The logical damage is due to Oracle bugs or memory errors, and there is no problem with detecting blocks of checksum, but logically these blocks have been corrupted.

Oracle controls the detection of physical and logical damage with two parameters:

Sql> Show parameter Db_block_checkname     TYPE VALUE---------------------------------------------------------- -------------------db_block_checking     string falsedb_block_checksum     string TRUE

Db_block_checking is a logical completeness and correctness check when the block changes. This parameter avoids corruption of the data blocks in memory. Block checks will have a 1% to 10% performance impact on the system. Depends on the setting of the db_block_checking parameter. Frequent DML will result in more overhead for block checking. It is recommended to set to full when the system load is allowed. This parameter is always in the open state for the system table space, regardless of whether the parameter is set to OFF. The following is the setting reference for this parameter . False and true are for compatibility with older versions.

Property Description

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

Parameter type String

Syntax db_block_checking = {false| Off| Low | MEDIUM | true| Full}-->off (=false), Full (=true)

DefaultValue FALSE

Modifiable ALTER SYSTEM

Basic No

One thing to note here is that db_block_checking only works when changes are being taken to a block of data. Such as

Sql> Show parameter Db_block_checkname TYPE VALUE--------------------------------------------------------------- --------------db_block_checking string Falsedb_block_checksum string truesql> alter system flush BUFFER_CACHE;S Ystem altered.   Sql> SELECT * from scott.s2;//query no problem T1 T2------------------------------51809 lllll51888 SC51574 pk_dept51573 Dept1575emp, 551576 pk_emp51578 SALGRADE7 rows selected. sql> Update scott.s2 set t2= ' aaaa ' where t1= ' 51809 ';//modification No problem 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= ' bbbb ' where t1 = ' 51809 ';//modification triggered Logic detect update scott.s2 set t2= ' bbbb ' where t1 = ' 51809 ' *error at line 1: ora-00607:internal error occurred while making a change to a data blockora-00600:internal error code, arguments: [kddum  MY_BLKCHK], [5], [[], [6113],[], [], [], []sql> SELECT * from scott.s2;//a logic error is found, Oracle blocks the block as a bad block, when the block cannot access the select * from Scott.s2*error at line 1:ora-01578:oracle data block corrupted (file # 5, Block #) Ora-01110:data file 5: '/home/app/ ORATEN/ORADATA/ORATEN/TBS201.DBF '

Db_block_checksum is used for DBWN and direct loader data blocks when writing to disk, a checksum is computed based on all the bytes in the block and written to the size. When this parameter is set to typical and full, the checksum is recalculated when the checksum is read-out, if the difference is considered a block corruption. If set to full mode , the checksum value is recalculated and written after a change based on the Update/delete application statement level occurs. Also, for log blocks, the test values are produced and written to the block size before writing. This parameter is primarily to prevent errors in the IO hardware and IO subsystems. If set to OFF, only the system table space is valid. The following is the setting reference for this parameter. false and true are for compatibility with older 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 setting the two block parameter to true, more CPU resources will be required to generate the checksum and to validate the memory block. At the same time, this operation is apt to cause the redo copy latch to increase the holding time and cause this latch competition. The system tablespace does checking and checksum, regardless of the values of the two parameters, Db_block_checking and db_block_checksum, which can be _db_always_check_ by means of an implicit argument System_ts is set to false, but for system table space data security, it is not recommended to set this implied parameter value to False.

We already know that Oracle divides the bad blocks into physical and logical corruptions, so how can oracle identify blocks of data after physical or logical corruption so that subsequent operations know that the block is a damaged block?

For physical damage, Oracle does not do any processing, and Oracle recalculates the checksum when it is processed, as long as the checksum inconsistency is found to be physically corrupt and throws a 01578 error.

For logical corruption, when Oracle first logically detects a block of data, it throws an error such as Ora 600 and modifies the marker bit in the data block, and when the next time it accesses the data block, the Oracle detection flag bit, or the Ora 01578 error if the flag bit is found to be logically corrupted. When a bad block is modified using Dbms_repair, if the physical damage is not handled at all, if the logic is corrupted, the data block's flag bit is modified.

There are two locations in the data block that indicate that the data block is logically corrupted (no physical damage), as follows:

Bbed> map/v File:/home/app/oraten/oradata/oraten/tbs201.dbf (5) block:20 dba:0x01 400014------------------------------------------------------------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, bytes @20 ub1 Ktbbhtyp @20 UnionKtbbhsid, 4 bytes @24 struct KTBBHCSC, 8 bytes @28 B2 ktbbhict                            @36 ub1 ktbbhflg @38 ub1 KTBBHFSL @39 ub4 ktbbhfnx @40 struct ktbbhitl[3], a. Bytes @44 Stru                             CT kdbh, 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 the File:/home/app/oraten/oradata/oraten/tbs201.dbf (5) Block:2 0      offsets:  dba:0x01400014-------------------------------------------------------< Strong>ff</strong>0425fe 00000100 0000fcca          L.. % ...... <16 bytes per line>bbed> set offset 8188OFFSET         8188bbed> dump/v Count of File:/home/app/orate N/ORADATA/ORATEN/TBS201.DBF (5) block:20      offsets:8188 to 8191  dba:0 x01400014-------------------------------------------------------<strong>ff</strong>060000                            L ....

The FF here is a block of logical damage to the data block, and we can manually modify bits 01 or 02 to remove the logical damage flag.

For data blocks that have been marked as logically corrupted, the test results for tools such as DBV are as follows:

Total Blocks examined         : 1Total Blocks processed (data): 1Total Blocks failing   (data): 0Total Blocks processed (I Ndex): 0Total Blocks failing   (Index): 0Total Blocks Empty            : 0Total Blocks Marked corrupt   : 0Total Blocks influ X           : 0

For non-marked logical damage, DBV tools such as the test results are as follows:

Total Blocks examined         : 1Total Blocks processed (data): 1Total Blocks failing   (data): 1Total Blocks processed (I Ndex): 0Total Blocks failing   (Index): 0Total Blocks Empty            : 0Total Blocks Marked corrupt   : 0Total Blocks influ X           : 0

For physical damage, the DBV test results are as follows

Dbverify-verification completetotal Blocks examined         : 1Total Blocks processed (Data): 0Total Blocks failing   (Da TA): 0Total Blocks processed (index): 0Total Blocks failing   (index): 0Total Blocks Empty            : 0Total Blocks Marked C Orrupt   : 1Total Blocks influx           : 0

For Dbms_repair packages, repair_table marked_corrupt columns are always true,fix methods and do not work if they are physically damaged. In the case of logical corruption, the repair_table Marked_corrupt is first listed as False,fix method corrected, Marked_corrupt modified to True, at which point the data block's flag bit has been modified.

Oracle Block corrupt bad block