9 Performing Backup and Recovery

Consistent and Inconsistent Backups

A backup is consistent or inconsistent. A backup is consistent when there are no changes in the redo log that have not already been applied to the datafiles at the time of the backup.

To make a consistent backup, your database must have been shut down normally and cannot be reopened for the duration of the backup. In shutting down the database, all committed changes in the redo log are written to the datafiles, so the datafiles are in a transaction-consistent state. This process is known as an offline backup because the entire database is offline for the duration of the backup.

In contrast to a consistent backup, an inconsistent backup is made while the database is open. In an inconsistent backup, the online redo logs contain changes that have not yet been applied to the datafiles. The datafiles are not in a transaction-consistent state. The database must be run in ARCHIVELOG mode to preserve the redo log. The online redo log at the moment of the backup must be archived and backed up along with the datafiles to preserve these changes.

In spite of the name, an inconsistent backup is as robust a form of backup as a consistent backup. The great advantage to making inconsistent backups is that you can back up your database while the database is open for updates.

Media Recovery

If you restore the archived redo logs and the datafiles from backups, then you must perform media recovery before you can open the database. Any database transactions in the archived redo logs not already reflected in the datafiles are applied to the datafiles, bringing them to a transaction-consistent state before the database is opened.

Media recovery requires a control file, datafiles (typically restored from backup), and online and archived redo logs containing changes since the time the datafiles were backed up. Media recovery is most often used to recover from a media failure, such as the loss of a file or disk, or a user error, such as the deletion of the contents of a table.

There are two forms of media recovery: complete recovery and point-in-time recovery. In complete recovery, datafiles are restored from backup; all changes from the archived and online redo logs are applied to the datafiles. The database is returned to its state at the time of failure and can be opened with no loss of committed changes.

In point-in-time recovery, you return your database to its contents at a target time of your choosing in the past. You start with a backup of datafiles created prior to the target time and a complete set of archived redo log files from the time of that backup through the target time. During recovery, each change between the backup time and the target time is applied to the datafiles.

Point-in-time recovery can return your whole database to its state at any time between the time of your backup and the most recent change in the archived redo logs. All changes after the target time are discarded. Point-in-time recovery is also sometimes called incomplete recovery because you do not recover the complete set of changes to your database.

Enterprise Manager provides a convenient interface to both complete and point-in-time recovery in the form of a Recovery Wizard. However, this book focuses on complete recovery. Point-in-time recovery is discussed at more length in Oracle Database Backup and Recovery Basics.

The Flash Recovery Area

To simplify the management of backup and recovery related files, Oracle enables you to create a flash recovery area for your database. You must designate the following:

• A location, which is typically a directory on disk

• A maximum disk quota for the flash recovery area

• A retention policy to specify your database recoverability goals

You can then direct backup-related activities, including archiving of redo logs, to store generated files in the flash recovery area. Oracle Database automatically manages this storage, deleting files that are no longer required on disk to meet your recoverability objectives if space is required for other files. A backup moved to tape is eligible for automatic deletion from the flash recovery area. Periodically copying backups to tape frees space in the flash recovery area for other files.

A flash recovery area simplifies backup storage management tasks. Therefore, it is strongly recommended. Except as noted, examples in this chapter assume the use of a flash recovery area.

The RMAN Repository

RMAN maintains a record of all database files, backup and recovery files, and backup history for your database. This record is referred to as the RMAN repository.

Every backup action that you perform through RMAN or Enterprise Manager is recorded in the repository, along with the location of and other metadata about every backup created by RMAN on disk or tape. If you back up a file without using RMAN, such as by copying the file on disk at the host operating system level, then you can add information about that copy to the RMAN repository as well. At recovery time, you can issue a command such as RESTORE DATABASE. Oracle uses the records in the repository to select backups on disk and tape needed to complete the recovery.

The primary location for the RMAN repository is in the database control file. This is one more reason why protecting your control file is a vital part of your backup strategy. In some installations, a second copy of the RMAN repository for one or more Oracle databases is stored in a set of tables called the recovery catalog. The recovery catalog is located in a separate Oracle database. Use of a recovery catalog is optional and is beyond the scope of this book.

Retention Policy and the Flash Recovery Area

