10 Backing Up the Database: Advanced Topics

About Backup Set Size

The MAXSETSIZE parameter of the BACKUP command specifies a maximum size for a backup set in units of bytes (default), kilobytes, megabytes, or gigabytes. Thus, to limit a backup set to 305 MB, specify MAXSETSIZE 305M. RMAN attempts to limit all backup sets to this size.

You can use BACKUP ... MAXSETSIZE to limit the size of backup sets so that the database is divided among multiple backup sets. If the backup fails partway through, then you can use the restartable backup feature to back up only those files that were not backed up during the previous attempt. See "Restarting RMAN Backups" to learn how to restart RMAN backups.

In some cases the MAXSETSIZE value may be too small to contain the largest file that you are backing up. When determining whether MAXSETSIZE is too small, RMAN uses the size of the original data file rather than the file size after compression. RMAN displays an error stack such as the following:

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup command at 11/03/06 14:40:33
RMAN-06182: archive log larger than MAXSETSIZE: thread 1 seq 1
            /oracle/oradata/trgt/arch/archive1_1.dbf

Limiting the Size of Backup Sets with BACKUP ... MAXSETSIZE

Backup piece size is an issue in those situations where it exceeds the maximum file size of the file system or media management software. Use the MAXSETSIZE parameter of the CONFIGURE CHANNEL or ALLOCATE CHANNEL command to limit the size of backup pieces.

To limit the size of backup sets:

1. Start RMAN and connect to a target database and recovery catalog (if used).

2. Execute the BACKUP command with the MAXSETSIZE parameter.

   The following example backs up archived logs to tape, limiting the size of each backup set to 100 MB:

   BACKUP DEVICE TYPE sbt
     MAXSETSIZE 100M
     ARCHIVELOG ALL;
   

Dividing the Backup of a Large Data File into Sections

If you specify the SECTION SIZE parameter on the BACKUP command, then RMAN creates a backup set in which each backup piece contains the blocks from one file section. A file section is a contiguous range of blocks in a file. This type of backup is called a multisection backup.

The purpose of multisection backups is to enable RMAN channels to back up a single large file in parallel. RMAN divides the work among multiple channels, with each channel backing up one file section in a file. Backing up a file in separate sections can improve the performance of backups of large datafiles.

If a multisection backup completes successfully, then none of the backup sets generated during the backup contain a partial data file. If a multisection backup is unsuccessful, then it is possible for the RMAN metadata to contain a record for a partial backup set. RMAN does not consider partial backups for restore and recovery. You must use the DELETE command to delete the partial backup set.

If you specify a section size that is larger than the size of the file, then RMAN does not use multisection backup for the file. If you specify a small section size that would produce more than 256 sections, then RMAN increases the section size to a value that results in exactly 256 sections.

To make a multisection backup:

1. Start RMAN and connect to a target database and recovery catalog (if used).

2. If necessary, configure channel parallelism so that RMAN can make the backup parallel.

3. Execute BACKUP with the SECTION SIZE parameter.

   For example, suppose that the users tablespace contains a single data file of 900 MB. Also assume that three SBT channels are configured, with the parallelism setting for the SBT device set to 3. You can break up the data file in this tablespace into file sections as shown in the following example:

   BACKUP
     SECTION SIZE 300M
     TABLESPACE users; 
   

   In this example, each of the three SBT channels backs up a 300 MB file section of the users data file.

Optimizing a Daily Archived Log Backup to a Single Tape: Scenario

Assume that you want to back up all the archived logs every night, but you do not want to have multiple copies of each log sequence number. With RMAN configured as shown in Example 10-1, you run the following command in a script nightly at 1 a.m.:

BACKUP DEVICE TYPE sbt 
  ARCHIVELOG ALL;

RMAN skips all logs except those produced in the last 24 hours. In this way, you keep only one copy of each archived log on tape.

Optimizing a Daily Archived Log Backup to Multiple Media Families: Scenario

In Oracle Secure Backup, a media family is a named group of volumes with a set of shared, user-defined attributes. In this scenario, you back up logs that are not on tape to one media family, then back up the same logs to a second media family. Finally, you delete old logs.

