RMAN Block Media Recovery (BMR) overview and sample


Hello,
RMAN Block Media Recovery is a option where you only need to restore Blocks that needs recovery. For example if you have damaged blocks in a Datafile which has a size of several gigabytes, you only need to restore a small subset from your rman backup or Flashback Logs.

Sample:

First we setup a simple testcase. We overwrite some blocks in our database to simulate a media error. Please note, do not try this with productions systems or system where you need a stable system.

dd if=/dev/zero of=/u01/app/oracle/oradata/orcl/users01.dbf conv=notrunc seek=4 count=5 bs=8192
5+0 records in
5+0 records out
40960 bytes (41 kB) copied, 0.000292765 seconds, 140 MB/s
dd if=/dev/zero of=/u01/app/oracle/oradata/orcl/users01.dbf conv=notrunc seek=16 count=3 bs=8192
3+0 records in
3+0 records out
24576 bytes (25 kB) copied, 5.8949e-05 seconds, 417 MB/s
dd if=/dev/zero of=/u01/app/oracle/oradata/orcl/users01.dbf conv=notrunc seek=39 count=2 bs=8192
2+0 records in
2+0 records out
16384 bytes (16 kB) copied, 6.9053e-05 seconds, 237 MB/s

The database currently did not know that there are some blocks defekt.

SQL> select * from v$database_block_corruption;
no rows selected

To scan the database for block corruption we can use RMAN (also DBVerify is able to find bad blocks).

RMAN> validate check logical database;

Starting validate at 06-APR-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=68 device type=DISK
channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
input datafile file number=00004 name=/u01/app/oracle/oradata/orcl/users01.dbf
input datafile file number=00002 name=/u01/app/oracle/oradata/orcl/sysaux01.dbf
input datafile file number=00001 name=/u01/app/oracle/oradata/orcl/system01.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/orcl/undotbs01.dbf
input datafile file number=00005 name=/u01/app/oracle/oradata/orcl/users02.dbf
channel ORA_DISK_1: validation complete, elapsed time: 00:01:45
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
1    OK     0              15793        92160           2486142   
  File Name: /u01/app/oracle/oradata/orcl/system01.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              60861           
  Index      0              12775           
  Other      0              2731            

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
2    OK     0              30573        183042          2486137   
  File Name: /u01/app/oracle/oradata/orcl/sysaux01.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              90899           
  Index      0              31712           
  Other      0              29856           

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
3    OK     0              1259         41600           2486142   
  File Name: /u01/app/oracle/oradata/orcl/undotbs01.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              0               
  Index      0              0               
  Other      0              40341           

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
4    FAILED 0              54329        918240          2485175   
  File Name: /u01/app/oracle/oradata/orcl/users01.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              862451          
  Index      0              2               
  Other      9              1458            

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
5    OK     0              1            1281            2374182   
  File Name: /u01/app/oracle/oradata/orcl/users02.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              1046            
  Index      0              0               
  Other      0              233             

validate found one or more corrupt blocks
See trace file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_14400.trc for details
channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
including current control file for validation
including current SPFILE in backup set
channel ORA_DISK_1: validation complete, elapsed time: 00:00:01
List of Control File and SPFILE
===============================
File Type    Status Blocks Failing Blocks Examined
------------ ------ -------------- ---------------
SPFILE       OK     0              2               
Control File OK     0              594             
Finished validate at 06-APR-13

Also if we try to make a backup of the tablespace users RMAN will give us a error.

RMAN> backup tablespace users;
Starting backup at 06-APR-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=12 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00004 
name=/u01/app/oracle/oradata/orcl/users01.dbf
input datafile file number=00005 
name=/u01/app/oracle/oradata/orcl/users02.dbfchannel 
ORA_DISK_1: starting piece 1 at 06-APR-13
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 04/06/2013 16:57:20
ORA-19566: exceeded limit of 0 corrupt blocks for file /u01/app/oracle/oradata/orcl/users01.dbf

Query the v$database_block_corruption again, to get a list of all defect database blocks.

SQL> select * from v$database_block_corruption;
     FILE#     BLOCK#      BLOCKS CORRUPTION_CHANGE# CORRUPTIO
---------- ---------- ---------- ------------------ ---------     
4          4           1          0                 ALL ZERO     
4          5           4          0                 ALL ZERO     
4          16          3          0                 ALL ZERO     
4          39          2          0                 ALL ZERO

Now we must restore some blocks to get the database back to 100% work. For this we can run the rman advisor or do a manuel recovery.

RMAN> advise failure all;


List of Database Failures

=========================


Failure ID Priority Status    Time Detected Summary

---------- -------- --------- ------------- -------

681        HIGH     OPEN      06-APR-13     Datafile 4: '/u01/app/oracle/oradata/orcl/users01.dbf' contains one or more corrupt blocks


analyzing automatic repair options; this may take some time

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=12 device type=DISK

analyzing automatic repair options complete


Mandatory Manual Actions

========================

no manual actions available


Optional Manual Actions

=======================

no manual actions available


Automated Repair Options

========================

Option Repair Description

------ ------------------

