9 Performing Backup and Recovery

Consistent and Inconsistent Backups

A backup is either consistent or inconsistent. To make a consistent backup, your database must have been shut down cleanly and remain closed for the duration of the backup. All committed changes in the online redo log files are written to the data files during the shut down process, so the data files are in a transaction-consistent state. When you restore your data files from a consistent backup, you can open the database immediately.

If the database is in ARCHIVELOG mode, then you can make inconsistent backups that are recoverable using archived redo log files. Open database backups are inconsistent because the online redo log files contain changes not yet applied to the data files. The online redo log files must be archived and then backed up with the data files to ensure recoverability.

Despite the name, an inconsistent backup is as robust a form of backup as a consistent backup. The advantage of 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 log files and data files, then you must perform media recovery before you can open the database. Any database transactions in the archived redo log files not reflected in the data files are applied to the data files, bringing them to a transaction-consistent state before the database is opened.

Media recovery requires a control file, data files (typically restored from backup), and online and archived redo log files containing changes since the time the data files were backed up. Media recovery is most often used to recover from 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.

Media recovery can be a complete recovery or a point-in-time recovery. In a complete recovery, you restore backup data files and apply all changes from the archived and online redo log files to the data files. The database is returned to its state at the time of failure and can be opened with no loss of data.

In a point-in-time recovery, you return a database to its contents at a user-selected time in the past. You restore a backup of data files created before the target time and a complete set of archived redo log files from backup creation through the target time. Recovery applies changes between the backup time and the target time to the data files. All changes after the target time are discarded.

Oracle Enterprise Manager Database Control (Database Control) provides an interface to both a complete and a point-in-time recovery in the form of a Recovery wizard. However, this documentation focuses on complete recovery.

Fast Recovery Area

To simplify the management of backup and recovery files, you can create a fast recovery area for your database. The fast recovery area is an Oracle-managed directory, file system, or Oracle Automatic Storage Management disk group that provides a centralized disk location for backup and recovery files. Oracle creates archived logs and flashback logs in the fast recovery area. RMAN can store its backup sets and image copies in the fast recovery area, and it uses it when restoring files during media recovery. The fast recovery area also acts as a disk cache for tape.

Oracle Database automatically manages this storage, deleting files that are no longer needed. Periodically copying backups to tape frees space in the fast recovery area for other files. Oracle recommends that you enable a recovery area to simplify backup management. Except as noted, this documentation assumes the use of a recovery area.

RMAN Repository

RMAN maintains a record of database files and backups for each database on which it performs operations. This metadata is called the RMAN repository.

If you back up a file without using RMAN, such as by copying the file at the host operating system level, then you can add metadata about the copy to the RMAN repository. If you later use a command such as RESTORE DATABASE, then RMAN uses the records in the repository to select backups needed for the recovery.

The primary location for the RMAN repository for a database is its control file. The importance of this metadata for RMAN 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 is stored in a schema called the recovery catalog. The recovery catalog is located in a separate database and can store metadata for multiple databases. Use of a recovery catalog is optional and is beyond the scope of this documentation.

Specifying Credentials for Backup and Recovery Using Database Control

You must have the proper credentials to perform some configuration tasks for backup and recovery, and to schedule backup jobs and perform recovery. The following credentials may be required:

• The Oracle Database user you specify when you log in to Oracle Enterprise Manager Database Control (Database Control)

• The host operating system user whose credentials you provide when performing backup and recovery tasks

To enter credentials for backup and recovery tasks:

1. Log in to Database Control as a database user with SYSDBA privileges, or provide host operating system credentials for a user in the dba group on UNIX or Linux, or the ora_dba group on Microsoft Windows.

   The host operating system user must also have execute permission for the RMAN command-line client.

   Note:

   The host user may require certain host privileges to run background jobs such as database backups. For example, on UNIX and Linux, the host user must belong to the OSDBA group (typically dba), and on Windows, the host user must be a member of the Administrators group and must be granted the Log on as batch job logon right. See your platform documentation for more information.

   For tasks requiring host operating system credentials, a Host Credentials form appears at the bottom of the page used to perform the task. Enterprise Manager uses the credentials when it starts RMAN jobs that you requested or scheduled.