Space management in the flash recovery area is governed by a backup retention policy. A retention policy determines when files are obsolete, meaning that they are no longer needed to meet your data recovery objectives. Retention policies can be based on redundancy of backups or on a recovery window.

Under a redundancy-based policy, the flash recovery area considers a backup of a file obsolete only when the RMAN repository has records of a specified number of more recent backups of that file. For example, assume your policy requires that two backups of each file be kept. You make backups nightly starting on a Monday night. After the Wednesday night backup succeeds, the Monday night backup becomes redundant because the Tuesday and Wednesday backups are available.

Under a recovery window-based policy, you specify a time interval measured in days. Files become obsolete only when they are no longer needed for successful complete recovery or point-in-time recovery to any point within that number of days into the past. For example, assume that you specify a recovery window of three days. A backup of all datafiles from at least three days ago must be retained, along with a full set of archived redo logs generated since that backup.

A redundancy-based retention policy makes it easier to predict space usage in the flash recovery area, but it does not allow you to predict how far into the past you can recover your database. A recovery window-based policy offers better protection for your data, but can make storage requirements for backups harder to predict. As already noted, a poorly designed backup strategy can cause unexpectedly high space requirements, even with a short recovery window. Oracle recommends the use of a recovery window-based retention policy as part of a well-designed backup strategy.

Even after files in the flash recovery area are obsolete, they are generally not deleted from the flash recovery area until space is needed to store new files. As long as space permits, files recently moved to tape will remain on disk as well, so that they will not have to be retrieved from tape in the event of a recovery.

The automatic deletion of obsolete files and files moved to tape from the flash recovery area makes the flash recovery area a very convenient redo log archiving destination. Other archiving destinations require manual cleanup of archived redo logs no longer needed on disk for recovery.

Sizing the Flash Recovery Area

An approach for sizing the flash recovery area is described at length in Oracle Database Backup and Recovery Basics, but as a general rule, the larger the flash recovery area, the more useful it becomes. Ideally, the flash recovery area should be large enough to hold a copy of all of your datafiles and control files, the online redo logs, and the archived redo log files needed to recover your database using the datafile backups kept under your retention policy.

If your backup strategy includes incremental backups, which are described in "Incremental Backups of Datafiles", then add enough space to the flash recovery area to accommodate these files as well. If you can move some backups to tape, then you can reduce the size of the flash recovery area somewhat. Note that retrieving those files from tape will cause longer database restore and recovery times.

Preferred Credentials for Backup and Recovery

The Host Credentials form always includes a checkbox labelled Save as Preferred Credential. If you check this box before performing your action, then the provided credentials are stored persistently for the currently logged-in Oracle user. The preferred credentials are reused by default whenever you log in as that user and perform operations requiring host credentials.

Understanding Backup Device Settings for Disk

The settings on the Device property page of the Backup Settings page affect how backups will be written to disk and to tape. For disk-based backups, you can configure the default format for storing backups, the location on disk where backups are stored, and whether backup tasks are run in parallel for improved performance.

Configuring Backup Policy Settings

From the Backup Settings page, click Policy. On the Policy property page, you can set the backup policies governing control file and SPFILE backups, tablespaces to exclude from whole database backup, and the backup retention policy.

Full Backups of Datafiles

A full backup of a datafile is a backup that includes all used blocks of the datafile. This backup can be either an image copy backup, which is an exact copy of the datafile as if copied with a host operating system file copy command, or a backup set created by RMAN. Regardless of the form in which the backup is stored, the entire datafile is backed up, even if only a few blocks have changed.

Incremental Backups of Datafiles

Incremental backups capture only those blocks that change between backups in each datafile. In a typical incremental backup strategy, a level 0 incremental backup is used as a starting point. A level 0 backup captures all blocks in the datafile.

Subsequent level 1 incremental backups, typically made at regular intervals such as once each day, capture images of each block in a datafile that changed. Level 1 backups can be cumulative, in which case all blocks changed since the most recent level 0 backup are included, or differential, in which case only those blocks changed since the most recent level 0 or level 1 incremental backup are included.

