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.