Performing Restore and Recovery Operations

Enterprise Manager's Guided Recovery capability provides a Recovery wizard that encapsulates the logic required for a wide range of restore and recovery scenarios, including the following:

Complete restore and recovery of the database
Point-in-time recovery of the database or selected tablespaces
Flashback Database
Other flashback features of Oracle for logical-level repair of unwanted changes to database objects
Block media recovery of datafiles with corrupt blocks

Enterprise Manager can determine which parts of the database must be restored and recovered, including proactively detecting situations such as corrupted database files. Enterprise Managers walks you through the recovery process, prompting for any needed information and performing needed recovery actions.

The examples in this section only illustrate a few common restore and recovery tasks. Nevertheless, the same Perform Recovery page is used to access the other whole database or object-level recovery features of Enterprise Manager.

To access the restore and recovery tasks:

In the Database home page, click Maintenance.

The Maintenance property page appears.
In the Backup/Recovery section of the Maintenance page, click Perform Recovery.

The Perform Recovery page appears. A section of this page is shown in Figure 9-2.

Figure 9-2 Perform Recovery Page

Description of "Figure 9-2 Perform Recovery Page"

On the Perform Recovery page, you can recover your whole database or only selected tablespaces, datafiles, archived logs, or tables.

Note:

In some recovery scenarios, such as a complete restore and recovery of your database, the database state will be altered by steps you take during the wizard. Changes, some of them irreversible, are made to your database at certain steps. For example, the database may be shut down and brought to MOUNTED state, or datafiles may be overwritten with versions from backup.

Oracle Enterprise Manager will display warnings each time a significant database change will result from pressing Continue during the recovery process. Pay close attention to these warnings.

Recovering a Whole Database from Backup

This example demonstrates recovery of the whole database from backup. The example assumes that you are restoring and recovering your database after the loss of one or more datafiles, but still have a usable SPFILE and control file. Enterprise Manager can also be used to restore a lost SPFILE or control file. See "Recovering from a Lost SPFILE or Control File" for details.

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

The Perform Recovery page appears.
Select Recover to the current time or a previous point-in-time and click Perform Whole Database Recovery. Also, provide the requested host credentials at this time if necessary. Click Continue.

The Confirmation page appears.

Click Yes to confirm the shutdown of the database.

The Recovery Wizard page appears. At this point, Oracle shuts down your database.

Note:

When the database is shut down and brought to the MOUNTED state, Enterprise Manager is also shut down briefly and restarted. During this process, there is a period during which Enterprise Manager cannot respond to your browser, or may respond with an error. Refresh the page until Enterprise Manager responds again.

When Enterprise Manager has restarted and the database is being started and brought to the MOUNTED state, Enterprise Manager may also briefly report that the database is reported to be in NOMOUNT state. You are offered the choices Refresh, Startup, and Perform Recovery. Refresh the page periodically until the Database Instance page reports that the database instance is mounted before proceeding.

Click Perform Recovery to resume your recovery session. You may be prompted for host and database credentials. Connect with SYSDBA role, or provide host credentials for a user in the DBA group.

When the Perform Recovery page is displayed again, it shows that the database is in a MOUNTED state (as is required for this operation).
At this point, as you did before, under Whole Database Recovery, select Recover to the current time or a previous point-in-time and click Perform Whole Database Recovery.

The Perform Whole Database Recovery: Point-in-time page appears.

Specify whether to recover all transactions to your database as of the present time (which is called complete recovery), or only transactions up through some point in time in the past (which is called point-in-time recovery).

Note:

Point-in-time recovery is a recovery technique that lets you return the database to its state before some unwanted major change. For more details about point-in-time recovery, see Oracle Database Backup and Recovery Basics.

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

The Perform Whole Database Recovery: Rename page appears.

You can specify a new directory or new filename for the restored files. For this example, select No to restore the files to the default location, which is its location before the restore operation. Click Next to continue.

The Perform Whole Database Recovery: Review page appears.
Review the options you chose. You can click Edit RMAN script to display the RMAN script that will be run to carry out your requested restore and recovery action. Click Submit to start the recovery.

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:

From the database home page, click Startup to attempt to start the database.
If the startup fails, click View Details to view the reason.
If the cause is a failure to locate the SPFILE or control file, return to the database home page and click Perform Recovery.
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:

On the Perform Recovery page, select Recover to the current time or a previous point in time and click Perform Whole Database Recovery.
On the Perform Whole Database Recovery: Rename page, select Restore files to the default location and click Next.
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.

Validating the Restore of Datafiles from RMAN Backup

Validating the restore of datafiles from a backup tests whether a sufficient set of backups exists that can be used to restore the specified files. After you specify which tablespaces to restore and, possibly, a point in time as of which to restore them, RMAN selects a set of backups that contain the needed data. RMAN then reads the selected backups in their entirety to confirm that they are not corrupt.

