Block corruption vs RMAN backup retention policy
Today was the 1st day (of 3) of attendance of Oracle Database 10g: Backup and Recovery course. During explanation of RMAN retention policies I found myself thinking about how backup of database containing corrupted blocks relates to the retention policy. The question is important in case Flash Recovery Area (FRA) + RMAN backups with any retention policy configured is being used, because if there is not enough space to store the ongoing backup ore archived redo log in FRA, an obsolete backups will be automatically deleted.
What happens if ORA-19566: exceeded limit of 0 corrupt blocks for file ... pops out during backup? One may want to allow some corrupted blocks in the backup just to have a fresh backup (and deal with the corruption later). So the question is: does RMAN still count this kind of backup as a valid backup to satisfy the retention policy and may deletes older backup this way loosing the possibility of using the block recovery?
Lets try to fnd that out!
I have created a DB for this test named TEST and the main settings we are interested in are:
RMAN> show RETENTION POLICY;
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
SQL> show parameter db_recovery
db_recovery_file_dest string D:/oracle/fra/TEST
db_recovery_file_dest_size big integer 1200M
Backups go to FRA by default, the FRA is 1200M which is enough to store 2 full backups (allowing REDUNDANCY 1). (some lines of command outputs will not be reported there to shorten the post)
RMAN> backup database;
Finished backup at 02-SEP-08
RMAN> list backup summary;
List of Backups
===============
Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- --------------- ------- ------- ---------- ---
16 B F A DISK 02-SEP-08 1 1 NO TAG20080902T020623
17 B F A DISK 02-SEP-08 1 1 NO TAG20080902T020623
Key 16 is the Full dbfile backup and key 17 is Controlfile+SPFILE backup.
RMAN> report obsolete device type disk;
RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 1
no obsolete backups found
At this moment there are no obsolete backups.
SQL> create table tabb(col varchar2(2000)) tablespace users;
Table created.
SQL> insert into tabb values ('ABCDEABCDE');
1 row created.
SQL> commit;
Commit complete.
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
Here I use editor to corrupt the data file for USERS tablespace. I’ll find the value ABCDEABCDE in the file and change it to XXXXXXXXXX. I’ll start up the database and try to backup it again:
RMAN> backup database;
ORA-19566: exceeded limit of 0 corrupt blocks for file D:/ORACLE/ORADATA/TEST/USERS01.DBF
RMAN has spotted the corrupted block, lets set the maxcorrupt and do backup anyway:
RMAN> run
2> {
3> set maxcorrupt for datafile 4 to 5;
4> backup database;
5> }
Finished backup at 02-SEP-08
backup succeeded, lets check what’s the status of our previous backup, (datafile backups are key 16 and 19, 19 - with the corrupted block):
RMAN> list backup summary;
Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- --------------- ------- ------- ---------- ---
16 B F A DISK 02-SEP-08 1 1 NO TAG20080902T020623
17 B F A DISK 02-SEP-08 1 1 NO TAG20080902T020623
18 B F A DISK 02-SEP-08 1 1 NO TAG20080902T023039
19 B F A DISK 02-SEP-08 1 1 NO TAG20080902T023342
20 B F A DISK 02-SEP-08 1 1 NO TAG20080902T023342
RMAN> report obsolete device type disk;
Type Key Completion Time Filename/Handle
-------------------- ------ ------------------ --------------------
Backup Set <strong>16</strong> 02-SEP-08
Backup Piece 15 02-SEP-08 D:/ORACLE/FRA/TEST/TEST/BACKUPSET/2008_09_02/O1_MF_NNNDF_TAG20080902T020623_4CRXCKD1_.BKP
Backup Set 17 02-SEP-08
Backup Piece 16 02-SEP-08 D:/ORACLE/FRA/TEST/TEST/BACKUPSET/2008_09_02/O1_MF_NCSNF_TAG20080902T020623_4CRXG8B0_.BKP
Backup Set 18 02-SEP-08
Backup Piece 17 02-SEP-08 D:/ORACLE/FRA/TEST/TEST/BACKUPSET/2008_09_02/O1_MF_NCSNF_TAG20080902T023039_4CRYSJYC_.BKP
Backupset 16 is reported as obsolete, so it may be deleted by RMAN to free some space for next backups or archived logs. Lets run the backup once again and let’s check if we still have our only backup that contains the block before corruption:
RMAN> run
2> {
3> set maxcorrupt for datafile 4 to 5;
4> backup database;
5> }
Finished backup at 02-SEP-08
RMAN> list backup summary;
Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- --------------- ------- ------- ---------- ---
17 B F A DISK 02-SEP-08 1 1 NO TAG20080902T020623
18 B F A DISK 02-SEP-08 1 1 NO TAG20080902T023039
19 B F A DISK 02-SEP-08 1 1 NO TAG20080902T023342
20 B F A DISK 02-SEP-08 1 1 NO TAG20080902T023342
21 B F A DISK 02-SEP-08 1 1 NO TAG20080902T024401
22 B F A DISK 02-SEP-08 1 1 NO TAG20080902T024401
You can see that the backupset 16 is not there any more, it was deleted by RMAN. Let’s see how block recovery behaves.
RMAN> blockrecover datafile 4 block 2832; #block number from alert.log
Starting blockrecover at 02-SEP-08
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 D:/ORACLE/FRA/TEST/TEST/BACKUPSET/2008_09_02/O1_MF_NNNDF_TAG20080902T024401_4CRZL2KV_.BKP
channel ORA_DISK_1: restored block(s) from backup piece 1
piece handle=D:/ORACLE/FRA/TEST/TEST/BACKUPSET/2008_09_02/O1_MF_NNNDF_TAG20080902T024401_4CRZL2KV_.BKP tag=TAG20080902T024401
channel ORA_DISK_1: block restore complete, elapsed time: 00:00:03
failover to previous backup
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 D:/ORACLE/FRA/TEST/TEST/BACKUPSET/2008_09_02/O1_MF_NNNDF_TAG20080902T023342_4CRYYQ68_.BKP
channel ORA_DISK_1: restored block(s) from backup piece 1
piece handle=D:/ORACLE/FRA/TEST/TEST/BACKUPSET/2008_09_02/O1_MF_NNNDF_TAG20080902T023342_4CRYYQ68_.BKP tag=TAG20080902T023342
channel ORA_DISK_1: block restore complete, elapsed time: 00:00:07
failover to previous backup
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of blockrecover command at 09/02/2008 02:48:12
RMAN-06026: some targets not found - aborting restore
RMAN-06023: no backup or copy of datafile 4 found to restore
It failed :(.
One should be very careful with set maxcorrupt if FRA is used as RMAN may delete the only backups that would allow block recovery to succeed.