2. Optionally, in the Host Credentials form, select Save as Preferred Credential.

   If you select this option before performing your action, then the provided credentials are stored persistently for the currently logged-in Oracle Database user. The preferred credentials are reused by default whenever you log in as that user and perform operations requiring host credentials.

   Note:

   In situations in which the database is shut down, you may still be prompted for host credentials even if you have saved the preferred credentials.

Planning Space Usage and Location for the Fast Recovery Area

You should place the fast recovery area on a separate disk from the working set of database files. Otherwise, the disk becomes a single point of failure for your database.

The amount of disk space to allocate for the fast recovery area depends on the size and activity levels of your database, which determine the size of your data files and online redo log files in addition to your recovery objectives. Your objectives dictate what kinds of backups you use, when you make them, and how long to keep them.

Configuring Recovery Settings

In the Recovery Settings page, you can configure settings for instance recovery, media recovery, and fast recovery. In this section, you configure the fast recovery area and enable archiving for the database.

You can configure a fast recovery area when first creating the database. If you did not perform this task at database creation time, however, then you can create a fast recovery area for your database now.

To configure a fast recovery area and put the database in ARCHIVELOG mode:

1. On the host operating system, create a directory to hold the fast recovery area. If you are using Oracle ASM for your storage, then create a new disk group.

   Make sure that the operating system permissions for the directory or disk devices allow the database to create files.

2. Log in to Enterprise Manager Database Control as user SYS.

3. On the Database Home page, click Availability to display the Availability subpage.

4. In the Backup/Recovery section, click Recovery Settings.

   The Recovery Settings page appears.

5. Complete the following steps:

   1. In the Media Recovery section, select ARCHIVELOG Mode.

   2. If USE_DB_RECOVERY_FILE_DEST is not set as an archiving destination, then set it.

      This initialization parameter indicates that the fast recovery area should be an archiving destination.

      For ease of database management, the best practice is to use the fast recovery area as your only archiving destination.

   3. In the Fast Recovery section, enter the path to the fast recovery area created in Step 1 in Fast Recovery Area Location, and select a value for Fast Recovery Area Size.

   4. Select Enable Flashback Database.

      This option specifies that the database should generate flashback logs in the fast recovery area, enabling you to use Flashback Database. During usual operation, the database occasionally logs images of data blocks to the flashback logs. The database automatically creates, deletes, and resizes flashback logs.

   5. Ensure that the Apply initialization parameter changes to SPFILE only box is not selected.

   6. Click Apply to save your changes.

      A message prompts you to restart the database.

6. Click Yes.

   The Restart Database: Specify Host and Target Database Credentials page appears.

7. Enter your host and database credentials, and then click OK.

   See "Specifying Credentials for Backup and Recovery Using Database Control" for information about credentials.

8. On the Restart Database: Confirmation page, click Yes to begin the restart operations.

   You can click Refresh periodically to monitor the progress of the operation.

9. Make a consistent (that is, offline) backup of your entire database immediately after switching your database to ARCHIVELOG mode.

   Caution:

   You cannot use backups from before the switch to ARCHIVELOG mode to restore and recover the database to a point in time after the switch. Thus, if you do not immediately make a backup after switching, then you are running your database without a valid backup. See "Performing and Scheduling Backups Using Database Control" to learn how to make database backups.

Monitoring Fast Recovery Area Usage

It is important to monitor space usage in the fast recovery area to ensure that it is large enough to contain backups and other recovery-related files.

To monitor available space in the fast recovery area:

In the High Availability section of the Database Home page, click the link next to Usable Fast Recovery Area (%).

The Recovery Settings page appears.

The Reclaimable Recovery Area (GB) and Free Recovery Area (GB) settings indicate how much space is available.

Configuring Backup Settings

You can configure several backup-related settings and policies. For example, you can determine how backups are stored, which data is backed up, and how long backups are retained. You can also configure settings to optimize backup performance.