Recovering changed blocks from incremental backups is used to improve media recovery performance. Because an incremental level 1 backup captures the final contents of all datafile blocks changed during the period covered by the incremental, the recovery process can skip reapplying individual updates from the redo logs of that period and simply update each block with its final contents. The redo logs are only used for changes from the period not covered by level 1 incremental backups.

Using Tags to Identify Backups

All RMAN backups, including incremental backups, can be labelled with a tag. A tag is a text string identifying that backup, either uniquely or as part of a group of backups. For instance, if you performed a weekly full database backup on Saturday nights, you could use the tag FULL_SATURDAY to identify all such backups. You can use these tags for referring to specific backups in RMAN commands; for example, you could issue a command to move the latest FULL_SATURDAY backup to tape.

Because you can use tags to refer to different groups of backups, you can create different routines in your overall backup strategy that do not interfere with each other. When you schedule a backup job and give the job a name, the job name is used to tag the backup.

Performing a Whole Database Backup with Oracle Enterprise Manager

Whole backups of a database are based on backing up the entire contents of the database at the time of backup. Full backups of all datafiles are created. The results may be stored as image copies or as backup sets, but in either case the complete contents of all datafiles of the database are represented in the backup, as well as the control file, archived redo log, and server parameter file. With this set of files, you can perform complete recovery of the database.

While whole database backups can be an important element in your overall backup strategy, they are also a required step in some situations, such as when you switch ARCHIVELOG mode on or off (see "Configuring ARCHIVELOG Mode For the Database").

To perform a whole database backup, follow these steps:

1. In the Backup/Recovery section of the Maintenance page, click Schedule Backup.

   The Schedule Backup page appears, as shown in .

   Figure 9-1 Schedule Backup Page

   
   Description of the illustration 10gR2_backup_strategy.gif
   
   

   The following sections of this page are important:

   • The Oracle-Suggested Backup section, where you can click Schedule Oracle-Suggested Backup to choose among backup strategies recommended by the database

   • The Customized Backup section, where you can schedule a one-time or repeated backup of database objects you select.

2. In the Customized Backup section, select Whole Database to make a full backup of your database immediately or schedule one as part of a user-designed backup strategy. Make sure the Username and Password fields are correct in the Host Credentials section and then click Schedule Customized Backup.

   The Schedule Customized Backup: Options page appears. In this page you specify the options for this whole database backup.

3. In the Backup Type section, select Full Backup. In the Backup Mode section, select either Online Backup or Offline Backup. Typically, you will want to perform online backups to maximize database availability.

   
   

   Note: As explained in "Configuring Your Database for Basic Backup and Recovery", you can only use online backups if your database is set up to run in ARCHIVELOG mode. In NOARCHIVELOG mode you can only perform offline backups.

   
   

   In the Advanced section, make the following selections:

   • Check Also back up all archived logs on disk if you are performing an online backup. There is no need to back up archived logs when performing an offline backup because the database is in a consistent state at the time of backup and does not require media recovery if you restore from this backup. Nevertheless, you can include archived logs in the backup if you wish.

   • Do not check Delete all archived logs from disk after they are successfully backed up if a flash recovery area is your only archiving destination. In this case, redo logs that have been backed up are deleted automatically as space is needed for storage of other files. If you are using some other destination, it may be useful to check this option as part of managing backup storage.

   • Do not check Use proxy copy supported by media management software to perform a backup for now.

   • Do not check Delete obsolete backups if you use a flash recovery area for backup storage. In this case, obsolete backups are deleted automatically as space is needed for storage of other files. If you are using some other destination, it may be useful to check this option as part of managing backup storage.

   • Do not enter a value for Maximum Files per Backup Set.

   After making your selections, click Next.

   The Schedule Customized Backup: Settings page appears.

4. Select a backup destination. Oracle recommends backing up to disk when possible to minimize recovery time by minimizing restores from tape. Backups created on disk can be moved to tape later. Click Next.

   The Schedule Customized Backup: Schedule page appears.

