martes, 14 de julio de 2009

RMAN Faster Backup, Faster Recovery

Faster Backup, Faster Recovery

http://www.oracle.com/technology/oramag/oracle/04-nov/o64rman.html

Use Block Change Tracking

The acquired bank's data warehouse runs on Oracle Database 10g, which offers a few tools John can use to achieve his objectives. For the first objective, he decides to use the Block Change Tracking feature, new in this version. Block change tracking causes the changed database blocks to be flagged in a special file.

ALTER DATABASE ENABLE
BLOCK CHANGE TRACKING USING FILE '/oracle/oradata/archivo.trc';


During incremental backup, RMAN checks this file to see which blocks need to be backed up instead of checking all the blocks of a data file. It dramatically reduces CPU cycles and speeds up incremental backup in the process.

The block change tracking file, if present, is used automatically by the incremental backup; no special RMAN syntax is required. Using a block change tracking file saves enough time and CPU cycles for John to take incremental backups every day (or night) instead of once a week.
How big will this block change tracking file be? The size of this file does not depend on the frequency or the size of updates to the database. For John's data warehouse database, a single instance with one old backup retained, the file will be approximately 10MB for 1TB of total database size, 20MB for 2TB, and so on.

John can ensure that block change tracking is turned on and check the location and name of the file used by querying a new dictionary view:

SELECT * FROM V$BLOCK_CHANGE_TRACKING;
The output is shown in vertical format:
STATUS : ENABLED
FILENAME: /oracle/admin/SMILEY/bct.dbf
BYTES : 11599872

Here the file size is about 11MB, for John's test database of about 1TB. John can also change the location of this file. One option is to do a normal data file rename when the database is in the

MOUNTED state:

ALTER DATABASE RENAME FILE
'/oracle/admin/SMILEY/bct.dbf'
TO '/tmp/bct.dbf';


If a database shutdown is not an option, John can disable the tracking and re-enable it with a different filename as follows:
SQL>; ALTER DATABASE
DISABLE BLOCK CHANGE TRACKING;

Database altered.

SQL> ALTER DATABASE
ENABLE BLOCK CHANGE TRACKING
USING FILE '/tmp/bct.dbf';


Database altered.

Using Incremental Merge
Now that John has a plan to address the first objective—to reduce the time required for incremental backup—he moves on to the next objective—to reduce the recovery time using only RMAN. Since incremental backups will be feasible every day thanks to the block change tracking file, John proposes a new backup strategy and schedule

BACKUP INCREMENTAL LEVEL 0 TAG = WEEKLY DATABASE;

This will create an RMAN backup set with the tag WEEKLY. The daily RMAN command for Monday will look like the following:

Backup Incremental LEVEL 1 TAG = MON DATABASE;
The daily command will be repeated for all other days, with appropriate tag values representing the weekday. After a week, if John queries the RMAN recovery catalog, he will see something similar to Listing 1.
Now let's examine a failure scenario with John's revised incremental backup schedule (which includes daily incremental backups). If the data file for tablespace USERS fails on Saturday, John has to restore the Level 0 backup taken (with tag WEEKLY) on Sunday and then apply all the incremental backups with keys from 36 through 46 from the output shown in Listing 1. After that he needs to apply the archived logs. The restore and recovery operations are shown in Listing 2. Note how all the incremental backups with tags MON, TUE, and so on are applied one after the other. After all of the incremental backups have been applied, the media recovery begins and applies the information in the archived logs.

If a full backup had been taken every day, the recovery would not have had to apply all the incremental backups, saving considerable time. But since that is not possible, what if John could apply the incremental backups taken on weekdays to the Level 0 backup taken earlier? This would make the Level 0 backup up-to-date with the latest day's changes and eliminate the need to apply all the subsequent incremental backups during recovery. In Oracle Database 10g, using a disk-based backup strategy, John can accomplish that using the Incremental Merge feature of the RMAN backup. With such an approach, the daily RMAN incremental backup script will look like this:

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

RECOVER COPY OF DATABASE
WITH TAG WEEKLY;

Adding ASM


Create an ASM instance on the data warehouse server.
You do this by creating a parameter file with a special parameter named INSTANCE_TYPE=ASM and bringing up the instance. Note that there are no control files, redo log files, or data files for ASM instances.

Once the ASM instance is started, create a disk group RMANDG as follows:

CREATE DISKGROUP rmandg
NORMAL REDUNDANCY
FAILGROUP failgrp1 DISK
'/dev/d1',
'/dev/d2',
FAILGROUP failgrp2 DISK
'/dev/d3',
'/dev/d4';