This section explains the concepts behind the available settings and also explains how to change them using the Backup Settings page in Database Control. The settings on the Device subpage affect how RMAN writes backups to disk and to tape.

RMAN Backup Concepts

This section provides the concepts you need to understand the Oracle suggested backup strategy and other backup types provided through Database Control.

Performing and Scheduling Backups Using Database Control

Database Control enables you to perform RMAN backups and schedule backup jobs required by your backup strategy.

Validating Backups and Testing Your Backup Strategy

As part of your backup strategy, you should periodically check whether your backups are intact and can be used to meet your recoverability objectives. You can validate your backups in the following ways:

• Select specific backup sets or image copies in Database Control and validate them. This technique indicates if a backup exists and can be restored. For this form of validation, use the Manage Current Backups pages as described in "Validating Selected Backups".

• Specify database files and let RMAN select backups to use when restoring those files, as it would for an actual restore operation. This technique ensures that your available backups are sufficient to restore the database. For this form of validation, use the Perform Recovery pages as described in "Validating Backups for Restore Operations".

  Note:

  Validating backups stored on tape can be time-consuming because the entire backup is read from tape.

You can set up both forms of validation as scheduled tasks in Database Control. You should incorporate both forms of validation into your backup strategy to ensure that your recoverability goals are met by your available backups.

About Backup Management

An essential part of a backup and recovery strategy is managing backups after you create them. Backup management includes deleting obsolete backups and performing periodic checks to ensure that backups are available and usable.

You can perform backup management tasks from the Manage Current Backups page.

To display the Manage Current Backups page:

1. On the Database Home page, click Availability to display the Availability subpage.

2. In the Availability page, in the Manage section, click Manage Current Backups.

This page has two subpages: Backup Sets and Image Copies. Both pages have similar purposes: listing backups based on the record in the RMAN repository and enabling you to manage the backups.

A backup recorded in the RMAN repository has one of the following status values:

• Available, meaning that the backup is still present on disk or tape, as recorded in the repository

• Expired, meaning that the backup no longer exists on disk or tape, but is still listed in the repository

• Unavailable, meaning that the backup is temporarily not available for data recovery operations (because, for example, it is stored on a tape that is stored offsite or on a disk that is currently not mounted)

Backups can also be obsolete. An obsolete backup is, based on the currently configured retention policy, no longer needed to satisfy data recovery goals.

Maintenance tasks that you can perform in Database Control include the following:

• Viewing details about your backups

• Cross-checking your repository, which means checking whether backups listed in the repository exist and are accessible, and marking as expired any backups not accessible at the time of the cross-check

• Deleting the record of expired backups from your RMAN repository

• Deleting obsolete backups from the repository and from the backup media

• Validating backups to ensure that a given backup is available and not corrupted

As with the backup and restore commands in Database Control, the commands to cross-check, delete, and change the status of backups are translated to RMAN commands. The commands are submitted as RMAN jobs that can be run immediately or be scheduled. Some tasks, such as periodic cross-checks of your backups, should be among the regularly scheduled components of your backup strategy.

If you use a fast recovery area for backup storage, then many maintenance activities are reduced or eliminated. The automatic disk space management mechanisms delete backups and other files as needed, thereby satisfying disk space demands for ongoing database operations without compromising the retention policy.

Cross-Checking Backups

Cross-checking a backup synchronizes the physical reality of backups with their logical records in the RMAN repository. For example, if a backup on disk was deleted with an operating system command, then a cross-check detects this condition. After the cross-check, the RMAN repository correctly reflects the state of the backups.

Backups to disk are listed as available if they are still on disk in the location listed in the RMAN repository, and if they have no corruption in the file header. Backups on tape are listed as available if they are still on tape. The file headers are not checked for corruption. Backups that are missing or corrupt are listed as expired.

To cross-check individual files:

1. On the Database Home page, click Availability to display the Availability subpage.

2. In the Manage section, click Manage Current Backups.

   The Manage Current Backups page appears.

3. Search for the backup sets or image copies whose contents you want to cross-check.

