Drop Down MenusCSS Drop Down MenuPure CSS Dropdown Menu

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

Comments

Popular posts from this blog

PostgreSQL Pgbadger Installation On Linux

PostgreSQL pgBadger

How to configure Replication Manager (repmgr) ?

Migrating From Oracle to PostgreSQL using ora2pg open source tools

7 Steps to configure BDR replication in postgresql