With RMAN configured as shown in Example 10-2, run the following script at the same time every night to back up the logs generated during the previous day to two separate media families.

# The following command backs up just the logs that are not on tape. The 
# first copies are saved to the tapes from the media family "log_family1".
RUN
{
  ALLOCATE CHANNEL c1 DEVICE TYPE sbt
    PARMS 'ENV=(OB_MEDIA_FAMILY=log_family1)';
  BACKUP ARCHIVELOG ALL;
}
# Make one more copy of the archived logs and save them to tapes from a 
# different media family
RUN
{
  ALLOCATE CHANNEL c2 DEVICE TYPE sbt 
    PARMS 'ENV=(OB_MEDIA_FAMILY=log_family2)';
  BACKUP ARCHIVELOG
    NOT BACKED UP 2 TIMES;
}

If your goal is to delete logs from disk that have been backed up two times to SBT, then the simplest way to achieve the goal is with an archived redo log deletion policy. The following one-time configuration specifies that archived redo logs are eligible for deletion from disk if two archived log backups exist on tape:

CONFIGURE ARCHIVELOG DELETION POLICY 
  TO BACKED UP 2 TIMES TO DEVICE TYPE sbt;

After running the script in Example 10-2, you can delete unneeded logs by executing DELETE ARCHIVELOG ALL.

Creating a Weekly Secondary Backup of Archived Logs: Example

Assume a more sophisticated scenario in which your goal is to back up the archived logs to tape every day. You are worried about tape failure, however, so you want to ensure that you have more than copy of each log sequence number on an separate tape before you perform your weekly deletion of logs from disk. This scenario assumes that the database is not using a fast recovery area.

First, perform a one-time configuration as follows:

CONFIGURE BACKUP OPTIMIZATION ON;
CONFIGURE DEVICE TYPE sbt PARALLELISM 1;
CONFIGURE default DEVICE TYPE TO sbt;
CONFIGURE CHANNEL DEVICE TYPE sbt PARMS 'ENV=(OB_MEDIA_FAMILY=first_copy);

Because you have optimization enabled, you can run the following command every evening to back up all archived logs to the first_copy media family that have not been backed up:

BACKUP ARCHIVELOG ALL TAG first_copy;

Every Friday evening you create an additional backup of all archived logs in a different media family. At the end of the backup, you want to delete all archived logs that have at least two copies on tape. So you run the following script:

RUN
{
  # manually allocate a channel, to specify that the backup run by this
  # channel should go to both media families "first_copy" and "second_copy"
  ALLOCATE CHANNEL c1 DEVICE TYPE sbt
      PARMS 'ENV=(OB_MEDIA_FAMILY=second_copy)';
  ALLOCATE CHANNEL c2 DEVICE TYPE sbt
      PARMS 'ENV=(OB_MEDIA_FAMILY=first_copy)';
  BACKUP 
    CHANNEL c1 
    ARCHIVELOG 
    UNTIL TIME 'SYSDATE' 
    NOT BACKED UP 2 TIMES # back up only logs without 2 backups on tape
    TAG SECOND_COPY; 
  BACKUP 
    CHANNEL c2 
    ARCHIVELOG 
    UNTIL TIME 'SYSDATE' 
    NOT BACKED UP 2 TIMES # back up only logs without 2 backups on tape
    TAG FIRST_COPY;
}

# now delete from disk all logs that have been backed up to tape at least twice
DELETE 
  ARCHIVELOG ALL
  BACKED UP 2 TIMES TO DEVICE TYPE sbt;

The following table explains the effects of the daily and weekly backup scripts.

After the weekly backup, you can send the tape from the media family second_copy to offsite storage. You should use this tape backup only if the primary tape from pool first_copy is damaged. Because the secondary tape is offsite, you do not want RMAN to use it for recovery, so you can mark the backup as unavailable:

CHANGE BACKUP OF ARCHIVELOG TAG SECOND_COPY UNAVAILABLE;

Duplexing Backup Sets with CONFIGURE BACKUP COPIES