4. In the Results section, select each backup to be included in the cross-check operation.

   Database Control does not support selecting both image copies and backup sets for a cross-check within a single operation.

5. Click Crosscheck at the top of the Results list.

   A confirmation page is displayed. After you click Yes, Database Control performs the cross-check.

To cross-check all files:

1. On the Database Home page, click Availability to display the Availability subpage.

2. In the Manage section, click Manage Current Backups.

   The Manage Current Backups page appears.

3. In the Manage Current Backups page, click Crosscheck All.

   The Crosscheck All: Specify Job Parameters page appears. You can schedule the cross-check to run now or later, or specify regularly scheduled cross-checks.

4. Click Submit Job.

Deleting Expired Backups

Deleting expired backups removes from the RMAN repository those backups that are listed as EXPIRED. Expired backups are those found to be inaccessible during a cross-check. No attempt is made to delete the files containing the backup from disk or tape; this action updates only the RMAN repository.

To delete expired backups:

1. On the Database Home page, click Availability to display the Availability subpage.

2. In the Manage section, click Manage Current Backups.

   The Manage Current Backups page appears.

3. In the Manage Current Backups page, click Delete All Expired.

   This action deletes expired backup sets and expired image copies from the RMAN repository, regardless of which subpage is currently active.

   The Delete All Expired: Specify Job Parameters page appears.

4. Optionally, select Perform the operation 'Crosscheck All' before 'Delete All Expired'.

   By performing the cross-check immediately before deleting expired backups, RMAN has up-to-date information about which backups are expired.

5. Click Submit Job.

   A message should appear indicating that your job was successfully submitted.

Marking Backups as Available or Unavailable

If one or more specific backups are unavailable because of a temporary condition, such as a disk drive that is temporarily offline or a tape stored offsite, then you can mark those backups as unavailable. RMAN does not use unavailable backups to restore or recover data.

RMAN keeps the record of unavailable backups in the RMAN repository and does not delete backups listed as unavailable when you delete expired backups. If the unavailable backups become accessible again, then you can mark them as available.

To mark backups as available or unavailable:

1. On the Database Home page, click Availability to display the Availability subpage.

2. In the Manage section, click Manage Current Backups.

   The Manage Current Backups page appears.

3. In the Search section, find the backups whose status you want to change.

4. Click the Select check box next to each backup in the Results list of backups for which you want to change its availability.

5. Do one of the following:

   • Select Change to Available.

   • Select Change to Unavailable.

   Note:

   If you restricted the backups listed by searching only for available backups, then only the Change to Unavailable button appears. If you restricted the backups listed by searching only for unavailable backups, then only the Change to Available button appears.

   A confirmation message appears.

6. Click Yes to perform the change operation.

Deleting Obsolete Backups

This section explains how to delete obsolete backups, which are those no longer needed by the configured retention policy. If you use a fast recovery area as your only disk-based backup destination, then you never have to delete obsolete backups from disk. The fast recovery area keeps files as specified by the retention policy, and deletes them only when space is needed.

To delete obsolete backups:

1. On the Database Home page, click Availability to display the Availability subpage.

2. In the Manage section, click Manage Current Backups.

   The Manage Current Backups page appears.

3. Click Delete All Obsolete.

   All obsolete backups (both backup sets and image copies) are deleted, regardless of whether you clicked Delete All Obsolete while viewing the Backup Sets or Image Copies subpage.

   The Delete All Obsolete: Specify Job Parameters page appears.

4. In the Schedule section, do one of the following:

   • Select One Time (Immediately) to run the deletion job immediately.

   • Schedule the deletion as you would a backup job.

5. Click Submit Job.

About Data Recovery Advisor

In the context of Data Recovery Advisor, a health check is a diagnostic procedure run by the Health Monitor to assess the state of the database or its components. Health checks are invoked reactively when an error occurs. You can also invoke checks manually.

A failure is a persistent data corruption detected by a health check. Failures are usually detected reactively. A database operation involving corrupted data results in an error, which automatically invokes a health check in the database. The check searches the database for failures related to the error. If failures are diagnosed, then they are recorded in the Automatic Diagnostic Repository (ADR).