5. Specify identifying information for the backup job (including a tag and a description for your reference), and specify when the tasks for this backup job are performed:

   • In the Job section of the page, you can enter values for Job Name and Job Description. Default values for the name and description are generated for you. If you want to provide a tag for this backup, however, then enter the desired tag in the Job Name field. The job name is used as a prefix for the backup tag for backups produced by this job.

     You can set Job Description to any descriptive text that is useful for your own reference.

   • In the Schedule section, specify when to start the backup and how often to repeat it. Leave the default start time of Immediately selected to run a backup immediately, or set Later and enter a time in the future. For recurring backup jobs, select options in the Repeat and Repeat Until sections. For one-time backups, select One Time Only and Indefinite.

     
     

     Note: For recurring jobs, it is useful to set a Job Name so that the resulting backups are easy to identify as part of an ongoing series. Use a descriptive tag, such as WEEKLY_FULL_BACKUP.

     
     

     When finished, click Next.

     The Schedule Customized Backup: Review page appears. This page presents a complete description of the backup job you specified in the previous pages.

6. You can perform one of the following actions:

   • Click the Back button to change these options.

   • Click Edit RMAN Script to view and, if desired, edit the RMAN commands that will be executed to perform your specified backup jobs.

   • Click Submit Job to add the specified backup job to the schedule (or to run it immediately, if you specified that the job should run immediately).

   • Click Cancel to stop the scheduled backup.

   In this example, click Submit Job.

   The Status page appears. This page should contain a message indicating that the job was successfully submitted.

7. Click View Job to monitor the progress of the backup job.

   The Execution page appears. This page contains a Summary section describing the job. The Logs section includes a table listing the progress of the various steps of the backup job. You can reload this page in your browser to monitor the ongoing progress of the job. In the Name column of the table in the Logs section, you can see what phase the RMAN job is in. Clicking on the name of the phase of the backup displays a page containing the RMAN output for that part of the job. From this page, click the Back button in your browser to return to the Execution page.

Using the Oracle-Suggested Backup Strategy

Enterprise Manager makes it easy to set up an Oracle-suggested backup strategy for backups to disk that protects your data and provides efficient recoverability to any point in the a recovery window of your choosing. (In the simplest case, examined in this section, this window is 24 hours.) The Oracle-suggested strategy leverages Oracle's incremental backup and incrementally-updated backup features to provide faster backups than whole database backups, and faster recoverability than is possible through applying database changes from the archived log to your datafiles.

The Oracle-suggested backup strategy is based on creating an image copy of your database. This copy is rolled forward by means of incrementally updated backups. Oracle Enterprise Manager schedules RMAN backups jobs for you to run during the overnight hours.

For each datafile, the strategy calls for backups as follows:

• At the beginning of day 1 of the strategy (the time the first scheduled job actually runs), an incremental level 0 datafile copy backup. It contains the datafile's contents at the beginning of day 1.

  In a restore-and-recovery scenario, the redo logs from day 1 can be used to recover to any point during day 1.

• At the beginning of day 2, an incremental level 1 backup is created, containing the blocks changed during day 1.

  In a restore-and-recovery scenario, this incremental level 1 can be applied to quickly roll forward the level 0 backup to the beginning of day 2, and redo logs can be used to recover to any point during day 2.

• At the beginning of each day n for days 3 and onwards, the level 1 backup from the beginning of day n-1 is applied to the level 0 backup. This brings the datafile copy to its state at the beginning of day n-1. Then, a new level 1 is created, containing the blocks changed during day n-1.

  In a restore-and-recovery scenario, this incremental level 1 can be applied to the datafile rolled forward on day n-1 to the beginning of day n, and redo logs can be used to recover the database to any point during day n.

The datafile copies used in the Oracle-suggested backup strategy are tagged with the tag ORA$OEM_LEVEL_0. The level 1 incremental backups for use in this strategy are created for use with datafile copies that are so labelled. You can safely implement other backup strategies without concern for interference from the backups for the Oracle suggested strategy.

There are also Oracle-suggested strategies that use tape backups along with disk backups, but those are beyond the scope of this chapter.

Scheduling Other Backup Tasks

After taking some time to understand the full range of available backup options as described in Oracle Database Backup and Recovery Basics, you may decide to schedule backup tasks beyond those used to implement the Oracle-suggested backup strategy.

While the particulars of specifying the job to perform differ for each type of backup, all backups begin from the Schedule Backup page shown in Figure 9-1. Here you can select any of several object types to back up. You can also back up existing backups in one destination to some other destination, such as moving backups from disk to tape.