As explained in "Configuring Backup Duplexing", the CONFIGURE ... BACKUP COPIES command specifies the number of identical backup sets to create on the specified device type. This setting applies to all backup sets except control file autobackups (because the autobackup of a control file always produces one copy) and backup sets when backed up with the BACKUP BACKUPSET command.

To duplex a backup with CONFIGURE ... BACKUP COPIES:

1. Configure the number of copies on the desired device type for datafiles and archived redo logs on the desired device types.

   By default, CONFIGURE ... BACKUP COPIES is set to 1 for each device type. The following example configures duplexing for datafiles and archived logs on tape and also duplexing for datafiles (but not archived redo logs) on disk:

   CONFIGURE DEVICE TYPE sbt PARALLELISM 1;
   CONFIGURE DEFAULT DEVICE TYPE TO sbt;
   CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/disk1/%U', '/disk2/%U';
   CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE sbt TO 2;
   CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE sbt TO 2;
   CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 2;
   

2. Execute the BACKUP command.

   The following command backs up the database and archived logs to tape, making two copies of each data file and archived log:

   BACKUP AS BACKUPSET DATABASE PLUS ARCHIVELOG;
   

   Because of the configured formats for the disk channel, the following command backs up the database to disk, placing one copy of the backup sets produced in the /disk1 directory and the other in the /disk2 directory:

   BACKUP DEVICE TYPE DISK AS BACKUPSET DATABASE;
   

   If the FORMAT clause were not configured on CONFIGURE CHANNNEL, then you specify FORMAT on the BACKUP command itself. For example, you issue the following command:

   BACKUP AS BACKUPSET DATABASE 
     FORMAT '/disk1/%U', 
            '/disk2/%U';
   

3. Issue a LIST BACKUP command to see a listing of backup sets and pieces.

   For example, enter the following command:

   LIST BACKUP SUMMARY;
   

   The #Copies column shows the number of backup sets, which may have been produced by duplexing or by multiple backup commands.

Duplexing Backup Sets with BACKUP ... COPIES

The COPIES option of the BACKUP command overrides every other COPIES or DUPLEX setting to control duplexing of backup sets.

To duplex a backup with BACKUP ... COPIES:

1. Specify the number of identical copies with the COPIES option of the BACKUP command. For example, run the following to make three copies of each backup set in the default DISK location:

   BACKUP AS BACKUPSET DEVICE TYPE DISK 
     COPIES 3 
     INCREMENTAL LEVEL 0 
     DATABASE;
   

   Because you specified COPIES in the BACKUP command, RMAN makes three backup sets of each data file regardless of the CONFIGURE DATAFILE COPIES setting.

