10.2. Performing Backups

Your backup strategy depends on the activity of your database, the level of availability required by your service-level agreements (SLAs), and how much downtime you can tolerate during a recovery effort.

In this section, we'll first review some terminology, and then we will show you a way to back up the control file to a text file that you can edit and use in case of the loss of all control files. Finally, we will introduce Recovery Manager and show you how to make some of the backups we describe in the terminology review.

10.2.1. Understanding Backup Terminology

You can make a whole backup, which backs up the entire database, or you can back up only part of the database, which is called a partial backup. Whole backups and partial backups are known as Oracle backup strategies. The backup type can be divided into two general categories: full backups and incremental backups. Depending on whether you make your database backups when the database is open or closed, backups can be further categorized into the backup modes known as consistent and inconsistent backups.

Your backups can be managed using operating system and SQL commands or entirely by RMAN. Many backup types are only available using RMAN, such as incremental backups; unless you have some specific requirements, it is highly recommended that you use RMAN to implement your backup strategy.

In the following definitions, we'll compare and contrast whole database backups versus partial database backups, full backups versus incremental backups, and consistent backups versus inconsistent backups.

Whole database A whole database backup includes all datafiles and at least one control file. Online redo log files are never backed up; restoring backed up redo log files and replacing the current redo log files will result in loss of data during media recovery. Only one of the control files needs to be backed up; all copies of the control file are identical.

Partial database A partial database backup includes zero or more tablespaces, which in turn includes zero or more datafiles; a control file is optional in a partial database backup. As you may infer, a partial database backup that includes no tablespaces and does not include the control file backs up zero bytes of data to the backup destination.

Full A full backup includes all blocks of every datafile backed up in a whole or partial database backup.

Incremental An incremental backup makes a copy of all data blocks that have changed since a previous backup. Oracle 10g supports five levels of incremental backups, from 0 to 4. An incremental backup at level 0 is considered a baseline backup; it is the equivalent of a full backup and contains all data blocks in the datafile(s) that are backed up. Although incremental backups can take less time, the potential downside is that you must first restore the baseline backup and then apply all incremental backups performed since the baseline backup.

Consistent A consistent backup, also known as an offline backup, is performed while the database is not open. These backups are consistent because the SCN in the control file matches the SCN in every datafile's header. Although recovering using a consistent backup requires no additional recovery operation after a failure, you reduce your database's availability during a consistent backup as well as risk the loss of committed transactions performed since the consistent backup.

Inconsistent Although the term inconsistent backup may sound like something you might avoid in a database, it is a way to maintain availability of the database while performing backups. An inconsistent backup, also known as an online backup, is performed while the database is open and available to users. The backup is inconsistent because the SCN in the control file is most likely out of synch with the SCN in the header of the datafiles. Inconsistent backups require recovery when they are used for recovering from a media failure, but keep availability high because the database is open while the backup is performed.

10.2.2. Backing Up the Control File

In addition to multiplexing the control file, you can guard against the loss of all control files by backing up the control file to an editable text file; this backup is called a backup to trace. The trace backup is created in the directory specified by the initialization parameter USER_DUMP_ DEST and its format is sid_ora_pid.trc, where sid is the session ID of the user creating the trace backup and pid is the process ID of the user creating the trace backup. This special backup of the control file is not a trace file per se; in other words, it is not a dump file or an error report for a failed user or system process. It is rather a proactive rather than reactive report of the contents of the control file, and the report happens to end up in a directory with other trace files.

Back up the control file to trace after any change to the structure of the database, such as adding or dropping a tablespace or creating a new redo log file group. Using the command line to create a backup of the control file is almost as easy as clicking the Backup to Trace button within the EM Database Control:

SQL> alter database backup controlfile to trace;
Database altered.

Here is an excerpt from the output from the command; note that a lot of editing might be required before using this file to recover your database and control file:

--
--     Set #1. NORESETLOGS case
--
-- The following commands will create a new control
-- file and use it to open the database.
-- Data used by Recovery Manager will be lost.
-- Additional logs may be required for media recovery
-- of offline
-- Use this only if the current versions of
-- all online logs are available.
-- After mounting the created controlfile, the following
-- SQL statement will place the database in the
-- appropriate protection mode:
--  ALTER DATABASE SET STANDBY DATABASE TO
--     MAXIMIZE PERFORMANCE
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "ORD"
       NORESETLOGS  ARCHIVELOG
     MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
     MAXINSTANCES 8
    MAXLOGHISTORY 454
