RMAN Recovering Block Corruption

Block corruption sounds a bad thing – and it is. But it is also easy to recover from using RMAN. Here we will simulate a block corruption and repair the corruption using RMAN:

1. Create a table in tablespace users

create table testtab tablespace users as select * from tab;

select count(*) from testtab;

COUNT(*)

———-

4742

2. Identify a block belonging to that table

select * from(select distinct dbms_rowid.rowid_block_number(rowid)from testtab) where rownum = 1;

DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)

————————————

539

3. Corrupt all or some of those blocks using the Unix dd command.

dd of=/u02/oradata/orcl/users.dbf bs=8192 seek=539 conv=notrunc count=1 if=/dev/zero

select count(*) from testtab;

COUNT(*)

———-

4742

4. Flush the buffer cache to ensure we read blocks from disk and not from memory(buffer cache)

alter system flush buffer_cache;

select count(*) from testtab;

select count(*) from testtab

*

ERROR at line 1:

ORA-01578: ORACLE data block corrupted (file # 4, block # 539)

ORA-01110: data file 4: ‘/u02/oradata/orcl/users.dbf’

5. Verify block corruptions from V$DATABASE_BLOCK_CORRUPTION

select * from v$database_block_corruption;

FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO

———- ———- ———- —————— ———

539 1 0 ALL ZERO

6. Fix the corrupt blocks

rman target /

RMAN> blockrecover corruption list;

OR

RMAN> blockrecover datafile 4, block 539;

7. Verify block corruptions are repaired from $DATABASE_BLOCK_CORRUPTION

select * from v$database_block_corruption;

no rows selected

select count(*) from testtab;

COUNT(*)

———-

4742

Voila! Fixed.