How to recover the ORACLE data block corrupted object with RMAN backup

Error:

ORA-01578: ORACLE data block corrupted (file # <file>, block # <block>)

ORA-01578: ORACLE data block corrupted (file # 5, block # 30123)
ORA-01110: data file 5: '/u01/oradata/PROD/users01.dbf'

ORA-01578: ORACLE data block corrupted
Data Block corrupted may occurred due to hard disk corruption and lot of factors. Oracle RMAN Utility has the capability to recover the Data block which is corrupted. This feature present only in Enterprise edition of Oracle.

Let us look, we have corruption in our database and we use RMAN block recovery feature for recover the corrupted block.

Following are the Steps involved in checking and resolve Corruption issue ORA-01578: Oracle data block corrupted

1. Find the corrupted block present in Database.

RMAN> Validate database;

RMAN> BACKUP VALIDATE CHECK LOGICAL DATABASE;

RMAN> BACKUP VALIDATE CHECK LOGICAL TABLESPACE users;


2. After finish 1 step, we will check in v$database_block_corruption view.

SQL> select * from v$database_block_corruption;

Example:

SQL> select count(*) from mytab;
select count(*) from mytab
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 4, block # 1027)
ORA-01110: data file 4: '/u03/oradata/orcl/users01.dbf'

SQL> select * from v$database_block_corruption;
FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTION
----- ------ ------ ------------------ ---------
4     1027   1      0                  ALL ZERO

3. Find the Object which is corrupted. Check its segment type that is index or table.

SELECT owner, segment_name, segment_type
FROM dba_extents
WHERE file_id = 5
AND 30123 BETWEEN block_id AND block_id + blocks - 1;

Note: 1. If Index found Rebuild it in other tablespace
    Alter index indexname rebuild tablespace tablespacename;
   2. If Table continue with step 4.

4. Recover the corrupted blocks through RMAN Command.

-- To recover the block
RMAN> BLOCKRECOVER DATAFILE 5 BLOCK 30123;

-- to recover multiple blocks:
RMAN> BLOCKRECOVER DATAFILE 5 BLOCK 30123, 30124, 30125;

-- generate a script of corrupted blocks:
SELECT 'BLOCKRECOVER DATAFILE ' || file# || ' BLOCK ' || block# || ';'
FROM V$DATABASE_BLOCK_CORRUPTION;

-- Recover all the corrupted blocks listed in V$DATABASE_BLOCK_CORRUPTION using available backups:
RMAN> BLOCKRECOVER CORRUPTION LIST;

Example:

RMAN target /

Recovery Manager: Release 11.2.0.2.0

connected to target database: ORCL (DBID=100254682)

RMAN> blockrecover corruption list;
Starting recover at 10-MAR-17
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=214 device type=DISKchannel ORA_DISK_1: restoring block(s)
channel ORA_DISK_1: specifying block(s) to restore from backup set
restoring blocks of datafile 00004
channel ORA_DISK_1: reading from backup piece /u02/oraback/orcl/rman/orcl_1am7fiir_1_1
ORA_DISK_1: piece handle=/u02/oraback/orcl/rman/orcl_1am7fiir_1_1 tag=TAG20110317T193450
channel ORA_DISK_1: restored block(s) from backup piece 1
channel ORA_DISK_1: block restore complete, elapsed time: 00:00:01
starting media recovery
media recovery complete, elapsed time: 00:00:07
Finished recover at 10-MAR-17
RMAN> quit
Recovery Manager complete.

6. Connect with SQLPLUS and check the corruption.

RMAN> VALIDATE DATABASE;

7. Test the table.

Select * from tablename;