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