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.