Click Schedule Customized Backup to continue to the pages where you specify details such as objects to back up, required options and settings. The choices presented on these pages are determined by the type of objects being backed up. On each page, after you have made your selections, click Next to proceed to the next page.

After you have specified the options, you reach the Schedule Customized Backup: Schedule page, where you specify the times at which the job is to be performed, as well as the Job Name and Job Description.

When you are done with the job name, description and schedule, click Next to move on to the Schedule Customized Backup: Review page. After reviewing the options, either click Back to make changes, click Edit RMAN Script to make changes to the script, or click Submit Job to add the job to the schedule.

Recovering from a Lost SPFILE or Control File

Recovery from loss of the control file or SPFILE must be started with the database shut down. Note that the instance cannot be running if the control file is lost.

Typically, diagnosis of the lost control file occurs when you attempt to start the database and the startup fails. The basic steps are as follows:

1. From the database home page, c lick Startup to attempt to start the database.

2. If the startup fails, click View Details to view the reason.

3. If the cause is a failure to locate the SPFILE or control file, return to the database home page and click Perform Recovery.

4. From this point, Enterprise Manager's Guided Recovery walks you through the process to restore the lost files.

After the control file is restored from backup, the database is mounted. When a control file restored from backup is used to start the database, you must perform complete recovery of the datafiles, even if none of the datafiles have been restored from backup. The database must be opened with the RESETLOGS option after the datafiles are recovered.

To perform complete recovery of the datafiles, use the following steps:

1. On the Perform Recovery page, select Recover to the current time or a previous point in time and click Perform Whole Database Recovery.

2. On the Perform Whole Database Recovery: Rename page, select Restore files to the default location and click Next.

3. On the Perform Whole Database Recovery: Review page, click Submit to start the media recovery process.

When recovery is complete, you are prompted to open the database with the RESETLOGS option.

Enabling Row Movement on a Table

To enable row movement on a table, or if you do not know whether row movement is enabled on the table, follow these steps:

1. In the Database Objects section of the Administration page, click Tables to administer tables.

   The Tables page appears.

2. To find the target table for flashback table, you can enter one or both of the schema name in the Schema field and the table name in the Object Name field. Then click Go to search for the table. For example, search for tables in the hr schema. You may need to page through the search results to find your table.

3. After you find your table in the schema, select the table from the list of tables. For example, select employees. Click Edit.

   The Edit Table: table_name page appears.

4. Click Options to navigate to the Options property page. Make sure Enable Row Movement is set to Yes, and click Apply to update the options for the table.

When the page has refreshed, you can click Tables in the locator link at the top of the page to return to the search results, and enable row movement on more tables by repeating these steps for each table.

Performing Flashback Table

To perform the Flashback Table operation:

1. In the Backup/Recovery section of the Maintenance page, select Perform Recovery.

   The Perform Recovery page appears.

2. In the Object Level Recovery section, select Tables for the object type. The page reloads with options appropriate for object level recovery of tables. Choose the Flashback Existing Tables option and click Perform Object Level Recovery.

   The Perform Object Level Recovery: Point-in-time page appears.

3. Choose the target time for your Flashback Table operation.

   
   

   Note: If you do not know the time at which the unwanted changes occurred, you can investigate the history of transactions affecting this table by choosing Evaluate row changes and transactions to decide upon a point in time. A feature called Oracle Flashback Versions Query lets you review all recent changes to the target table. Use of this feature is beyond the scope of this manual.

   
   

   For this example, assume that the time of the corruption is known to be October 23, 2005, 15:36:00. In the form offered, select Flashback to a timestamp, and enter your target time. Click Next to continue with the Flashback Table process.

   The Perform Object Level Recovery: Flashback Tables page appears.

4. Specify the target tables for Flashback Table by entering table names (one on each line) in the Tables to Flashback text box. You can enter multiple table names to flash several tables back to the same time. You can also click Add Tables and search for more tables to add. For this example, manually enter the hr.employees table in the Tables to Flashback text box. Click Next to continue with the Flashback Table process.

   If your table has other dependent tables, then the Dependency Options page appears. This page asks how dependencies should be handled.