2. Issue a LIST BACKUP command to see a listing of backup sets and pieces (the #Copies column shows the number of copies, which may have been produced through duplexing or through multiple invocations of the BACKUP command). For example, enter:

   LIST BACKUP SUMMARY;
   

About RMAN Backup Encryption Settings

Backup encryption is performed based on the encryption settings specified with the following commands:

• CONFIGURE ENCRYPTION

  You can use this command to persistently configure transparent encryption. You cannot persistently configure dual mode or password mode encryption.

• SET ENCRYPTION

  You can use this command to configure dual mode or password mode encryption at the RMAN session level.

The database uses a new encryption key for every encrypted backup. The backup encryption key is then encrypted with either the password, the database master key, or both, depending on the chosen encryption mode. Individual backup encryption keys or passwords are never stored in clear text.

A single restore operation can process backups encrypted in different modes. For each backup piece that it restores, RMAN checks whether it is encrypted. Transparently encrypted backups need no intervention if the Oracle wallet is open and available.

If password encryption is detected, then RMAN searches for a matching key in the list of passwords entered in the SET DECRYPTION command. If RMAN finds a usable key, then the restore operation proceeds. Otherwise, RMAN searches for a key in the Oracle wallet. If RMAN finds a usable key, then the restore operation proceeds; otherwise, RMAN signals an error that the backup piece cannot be decrypted.

RMAN encryption is a CPU-intensive operation and can affect backup performance. The actual amount of CPU utilization during encryption depends on whether both input and output devices for disk and SBT produce and consume data faster than the CPU can encrypt it. Here are a few guidelines for managing and trying to maximize CPU performance:

• Because encrypted backups consume more CPU resources than unencrypted backups, you can improve performance of encrypted backups to disk by using more RMAN channels. A general rule is to use the same number of channels as the number of CPU cores in your system. For example, use two channels for a dual-core processor.

• If both the disk subsystem and SBT-subsystem are fast, you can expect very high CPU utilization. You may want to consider slowing the rate of the backup by setting the RMAN READRATE parameter. For example, you can set an upper limit for block reads so that RMAN does not consume excessive disk bandwidth and thereby degrade online performance.

Making Transparent-Mode Encrypted Backups

If you have configured transparent encryption with the CONFIGURE command as explained in "Configuring RMAN Backup Encryption Modes", then no additional commands are required to make encrypted backups. Make RMAN backups as normal.

Making Password-Mode Encrypted Backups

You can set an encryption password in an RMAN session by executing the SET ENCRYPTION BY PASSWORD command. If transparent encryption is configured, then specify the ONLY keyword to indicate that the backups should be protected with a password and not with the configured transparent encryption.

To make password-mode encrypted backups:

1. Start RMAN and connect to a target database and recovery catalog (if used).

2. Execute the SET ENCRYPTION ON IDENTIFIED BY password ONLY command.

   The following example sets the encryption password for all tablespaces (where password is a placeholder for the actual password that you enter) in the backup and specifies ONLY to indicate that the encryption is password-only:

   SET ENCRYPTION IDENTIFIED BY password ONLY ON FOR ALL TABLESPACES;
   

3. Back up the database.

   For example, enter the following command:

   BACKUP DATABASE PLUS ARCHIVELOG;
   

Making Dual-Mode Encrypted Backups

Use the SET ENCRYPTION BY PASSWORD command at the RMAN prompt to make password-protected backups. If transparent encryption is configured, then omit the ONLY keyword to indicate that the backups should be protected with a password and also with the configured transparent encryption.

To make dual-mode encrypted backups:

1. Start RMAN and connect to a target database and recovery catalog (if used).

2. Execute the SET ENCRYPTION BY PASSWORD command, making sure to omit the ONLY keyword.

   The following example sets the encryption password for all tablespaces (where password is a placeholder for the actual password that you enter) in the backup and omits ONLY to indicate dual-mode encryption:

   SET ENCRYPTION IDENTIFIED BY password ON FOR ALL TABLESPACES;
   

3. Back up the database.

   For example, enter the following command:

   BACKUP DATABASE PLUS ARCHIVELOG;
   

About Restartable Backups

The minimum unit of restartability is a data file. However, if a backup set contains one backup piece, and if this piece contains blocks from multiple data files, then the unit of restartability is the backup piece. The unit of restartability for image copies is a data file.

The benefit of restartable backups is that if the backup generates multiple backup sets, then the backup sets that completed successfully do not have to be rerun. However, if the entire database is written into one backup set, and if the backup fails halfway through, then the entire backup has to be restarted.

Any I/O errors that RMAN encounters when reading files or writing to the backup pieces or image copies cause RMAN to terminate the backup job in progress. For example, if RMAN tries to back up a data file but the data file is not on disk, then RMAN terminates the backup. If multiple channels are being used or redundant copies of backups are being created, however, then RMAN may be able to continue the backup without user intervention.

RMAN can back up only those files that have not been backed up since a specified date. Use this feature after a backup fails to back up the parts of the database missed by the failed backup.

You can restart a backup by specifying the SINCE TIME clause on the BACKUP command. If the SINCE TIME is later than the completion time, then RMAN backs up the file. If you use BACKUP DATABASE NOT BACKED UP without the SINCE TIME parameter, then RMAN only backs up files that have never been backed up.

Restarting a Backup After It Partially Completes

Use the SINCE TIME parameter of the BACKUP command to specify a date after which a new backup is required. If the SINCE TIME is later than the completion time, then RMAN backs up the file. If you use BACKUP DATABASE NOT BACKED UP without the SINCE TIME parameter, then RMAN only backs up files that have never been backed up.

To only back up files that were not backed up after a specified date:

1. Start RMAN and connect to a target database and recovery catalog (if used).

2. Execute the BACKUP ... NOT BACKED UP SINCE TIME command.

   Specify a valid date in the SINCE TIME parameter. The following example uses the default configured channel to back up all database files and archived redo logs that have not been backed up in the last two weeks:

   BACKUP 
     NOT BACKED UP SINCE TIME 'SYSDATE-14'
     DATABASE PLUS ARCHIVELOG;
   

About Backup Windows

A backup window is a period of time during which a backup must complete. For example, you may want to restrict your database backups to a window of time when user activity on your system is low, such as between 2:00 a.m. and 6:00 a.m.

RMAN backs up the least recently backed up files first. By default, RMAN backs up the files at the maximum possible speed. Specifying a window does not mean that RMAN backs up data faster than normal to ensure that the backup completes before the window ends.

By default, if the backup is not complete within the DURATION time, then RMAN interrupts the backup and reports an error. If the BACKUP command is in a RUN command, then the RUN command terminates. Any completed backup sets are retained and can be used in restore operations, even if the entire backup is not complete. Thus, if you retry a job that was interrupted when the available duration expired, each successive attempt covers more of the files needing backup. Any incomplete backup sets are discarded.

Specifying a Backup Duration

Use the DURATION parameter of the BACKUP command to specify how long a given backup job is allowed to run.

To specify a backup duration:

1. Start RMAN and connect to a target database and recovery catalog (if used).

2. Execute the BACKUP DURATION command.

   For example, run the following command at 2:00 a.m. to specify that the backup should run until 6:00 a.m.:

   BACKUP 
     DURATION 4:00 
     TABLESPACE users;
   

Permitting Partial Backups in a Backup Window

When you specify PARTIAL, RMAN does not report an error when a backup is interrupted because of the end of the backup window. Instead, RMAN displays a message showing which files are not backed up. If the BACKUP command is part of a RUN block, then the remaining commands in the RUN block continue to execute.

If you specify FILESPERSET 1, then RMAN puts each file into its own backup set. When a backup is interrupted at the end of the backup window, only the backup of the file currently being backed up is lost. All backup sets completed during the window are saved, minimizing the lost work caused by the end of the backup window.

To prevent RMAN from issuing an error when a backup partially completes:

1. Start RMAN and connect to a target database and recovery catalog (if used).

2. Execute the BACKUP DURATION command with the PARTIAL option.

   For example, you run the following command at 2:00 a.m. to specify that the backup should run until 6:00 a.m. and that each data file should be in a separate backup set:

   BACKUP 
     DURATION 4:00 PARTIAL 
     TABLESPACE users 
     FILESPERSET 1;
   

Minimizing Backup Load and Duration

When using DURATION you can run the backup with the maximum possible performance, or run as slowly as possible while still finishing within the allotted time, to minimize the performance impact of backup tasks. To maximize performance, use the MINIMIZE TIME option with DURATION, as shown in Example 10-4.

BACKUP 
  DURATION 4:00 PARTIAL 
  MINIMIZE TIME 
  DATABASE 
  FILESPERSET 1;

To extend the backup to use the full time available, use the MINIMIZE LOAD option, as in Example 10-5.

BACKUP 
  DURATION 4:00 PARTIAL 
  MINIMIZE LOAD 
  DATABASE 
  FILESPERSET 1;

In Example 10-5, RMAN monitors the progress of the running backup, and periodically estimates how long the backup takes to complete at its present rate. If RMAN estimates that the backup will finish before the end of the backup window, then it slows down the rate of backup so that the full available duration is used. This reduces the overhead on the database associated with the backup.

Note these issues when using DURATION and MINIMIZE LOAD with a tape backup:

• Efficient backup to tape requires tape streaming. If you use MINIMIZE LOAD, then RMAN may reduce the rate of backup to the point where tape streaming is not optimal.

• RMAN holds the tape resource for the entire duration of the backup window. This prevents the use of the tape resource for any other purpose during the backup window.

Because of these concerns, it is not recommended that you use MINIMIZE LOAD when backing up to tape.