Oracle Database Bad blocks-Physical Bad blocks

Oracle Database Bad blocks-Physical Bad blocks

Overview

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

The types of Bad blocks in the database can be divided into Bad blocks of user data, Bad blocks of Data Dictionary, Undo Bad blocks, and bad blocks of control files according to the objects of Bad blocks, redo Bad blocks, Lob Bad blocks, index Bad blocks, and so on. It can also be divided into physical Bad blocks (physical damage uption) and logical Bad blocks (logical damage uption) based on the causes of Bad blocks ).

This article mainly discusses the analysis and solution of physical Bad blocks of user data.

Physical Bad blocks

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

A common Physical Block contains parts with inconsistent header and end information (Fractured/Incomplete). The checksum value is invalid and all data Block information is 0, and may be accompanied by wrong ORA-1578 and ORA-1110

In order to promptly identify Physical Bad blocks and accurately identify the causes of Bad blocks, we recommend that you set the initialization parameter DB_BLOCK_CHECKSUM = TYPICAL (default ). Generally, the underlying OS/disk system error/damage causes the data block to be modified, and the data block is marked as a corrupt uption ).

Case sharing

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

Invalid Checksum value of data blocks is a common physical bad block. When DB_BLOCK_CHECKSUM = TYPICAL (default) is used for database initialization, when the DBWR process writes a data block to disk, it calculates the Checksum of the data block, and records the Checksum value in the positions of the data block offset 16 and 17. When the data block is read from the disk, oracle recalculates the Checksum of the data block and performs an exclusive or operation (Xor) with the Checksum recorded in the data block. If the exclusive or result is not 0, it indicates that the data block has been modified, data blocks are bad blocks ).

1. The current database initialization parameter is configured with DB_BLOCK_CHECKSUM = TYPICAL. Therefore, check checksum when reading data blocks from disk:

SQL> show parameter DB_BLOCK_CHECKSUM

NAME TYPE VALUE

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

Db_block_checksum string TYPICAL

2. When querying the table dept, there is a bad block, the error message ORA-1578 and ORA-1110, the bad block is file #4, block #133

SQL> select * from dept;

Select * from dept

*

ERROR at line 1:

ORA-01578: ORACLE data block upted (file #4, block #133)

ORA-01110: data file 4: '/u01/app/oracle/oradata/orcl/users01.dbf'

3. When the preceding error occurs, a detailed error message is displayed in the alert log. The error message indicates that the checksum is invalid because the data block (file #4, block #133) is damaged. The checksum value recorded in the data block is 0x8167 (this value was calculated when DBWR was last written to the disk). When reading the data block, the calculated checksum is 0x8122, the result of the checksum value exclusive or operation (Xor) is 0x45 (computed block checksum ). Because the two checksum values are different (that is, the difference or the result is not 0), it indicates that the data block has been modified and the data block is a bad partition ).

Alert log error message:

Hex dump of (file 4, block 133) in trace file/u01/app/oracle/diag/rdbms/orcl/trace/orcl_ora_20892.trc

Corrupt block relative dba: 0x01000085 (file 4, block 133)

Bad check value found during multiblock buffer read <Note that the cause of the Bad block is that checksum is invalid.

Data in bad block:

Type: 6 format: 2 rdba: 0x01000085

Last change scn: 0x0000. 0023d69a seq: 0x5 flg: 0x06

Spare1: 0x0 spare2: 0x0 spare3: 0x0

Consistency value in tail: 0xd69a0605

Check value in block header: 0x8167 <The checksum value recorded in the data block is 0x8167.

Computed block checksum: 0x45 <0x8167 and 0x8122 exclusive or operation (Xor) results in 0x45

Reading datafile '/u01/app/oracle/oradata/orcl/users01.dbf' for partition uption at rdba: 0x01000085 (file 4, block 133)

Reread (file 4, block 133) found same upload upt data (no logical check)

Sun Mar 23 22:53:40 2014

Corrupt Block Found

TSN = 4, TSNAME = USERS

RFN = 4, BLK = 133, RDBA = 16777349

OBJN = 14343, OBJD = 14343, OBJECT = DEPT, SUBOBJECT =

Segment owner = JAMES, segment type = Table Segment <ID of the object corresponding to the bad block

Errors in file/u01/app/oracle/diag/rdbms/orcl/trace/orcl_ora_20892.trc (incident = 182595 ):

ORA-01578: ORACLE data block upted (file #4, block #133)

ORA-01110: data file 4: '/u01/app/oracle/oradata/orcl/users01.dbf'

The corresponding orcl_ora_20892.trc of 4.1 also contains information about data blocks. The checksum value recorded on the data blocks is 0x8167 (chkval)

Block dump from disk:

Buffer tsn: 4 rdba: 0x01000085 (4/133)

Scn: 0x0000. 0023d69a seq: 0x05 flg: 0x06 tail: 0xd69a0605

Frmt: 0x02 chkval: 0x8167 type: 0x06 = trans data

Hex dump of block: st = 0, typ_found = 1

4.2 check the checksum value recorded in the data block through dd. The value of offset 8167 corresponds to the checksum value 0 x.

$ Dd if =/u01/app/oracle/oradata/orcl/users01.dbf bs = 8192 count = 1 skip = 133 of =/tmp/dd133.out

$ Od-x/tmp/dd133.out

0000000 a206 0000 0085 0100 d69a 0023 0000 0605

0000020 8167 0000 0001 0000 3807 0000 2fef 000c

^

5. You can use Backup recovery or DBMS_REPAIR.SKIP_CORRUPT_BLOCKS to restore bad data blocks.

5.1 Method #1 RMAN data block recovery:

RMAN> run {blockrecover datafile 4 block 133 ;}

SQL> select * from dept;

DEPTNO DNAME LOC

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

10 ACCOUNTING DALIAN

20 RESEARCH DALLAS

30 SALES CHICAGO

40 OPERATIONS BOSTON

5.2 method #2 DBMS_REPAIR.SKIP_CORRUPT_BLOCKS skip Bad blocks, and then export other data in the dept table to recreate the table

SQL> alter session set db_file_multiblock_read_count = 1;

SQL> execute DBMS_REPAIR.SKIP_CORRUPT_BLOCKS ('James ', 'dept ');

SQL> create table dept_new as select * from dept;