watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=

          subject         part

stay Oracle in , What is a bad block ? What kinds of bad blocks can be divided ?


     




          Answer section          


Oracle Bad block of data file can be divided into physical bad block (Physical Block Corruptions) And bad logic (Logical Block Corruptions). Bad blocks usually pass through Oracle Of ORA-01578 The error report came out , The detailed damage description will be printed out in the alarm log ,“oerr ora” The explanation for this error is as follows :

1[[email protected] ~]# oerr ora 1578
201578, 00000, "ORACLE data block corrupted (file # %s, block # %s)"
3// *Cause:  The data block indicated was corrupted, mostly due to software
4//          errors.
5// *Action: Try to restore the segment containing the block indicated. This
6//          may involve dropping the segment and recreating it. If there
7//          is a trace file, report the errors in it to your ORACLE
8//          representative.
     

The possible errors are as follows :

1ORA-01578: ORACLE data block corrupted (file # 11, block # 110)
2ORA-01110: data file 6: '/u01/app/oracle/oradata/orcl/tbs01.dbf'
     

In the example above , Absolute document number (AFN,Absolute File Number) by 11, Block number (Block Number) by 110.

Use the following SQL You can get the relative file number RFN(RFN,Relative File Number):

1SELECT DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE(&&RDBA) RFN,
2       DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK(&&RDBA) BL
3FROM DUAL;
     

among ,RDBA(Relative Data Block Address) Represents the relative block address .

Use the following SQL You can get the absolute file number AFN:

1SELECT FILE_ID AFN, RELATIVE_FNO, TABLESPACE_NAME
2FROM DBA_DATA_FILES
3WHERE RELATIVE_FNO=&RFN;
     


Physical bad block means that the block format itself is corrupted , The data in the block doesn't make any sense . Physical bad blocks are usually due to memory problems 、OS problem 、I/O Caused by subsystem problems or hardware , The bad logic block is usually made up of Oracle Bug Caused by, etc . Physical block damage can also be called media block damage (Media Corrupt Block). Physical bad blocks can be divided into the following categories :

① Bad head (Bad header): block header (Cache Header) Damaged by invalid value

② There are cracks in the block / Incomplete (Fractured/Incompleted Block): Data block head and block tail do not match , Its trace The contents of the document are as follows :

 1Corrupt block relative dba: 0x0380e573 (file 14, block 58739)
 2Fractured block found during buffer read
 3Data in bad block -
 4type: 6 format: 2 rdba: 0x0380e573
 5last change scn: 0x0288.8e5a2f78 seq: 0x1 flg: 0x04
 6consistency value in tail: 0x00780601
 7check value in block header: 0x8739, computed block checksum: 0x2f00
 8spare1: 0x0, spare2: 0x0, spare3: 0x0
 9***
10Reread of rdba: 0x0380e573 (file 14, block 58739) found same corrupted data
     

③ Block checksums of blocks (checksum) Invalid , Its trace The contents are shown below :

 1Corrupt block relative dba: 0x0380a58f (file 14, block 42383)
 2Bad check value found during buffer read
 3Data in bad block -
 4type: 6 format: 2 rdba: 0x0380a58f
 5last change scn: 0x0288.7784c5ee seq: 0x1 flg: 0x06
 6consistency value in tail: 0xc5ee0601
 7check value in block header: 0x68a7, computed block checksum: 0x2f00
 8spare1: 0x0, spare2: 0x0, spare3: 0x0
 9***
10Reread of rdba: 0x0380a58f (file 14, block 42383) found same corrupted data
     

④ Block position error (Block Misplaced): Check and be right , But the contents of the data block being read belong to another block

1Corrupt block relative dba: 0x0d805a89 (file 54, block 23177)
2Bad header found during buffer read
3Data in bad block -
4type: 6 format: 2 rdba: 0x0d805b08 ----> Block is different than expected 0x0d805a89
5last change scn: 0x0692.86dc08e3 seq: 0x1 flg: 0x04
6consistency value in tail: 0x08e30601
7check value in block header: 0x2a6e, computed block checksum: 0x0
8spare1: 0x0, spare2: 0x0, spare3: 0x0
9***
     

⑤ The zero piece (Zeroed out blocks): May refer to Note 1545366.1.

 

Logical bad block refers to the logical problem of the data in the block , For example, the index value of the index block is not arranged in order, resulting in the logical bad block . A bad logic block usually contains a correct checksum And structure , But below the block ( Block content ) Damaged , May cause different ORA-600 error . The details of logical corruption are usually not printed in alert In the alarm log , however DBV Blocks that will report logical corruption .

Logical bad blocks can be divided into the following categories :

① The row is locked by a nonexistent transaction --ORA-600[4512] etc.

② The size of the space used is not equal to the size of the block

③ avsp bad

④ Other