5. You can choose Cascade (flashing back any dependent tables), Restrict (flashing back only the target table), or Customize (selecting which dependent tables to flashback and which to leave as they are). You can click Show Dependencies to see which tables will be affected. How you proceed at this point will depend upon your application.

   hr.employees has dependent tables hr.jobs and hr.departments. For this example, assume that it is safe to cascade any changes, flashing back those two tables as well as the hr.employees table.

   
   

   Note: Row movement must be enabled on all affected tables, not just the initial target tables.

   
   

   Click Next to continue.

   The Perform Object Level Recovery: Review page appears.

6. Review the target timestamp and tables to be flashed back. Click Submit to perform the Flashback Table operation.

   When the operation is completed, a Confirmation page displays the results. Click OK to return to the database home page.

Searching for Backups on the Manage Current Backups Page

The Search section of each property page lets you restrict the listed backups based on the following:

• Backup status (available, unavailable, or expired)

• The types of files contained in the backup (archived log, datafile, control file, or SPFILE)

• How recently the backup was completed

By default, all available backups from the last month are listed. Specify criteria as necessary to filter the backup list, and click Go to search the backups.

The Results section of the page lists backups that match the criteria specified in the Search section. Different details are listed for backup sets (on the Backup Sets property page) and for image copies (on the Image Copies property page).

Managing Current Backups: Backup Sets

On the Backup Sets property page, backup sets are identified by the unique value in the Key column (an internally generated number used to identify the backup set in RMAN commands) as well as the value in the Tag column. For each backup, the page lists information such as the type of files backed up, the destination device type, completion time of the backup, and current status.

You can click the value in the Tag column for a given backup set to view details about the backup set, including:

• The input files backed up (listed in the Input Files section of the page), and details about each input file, including the original file location, size, and file checkpoint time and SCN when the backup was taken

• The backup pieces produced as output by the backup job, including the output filename, tag, destination device type, size, and status

Managing Current Backups: Image Copies

On the Image Copies property page, image copies are identified by the unique value in the Key column (an internally generated number used to identify the backup set in RMAN commands) as well as the filename in the Name column. For each backup, the file type, tag, completion time and status are also listed. This page also lists the status of each image copy (available, unavailable, or expired) and a Keep column, which shows whether special exceptions to the retention policy for backups apply to this file.

By clicking the value in the Name column, you can also view details about that file, such as datafile number, file size, tablespace name, creation SCN, and last checkpoint SCN and time when the file was backed up.

You can also crosscheck or delete individual backups, or mark individual backups as unavailable if you know that they are temporarily not accessible by RMAN. For example, if you know that some backups are stored on tapes that have been moved offsite for long-term storage, you can mark them as unavailable to prevent RMAN from considering them usable in restore operations. Use the Select checkbox next to the file, and click the appropriate action button at the top of the Results list.

The Image Copy property page presents similar functionality to the Backup Sets property page. The focus in this section is on commands in the Backup Sets property page, where they are substantially similar to those for image copies.

As with the backup and restore commands in Enterprise Manager, the commands to crosscheck, delete and change the status of backups are ultimately translated to RMAN commands. The commands are submitted as RMAN jobs that can be run immediately or scheduled. Some tasks, such as periodic crosschecks of your backups, should be among the regularly scheduled components of your backup strategy.

Validating the Contents of Backup Sets or Image Copies

When you validate a backup, you ensure that the files for the backup are present, have not been corrupted, and can be used in recovery operations. The selected backup files are read in their entirety and checked for errors.

To validate whether specific backups are usable in a restore and recovery operation:

1. In the Backup/Recovery section of the Maintenance property page, click Manage Current Backups.

   The Manage Current Backups page appears.

2. Use the search features on this page to identify the backups whose contents you wish to validate, as described in "Using the Manage Current Backups Page".

3. Select the checkboxes next to each backup of interest in the list of current backups and click Validate.

   The Validate: Specify Job Parameters page appears.

4. Specify a job name and description, as well as time settings for starting and repeating the operation. You can click Show RMAN Script to view the RMAN commands used to perform your validation or Submit Job to start the job running according to its schedule.

   A message confirms the submission of the job.

5. Click View Job to display details about the validation.

This process corresponds to the RMAN VALIDATE command. See Oracle Database Backup and Recovery Basics for details on the use of the RMAN VALIDATE command.