Note:

This operation corresponds to the RESTORE ... VALIDATE command in Recovery Manager. See Oracle Database Backup and Recovery Basics for more information.

Validating the restore of files tests whether the file can be restored given the available backups, but it does not test whether all backups of the specified object are valid.

Validating particular backups and validating specific restore tasks are both useful in validating your backup strategy. For more information, see "Validating Backups and Testing Your Backup Strategy".

In the Backup/Recovery section of the Maintenance property page, click Perform Recovery.

The Perform Recovery page appears.
Specify the datafiles to validate individually or you can specify entire tablespaces. In the Object Level Recovery section, select Datafiles or Tablespaces. For the Operation Type, select Restore Datafiles or Restore Tablespaces. Make sure the host credentials are correct, and click Perform Object Level Recovery.

The Perform Object Level Recovery page appears.
Click Add to add tablespaces or datafiles for the validate operation. After making your selections, click Next.

The Perform Object Level Recovery: Restore page appears.
In the Backup Selection section, specify which backups to restore from. In the Backup Validation section, be sure to check Validate the specified backup without restoring the datafiles. Then click Next.

The Perform Object Level Recovery: Schedule page appears.
Specify a job name and description. The job will run immediately, so you are not prompted for a scheduled time. Click Next.

The Perform Object Level Recovery: Review page appears.
You can edit the RMAN script to be run or leave it as-is. Click Submit Job to run the validation.

The Perform Recovery: Result page appears.
Click View Job to view the progress of the running job, or click OK to return to the database home page.

Returning a Table to a Past State: Flashback Table

Oracle Flashback Table lets you revert one or more tables back to their contents at a previous time without affecting other objects in your database. This recovery technique lets you recover from logical data corruptions, such as erroneously inserting rows into a table or deleting data from a table. Flashback Table lets you return tables you select to their state at a past point in time without undoing desired changes to the other objects in your database, as would be required by a point-in-time recovery of the entire database. Also, unlike point-in-time recovery, your database remains available during the operation.

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

Before you can perform Flashback Table, you must ensure that row movement is enabled on the table to be flashed back.

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:

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

The Tables page appears.
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.
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.
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:

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

The Perform Recovery page appears.
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.

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.

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.
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.
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.

Recovering Dropped Tables: Flashback Drop

Oracle Flashback Drop lets you reverse the effects of dropping a table, returning the dropped table to the database along with its dependent objects such as indexes and triggers. It works by storing dropped objects in a Recycle Bin, from which they may be retrieved until the Recycle Bin is purged, either explicitly or because space is needed for new database objects.

As with Flashback Table, Flashback Drop can be used while the rest of your database remains open, and without undoing desired changes in objects not affected by the Flashback Drop operation. It is more convenient than forms of recovery that require taking the database offline and restoring files from backup.

Note:

For a table to be recoverable using Flashback Drop, it must reside in a locally managed tablespace. Also, tables in the SYSTEM tablespaces cannot recovered using Flashback Drop regardless of the tablespace type.

To perform the Flashback Drop operation:

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

The Perform Recovery page appears.
In the Object Level Recovery section, select Tables for the object type. The page reloads with options appropriate for tables in the Object Level Recovery section. For the Operation Type, select Flashback dropped tables and click Perform Object Level Recovery.

The Perform Object Level Recovery: Dropped Objects Selection page appears.
The Search form lets you search among the dropped objects in the Recycle Bin for the objects you want to recover. Provide values for one or both of the Schema Name and Table fields, and click Go to search.

When the page refreshes, the Results section lists the objects matching your search. If you only see the Recycle Bin listed, then click the arrow next to the Recycle Bin to expand its contents by one level, showing dropped tables matching your search but not their dependent objects. You can further expand individual tables, or click Expand All to see all objects in the Recycle Bin, including both dropped tables and dependent objects such as indexes and triggers. For each table listed, you can click View Content in the Operation column to display its contents.To select one or more tables for Flashback Drop, click the checkbox next to each table.

Note:
When a table is retrieved from the Recycle Bin, all of the dependent objects for the table that are in the recycle bin are retrieved with it. They cannot be retrieved separately.

When you have selected all of the objects to restore, click Next.

The Perform Object Level Recovery: Rename page appears.
If needed, specify new names for any dropped objects you are returning to your database. The primary reason for renaming objects when you retrieve them from the recycle bin is if you have created new tables with the same names as tables being retrieved. If you need to rename some objects, then enter new names as needed in the New Name field in the list of tables being flashed back. Click Next to continue.

The Perform Object Level Recovery: Review page appears. This page displays an impact analysis, showing the full set of objects to be flashed back, including the dependent objects, as well as the names they will have when the Flashback Drop operation is complete.
If you are satisfied with the changes listed in the review, click Submit to perform the Flashback Drop.

A confirmation page should indicate the success of the operation.
Click OK to return to the database home page.