RMAN Recovering Block Corruption
Let us look at a test case where we corrupt from blocks in a particular datafile and then use the RMAN blockrecover command to recover the corrupted data blocks.
To simulate a block corruption scenario, we will do the following:
SQL> create table mytab
2 tablespace users
3 as select * from tab;
Table created.
SQL> select count(*) from mytab;
COUNT(*)
----------
183
SQL> select * from
(select distinct dbms_rowid.rowid_block_number(rowid) 2
3 from mytab)
4 where rownum < 6;
DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
------------------------------------
1027
sun01:/export/home/oracle $ dd of=/u03/oradata/leventwo/users01.dbf bs=8192 seek=1027 conv=notrunc count=1 if=/dev/zero
1+0 records in
1+0 records out
sun01:/export/home/oracle $ sqlplus system/manager
SQL*Plus: Release 10.2.0.4.0 - Production on Fri Mar 18 09:34:53 2011
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>
SQL> select count(*) from mytab;
COUNT(*)
----------
183
SQL> alter system flush buffer_cache;
System altered.
SQL> /
System altered.
SQL> /
System altered.
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/leventwo/users01.dbf'
SQL> select * from v$database_block_corruption;
FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO
---------- ---------- ---------- ------------------ ---------
4 1027 1 0 ALL ZERO
We can either now recover the corrupted blocks using the command
BLOCKRECOVER DATAFILE 4, BLOCK 1027
Or, if there are a number of data blocks which are corrupted, we can issue a single command
BLOCKRECOVER CORRUPTION LIST
sun01:/export/home/oracle $ rman target /
Recovery Manager: Release 11.2.0.2.0 - Production on Fri Mar 18 09:36:51 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: LEVENTWO (DBID=2678523375)
RMAN> blockrecover corruption list;
Starting recover at 18-MAR-11
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=214 device type=DISK
channel 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/leventwo/rman/1am7fiir_1_1
channel ORA_DISK_1: piece handle=/u02/oraback/leventwo/rman/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 18-MAR-11
RMAN> quit
Recovery Manager complete.
sun01:/export/home/oracle $ sqlplus system/manager
SQL*Plus: Release 10.2.0.4.0 - Production on Fri Mar 18 09:37:36 2011
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select * from v$database_block_corruption;
no rows selected
SQL> select count(*) from mytab;
COUNT(*)
----------
183