This assumes that John has four disks with which he wants to make an ASM group. He can expand it to as many disks as he likes. The above disk group is analogous to mirrored disks with the creation of failure groups, and each failure group is striped across two disks.
Change the flash recovery area of the database to point to the disk group:

ALTER SYSTEM SET DB_RECOVERY_FILE_DEST = '+RMANDG'

Note the plus (+) sign before the disk group name, which indicates an ASM disk group.

use RMAN to back up the recovery area from ASM to tape (at the network backup server) through the following command:

BACKUP RECOVERY AREA;
This backs up the entire recovery area, including data file backups, archived logs, and control file backups from the ASM disk group to the tape.

There is no need to keep multiple copies of the backups on tape. The following RMAN command deletes the old backup sets:

DELETE OBSOLETE DEVICE TYPE sbt;

John proposes to back up the ASM disks to tape every week, so the tapes will contain a week's worth of data. In case of a data warehouse database failure, the recovery can be quick from the SATA disks. If the disks fail, the database can also be recovered from the tape.

Reducing Traffic with Compression
John's last objective is to reduce the size of the incremental backups significantly so that they do not overwhelm the network. John addresses this issue by proposing to enable the compression feature of RMAN, which uses a proprietary algorithm to compress the backup sets. John's proposed backup scripts change a little to add the compression feature:

BACKUP AS COMPRESSED BACKUPSET INCREMENTAL
LEVEL 1 TAG = WEEKLY DATABASE;



Incrementally Updated Backups
Using this feature all changes between the SCN of the original image copy and the SCN of the incremental backup are applied to the image copy, winding it forward to make the equivalent of a new database image copy without the overhead of such a backup. The following example shows how this can be used:

RUN {
RECOVER COPY OF DATABASE WITH TAG 'incr_backup' UNTIL TIME 'SYSDATE - 7';
BACKUP INCREMENTAL LEVEL 1 FOR RECOVER OF COPY WITH TAG 'incr_backup' DATABASE;
}The RECOVER COPY... line will not do anything until the script has been running for more than 7 days. The BACKUP INCREMENTAL line will perform a complete backup (level 0) the first day it is run, with all subsequent backups being level 1 incremental backups. After 7 days, the RECOVER COPY... line will start to take effect, merging all incremental backups older than 7 days into the level 0 backup, effectively moving the level 0 backup forward. The effect of this is that you will permanently have a 7 day recovery window with a 7 day old level 0 backup and 6 level 1 incremental backups. Notice that the tag must be used to identify which incremental backups apply to which image copies.

If you wanted to keep your image copy as up to date as possible you might do the following:

RUN {
BACKUP INCREMENTAL LEVEL 1 FOR RECOVER OF COPY WITH TAG 'incr_backup' DATABASE;
RECOVER COPY OF DATABASE WITH TAG 'incr_backup';
}In this example the incremental backup is merged into the image copy as soon as it is completed.
Fast Incremental Backups
There are performance issues associated with incremental backups as the whole of each datafile must be scanned to identify changed blocks. In Oracle 10g it is possible to track changed blocks using a change tracking file. Enabling change tracking does produce a small overhead, but it greatly improves the performance of incremental backups. The current change tracking status can be displayed using the following query:

SELECT status FROM v$block_change_tracking;Change tracking is enabled using the ALTER DATABASE command:

ALTER DATABASE ENABLE BLOCK CHANGE TRACKING;By default the change tracking file is created as an Oracle Managed File (OMF) in the location pointed to by the DB_CREATE_FILE_DEST parameter. An alternate location can be specified using the following command:

ALTER DATABASE ENABLE BLOCK CHANGE TRACKING
USING FILE '/u01/oradata/MYSID/rman_change_track.f' REUSE;The tracking file is created with a minumum size of 10M and grows in 10M increments. It's size is typically 1/30,000 the size of the datablocks to be tracked.

Change tracking can be disabled using the following command:

ALTER DATABASE DISABLE BLOCK CHANGE TRACKING;


Estimating Size of the Change Tracking File on Disk

The size of the change tracking file is proportional to the size of the database and the number of enabled threads of redo. The size is not related to the frequency of updates to the database. Typically, the space required for block change tracking is approximately 1/30,000 the size of the data blocks to be tracked. Note, however, the following two factors that may cause the file to be larger than this estimate suggests:

•To avoid overhead of allocating space as your database grows, the change tracking file size starts at 10MB, and new space is allocated in 10MB incremenents. Thus, for any database up to approximately 300GB the file size is no smaller than 10MB, for up to approximately 600GB the file size is no smaller than 20MB, and so on.

•For each datafile, a minimum of 320K of space is allocated in the change tracking file, regardless of the size of the file. Thus, if you have a large number of relatively small datafiles, the change tracking file is larger than for databases with a smaller number of larger datafiles containing the same data.