A technical troubleshooting blog about Oracle with other Databases & Cloud Technologies.

RMAN Retention Policy Based On Redundancy Policy

3 min read
faceless black man showing stop gesture with hand

Photo by Monstera Production on Pexels.com

When you are taking backups regularly to a specified disk then after some period of time the backup disk will get full and you will need to delete old backups to make space for new backups.

Usually you delete the old backups manually by following a particular rule like, deleting all the backups taken before 1 month or keep 2 backup copies of datafiles and delete older copies.

Manually identifying and deleting old backup files is cumbersome. To help you out, Oracle RMAN has a feature called RETENTION POLICY.

The REDUNDANCY parameter of the CONFIGURE RETENTION POLICY command specifies how many backups of each datafile and control file that RMAN should keep. In other words, if the number of backups for a specific datafile or control file exceeds the REDUNDANCY setting, then RMAN considers the extra backups as obsolete.

Suppose we have a RMAN retention policy of "REDUNDANCY 2". This means that as long as we have at least two backups of the same datafile, controlfile/spfile or archivelog the other older backups become obsolete and RMAN is allowed to safely remove them.

Now, let’s also suppose that every night we backup our database using the following script:

SQL> rman {
                        backup database plus archivelog;
                        delete noprompt obsolete redundancy 2;

The backup task is quite simple    :   

First of all it ensures that we have the controlfile autobackup feature on, then it backups the database and archives and, at the end, it deletes all obsolete backups using the REDUNDANCY 2 retention policy.

Using the above approach we might think that we can restore our database as it was two days ago, right? For example, if we have a backup taken on Monday and another one taken on Tuesday we may restore our database as it was within the (Monday_last_backup - Today) time interval. Well, that's wrong!
Consider the following scenario :

1.)  On Monday night we backup the database using the above script;

2.) On Tuesday, during the day, we drop a tablespace. Because this is a structural database change a controlfile autobackup will be triggered. Ieeei, we have a new controlfile backup.

3.) On Tuesday night we backup again the database... nothing unusual, right? 
Well, the tricky part is regarding the DELETE OBSOLETE command. 

When the backup script will run this command, RMAN finds out three controlfile backups: One is originating from the Monday backup, One is from the structural change and the third is from our just finished Tuesday backup database command.

Now according to the retention policy of "REDUNDANCY 2", RMAN will assume that it is safe to delete the backup of the controlfile taken on Monday night backup because it's out of our retention policy and because this backup is the oldest one. 

This means that we gonna have a big problem restoring the database as it was before our structural change because we don't have a controlfile backup from that time.

So, if we intend to incomplete recover our database to a previous time in the past it's really a good idea to switch to a retention policy based on a "RECOVERY WINDOW" instead. In our case a RECOVERY WINDOW OF 2 DAYS would be more appropriate.