Oracle Database Block Corruption

What is a data block corruption?

Data block corruptions occur whenever data is not in its expected state. 

Data Block corruptions are one of the common source of database outages. A database block is corrupted when its content has changed from what Oracle Database expects.

The block may have contents that are not internally consistent, or it may have been altered to be unrecognizable as part of the Oracle Database format.

Some data block corruptions are relatively minor, affecting only a single block. 

Why do data block corruptions occur?

Data block corruptions can occur any time that data is altered, and can be traced back to both hardware and software problems. Problems with any or all of the following components can result in data block corruptions:
 
PHYSICAL CORRUPTION & LOGICAL CORRUPTION

1.Hard drive
2. Disk controller
3. Operating system
4. Storage area network (SAN)
5. Storage device

Detect and monitor data block corruption?

Despite managing DB in efficient way, data block corruption can occur at any time and there are multiple ways that Oracle can detect and repair data block corruption:

1) RMAN
2) DBVerify (DBV)
3) ANALYZE VALIDATE 
4) DB_BLOCK_CHECKING parameter
5) DB_BLOCK_CHECKSUM

1). Oracle Recovery Manager (RMAN)

To check a database for physical and logical corruption, run the following command within Oracle RMAN:

BACKUP VALIDATE CHECK LOGICAL DATABASE ARCHIVELOG ALL;
 
The VALIDATE command in Oracle RMAN can be used to validate files, tablespaces, and even entire databases. For example:
 

VALIDATE DATAFILE ‘/opt/app/oracle/oradata/PROD/system01.dbf’;
VALIDATE TABLESPACE users;
VALIDATE DATABASE;

VALIDATE WHOLE DATABASE FOR CORRUPTION

RMAN> connect target /

RMAN> run {
 CONFIGURE DEFAULT DEVICE TYPE TO DISK;
 CONFIGURE DEVICE TYPE DISK PARALLELISM 10 BACKUP TYPE TO BACKUPSET;
 BACKUP VALIDATE CHECK LOGICAL DATABASE FILESPERSET=10;
 }

2). DBVerify (DBV)

dbv file='+DATA/PROD/datafile/whale_2985643.27152' blocksize=8192

dbv file=/u01/app/oracle/oradata/PROD/system01.dbf feedback=10000 blocksize=8192

3). ANALYZE 

You can use the ANALYZE… VALIDATE STRUCTURE construction to verify the data blocks in an object. 
Once the analysis is complete, check the INVALID_ROWS table to see the corrupted data.

To check for table or index corruption, however it will only check for corruption below the high watermark, so will not check unused space. It does not identify the corrupt block but gives a ORA-01498 error.

To first create the INVALID_ROWS table, run the following command within SQL:

@$ORACLE_HOME/rdbms/admin/UTLVALID.SQL

Second, validate the table and index structure with the following command:

ANALYZE TABLE hr.employee VALIDATE STRUCTURE CASCADE;

4) DB_BLOCK_CHECKING parameter

By setting the DB_BLOCK_CHECKING parameter to [TRUE|HIGH],
We can have Oracle check database blocks for self-consistency. 

Note that this may add up to 10 percent overhead to the server, so only use this option if performance is not of great importance.

5) DB_BLOCK_CHECKSUM

Calculates a checksum for each block before it is written to disk, every time.

Default value is TRUE

System tablespace is always set to true regardless of this parameter, others are only set when you enable them.

DB_BLOCK_CHECKING

Help to detect damage introduced by faulty memory.

Default value is FALSE

When the DB_BLOCK_CHECKING parameter is set to [TRUE|HIGH] Oracle performs a data check in the block for self-consistency.

DB_LOST_WRITE_PROTECT

Enables or disables lost write detection

Default value is NONE

A data block lost write occurs when an I/O subsystem acknowledges the completion of the block write, while in fact the write did not occur in the persistent storage.

Values can be NONE|TYPICAL|FULL
 

IMPORTANT VIEWS :

SELECT * FROM V$DATABASE_BLOCK_CORRUPTION

V$DATABASE_BLOCK_CORRUPTION displays information about database blocks that were corrupted after the last backup or validation.

V$BACKUP_CORRUPTION

This view displays information about corrupt block ranges in datafile backups from the control file.