As of Oracle 10g it’s possible to recover (or wind forward) a RMAN disk copy of the database to a specific time in the past. This type of backup is quite suitable for big databases (> 100GB), because you have to perform a time consuming full backup only once. All other backups to disk will be incremental ones, and with block change tracking on, these backups are done in a fraction of the time a full backup takes.
A setup like this needs a bit of disk space. Using the FRA (Flash Recovery Area, but from 11gR2 on it stands for the more appropriate Fast Recovery Area ), you may need to size it to 2 times the size of the database, or even more, depending on the number of database transactions.
However, till now I have been able to convince most clients I worked for of the benefits of this kind of backup regime, and to provide or even buy the extra disks necessary.
My favorite configuration of a RMAN database backup is as follows:
In the first week, on a Sunday, a onetime full (level 0) disk backup (copy of datafiles) to the FRA, and a subsequent backup of the database copy to tape. From then on, every day an incremental level 1 disk backup, and the backup to tape of all archives and incremental disk backups, not yet back upped.
Next Sunday, because I like to have a recovery window of 1 week if possible, the incremental merge of the database disk copy kicks in, winding it forward to make the equivalent of a new database image copy. An incremental merge of the database is executed every day from now on. On this Sunday (and every next Sunday), backup the database disk copy to tape, after it’s incrementally updated.
Why backup the database copy to tape on Sunday only…. well, you can save yourself a lot of time and a lot of tape this way, especially when your database is a big one. If you need to restore/recover from tape instead of from disk you lose the benefit of Fast Recovery anyway, so the extra time needed to apply some extra incremental backups to a Sundayâ€™s full backup to roll forward to a specific point in time, won’t make that much a difference in restore/recovery time.
You yourself don’t have to delete old (older than 7 days!) backup sets, copy controlfiles and archives from disk, if you stick to this backup regime. Oracle labels every recovery file that is successfully back upped to tape to ‘reclaimable’, meaning that it will be deleted from disk if FRA disk space is needed for more recent recovery files. A nice feature, but it also means that the database copy on disk is set to reclaimable, every Sunday after it’s back upped to tape.
I really think this is a problem, because you want to make sure the database copy is intact in case you have to do a switch from one or more datafiles from the original database to datafiles of the copy. By eliminating restore time, you can be up and running again in minutes instead of hours, even if this is a big database. So, how do you prevent Oracle to label the database copy as ‘reclaimable’?
I checked some forums, and found this solution/workaround: set redundancy to 2.
Well, although this prevents the database copy to be set to reclaimable, it also prevents this nice automatic delete of disk backup sets, copy controlfiles, and archives, because they too will now never be labeled as reclaimable. So I adjusted the backup script, and added a delete from disk of all backup sets, copy controlfiles and archives older than 7 days, to be executed every day at the end of the database backup.
But the fact that this way the automatic administration of the FRA by Oracle is in effect disabled bothered me. It’s quite comforting to know that a sudden and unforeseen burst in database transactions can be alleviated by ‘reclaimable’ space within the FRA, and can provide some extra time to prevent the database from stalling. And I came up with the following solution: yes indeed, set redundancy to 2, but also backup all recovery files (except the database copy!) at least 2 times to tape.
This really solved my problem.
At the expense of some extra tape usage for double back upped archives and incremental disk backups, I can now be sure of the all time existence of a full copy of the database on disk that will never be labeled as ‘reclaimable’.Â I can leave the FRA administration to Oracle now, because recovery files back upped 3 times (1* disk + 2* tape) will be deleted from the FRA, if more recent recovery files need disk space.