Zahid Karim
Recent Posts
1.Draw plus and cross sign into PDF via PLSQL using PL_FPDF
2.Draw Triangle directly into PDF via PLSQL using PL_FPDF
3.Write fast and efficient PLSQL - DBMS_PROFILER
4.Fix physically corrupt data blocks using PLSQL - DBMS_REPAIR
5.Encrypt or Decrypt sensitive data using PLSQL - DBMS_CRYPTO
Find me on
Automatic Shared Memory Management in Oracle 10g How to migrate Oracle datafiles to ASM storage via RMAN

How to perform Block Media Recovery using RMAN

Posted by Zahid on August 19, 2009.

When you get some data blocks in the data files physically corrupt, then you will have to restore the full data file from backup for the recovery of those few block, if you don't have RMAN backups. But if you do then you can take benefit of this very powerful feature of RMAN called BMR (BLOCK MEDIA RECOVERY). Using BMR only those blocks which are corrupt can be recovered from a backup instead of recovering whole file.

Lets see how its done.

create tablespace ts_corrupt datafile '/d01/apps/oradata/oraxpo/ts_corrupt01.dbf' size 10m autoextend on next 5m extent management local segment space management auto / Tablespace created. create user usr_corrupt identified by usr_corrupt default tablespace ts_corrupt quota unlimited on ts_corrupt / User created. grant connect , resource to usr_corrupt / Grant succeeded. conn usr_corrupt/usr_corrupt Connected. create table t_corrupt as select * from all_objects / Table created. SQL> exit

We have a tablespace ts_corrupt, which has a table named t_corrupt owned by the user usr_corrupt. Lets take a backup of this tablespace using RMAN.

$ rman target / RMAN> backup datafile 6 tag=healthy; Starting backup at 04-APR-09 using channel ORA_DISK_1 channel ORA_DISK_1: starting full datafile backupset channel ORA_DISK_1: specifying datafile(s) in backupset input datafile fno=00006 name=/d01/apps/oradata/oraxpo/ts_corrupt01.dbf channel ORA_DISK_1: starting piece 1 at 04-APR-09 channel ORA_DISK_1: finished piece 1 at 04-APR-09 piece handle=/d01/apps/flash_recovery_area/ORAXPO/backupset/ 2009_04_04/o1_mf_nnndf_HEALTHY_5vhcr4vz_.bkp tag=HEALTHY comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01 Finished backup at 04-APR-09 RMAN> list backup; List of Backup Sets =================== BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 2 Full 4.66M DISK 00:00:01 04-APR-09 BP Key: 2 Status: AVAILABLE Compressed: NO Tag: HEALTHY Piece Name: /d01/apps/flash_recovery_area/ORAXPO/backupset/ 2009_04_04/o1_mf_nnndf_HEALTHY_5vhcr4vz_.bkp List of Datafiles in backup set 2 File LV Type Ckp SCN Ckp Time Name ---- -- ---- ------- --------- ---- 6 Full 525109 04-APR-09 /d01/apps/oradata/oraxpo/ts_corrupt01.dbf RMAN> exit Recovery Manager complete.

Now we have a backup of data file "/d01/apps/oradata/oraxpo/ts_corrupt01.dbf" tagged as healthy.
Lets see where in the data file this table is sitting.

$ sqlplus / as sysdba select segment_name , header_file , header_block from dba_segments where segment_name = 'T_CORRUPT' and owner = 'USR_CORRUPT'; SEGMENT_NAME HEADER_FILE HEADER_BLOCK ---------------------------- ----------- ------------ T_CORRUPT 6 11 SQL> exit

The header of the table is in block 11, so if the block 12 or so are corrupted we will get an error when we try to select data from the table. Lets corrupt block 12 using the "dd" command in Linux.
DISCLAIMER: The dd command given below is just for learning purposes and should only be used on testing systems. I will not take any responsibility of any consequences or loss of data caused by this command.

$ cd /d01/apps/oradata/oraxpo $ dd of=ts_corrupt01.dbf bs=8192 conv=notrunc seek=12 << EOF > Bingo! Corrupted. > EOF 0+1 records in 0+1 records out 18 bytes (18 B) copied, 0.000487 seconds, 37.0 kB/s

After this command successfully executed block 12 in the data file "/d01/apps/oradata/oraxpo/ts_corrupt01.dbf" is corrupt.

