9 Performing Backup and Recovery

Consistent and Inconsistent Backups

A backup can be consistent or inconsistent. A consistent backup is one made 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 process.

An inconsistent backup, by contrast, is a backup made while the database is open. In an inconsistent backup, the 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, and 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 taking inconsistent backups is that you can back up your database while the database is open. Users can continue to update the database during the backup process.

Media Recovery

If you restore the archived redo logs and the datafiles from backup, Oracle will perform the media recovery process when you try to 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. It is most often used to recover from a media failure, such as the loss of a file or disk, or a user error, like the deletion of the contents of a table. There are two forms of media recovery: complete recovery or point-in-time recovery.

In complete recovery datafiles are restored from backup, and 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. Starting 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, Oracle applies to the datafiles each change between the backup time and the target time. 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 have chosen to not recover the complete set of changes to your database.

Enterprise Manager provides a convenient interface to both complete and point-in-time recovery. Complete recovery will be the focus of the examples in this book. The concepts behind point-in-time recovery and detailed examples are discussed at more length in Oracle Database Backup and Recovery Advanced User's Guide.

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 you perform through RMAN or Enterprise Manager is recorded in the repository, along with the location of and other information 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, you can add information about that copy to the RMAN repository as well. At recovery time, you can simply issue a command like RESTORE DATABASE and Oracle uses the record in the repository to select backups on disk and tape needed to complete the recovery.

The primary store for the RMAN repository is in the control file for your database. 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 separate Oracle database called the recovery catalog. Use of a recovery catalog is optional and is beyond the scope of this book.

The Flash Recovery Area

To simplify the management of backup and recovery related files, Oracle lets you create a flash recovery area for your database. Designate a location (typically, a directory on disk) and a maximum disk quota for the flash recovery area, and a retention policy to specify your database recoverability goals. You can then direct backup-related activities (including archiving of redo logs) to store their files there. Oracle automatically manages this storage, deleting files that are no longer required on disk to meet your recoverability objectives. (A backup moved to tape is eligible for automatic deletion from the flash recovery area, but the record of the copy on tape remains in the RMAN repository for use in restore and recovery operations.)

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

Retention Policy and the Flash Recovery Area

Space management in the flash recovery area is governed by a backup retention policy. 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 to be obsolete only when the RMAN repository has records of a specified number of more recent backups of that file. For example, if you specify a retention policy requiring two backups of each file be kept, and make backups nightly starting on a Monday night, then 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, and 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, if 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 since that backup.

A redundancy-based retention policy makes it easier to predict space usage in the flash recovery area, but do 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 backup retention policy as part of a well-designed backup strategy.

Even after they are obsolete, files 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, described in "Incremental Backups of Datafiles", add enough space to the flash recovery area to accommodate these files as well. If you can move some backups to tape, you can reduce the size of the flash recovery area somewhat, although retrieving those files from tape will cause longer database restore and recovery times.

Understanding Backup Device Settings for Disk

The settings available on the Device property page of Configure Backup Settings 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, choose the Policy property page. On this 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 which includes all used blocks of the datafile. This 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 into 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 are based around capturing only those blocks that change between backups in each datafile of your database. In a typical incremental backup strategy, a level 0 incremental backup, capturing all blocks in the datafile, is used as a starting point. Subsequent level 1 incremental backups, typically made at regular intervals such as once per 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 performance of the media recovery process. Since 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.

Incrementally Updated Backups: Rolling Forward Image Copies of Datafiles

The incrementally updated backups feature of Oracle lets you use one or more level 1 incremental backups with an older image copy backup of your datafiles, to roll the copy forward to the point in time of the last level 1 incremental backup. All blocks changed since the image copy was created are overwritten with their new contents as of the time of the last level 1 incremental backup. The effect is to roll the file forward in time, so that its contents are equivalent, for the purposes of database recovery, to an image copy full datafile backup made at the time of the last incremental level 1 backup. Incorporating incrementally updated backups into your backup strategy shortens expected recovery times, because media recovery to the present time or a point in time in the recent past can begin at the time of the last level 1 backup applied, rather than the time of the last full database backup.

Tagging Backups

All RMAN backups, including incremental backups, can be tagged with a label, 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.) These tags can be used 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, they are useful if you want to create several different routines in your overall backup strategy which 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, the database can be recovered completely.

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.

To perform a whole database backup, follow the steps presented here:

1. Go to the Maintenance property page and under the Backup/Recovery column, choose Schedule Backup. The Schedule Backup: Strategy page shown in Figure 9-1, "Schedule Backup: Strategy Page" appears:

   Figure 9-1 Schedule Backup: Strategy Page

   
   Description of the illustration backup_strategy.gif
   
   

   The first field is a Backup Strategy field, where you can choose between Oracle-suggested (the default) and Customized. For a whole database backup, choose Customized.

2. The page refreshes itself, to offer a choice of objects to back up and a space to enter host credentials. Select Whole Database as the set of objects to back up. Enter your host operating system credentials if required, and click Continue.

