Oracle Block corrupt bad block

On the whole, Oracle's bad blocks can be divided into two scenarios: physical damage 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 by checking the checksum of the data blocks, there is no problem, 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 a block occurs regardless of any change.

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 a lot of other overhead for block checking.

It is recommended to set to full when the system load is agreed.

The parameter is always in the open state for the system table space. Regardless of whether the parameter is set to OFF. The following is a set reference for this parameter .

False and true are for compatibility with the old version number.

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 only when changes are made to a block of data. Db_block_checking will only take effect. 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 ';//change 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 ';//change triggered logical check 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 was found, Oracle marked the block as a bad block. The data block cannot access the select * from Scott.s2*error at line 1:ora-01578:oracle data block corrupted (file # 5, Block #) Ora-01110:d ATA file 5: '/HOME/APP/ORATEN/ORADATA/ORATEN/TBS201.DBF '

Db_block_checksum is used when dbwn and direct loader data blocks are written to disk. Calculates a checksum based on all the bytes in the block and writes it to the size. When this parameter is set to typical and full, the checksum is computed once again when the checksum is written in the read-in time. Suppose the difference is that the block is damaged.

If set to full mode , the checksum is computed and written again after a change based on the Update/delete application statement level occurs. Same time for log blocks. Before the write. The same will produce the calibration values and write to the size.

This is mainly to prevent IO hardware and IO subsystem errors. Assume that set to OFF is only valid for system table space.

The following is a set reference for this parameter. false and True are for compatibility with the old version number.

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 two block parameters to true, many additional CPU resources are required to generate the checksum and to validate the memory block.

At the same time. This operation easy causes redo copy latch to be added and to cause this latch competition.

Regardless of the value of the two parameters of db_block_checking and Db_block_checksum, the system tablespace will do checking and checksum, which can be _db_always_check_ by means of implicit references System_ts is set to false, but for system table space data security. Setting this implied value to False is not recommended.

We already know. Oracle divides the bad blocks into physical and logical corruptions, so what happens when Oracle discovers a physical or logical corruption that blocks the block so that subsequent operations know that the block is a damaged block?

for physical damage. Oracle does not do whatever it does, and Oracle computes checksum again when it does so, just to find that the checksum inconsistency finds the block physically corrupted 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 change the marker bit in the data block, the Oracle detects the flag bit the next time the data block is visited. Suppose the discovery flag bit is set to logical corruption. The Ora 01578 error is thrown. When using Dbms_repair to make changes to a bad block, it is assumed that physical damage is not treated as a matter of any kind. Assuming that the logic is corrupted, change the data block's flag bit.

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

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 marks the block of data block logic damage, and we can manually change the bits 01 or 02 to remove the logical damage flag.

For data blocks that have been marked as logically corrupted, the results of the detection of tools such as DBV include the following:

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-labeled logic damage, tools such as DBV test results such as the following:

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. DBV test results such as the following

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, the repair_table marked_corrupt column is always true if physical corruption is assumed. The fix method does not take effect. For logical corruption, the first repair_table Marked_corrupt is listed as the False,fix method after correction. Marked_corrupt changes to True when the flag bit of the data block has changed.

Oracle Block corrupt bad block