Restriction
The data file header block (block 1) cannot be recovered. Block-level Media Recovery is most useful in situations where only a small subset of blocks in a datafile need media recovery. Before Oracle9i if a single block required recovery the DBA would need to restore the complete datafile and apply all redo changes. Recovery time is reduced as only blocks needing recovery need to be restored from backup and only the required set of corrupt blocks undergo redo application.
Data blocks undergoing media recovery are inaccessible to queries or DML because they are media corrupt, but the datafile itself remains online. This is a significant availability improvement over file-level recovery, where the entire datafile is offline for the duration of the recovery. Blocks undergoing recovery are not visible to users until the recovery process is complete.
Recovery Manager Interface
RMAN will support BMR via the new BLOCKRECOVER command:
BLOCKRECOVER bmr_block_specifier_list bmr_option_list; bmr_block_specifier_list: bmr_block_specifier | bmr_block_specifier_list bmr_block_specifier bmr_block_specifier: DATAFILE datafile_specifier BLOCK block_list | TABLESPACE tablespace_specifier DBA dba_list CORRUPTION LIST datafile_specifier: text_string | integer block_list: integer | block_list , integer tablespace_specifier: text_string dba_list: integer | dba_list , integer bmr_option_list: bmr_option | bmr_option_list | bmr_option bmr_option: from backupset | from datafilecopy | from tag text_string | restore until time_clause | nofileupdate | save final blocks | save all blocks time_clause: TIME date_string | SCN integer | LOGSEQ integer THREAD integer
This command will identify the backups from which to obtain the blocks to recover. If the user has never used RMAN before with this database, and their only existing backups are image copies taken with v7 methods, they should use the catalog datafilecopy command to identify those files to RMAN prior to using the blockrecover command.. The catalog archivelog command may also be required to specify restored archives. In some cases, it may be necessary for the customer to first configure the catalog if they are not using RMAN at all.
% rman target /
RMAN> catalog datafilecopy '[name]';
catalog archivelog '[name]';
If the user has backups or archivelogs that need to be restored from tape, they should allocate the required channel(s) before the blockrecover command. Archivelog restores for BMR can be run in parallel on multiple channels, but datafile/backupset scans and the recovery session must all run in the same server session.
To allow selection of which backup will be used to select the desired blocks, the blockrecover command supports options used in the restore command:
- FROM BACKUPSET — restore blocks from backupsets only.
- FROM DATAFILECOPY — restore blocks from datafile copies only
- FROM TAG — restore blocks from tagged backup
- RESTORE UNTIL TIME|SCN|LOGSEQ — limit search to backups made at or before this time.
Starting from 9.0.1, a new view, V$DATABASE_BLOCK_CORRUPTION, will reflect the blocks that were found to be corrupted since the last RMAN backup.
Examples
How to identify blocks needing recovery:
SQL> select * from mine; select * from mine * ERROR at line 1: ORA-01578: ORACLE data block corrupted (file # 5, block # 114) ORA-01110: data file 5: '/datafile_location/datafile5'
Or use RMAN to populate the V$DATABASE_BLOCK_CORRUPTION view.
RMAN> run {BACKUP VALIDATE DATABASE;}
Starting backup at 09-MAY-01
using channel ORA_DISK_1
using channel ORA_DISK_2
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00002 name=/datafile_location/datafile2.dbf
input datafile fno=00003 name=/datafile_location/datafile3.dbf
input datafile fno=00005 name=/datafile_location/datafile5.dbf
input datafile fno=00008 name=/datafile_location/datafile8.dbf
input datafile fno=00004 name=/datafile_location/datafile4.dbf
channel ORA_DISK_2: starting full datafile backupset
channel ORA_DISK_2: specifying datafile(s) in backupset
including current controlfile in backupset
input datafile fno=00001 name=/datafile_location/datafile1.dbf
input datafile fno=00006 name=/datafile_location/datafile6.dbf
input datafile fno=00007 name=/datafile_location/datafile7.dbf
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:28
channel ORA_DISK_2: backup set complete, elapsed time: 00:01:54
Finished backup at 09-MAY-01
Result:
SQL> select * from V$backup_corruption; RECID STAMP SET_STAMP SET_COUNT PIECE# FILE# BLOCK# ---------- ---------- ---------- ---------- ---------- ---------- ---------- BLOCKS CORRUPTION_CHANGE# MAR ---------- ------------------ --- 1 429201733 429201725 8 1 5 114 1 0 YES
Perform Block Level Recovery by either explicitly identifying the file and block to be recovered or use the corruption list – v$backup_corruption.
Recovery using Explicit File/Block:
$ rman catalog catalog_user/catalog_password@catalog_tns_alias target / log=rman1.log
RMAN> run {blockrecover datafile 5 block 114;}
RMAN> exit
Recovery Manager: Release 9.0.0.0.0 - Beta
(c) Copyright 2000 Oracle Corporation. All rights reserved.
connected to target database: XXXX (DBID=XXXXXXXXXX)
connected to recovery catalog database
RMAN>
Starting blockrecover at 08-MAY-01
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=8 devtype=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: sid=7 devtype=DISK
channel ORA_DISK_1: restoring block(s)
channel ORA_DISK_1: specifying block(s) to restore from backup set
restoring blocks of datafile 00005
channel ORA_DISK_1: restored block(s) from backup piece 1
piece handle=//04cp9jk9_1_1 tag=null params=NULL
channel ORA_DISK_1: block restore complete
channel ORA_DISK_1: block restore complete
starting media recovery
media recovery complete
Finished blockrecover at 08-MAY-01
Alternatively, you can use Data Recovery Advisor (DRA):
RMAN> list failure; List of Database Failures ========================= Failure ID Priority Status Time Detected Summary ---------- -------- --------- ------------- ------- 441 HIGH OPEN 20-SEP-13 Datafile 4: '/datafile_location/datafile4.dbf' contains one or more corrupt blocks
RMAN> advise failure; List of Database Failures ========================= Failure ID Priority Status Time Detected Summary ---------- -------- --------- ------------- ------- 441 HIGH OPEN 20-SEP-13 Datafile 4: '/datafile_location/datafile4.dbf' contains one or more corrupt blocks analyzing automatic repair options; this may take some time using channel ORA_DISK_1 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 520 in file 4 Strategy: The repair includes complete media recovery with no data loss Repair script: /ORACLE_BASE/diag/rdbms// /hm/reco_4024241654.hm
RMAN> repair failure preview; Strategy: The repair includes complete media recovery with no data loss Repair script: /ORACLE_BASE/diag/rdbms// /hm/reco_4024241654.hm contents of repair script: # block media recovery recover datafile 4 block 520;
RMAN> repair failure noprompt; Strategy: The repair includes complete media recovery with no data loss Repair script: /ORACLE_BASE/diag/rdbms// /hm/reco_4024241654.hm contents of repair script: # block media recovery recover datafile 4 block 520; executing repair script Starting recover at 20-SEP-13 using channel ORA_DISK_1 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 / / /backupset/YYYY-MM-DD/o1_mf_nnndf_TAG20130920T092224_93rm0khr_.bkp channel ORA_DISK_1: piece handle=/ / /backupset/YYYY-MM-DD/o1_mf_nnndf_TAG20130920T092224_93rm0khr_.bkp tag=TAG20130920T092224 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 187 is already on disk as file / / /archivelog/YYYY-MM-DD/o1_mf_1_187_93rmnqwg_.arc archived log for thread 1 with sequence 188 is already on disk as file / / /archivelog/YYYY-MM-DD/o1_mf_1_188_93rmq47g_.arc archived log for thread 1 with sequence 189 is already on disk as file / / /archivelog/YYYY-MM-DD/o1_mf_1_189_93rn7994_.arc archived log for thread 1 with sequence 190 is already on disk as file / / /archivelog/YYYY-MM-DD/o1_mf_1_190_93rnymmf_.arc media recovery complete, elapsed time: 00:00:03 Finished recover at 20-SEP-13 repair failure complete
Recovery using Corruption list:
RMAN> run {blockrecover corruption list;}
Starting blockrecover at 08-MAY-01
using channel ORA_DISK_1
using channel ORA_DISK_2
channel ORA_DISK_1: restoring block(s)
channel ORA_DISK_1: specifying block(s) to restore from backup set
restoring blocks of datafile 00005
channel ORA_DISK_1: restored block(s) from backup piece 1
piece handle=//08cpa69t_1_1 tag=null params=NULL
channel ORA_DISK_1: block restore complete
channel ORA_DISK_1: restoring block(s)
channel ORA_DISK_1: specifying block(s) to restore from backup set
restoring blocks of datafile 00005
channel ORA_DISK_1: restored block(s) from backup piece 1
piece handle=//04cp9jk9_1_1 tag=null params=NULL
channel ORA_DISK_1: block restore complete
starting media recovery
media recovery complete
Finished blockrecover at 08-MAY-01
11.2 RMAN Block recovery syntax
In 11.2 you can use RMAN to:
1. validate all database files and archived redo log files for physical and logical corruption:
RMAN> BACKUP VALIDATE CHECK LOGICAL DATABASE ARCHIVELOG ALL;
2. to check individual data blocks, as shown in the following example:
RMAN> VALIDATE DATAFILE 4 BLOCK 10 TO 13;
3. validate backup sets:
RMAN> VALIDATE BACKUPSET 3;
You specify backup sets by primary key, which is shown in the output of the LIST BACKUP command. The following RMAN command recovers the corrupted blocks:
1. recover all corrupted blocks reported in v$database_block_corruption
RMAN> RECOVER CORRUPTION LIST;
2. recover individual blocks, for example:
RMAN> RECOVER DATAFILE 1 BLOCK 233, 235 DATAFILE 2 BLOCK 100 TO 200;