LOGFILE
  GROUP 1 (
    '/u07/oradata/ord/redo01.log',
    '/u08/oradata/ord/redo01.log'

) SIZE 10M,
  GROUP 2 (
    '/u07/oradata/ord/redo02.log',
    '/u08/oradata/ord/redo02.log'
  ) SIZE 10M,
  GROUP 3 (
    '/u07/oradata/ord/redo03.log',
    '/u08/oradata/ord/redo03.log'
  ) SIZE 10M
-- STANDBY LOGFILE
DATAFILE
'/u05/oradata/ord/system01.dbf',
'/u05/oradata/ord/undotbs01.dbf',
'/u05/oradata/ord/sysaux01.dbf',
'/u05/oradata/ord/users01.dbf',
'/u09/oradata/ord/example01.dbf',
'/u09/oradata/ord/oe_trans01.dbf',
'/u05/oradata/ord/users02.dbf',
'/u06/oradata/ord/logmnr_rep01.dbf',
'/u09/oradata/ord/big_users.dbf',
'/u08/oradata/ord/idx01.dbf',
'/u08/oradata/ord/idx02.dbf',
'/u08/oradata/ord/idx03.dbf',
'/u08/oradata/ord/idx04.dbf',
'/u08/oradata/ord/idx05.dbf',
'/u08/oradata/ord/idx06.dbf',
'/u08/oradata/ord/idx07.dbf',
'/u08/oradata/ord/idx08.dbf',
'/u04/oradata/ord/ORD/datafile/o1_mf_prd01_0l059ht2_.dbf',
'/u04/oradata/ord/ORD/datafile/o1_mf_prd02_0l05b53n_.dbf',
'/u04/oradata/ord/ORD/datafile/o1_mf_prd03_0l05bfp5_.dbf',
'/u04/oradata/ord/ORD/datafile/o1_mf_prd04_0l05c2pg_.dbf'
CHARACTER SET WE8ISO8859P1
;

Another way to back up your control file is to make a binary copy of it using a similar ALTER DATABASE command, as in the following example:

SQL> alter database backup controlfile to
                '/u03/oradata/ctlfile20040911.bkp';
Database altered.

10.2.3. Using RMAN to Create Backups

RMAN is the primary component of the Oracle database used to perform backup and recovery operations. You can use RMAN to back up all types: whole or partial databases, full or incremental, and consistent or inconsistent.

RMAN has a command-line interface for advanced configuration and backup operations; the most common backup functions are available via a GUI interface within the EM Database Control. It includes a scripting language to make it easy to automate backups, and it can back up the most critical types of files in your database: datafiles, control files, archived log files, and SPFILEs. It is not used to back up online redo log files (which you should not ever back up anyway), password files, and text-based init.ora files. In other words, RMAN is a "one-stop shopping" solution for your entire backup and recovery needs. In the rare circumstance that you have to back up outside RMAN, you can register the file created during this backup with RMAN for future use in an RMAN recovery scenario.

In the following sections, we will explain the difference between image copies and backup sets and how RMAN handles each of these backup types. After showing you some of the RMAN configuration settings, we will show you some examples of how RMAN performs full and incremental backups, using both the command-line and GUI interface.

10.2.3.1. Configuring RMAN Backup Settings

Configuring RMAN backup settings is straightforward using the EM Database Control. In the home page, click the Maintenance tab, and then click Configure Backup Settings to open the Device tab screen, as shown in Figure 10.6.

There is a separate section in this screen for your disk device and any tape devices. Under the Disk Settings section, you can control the following parameters:

Parallelism To take advantage of multiple CPUs or disk controllers, increase the value of this parameter to reduce the overall backup time by performing different portions of the backup in parallel.

Disk Backup Location If you are not backing up to the Flash Recovery area, change this value to the location where you want the backups stored.

Disk Backup Type You can choose image copy, backup set, or compressed backup set.

Figure 10.6. The Configure Backup Settings: Device screen

