Understanding Block Curruption

When oracle detects a block curruption, It writes an error in alert log as follows.

ORA-01578: ORACLE data block corrupted (file # X, block # Y)
ORA-01110: data file X: '/x01/app/oracle/oradata/ora11g/users01.dbf

Error itself shows which data file has the corrupted block. Usually curruption is caused by hardware failure or operating system caching problems.

To get the tablespace name, we can use following query.

select tablespace_name from dba_data_files where file_id=X;

Let's say tablespace is USERS for the moment.
Then we can use following query to find more details.

select tablespace_name,segment_name,extent_id,bytes,file_id,blocks,block_id from dba_extents where tablespace_name='USERS' order by block_id;

Output will give each extents and the starting block id. So we can easily find the specific segment name which has the corrupted block.


Block media recovery.
Following prerequisites should be fullfilled.
Database must be in ARCHIVELOG mode.
Full backup or Level 0 incremental backup should be available.

RMAN>recover datafile X block Y;