1      Perform block media recovery of block 4 in file 4  
  
   Strategy: The repair includes complete media recovery with no data loss
  
   Repair script: /u01/app/oracle/diag/rdbms/orcl/orcl/hm/reco_2587262667.hmtl

   recover datafile 4 block 4;

I prefer to do a simple blockrecover command to restore the bad blocks in the database.

RMAN> blockrecover corruption list;

Starting recover at 06-APR-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=68 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 /u01/app/oracle/fast_recovery_area/ORCL/backupset/2013_04_06/o1_mf_nnndf_TAG20130406T162858_8p0d9bjv_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/ORCL/backupset/2013_04_06/o1_mf_nnndf_TAG20130406T162858_8p0d9bjv_.bkp tag=TAG20130406T162858
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

archived log for thread 1 with sequence 16 is already on disk as file /u01/app/oracle/fast_recovery_area/ORCL/archivelog/2013_04_06/o1_mf_1_16_8p0dh00p_.arc
archived log for thread 1 with sequence 17 is already on disk as file /u01/app/oracle/fast_recovery_area/ORCL/archivelog/2013_04_06/o1_mf_1_17_8p0dhss2_.arc
archived log for thread 1 with sequence 18 is already on disk as file /u01/app/oracle/fast_recovery_area/ORCL/archivelog/2013_04_06/o1_mf_1_18_8p0djp5d_.arc
archived log for thread 1 with sequence 19 is already on disk as file /u01/app/oracle/fast_recovery_area/ORCL/archivelog/2013_04_06/o1_mf_1_19_8p0dkg83_.arc
archived log for thread 1 with sequence 20 is already on disk as file /u01/app/oracle/fast_recovery_area/ORCL/archivelog/2013_04_06/o1_mf_1_20_8p0dl70x_.arc
archived log for thread 1 with sequence 21 is already on disk as file /u01/app/oracle/fast_recovery_area/ORCL/archivelog/2013_04_06/o1_mf_1_21_8p0dm230_.arc
archived log for thread 1 with sequence 22 is already on disk as file /u01/app/oracle/fast_recovery_area/ORCL/archivelog/2013_04_06/o1_mf_1_22_8p0dmqd4_.arc
archived log for thread 1 with sequence 23 is already on disk as file /u01/app/oracle/fast_recovery_area/ORCL/archivelog/2013_04_06/o1_mf_1_23_8p0dnllh_.arc
archived log for thread 1 with sequence 24 is already on disk as file /u01/app/oracle/fast_recovery_area/ORCL/archivelog/2013_04_06/o1_mf_1_24_8p0dobxc_.arc
archived log for thread 1 with sequence 25 is already on disk as file /u01/app/oracle/fast_recovery_area/ORCL/archivelog/2013_04_06/o1_mf_1_25_8p0dp0dp_.arc
archived log for thread 1 with sequence 26 is already on disk as file /u01/app/oracle/fast_recovery_area/ORCL/archivelog/2013_04_06/o1_mf_1_26_8p0dprgm_.arc
archived log for thread 1 with sequence 27 is already on disk as file /u01/app/oracle/fast_recovery_area/ORCL/archivelog/2013_04_06/o1_mf_1_27_8p0dqg02_.arc
archived log for thread 1 with sequence 28 is already on disk as file /u01/app/oracle/fast_recovery_area/ORCL/archivelog/2013_04_06/o1_mf_1_28_8p0dr35y_.arc
media recovery complete, elapsed time: 00:00:51
Finished recover at 06-APR-13

After we are finished all blocks disappear from the v$database_block_corruption view.

SQL> select * from v$database_block_corruption;

no rows selected

Last step check the DBVerify Output.

DBVERIFY: Release 11.2.0.3.0 - Production on Sat Apr 6 17:11:05 2013

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

DBVERIFY - Verification starting : FILE = /u01/app/oracle/oradata/orcl/users01.dbf

DBVERIFY - Verification complete

Total Pages Examined         : 918240
Total Pages Processed (Data) : 862451
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 2
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 1458
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 54329
Total Pages Marked Corrupt   : 0
Total Pages Influx           : 0
Total Pages Encrypted        : 0
Highest block SCN            : 2485175 (0.2485175)

Conclusion: RMAN is a powerfully tool which allows to recover the database from various scenarios. Block Media Recovery is not the hottest new feature, but don`t forget also since Oracle 11G that you are able to do the same from the Flashback Logs, which is probably faster. Also you have seen that the database is full online and also there is no offline tablespace command. The only downtime you have is for the objects to which the bad blocks depends.

Advertisements

Kommentar verfassen

Trage deine Daten unten ein oder klicke ein Icon um dich einzuloggen:

WordPress.com-Logo

Du kommentierst mit Deinem WordPress.com-Konto. Abmelden / Ändern )

Twitter-Bild

Du kommentierst mit Deinem Twitter-Konto. Abmelden / Ändern )

Facebook-Foto

Du kommentierst mit Deinem Facebook-Konto. Abmelden / Ändern )

Google+ Foto

Du kommentierst mit Deinem Google+-Konto. Abmelden / Ändern )

Verbinde mit %s