$ sqlplus / as sysdba SQL> alter system flush buffer_cache; System altered. /* We need to flush the buffer_cache because if the block 12 is in the buffer_cache already it will not be read from the data file. */ SQL> conn usr_corrupt/usr_corrupt Connected. SQL> select count(*) from t_corrupt; select count(*) from t_corrupt * ERROR at line 1: ORA-01578: ORACLE data block corrupted (file # 6, block # 12) ORA-01110: data file 6: '/d01/apps/oradata/oraxpo/ts_corrupt01.dbf' SQL> exit

The error that we get due to a block corruption is very clear. It will tell you the file and block#.
Lets do BMR for data file 6 and block 12.

$ rman target / RMAN> BLOCKRECOVER DATAFILE 6 BLOCK 12; Starting blockrecover at 04-APR-09 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: sid=154 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 00006 channel ORA_DISK_1: reading from backup piece /d01/apps /flash_recovery_area/ORAXPO/backupset/2009_04_04 /o1_mf_nnndf_HEALTHY_5vhcr4vz_.bkp channel ORA_DISK_1: restored block(s) from backup piece 1 piece handle=/d01/apps/flash_recovery_area/ORAXPO/backupset/ 2009_04_04/o1_mf_nnndf_HEALTHY_5vhcr4vz_.bkp tag=HEALTHY channel ORA_DISK_1: block restore complete, elapsed time: 00:00:01 starting media recovery media recovery complete, elapsed time: 00:00:01 Finished blockrecover at 04-APR-09 RMAN> exit Recovery Manager complete.

BLOCK MEDIA RECOVERY Complete we should now get the block 12 recovered back.

$ sqlplus usr_corrupt/usr_corrupt SQL> select count(*) from t_corrupt; COUNT(*) ---------- 40688 SQL> exit

Lets now corrupt more then one blocks in the file and test BMR in a different way.

$ cd /d01/apps/oradata/oraxpo $ dd of=ts_corrupt01.dbf bs=8192 conv=notrunc seek=13 << EOF > A Messy Corruption. > EOF 0+1 records in 0+1 records out 20 bytes (20 B) copied, 0.000382 seconds, 52.4 kB/s $ dd of=ts_corrupt01.dbf bs=8192 conv=notrunc seek=14 << EOF > A Messy Corruption. > EOF 0+1 records in 0+1 records out 20 bytes (20 B) copied, 0.003494 seconds, 5.7 kB/s $ dd of=ts_corrupt01.dbf bs=8192 conv=notrunc seek=15 << EOF > A Messy Corruption. > EOF 0+1 records in 0+1 records out 20 bytes (20 B) copied, 0.000374 seconds, 53.5 kB/s

We have successfully corrupted block 13,14,15 in the datafile "/d01/apps/oradata/oraxpo/ts_corrupt01.dbf".

$ sqlplus usr_corrupt/usr_corrupt SQL> select count(*) from t_corrupt; COUNT(*) ---------- 40688 /* Why are we still able to do a successful select on the table? */ SQL> conn / as sysdba Connected. SQL> alter system flush buffer_cache; System altered. /* The table was in buffer_cache so when we did a select we got all blocks from buffer_cache. Flush the buffer cache to read blocks from file. */ SQL> conn usr_corrupt/usr_corrupt Connected. SQL> select count(*) from t_corrupt; select count(*) from t_corrupt * ERROR at line 1: ORA-01578: ORACLE data block corrupted (file # 6, block # 13) ORA-01110: data file 6: '/d01/apps/oradata/oraxpo/ts_corrupt01.dbf' SQL> exit

Having said that you have three blocks corrupt in your data file, your automated backup script started in the night somewhere and took a backup of the file marking the blocks as corrupt. So you don't have these corrupt blocks in your latest backup. When RMAN finds corrupt blocks in the data file it reports them in v$backup_corruption.

$ rman target / RMAN> run { set maxcorrupt for datafile 6 to 3; /* This will ignore 3 corruptions in the file 6 and mark the blocks as corrupt. */ backup datafile 6; } executing command: SET MAX CORRUPT using target database control file instead of recovery catalog Starting backup at 04-APR-09 allocated channel: ORA_DISK_1 channel ORA_DISK_1: sid=158 devtype=DISK channel ORA_DISK_1: starting full datafile backupset channel ORA_DISK_1: specifying datafile(s) in backupset input datafile fno=00006 name=/d01/apps/oradata/oraxpo/ts_corrupt01.dbf channel ORA_DISK_1: starting piece 1 at 04-APR-09 channel ORA_DISK_1: finished piece 1 at 04-APR-09 piece handle=/d01/apps/flash_recovery_area/ORAXPO/backupset/ 2009_04_04/o1_mf_nnndf_TAG20090404T022906_5vhds2lc_.bkp tag=TAG20090404T022906 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03 Finished backup at 04-APR-09 RMAN> exit Recovery Manager complete.

The backup is complete now lets see what do we have in v$backup_corruption.

$ sqlplus / as sysdba select piece#, file#, block# , blocks , marked_corrupt from v$backup_corruption; PIECE# FILE# BLOCK# BLOCKS MAR ---------- ---------- ---------- ---------- --- 1 6 13 3 YES SQL> exit

It tells us there are three blocks corrupted in data file 6. Now we can simply go to RMAN use the BLOCK RECOVER command to recover all these blocks in one go from the backup we took before this corruption.

$ rman target / RMAN> list backup; using target database control file instead of recovery catalog List of Backup Sets =================== BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 2 Full 4.66M DISK 00:00:01 04-APR-09 BP Key: 2 Status: AVAILABLE Compressed: NO Tag: HEALTHY Piece Name: /d01/apps/flash_recovery_area/ORAXPO/backupset/ 2009_04_04/o1_mf_nnndf_HEALTHY_5vhcr4vz_.bkp List of Datafiles in backup set 2 File LV Type Ckp SCN Ckp Time Name ---- -- ---- ------- --------- ---- 6 Full 525109 04-APR-09 /d01/apps/oradata/oraxpo/ts_corrupt01.dbf BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 3 Full 4.66M DISK 00:00:01 04-APR-09 BP Key: 3 Status: AVAILABLE Compressed: NO Tag: TAG20090404T022906 Piece Name: /d01/apps/flash_recovery_area/ORAXPO/backupset /2009_04_04/o1_mf_nnndf_TAG20090404T022906_5vhds2lc_.bkp List of Datafiles in backup set 3 File LV Type Ckp SCN Ckp Time Name ---- -- ---- ------- --------- ---- 6 Full 526341 04-APR-09 /d01/apps/oradata/oraxpo/ts_corrupt01.dbf /* We can use the backup with the TAG=HEALTHY */ RMAN> BLOCKRECOVER CORRUPTION LIST FROM TAG "HEALTHY"; /* CORRUPTION LIST means all the blocks reported corrupt in v$backup_corruption. */ Starting blockrecover at 04-APR-09 allocated channel: ORA_DISK_1 channel ORA_DISK_1: sid=154 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 00006 channel ORA_DISK_1: reading from backup piece /d01/apps/ flash_recovery_area/ORAXPO/backupset/2009_04_04/ o1_mf_nnndf_HEALTHY_5vhcr4vz_.bkp channel ORA_DISK_1: restored block(s) from backup piece 1 piece handle=/d01/apps/flash_recovery_area/ORAXPO/backupset/2009_04_04/ o1_mf_nnndf_HEALTHY_5vhcr4vz_.bkp tag=HEALTHY channel ORA_DISK_1: block restore complete, elapsed time: 00:00:01 starting media recovery media recovery complete, elapsed time: 00:00:04 Finished blockrecover at 04-APR-09 RMAN> exit Recovery Manager complete.

Lets see if we have our data back.

$ sqlplus usr_corrupt/usr_corrupt SQL> select count(*) from t_corrupt; COUNT(*) ---------- 40688 SQL> exit

All blocks are recovered now. Before anything else happens you should quickly take another healthy backup of the data file.

$ rman target / RMAN> backup datafile 6 tag=healthy_new; Starting backup at 04-APR-09 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: sid=158 devtype=DISK channel ORA_DISK_1: starting full datafile backupset channel ORA_DISK_1: specifying datafile(s) in backupset input datafile fno=00006 name=/d01/apps/oradata/oraxpo/ts_corrupt01.dbf channel ORA_DISK_1: starting piece 1 at 04-APR-09 channel ORA_DISK_1: finished piece 1 at 04-APR-09 piece handle=/d01/apps/flash_recovery_area/ORAXPO/backupset/2009_04_04/ o1_mf_nnndf_HEALTHY_NEW_5vhffqf6_.bkp tag=HEALTHY_NEW comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03 Finished backup at 04-APR-09 RMAN> exit Recovery Manager complete. $

See also:
How to convert user managed copies into RMAN copies
Fix physically corrupt data blocks using PLSQL - DBMS_REPAIR