You can use Data Recovery Advisor to generate repair advice and repair failures only after failures have been detected by the database and stored in ADR. Data Recovery Advisor can report on and repair failures such as inaccessible files, physical and logical block corruptions, and I/O failures. Every failure has a failure priority: CRITICAL, HIGH, or LOW. Every failure also has a failure status of OPEN or CLOSED.

You can also use Data Recovery Advisor to view repair options. A repair is an action that fixes one or more failures. Examples of repairs include block media recovery, data file media recovery, and Oracle Flashback Database. Typically, Data Recovery Advisor presents both automated and manual repair options. If appropriate, you can choose an automated repair option in order to perform a repair. In this case, Data Recovery Advisor verifies the repair success, and closes the relevant repaired failures.

Using Data Recovery Advisor

The recovery process begins when you either suspect or discover a failure. You can discover failures in many ways, including error messages, alerts, trace files, and health checks. You can then use Data Recovery Advisor to gain information and advice about failures and repair them automatically.

This section describes a scenario in which you use Data Recovery Advisor to repair a corrupted block. Assume that the Health Meter on the Database Home page indicates that an incident has occurred. The Alerts section of the page indicates that a block corruption has occurred.

To use the Oracle advised recovery strategy:

1. On the Database Home page, click Availability to display the Availability subpage.

2. Click Perform Recovery in the Manage section.

   The Perform Recovery page appears. The Perform Recovery page is divided into two sections: Oracle Advised Recovery and User-Directed Recovery. The Oracle Advised Recovery section uses Data Recovery Advisor to automate recovery.

   
   Description of the illustration perform_reco_11gr1.gif
   
   

3. Look for failures in the Oracle Advised Recovery section.

   In the screenshot shown in Step 2, the section indicates that one failure with high priority exists. The failure indicates that 1 or more data files are offline.

4. Do one of the following:

   • Click Advise and Recover.

   • Click the number next to the failure status.

   The View and Manage Failures page appears.

5. In the Priority drop-down list, select All and then click Go.

   This action enables you to view all failures known to Data Recovery Advisor. The data failures are represented as a navigation tree at the bottom of the page.

6. Perform the following actions:

   1. Select Data Failures from the navigation tree to expand it (if it is not expanded).

   2. Select the failure to expand it.

   3. Click Advise.

   The Recovery Advice page appears.

   This page shows the RMAN script that is used to repair the failure. For example, for a corrupted block, the script might look similar to the following:

   # block media recovery
   recover data file 1 block 63467;
   

7. Click Continue.

   The Review page appears. This page summarizes the recovery job.

   
   Description of the illustration review_repair_11gr1.gif
   
   

8. Click Submit Recovery Job.

   The Job Activity page appears.

   A table describes details of the repair such as the job name, the job status, the time that the repair is scheduled to be performed, the owner of the job, and so on.

9. Click the name of the repair job and click View Results.

   The Job Run page appears.

   You can click the job step in the navigation tree to view the results of the job.

Rewinding a Table Using Oracle Flashback Table

Oracle Flashback Table enables you to rewind one or more tables back to their contents at a previous time without affecting other database objects. Thus, you can recover from logical data corruptions such as table rows added or deleted accidentally. Unlike point-in-time recovery, the database remains available during the flashback operation.

For this example, you use Flashback Table on the employees table in the hr schema. Assume that an erroneous update shortly after October 23, 2005 at 15:30:00 has changed the lastname column for all employees to an empty string, and you must return the original lastname values to the table.

Recovering a Dropped Table Using Oracle Flashback Drop

Oracle Flashback Drop enables you to reverse the effects of dropping (deleting) a table, returning the dropped table to the database along with dependent objects such as indexes and triggers. This feature stores dropped objects in a recycle bin, from which they can be retrieved until the recycle bin is purged, either explicitly or because space is needed.

