How to check block corruption in Oracle database and Fix it

Table of Contents

Types of Corruption in Oracle

There are two types of corruption.

a. Physical corruption: This happens generally due to media corruption. In this case, the block does have an invalid checksum. It may contain zero. This is generally the result of infrastructure issues like media, network, and memory.

RMAN block recovery can be used to remove physical corruption

We see the error ORA-01578: ORACLE data block corrupted in the alert log when Oracle detects physical corruption. Oracle detects corruption by checking the block header and block footer.

b. Logical corruption: This happens when Oracle find the block header and footer good but it finds internal consistency in the block. Generally, it happens due to some bug in Oracle software. When Oracle detects it, it throws the error in the alert log.

ORA-08103 is a logical corruption error for a particular data block.
ORA-00600 [2662] – This error is related to block corruption and occurs due to a higher SCN than database SCN.

How to check block corruption in Oracle database

Block corruption in Oracle Database can be found using the below method

RMAN
Checking alert log for possible occurrence

dbverify
Export
Analyze command

Using RMAN

Run below command

RMAN> backup validate check logical database;

The “CHECK LOGICAL” option is used to identify both Physical and Logical Block Corruptions.

We can use the below command in case we want to check selectively

BACKUP VALIDATE DATAFILE 1;
BACKUP VALIDATE check logical DATAFILE 1;

BACKUP VALIDATE TABLESPACE TOOLS;
BACKUP VALIDATE check logical TABLESPACE TOOLS;

BACKUP VALIDATE BACKUPSET 1;
BACKUP VALIDATE CURRENT CONTROLFILE;
For Multitenant
VALIDATE CHECK LOGICAL PLUGGABLE DATABASE test1;

Select the view to identify the corrupted blocks detected by RMAN.

SQL> select * from v$database_block_corruption;
For each row, find the segment impacted
SQL> SELECT TABLESPACE_NAME, SEGMENT_NAME, PARTITION_NAME, SEGMENT_TYPE 
FROM DBA_EXTENTS 
WHERE FILE_ID=&FILE_ID AND 
&BLOCK_ID BETWEEN BLOCK_ID AND BLOCK_ID + BLOCKS - 1;

Please note that after a corrupt block is repaired, the row identifying the block is deleted from the view.

Checking alert log for possible occurrence

Check alert. log file for corrupted blocks, data file list.

For ex:- ORA-01578: ORACLE data block corrupted (file # 11, block # 45)
ORA-01110: data file 5: '/xyz/db01.dbf'

dbverify

You can also use the dbverify utility to identify Physical and Logical Intra Block Corruptions.

dbv file=datafile_name blocksize=datafile_block_size

You can use the below command to generate the dbv command for all the datafiles

sqlplus /nolog
connect / as sysdba
set feedback off
set head off
set echo off
set linesize 200
set pagesize 2500
spool /tmp/dbvlogs/dbvchk.sh
select 'dbv file=' || name || ' blocksize='|| block_size || ' logfile=' ||
substr(name, instr(name, '/', -1, 1) +1) ||
'.' || file# || '.log' from v$datafile
/
spool off

In the case of RAW device

select 'dbv file='||name||' blocksize='||block_size||' end='||
(bytes/block_size)||' logfile='||
substr (name, instr (name, '/', -1, 1) + 1)||'.'||file#||'.log'
from  v$datafile;

In the case of ASM storage

select 'dbv file=' || name || ' blocksize='|| block_size || ' USERID=sys/&SYS_PASSWORD logfile=' ||
substr(name, instr(name, '/', -1, 1) +1) ||
'.' || file# || '.log' from v$datafile
/

Export

Using the export of a table or schema will also find the corruption in the table or schema

ANALYZE command

ANALYZE has been referred to as the best utility that performs maximum checks.

This is the easy utility to check the corruptions associated with an index by any means.

To check the corruption with a table do the following:

ANALYZE TABLE <OWNER.TABLE_NAME> VALIDATE STRUCTURE;

To check the corruption with an index do the following:

ANALYZE INDEX <OWNER.INDEX_NAME> VALIDATE STRUCTURE;

To check the corruption with the table and its index(s) to perform the cross-reference checkings do the following:

ANALYZE TABLE <OWNER.TABLE_NAME> VALIDATE STRUCTURE CASCADE;

For partition tables, we need to use the ANALYZE command with the INTO INVALID_ROWS option, similar to the following:

ANALYZE TABLE <OWNER.TABLE_NAME> VALIDATE STRUCTURE CASCADE INTO INVALID_ROWS;

How to Correct the Block Corruption using RMAN Blockrecover

1. Recovering Data blocks By Using All Available Backups

Run the BLOCKRECOVER command at the RMAN prompt, specifying the file and block numbers for the corrupted blocks

RMAN>BLOCKRECOVER DATAFILE 11 BLOCK 15;

Recover multiple blocks in a single command

RMAN>BLOCKRECOVER DATAFILE 11 BLOCK 15 DATAFILE 2 BLOCK 10

2. Recovering Data blocks Using Selected Backups

Run the BLOCKRECOVER command at the RMAN prompt, Specifying the data file and block numbers for the corrupted blocks and limiting the backup candidates by means of the available options. For example, specify what type of backup should be used to restore the blocks.

# restore from backupset


RMAN> BLOCKRECOVER DATAFILE 11 BLOCK 15 FROM BACKUPSET;

# Restore from datafile image copy


RMAN> BLOCKRECOVER DATAFILE 11 BLOCK 15 FROM DATAFILECOPY;

# Restore from backup set with tag “Sunday”


RMAN> BLOCKRECOVER DATAFILE 11 BLOCK 15 FROM TAG = Sunday

3 . Recovering blocks listed in V$DATABASE_BLOCK_CORRUPTION view

Run the below command to recover all blocks marked corrupt in V$DATABASE_BLOCK_CORRUPTION


RMAN> BLOCKRECOVER CORRUPTION LIST;

Restores blocks from backup sets created more than 7 days ago

RMAN> BLOCKRECOVER CORRUPTION LIST FROM BACKUPSET RESTORE UNTIL TIME 'SYSDATE-7';

How to Correct the Block Corruption Using Backup

Using data file recovery from backup or standby (if you have one)

Complete database recovery

How to Correct the Block Corruption Using Other Means

In case it is index corruption only, we can drop the index and recreate it
In the case of a table, we can create table as select * from the table by restricting the rows which are corrupt

Skipping Block Corruption using DBMS_REPAIR

We can use DBMS_REPAIR. It will not correct the corrupt block but it will mark that block as skip and you will not check that block from now onwards

I hope you like this post on How to check block corruption in Oracle using dbverify, rman, export and How to Correct the Block Corruption using RMAN Blockrecover

Related Articles

Recover Manager (RMAN)
RMAN List backup commands