How to identify corrupts blocks in the Oracle database and correct it

There are two type of corruption.

a. Physical corruption: This happens generally due to media corruption. In this case,block does have invalid checksum.it may contains zero. This is generally the result of infrastructure issue like media,network ,memory.

RMAN block recover can be used to remove physical corruption

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

b. Logical corruption:  This happens when oracle find 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 alert log.

ORA-08103 – This error 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 of database SCN.

How to check the database corruption

Database corruption can be find using the below method

a. RMAN

c. Checking alert log for possible occurrence

c. dbverify

d. Export

e. Analyze command

RMAN 

Run below command

RMAN> backup validate check logical database;

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

We can use below command in case we want to do checking 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;

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, 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 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 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 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 table or schema will also find the corruption in the table or schema

But the big limitation with export is that It fails immediately after encountering the first corruption., so complete picture will be missed-out.

ANALYZE command

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

This is the easy utility to check the corruptions associated with index on 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 ANALYZE command with INTO INVALID_ROWS option, similar to the following:

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

How to correct the Corruption

a. Using blockrecover  RMAN command

With Block recover, only the blocks being recovered are not available ,rest datafile is available

RMAN >blockrecover datafile 10 block 1688

RMAN > BLOCKRECOVER CORRUPTION LIST RESTORE UNTIL TIME ‘SYSDATE – 7’;

b. Using datafile recovery from backup or standby (if you have)

c. Complete database recovery

d. In case it is index corruption only, we can drop the index and recreate it

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