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  – 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
c. Checking alert log for possible occurrence
e. Analyze command
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'
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 /
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 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