RMAN Incremental Merge 10g+

New feature from 10g+ allows incremental backups to be applied to the base image copy on the fly. Which means each incremental backup cycle will roll forward the database image copy to a later point in time. Then when there is a need to do a restore and recover there will be an updated image copy of the database available on disk.

If MTTR is a constraint then SWITCH DATABASE TO COPY can be used to switch the datafiles to the updated image copy available on disk and there is no need to restore!

Even if the database is restored, fewer archive log files will be required to roll the database forward. For example, less than 24 hours of archivelog files will be required if the latest incremental backup is executed on a daily basis. This is the suggested or Oracle Backup Strategy.

One limitation in using this approach is that more disk space will be required as a full image copy of the database will reside in the backup location and another limit is that point in time recovery of the database can not be performed before the last application of the incremental backup (meaning before the time that the last incremental backup was executed).

The last limitation is dependent upon how frequent the incremental backups are taken and the application of the incremental changes on the image copies.

There are some considerations:

1. At any given time what is the fallback duration? The time which will be required to perform an incomplete recovery on the database.

2. Depending on the time needed for fall back there will be a requirement for additional disk space.

With this approach “Incremental Merge” approach, the daily RMAN incremental backup script will be similar to the following:

RMAN> BACKUP AS INCREMENTAL LEVEL 1 FOR RECOVER OF COPY WITH TAG WEEKLY DATABASE;

RMAN> RECOVER COPY OF DATABASE WITH TAG WEEKLY;

When the above command is executed every day, the incremental backups are applied to the Level 0 backup with the tag WEEKLY. Each incremental backup is merged with the current full backup, making it a complete up-to-date backup as of that moment in time.

Example:

Day One – Sunday:

The first time this script runs, it creates the level 0 backup of the datafiles needed to begin the cycle of incremental updates. Since, Sunday is the first Incremental Merge Backup RMAN will take a level 0 backup of all the datafiles.

RMAN> BACKUP INCREMENTAL LEVEL 1 FOR RECOVER OF COPY WITH TAG WEEKLY DATABASE;

This will create an image copy of all the datafiles that can be seen by issuing the RMAN command ‘list copy of database’;. The ‘RECOVER COPY OF DATABASE WITH TAG…’ command instructs RMAN to apply any incremental level 1 backups to a set of datafile copies with the same tag. At this time, there is no incremental backup to apply, so the command generates a message for each datafile in the database that looks like the following:

no copy of datafile 1 found to recover

no copy of datafile 2 found to recover

no copy of datafile 3 found to recover

…..

no copy of datafile x found to recover

Day 2 – Monday

This will constitute the second execution of the Incremental Merge approach and RMAN will produce a level 1 incremental backup of the datafiles:

RMAN> BACKUP INCREMENTAL LEVEL 1 FOR RECOVER OF COPY;

The ‘RECOVER COPY OF DATABASE WITH TAG…’ command will cause RMAN to apply Monday’s incremental level 1 backups to the set of datafile copies with the tag WEEKLY on SUNDAY.

RMAN> RECOVER COPY OF DATABASE WITH TAG WEEKLY;

Here the recover command applies the incremental backup on Image copy and thus rolling forward the image copy of the database to the time of the backup. If there is a need to fall back to a time in the past, it can not be done as there is no datafile backup old enough available to restore.

This Incremental Backup can be delayed to achieve a larger fall back window using commands like:

RECOVER COPY OF DATABASE WITH TAG WEEKLY until time ’sysdate-1′ or

RECOVER COPY OF DATABASE WITH TAG WEEKLY until time ’sysdate-2′

Day 3 – Tuesday

On Tuesday, the DBA adds a new tablespace to the database and this results in the scenario where the current level 0 backup does not have an image copy of the newly created datafile for the new tablespace. The next RMAN backup will create an image copy of the datafile belonging to this tablespace and will take an incremental backup for other datafiles.

‘RECOVER COPY OF DATABASE WITH TAG….’ causes RMAN to apply Tuesday’s incremental level 1 backups on the previously updated backup of Monday making it a complete up-to-date backup as of Tuesday’s backup execution time. However, for the new tablespace (datafile) it is a newly created image copy so no update is required.

RMAN> RECOVER COPY OF DATABASE WITH TAG WEEKLY;

Day 4 – Wednesday

RMAN> BACKUP INCREMENTAL LEVEL 1 FOR RECOVER OF COPY WITH TAG WEEKLY DATABASE;

The incremental applies the updates to all datafiles including the newly added tablespace on Tuesday.

Day 5 – Thursday

The database crashes and all datafiles are destroyed.

The DBA can restore the INCREMENTAL MERGE backup as of Wednesday and apply all of the archivelogs generated since the backup to recover the database.

RMAN> restore database;

In order to save time for the restore you can switch the database to copy command with:

RMAN> switch database to copy;

All datafile pointers will be pointed to the image backup copies of the datafiles.

RMAN> recover database;

RMAN> alter database open;

The database is recovered much faster than the traditional method of recovery.

The Traditional Approach to Recovering from the same scenario compared to the above method:

1. Restore Database – the traditional approach calls for the restoration of the level 0 backup taken on Sunday and by using the Merge Incremental approach you start with the restore of the backup that took place on Wednesday by issuing the ’switch database’ command’.

2. Recover Database – using the traditional approach you would apply all of the incremental backups starting with Monday’s while there aren’t any incremental backups to apply using the Incremental Merge method.

3. Media Recovery – using the traditional approach you would apply all of the archivelogs since the last incremental backup while using the Incremental Merge you perform the same process but you are are skipping the steps 1 and 2 and thereby dramatically reducing the overall elapsed time to restore and recover from a database failure.

The RMAN documentation uses the Incremental Merge as one of the examples in the 10g and 11g Basic Concepts Oracle document.

The Incremental Merge is an excellent solution and I’ve used it several times, the downside is the amount of disk space it consumes. You need to allow for a set of datafiles the exact same size as the source database plus archivelogs and the other key consideration is the window between incremental executions. You need to execute the incremental (level 1) backups everyday but map out whether you need to use the ‘until time sysdate-x’ and determine what ‘x’ should be set to. The Basic RMAN 10g and 11g Oracle Manual provides other examples and offers some formulas for calculating the amount of disk required to support the various approaches including the Incremental Merge. I’ll post the formulas over the next few days.