learn-oracle
Backing Up to the Oracle Flash Recovery Area: Basic Scenarios







Backing Up to the Oracle Flash Recovery Area: Basic Scenarios

A.1.1 Scripting Disk-Only Backups

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:





A.1.1.1 Backup Scripts When Few Data Blocks Change

If most data blocks do not change each day, incremental backups will be small and you can plan your strategy around daily incremental backups.

A.1.1.1.1 Initial Setup

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.

A.1.1.1.2 Daily Script

The daily backup script would look like this:

RMAN> RECOVER COPY OF DATABASE WITH TAG "whole_db_copy";
# 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

  1. Attempt incremental update, which has no effect because there is no level 0 backup.

  2. Create level 0 backup.

Level 0 incremental image copy backup, with SCN as of Sun Feb 1

Mon Feb 2

  1. Attempt incremental update, which has no effect because there is no level 1 incremental.

  2. Back up level 1 incremental.

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

  1. Perform incremental update of level 0, rolling it forward to the prcvious day.

  2. Back up level 1 incremental.

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

  1. Attempt incremental update of level 0 backup, which has no effect because there is no level 0 backup old enough to roll forward to "SYSDATE-3"

  2. Create level 0 backup

Level 0 backup from Feb 1

Mon Feb 2

  1. Attempt incremental update of level 0 backup, which has no effect because there is no level 0 backup old enough to roll forward to "SYSDATE-3"

  2. Create level 1 incremental backup for Feb 2 changes

Level 0 backup from Feb 1, level 1 backup with changes from Feb 2, archived logs from Feb 1 through today

Tue Feb 3

  1. Attempt incremental update of level 0 backup, which has no effect because there is no level 0 backup old enough to roll forward to "SYSDATE-3"

  2. Create level 1 incremental backup for Feb 3 changes

Level 0 backup, level 1 incremental backups from Feb 2 through Feb 3, archived logs from Feb 1 through Feb 3

Wed Feb 4

  1. Attempt incremental update of level 0 backup, which has no effect because there is no level 0 backup old enough to roll forward to "SYSDATE-3"

  2. Create level 1 incrememtal backup for Feb 4 changes

Level 0 backup, level 1 backups from Feb 2 through today, archived logs from Feb 1 through today

Thur Feb 5

  1. Attempt incremental update of level 0 backup, which rolls level 0 backup forward to Feb 2

  2. Create level 1 incrememtal backup for Feb 5 changes.

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

  1. Attempt incremental update of level 0 backup, which rolls level 0 backup to Feb 3

  2. Create level 1 incrememtal backup for the changes for that day

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.


A.1.1.2 Backup Scripts When Blocks Change Frequently

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


A.1.1.3 Backup Scripts When a Moderate Number of Blocks Change Weekly

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.

A.1.1.3.1 Initial Setup

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.

A.1.1.3.2 Weekly Script

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_copy";
# 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

  1. Attempt roll-forward of incremental level 0 copy of database with tag "whole_db_copy". Fail, because there is no level 0 copy.

  2. Create level 0 incremental backup.

Level 0 backup

Sun Feb 8

  1. Attempt roll-forward of incremental level 0 copy of database with tag "whole_db_copy" using previous week's incremental level 1 backup. Fail, because there is no level 1 backup from the previous week.

  2. Create level 1 incremental backup.

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

  1. Roll forward level 0 copy of database to SCN of most recent incremental level 1 backup.

  2. Create incremental level 1 backup with changes since last incremental level 1 backup SCN.

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 DELETE OBSOLETE command is used.



Source : http://www.oraclebase.com



More Tutorials on Oracle dba | Source : oraclebase.com




Want to share or request more about Oracle Database Administrators in SQL Tutorial to become a Oracle DBA. Direct your requests to webmaster@oracleonline.info