| Oracle® Database Backup and Recovery Basics 10g Release 2 (10.2) Part Number B14192-02 |
|
|
View PDF |
| Oracle® Database Backup and Recovery Basics 10g Release 2 (10.2) Part Number B14192-02 |
|
|
View PDF |
For all of the following scenarios, assume that the RMAN environment and flash recovery area are configured as shown in "Configure Flash Recovery Area for Disk-Based Backups: Example".
The details of your backup script depend on the database load and the amount of disk space allocated. The following scripts are categorized based on different patterns of database updates and the minimum amount of disk space allocated for the flash recovery area required to achieve the recovery window on disk.
If few database blocks change, then the incremental backup size will be significantly less than the size of the database, and the best practice is to take incremental backups, to make efficient use of disk space and other resources.
If most or all database blocks change frequently, then the size of incremental backup will be roughly as large as the size of the database, and the best practice is to periodically make a complete image copy of the database.
In the following scenarios, you do not need to back up archived logs because Oracle archives the logs to the flash recovery area. They will remain in the flash recovery area for as long as they are required by the retention policy. All database backups are also all directed to the flash recovery area.
This section contains the following topics:
If most data blocks do not change each day, incremental backups will be small and you can plan your strategy around daily incremental backups.
The minimum recommended flash recovery area size on disk is dependent on the how frequent the backups are taken and retention policy used.
If your configured retention policy is REDUNDANCY X and you plan to take a backup every Y days, then the formula for the recommended flash recovery area disk quota is:
Disk Quota = Size of X copies of database + Size of one copy of incremental backups + Size of (Y+1) days of archived logs
If you configure your retention policy to a recovery window of X days and execute your backup script once in Y days then you can calculate your required disk quota by one of two formulas. If X>=Y then the formula for the recommended flash recovery area disk quota is:
Disk Quota = Size of one copy of database + Size of (floor(X/Y) + 1) copies of incremental backups + Size of (X * ceil(X/Y) +1) days of archived logs
where ceil(X/Y) is the smallest integer greater than or equal to X/Y and floor(X/Y) is the largest integer less than or equal to X/Y.
If X <Y then the recommended formula is:
Disk Quota = Size of one copy of database + Size of 2 copies of incremental backups + Size of (Y +1) days of archived logs
Size your flash recovery area according to the applicable formula.
For this example, assume that the retention policy is REDUNDANCY 1:
RMAN> CONFIGURE RETENTION POLICY TO REDUNDANCY 1;
Also assume that backups will be performed daily.
The daily backup script would look like this:
RMAN> RECOVER COPY OF DATABASE WITH TAG "whole_db_cpy";
# Make an incremental backup of the database to the flash recovery area.
RMAN> BACKUP INCREMENTAL LEVEL 1
FOR RECOVER OF COPY WITH TAG "whole_db_copy"
DATABASE;
Assume that there are no backups tagged "whole_db_copy" as of the first day of this backup scheme. The results of running this script daily are as follows:
On the first day, the RECOVER statement would have no effect, and the BACKUP... FOR RECOVER OF COPY would create the initial level 0 database copy.
On the second day, the RECOVER statement still has no effect, as there is no level 1 incremental backup to be applied to the level 0 incremental database copy. The BACKUP command creates the first incremental level 1 backup.
Each day after that, the incremental level 1 from the previous day is applied over the level 0 database copy, rolling it forward to the time of the incremental level 1 of the previous day, and a new incremental level 1 is created, containing all changes since the level 1 incremental backup of the previous day.
Assuming that this backup strategy is put into effect on February 1, the following table shows how the flash recovery area contents change over time as a result of the strategy. (Note that the flash recovery area may contain other files based on other backup activities; this table only represents files related to this strategy.)
Table A-1 Backup Timeline for Scenario When Few Blocks Change: Version 1
| Day | Script Effects | Flash Recovery Area Contents After Script |
|---|---|---|
|
Sun Feb 1 |
|
Level 0 incremental image copy backup, with SCN as of Sun Feb 1 |
|
Mon Feb 2 |
|
Level 0 incremental image copy backup with SCN as of Sun Feb 1, level 1 incremental backup containing changes from Feb 1 through Feb. 2, archived logs from Feb 1 through today |
|
Feb 3 and after |
|
Level 0 incremental image copy backup rolled forward to previous day's level 1 SCN, level 1 incremental backup with changes for previous 24 hours, archived logs from February 1 through today. Old incremental backups and archived logs not useful for recovery of the rolled-forward level 0 backup are automatically deleted when more free space is needed in the flash recovery area. |
To alter the example slightly: if you can size the flash recovery area to hold n days worth of archived logs and incremental backups (where n > 1), then you can alter the RECOVER line to RECOVER COPY UNTIL TIME 'SYSDATE-n'. For example, if the flash recovery area is large enough to hold three days of incremental backups, then you can change the script as follows:
RECOVER COPY OF DATABASE TAG "whole_db_copy" UNTIL TIME 'SYSDATE-3';
# Make an incremental backup of the database to the flash recovery area.
BACKUP INCREMENTAL LEVEL 1
FOR RECOVER OF COPY WITH TAG "whole_db_copy"
DATABASE;
Every day that you run the script, RMAN rolls forward the whole database copy to an SCN three days before the current time. Hence, the disk quota rules will preserve archived logs and incremental backups created after SYSDATE-3 (because they are needed to recover the database to an SCN within the last three days.
The following table lists the set of files expected to be in the flash recovery area each day after the daily script for this strategy is run.
Table A-2 Backup Timeline, Scenario When Few Blocks Change: Three Days of Backups
| Day | Script Effects | Contents of Flash Recovery Area |
|---|---|---|
|
Sun Feb 1 |
|
Level 0 backup from Feb 1 |
|
Mon Feb 2 |
|
Level 0 backup from Feb 1, level 1 backup with changes from Feb 2, archived logs from Feb 1 through today |
|
Tue Feb 3 |
|
Level 0 backup, level 1 incremental backups from Feb 2 through Feb 3, archived logs from Feb 1 through Feb 3 |
|
Wed Feb 4 |
|
Level 0 backup, level 1 backups from Feb 2 through today, archived logs from Feb 1 through today |
|
Thur Feb 5 |
|
Level 0 backup rolled forward to Feb 2, level 1 backups from Feb 3 through today, archived logs from Feb 2 through today |
|
After Feb 5 |
|
Level 0 backup rolled forward to level 1 backups for the last three days, archived logs from Feb 1 through today. Old incremental backups and archived logs not useful for recovery of the rolled-forward level 0 backup are automatically deleted when more space is needed in the flash recovery area. |
In this scenario, typical of a CRM environment, many or most of the data blocks are updated over the course of a week.
If you use a retention policy of redundancy X, and a backup script executed once each Y days, the minimum disk quota required is determined by the following formula:
Disk Quota = Size of X copies of the database +
size of Y days of archived redo logs
If you use a retention policy of a recovery window of X days, and the backup script is executed once each Y days, then if X>=Y, then disk quota is determined by the following formula:
Disk Quota = Size of 1 copy of the database +
size of ((X * ceil(X/Y)) +1) days of archived redo logs
where ceil(X/Y) is the smallest integer greater than or equal to X/Y.
If X<Y, the disk quota is the same as when X=Y, that is,
Disk Quota = Size of 1 copy of the database +
size of (Y +1) days of archived redo logs
For this example, assume that the retention policy is REDUNDANCY 1, and you are executing the backup script once each week. The minimum disk quota required for this scenario is as follows:
Disk Quota = Size of 1 copy of the database +
size of 8 days of archived logs
This scenario requires no initial setup script. The weekly backup script is shown here:
# Execute once a week # Make a full backup of the database to the flash recovery area. BACKUP DATABASE TAG "weekly_full_bkup";
As shown in the following table, the disk quota needs only to keep one level 0 copy of the database and one week's worth of archived logs.
Table A-3 Backup Timeline for Scenario When Most Blocks Change
| Day | Action | Contents of Flash Recovery Area |
|---|---|---|
|
Sun Feb 1 |
Back up level 0. |
Full backup |
|
Sun Feb 8 |
Back up level 0. |
Full backup from today, archived logs from Feb 1 through Feb 8 |
|
Each subsequent Sunday |
Back up level 0. |
Full backup from this Sunday, archived logs from the previous Sunday through this Sunday |
Use this strategy when roughly half the data blocks change weekly, or when the number of data blocks that change varies widely from week to week. The scripts in this strategy are "all purpose" scripts that use incrementally updated backups, starting with an image copy of the database, taking incremental level 1 backups at regular intervals, and rolling forward the existing level 0 copy.
The formula for determining space required for the flash recovery area depends upon the retention policy. For a retention policy of REDUNDANCY X and a backup script that runs once every Y days, the disk space required is:
Disk Quota = Size of X copies of the database +
Size of 1 incremental backup +
Size of Y+1 days of archived redo logs
For a recovery window of X days and a backup script executed once each Y days, if X>=Y then the space required is:
Disk Quota = Size of 1 copy of the database +
Size of floor(X/Y)+1 incremental backups +
Size of (X * ceil(X/Y)) + 1 days of archived logs
where ceil(X/Y) is the smallest integer greater than or equal to X/Y and floor(X/Y) is the largest integer less than or equal to X/Y.
If X< Y then the space required is the same as when X=Y:
Disk Quota = Size of 1 copy of the database +
Size of 2 incremental backups +
Size of Y+1 days of archived logs
For the example that follows, assume that the retention policy is REDUNDANCY 1 and the backup script is run once each week.
The minimum disk quota for such a scenario is
Disk Quota = Size of 1 copy of the database +
Size of 1 incremental backup +
Size of 8 days of archived logs
The first week, the BACKUP... FOR RECOVER OF COPY command makes a level 0 incremental backup of the database to the flash recovery area. This backup will be the basis for the weekly strategy. Each subsequent week, this copy is rolled forward to the last incremental backup checkpoint time. In this way, you create an on-disk recovery window of one week.
After the initial setup, the weekly backup script (which, for example, you could run every Sunday night) should look like the following:
# Execute once a week
# Roll forward the whole copy of the database to last incremental backup SCN
RECOVER COPY OF DATABASE TAG "whole_db_cpy";
# Make an incremental backup of the database to the flash recovery area.
BACKUP INCREMENTAL LEVEL 1
FOR RECOVER OF COPY WITH TAG "whole_db_copy"
DATABASE;
The following table illustrates how the contents of the flash recovery area change with each run of the backup script, always keeping enough archived logs and backups on hand to maintain the seven day recovery window.
Table A-4 Backup Timeline for Scenario When Moderate Number of Blocks Change
| Day | Action | Contents of Flash Recovery Area After Run |
|---|---|---|
|
Sun Feb 1 |
|
Level 0 backup |
|
Sun Feb 8 |
|
Level 0 backup from Sunday Feb 1 (not rolled forward because no level 1 exists), archived logs from Feb 1 through Feb 8 |
|
All future Sundays |
|
Level 0 backup rolled forward to SCN of previous Sunday's incremental level 1, archived logs from previous Sunday through today. Archived logs from prior to previous Sunday and the previous week's incremental level 1 backup are obsolete after the roll-forward of the level 0 backup, because there is no other backup to which they could be applied. Obsolete files may remain in the flash recovery area until the need for disk space causes the database to delete them or until the |