As with Flashback Table, you can use Flashback Drop while the database is open. Also, you can perform the flashback without undoing changes in objects not affected by the Flashback Drop operation. Flashback Table is more convenient than forms of media recovery that require taking the database offline and restoring files from backup.

Rewinding a Database Using Oracle Flashback Database

Unlike the other flashback features, Oracle Flashback Database operates at a physical level. When you use Flashback Database, your current data files revert to their contents at a previous time. The result is similar to database point-in-time recovery, but Flashback Database can be much faster because it does not require you to restore and recover data files. Also, Flashback Database requires limited application of redo data as compared to media recovery.

Flashback Database uses flashback logs to access previous versions of data blocks and also uses some data in the archived redo log files. To have the option of using Flashback Database to repair your database, you must have configured the database to generate flashback logs as explained in "Configuring Recovery Settings".

To perform a Flashback Database operation:

1. On the Database Home page, click Availability to display the Availability subpage.

2. Click Perform Recovery.

   The Perform Recovery page appears.

3. Complete the following steps:

   1. In the User-Directed Recovery section, select Whole Database.

   2. Select Recover to the current time or a previous point-in-time.

   3. If necessary, provide the host computer credentials.

   4. Click Recover.

   A Confirmation page appears.

4. Click Yes to confirm the shutdown of the database.

   The Recovery Wizard page appears. At this point, the shutdown begins.

   When the database is shut down and brought to the mounted state, Database Control is also shut down briefly and restarted. During this process, there is a period during which Database Control cannot respond to your browser. Refresh the page until Database Control responds again.

   When Database Control has restarted and the database is being started and mounted, Database Control may also briefly report that the database is in the NOMOUNT state. You are given the choices Refresh, Startup, and Perform Recovery. Refresh the page periodically until the Database Instance page reports that the database instance is mounted before you proceed.

5. Click Perform Recovery to resume your recovery session.

6. If you are prompted for host computer and database credentials, then connect with the SYSDBA privilege, or provide host computer credentials for a user in the DBA group.

   See "Specifying Credentials for Backup and Recovery Using Database Control" for information about credentials.

   When the Perform Recovery page is displayed again, it shows that the database is mounted, which is required to restore and recover the entire database.

7. Complete the following steps:

   1. In the User-Directed Recovery section, select Whole Database.

   2. Select Recover to the current time or a previous point-in-time.

   3. If necessary, provide the host credentials.

   4. Click Recover.

   A Confirmation page appears.

8. Complete the following steps:

   1. Select Recover to a prior point-in-time.

   2. In the Date field, select a time 5 minutes before now.

   3. Click Next.

   The Perform Whole Database Recovery: Flashback page appears.

9. Select Yes to use Flashback Database and then click Next.

   The Perform Whole Database Recovery: Review page appears.

10. Review the options that you selected and click Submit.

    When the flashback operation completes, the Perform Recovery: Result page appears.

11. Click Open Database.

    After the database has opened successfully, click OK.

Restoring and Recovering the Database

This section demonstrates how to restore and recover the entire database. This example assumes that you are restoring and recovering your database after the loss of one or more data files, but you still have a usable server parameter file and control file. You can also use Database Control to restore a lost server parameter file or control file.

To restore and recover the entire database:

1. Perform Step 1 through Step 7 from the procedure in "Rewinding a Database Using Oracle Flashback Database".

2. Do one of the following:

   • Select Recover to the current time to recover all transactions to your database up until now.

   • Select Recover to a prior point-in-time to recover transactions only up through some point in time in the past.

   For this example, select Recover to the current time, and then click Next.

   The Perform Whole Database Recovery: Rename page appears.

3. Select No to restore the files to their default locations, and then click Next.

   The Perform Whole Database Recovery: Review page appears.

4. Click Submit to start the restoration and recovery of the database.

   Note:

   In some repair scenarios, such as a complete restoration and recovery of your database, the database state is altered by steps you take while using the wizard. Database Control displays warnings each time a significant database change results from selecting Continue during the recovery process. Pay close attention to these warnings.

   When the recovery completes, the Perform Recovery: Result page appears.

5. Click Open Database and then OK.