Infrequently used parameters, such as the control file autobackup filename format and the snapshot control file destination filename, are not available from the GUI interface; you must use the RMAN command-line interface to change these values. The following RMAN command-line session uses the RMAN SHOW ALL command to display all default RMAN backup settings:

[oracle@oltp oracle]$ rman target /

Recovery Manager: Release 10.1.0.2.0 - Production

Copyright (c) 1995, 2004, Oracle.  All rights reserved.

connected to target database: ORD (DBID=1387044942)

RMAN> show all;

using target database controlfile instead of
       recovery catalog
RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1;
CONFIGURE BACKUP OPTIMIZATION ON;

CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR
       DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COMPRESSED
       BACKUPSET PARALLELISM 1;
CONFIGURE DATAFILE BACKUP COPIES FOR
        DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR
        DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO
       '/u01/app/oracle/product/10.1.0/dbs/snapcf_ord.f';
       # default
RMAN>

Click the Backup Set tab, and specify the maximum size for a backup set piece (a single file), as you can see in Figure 10.7. In this case, set the maximum backup set piece size to 2GB to make it easier to move files around on file systems whose file size limit is 2GB.

Figure 10.7. The Configure Backup Settings: Backup Set screen

You use the last tab in the Configure Backup Settings screen, the Policy tab, to set a number of other default backup settings, such as automatically backing up the control file with each backup, skipping read-only and offline datafiles, and using a block-change tracking file. A block-change tracking file keeps track of changed blocks in each tablespace so that incremental backups need not read every block in every datafile to determine which blocks need to be backed up during an incremental backup. Figure 10.8 shows an example of the Policy tab with a block change tracking file specified.

10.2.3.2. Understanding Image Copies and Backup Sets

Image copies are duplicates of datafiles or archived redo log files, which means that every block of every file is backed up; you can use RMAN or operating system commands to make image copies. In contrast, backup sets are copies of one or more datafiles or archived redo log files that are stored in a proprietary format readable only by RMAN; backup sets consist of one or more physical files and do not include never used blocks in the datafiles being backed up. Backup sets can save even more space by using a compression algorithm designed specifically for the type of data found in an Oracle datafile.

Another difference between image copies and backup sets is that image copies can only be copied to a disk location; backup sets can be written to disk or directly to a tape or other secondary storage device.

Figure 10.8. The Configure Backup Settings: Policy screen

10.2.3.3. Creating Full and Incremental Backups

The Oracle recommended backup strategy uses RMAN to make a one-time whole-database, baseline incremental level 0 online backup weekly, and then a level 1 incremental backup for the other days of the week. You can easily fine-tune this strategy for your own needs by making, for example, a level 2 incremental backup at noon during the weekdays if heavy DML (Data Manipulation Language) is occurring in the database.

Using RMAN, you can accomplish this backup strategy with just a couple of the RMAN commands that follow. First, here is the baseline level 0 backup at the RMAN command prompt:

RMAN> backup incremental level 0
2>      as compressed backupset database;

The entire database is backed up using compression to save disk space, in addition to the space savings already gained by using backup sets instead of image copies.

Starting with a baseline level 0 incremental backup, you can make level 1 incremental backups during the rest of the week, as in the following example:

RMAN> backup incremental level 1
2>      as compressed backupset database;

The options are the same as in our previous example, except that only the blocks that have changed since the last backup are copied to the backup set.

Another variation is to make an incrementally updated backup. An incrementally updated backup uses an incremental backup and updates the changed blocks in an existing image copy as if the entire image copy were backed up. In a recovery scenario, you can restore the image copy of the datafile(s) without using an incremental backup; the incremental backup is already applied, saving a significant amount of time during a recovery operation. The following RMAN script shows how an incrementally updated backup works at the command line:

run
{
   recover copy of database with tag 'inc_upd_img';
   backup incremental level 1 for
      recover of copy with tag 'inc_upd_img' database;
}

This short and cryptic script demonstrates the advantages of using a GUI interface to perform incrementally updated backups. As you can see in Figure 10.9, in the Schedule Backup: Options screen, you can click a check box to perform an incrementally updated backup, in addition to the full backup or incremental backup that we discussed previously in this section.

Figure 10.9. Scheduling the backup and specifying the backup type