Repair Data Block Corruption through RMAN

Repair Block Corruption through RMAN in Oracle Database

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:

1. Find the corrupted block present in Database.

RUN {
allocate channel ch01 TYPE 'DISK';
allocate channel ch02 TYPE 'DISK';
BACKUP VALIDATE CHECK LOGICAL DATABASE;
}

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

-- Find the Blocks and file id
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.

select * from dba_extents where 1027 between block_id and block_id + blocks and file_id=4;

4. Check the type of Object it is index or Table.

select object_type from dba_objects where object_name='MYTAB'

Note: 1. If Index found Rebuild it in other tablespace
Alter index indexname rebuild tablespace tablespacename;
2. If Table continue with step 4.
 
5. Recover the corrupted blocks through RMAN Command.

RMAN>BLOCKRECOVER DATAFILE 4, BLOCK 1027
Note : block recover command for restore only work in Enterprise edition
OR
If there are a number of data blocks we need to repair, we use:
BLOCKRECOVER CORRUPTION LIST : command recover all corrupted block present in Database.

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.

-- validate command of rman
RMAN> BACKUP VALIDATE CHECK LOGICAL DATABASE;--Check corruption from following query:
select * from v$database_block_corruption;
no rows selected

7. Test the table.

select count(*) from mytab;
COUNT(*)
----------
183

Advertisements

2 thoughts on “Repair Data Block Corruption through RMAN

  1. Mansi Khurana

    If RMAN blockrecover command fails to recover the corrupted block then you can try a good third party Oracle repair tool. One such tool that I have used is Stellar Phoenix Oracle recovery. It repairs damaged Oracle database in all instances of corruption.

    Like

    Reply

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s