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:

·        Create a table in tablespace users

·        Identify the blocks belonging to that table

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

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

·        Verify block corruptions from V$DATABASE_BLOCK_CORRUPTION

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