3. On the Schedule Backup: Options page, you specify the options for this whole database backup. Under Backup Type, choose Full Backup. For Backup Mode, choose between an online and 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.

   Under the Advanced section, if this is an online backup, check the box to back up all archived logs. (There is no need to back up archived logs when performing an offline backup, as the database will be in a consistent state at the time of backup and does not require media recovery if you restore from this backup.) For now, leave the option for proxy copies unchecked. Do not specify a maximum number of files for each backup set. Click Next to move on to the Backup: Settings page.

4. On the Schedule Backup: Settings page, select the appropriate backup destination. Oracle Corporation recommends backing up to disk, to minimize recovery time. Then, click Next to move on to the Schedule Backup: Schedule page.

5. On the Schedule Backup: Schedule page, enter the schedule for the backup job, or specify to perform it immediately. A Job Name is generated for you, but if you want to tag this backup, you can edit the assigned job name, which is used as the tag. You can also edit the Job Description as needed for your own reference. Then, in the Schedule fields, you 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 later time. For jobs to be run regularly, set the Repeat and Repeat Until parameters as appropriate. (You probably want to change the Job Name in this case, to identify these scheduled backups as part of an ongoing series, such as WEEKLY_FULL_BACKUP. When finished, click Next to move on to the Schedule Backup: Review page.

6. On the Schedule Backup: Review page, you have one last chance to use the Back button and change these options.You can also click Edit RMAN Script to see 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).

To monitor the progress of the backup job, you can click View Job.You will arrive on a page that shows a summary of the job submitted, and a Logs section where you can follow the progress of the various steps of the backup job and drill down to see the RMAN output from the running job.

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 which is rolled forward using 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 to be made 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 document.

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, whether performed immediately or scheduled, begin from the Schedule Backup: Strategy page, shown in "Performing and Scheduling Backups with Enterprise Manager". Here you can select any of several object types to back up: the whole database, individual tablespaces, datafiles, or archived logs. (You can also back up existing backups in one destination to some other destination, such as moving backups from disk to tape.) For any object you select to back up, there will be options governing how that backup is made. These will, however, differ by the type of object being backed up.

Next, Enterprise Manager displays the Schedule Backup: Schedule page. You can either schedule a job to run once, immediately or at a future time, or set it to run multiple times, using the options in this form.

If you schedule a regular backup task as part of a strategy of your own devising, it is recommended that you give the resulting backup job a distinctive name, such as WEEKLY_FULL_BACKUP or DAILY_SALES. The tags for all backups produced as part of this strategy consist of the job name with a timestamp appended. The common tag will permit you to more easily refer to the backups from this strategy during recovery activities.

Recovering from a Lost SPFILE or Control File

The process for recovering a database with a lost SPFILE or control file is similar to recovery of all datafiles.

Recovery from loss of an SPFILE or control file must be started with the database down. However, you do not have to shut down the database at the beginning of the procedure, because the database cannot be open if the SPFILE or control file is missing.

When you try to start an Enterprise Manager session to your database in this situation, Enterprise Manager displays the warning that the database state is unavailable, and you must choose between Startup and Perform Recovery. Because you do not know why your database is down, click Startup. Enterprise Manager attempts to open the database. In the process, it will detect a lost or unusable SPFILE or control file. If the startup fails, choose Perform Recovery and Enterprise Manager will prompt you to restore the lost files. If you are not using a recovery catalog, you will have to restore these files from autobackup. For this process, you will need the DBID and DB_UNIQUE_NAME you recorded in "Recording Your DBID and DB_UNIQUE_NAME". You may also need to provide Enterprise Manager the location where you directed control file autobackups, which can be the location of the flash recovery area or some other location on disk of your choosing.

Enabling Row Movement on a Table

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

1. From the database home page, choose the Administration page. Then, under Schema, click Tables to administer tables. To find the target table for flashback table, you can enter one or both of the schema name in the Type field and the table name in the Object Name field. Then click OK to search for the table. You may need to page through the search results to find your table.

2. After you find your table in the schema, click the name of the table in the Table Name column of the search results. Enterprise Manager displays a page with several property pages for administering different aspects of the table. Select the Options property page.

3. 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, use the following steps:

1. From the Maintenance property page of the database home page, under the Backup/Recovery heading, select Perform Recovery, and on the Perform Recovery: Type page, select Tables for the object type. Choose the Flashback Existing Tables option and click Continue.

2. On the Perform Recovery: Point-in-time page, you must choose the target time for your Flashback Table operation. 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. However, the 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 Oct. 3, 2004, 11:53AM. In the form offered, select Flashback to a timestamp, and enter your target time. Click Next to continue with the Flashback Table process.

3. Now, on the Perform Recovery: Flashback Tables page, specify the target tables for Flashback Table, by entering table names (one on each line) in the Tables to Flashback text box. 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.

4. If your table has other dependent tables, you next see the Dependency Options page, where you are asked how they should be handled. Your choices are 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 that row movement must be enabled on all affected tables, not just the initial target table. Click Next to continue. The next page to appear is the Perform Recovery: Review page, where you have a chance to confirm the Flashback Table operation to be performed. The target timestamp and tables to be flashed back are displayed. Click Submit to perform the actual Flashback Table operation. When the operation is completed, a Confirmation page appears announcing the results. Click OK to return to the database home page.