Performing User-Directed Recovery

Oracle Enterprise Manager Database Control (Database Control) User-Directed Recovery provides a Recovery wizard that enables you to use Flashback features and perform restoration and recovery procedures. For example, you can do the following:

Repair of unwanted changes to database objects with the logical Flashback features
Rewinding the entire database with Flashback Database
Complete restoration and recovery of the database
Point-in-time recovery of the database or selected tablespaces
Block media recovery of datafiles that have corrupted blocks

Database Control can determine which parts of the database must be restored and recovered, including proactively detecting situations such as corrupted database files. Database Control guides you through the recovery, prompting you for any required information and performing the specified recovery actions.

This section contains a few typical recovery examples so that you can become familiar with the Perform Recovery page. You can use the Perform Recovery page to access other whole database or object-level recovery features of Database Control.

Rewinding a Table with 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 will 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 need to return the original lastname values to the table.

Enabling Row Movement on a Table

Before you can use Flashback Table, you must ensure that row movement is enabled on the table to be flashed back. Row movement indicates that rowids will change after the flashback occurs. This restriction exists because if rowids before the flashback were stored by an application, then there is no guarantee that the rowids will correspond to the same rows after the flashback.

To enable row movement on a table:

On the Database Home page, click Schema to display the Schema subpage.
Click Tables in the Database Objects section.

The Tables page appears.
To find the target table for Flashback Table, do the following:
1. Enter the schema name in the Schema field and, optionally, the table name in the Object Name field.
2. Click Go to search for the table.
  
  When you search for tables, for example, in the hr schema, you may need to page through the search results to find your table.
Select the table from the list of tables and click Edit.

In this scenario, select employees.

The Edit Table: table_name page appears.
Click Options to go to the Options subpage.
Complete the following steps:
1. Set Enable Row Movement to Yes.
2. Click Apply to update the options for the table.
An update message appears.
Complete the following steps:
1. Click Tables at the top of the page to return to the search results.
2. Enable row movement on more tables by repeating Step 1 through Step 6 for each table.
For this example, you should also enable row movement on the tables hr.jobs and hr.departments.

Performing a Flashback Table Operation

In this example, you rewind the hr.employees table and its dependent tables to a previous point in time.

To perform the Flashback Table operation:

On the Database Home page, click Availability to display the Availability subpage.
Select Perform Recovery from the Backup/Recovery section.

The Perform Recovery page appears.
In the User-Directed Recovery section, select Tables from the Recovery Scope list.

The page reloads with options appropriate for object-level recovery of tables.
For Operation Type, choose Flashback Existing Tables, and click Recover.

The Perform Object Level Recovery: Point-in-time page appears.
Choose the target time for the Flashback Table operation, and click Next.

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

For this example, assume that rows were accidentally inserted 5 minutes ago. Select Flashback to a timestamp and enter a time 5 minutes before the present time.

The Perform Object Level Recovery: Flashback Tables page appears.
Enter table names in the Tables to Flashback text box and then click Next.

You can enter multiple table names to flash back several tables to the same time. You can also click Add Tables and search for more tables. For this example, enter the text hr.employees in the Tables to Flashback text box.

If your table has other dependent tables, then the Dependency Options page appears. This page asks how dependencies should be handled.
Do one of the following, and then click Next:
- Select Cascade to flash back any dependent tables.
- Select Restrict to flash back only the target table.
- Select Customize to choose which dependent tables to flash back and which to leave as they are.
You can click Show Dependencies to see which tables will be affected.

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

In this example, the hr.employees table has dependent tables hr.jobs and hr.departments, so select Cascade and then click Next.

The Perform Object Level Recovery: Review page appears.
Click Submit.

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

Recovering a Dropped Table with Oracle Flashback Drop

Oracle Flashback Drop enables you to reverse the effects of dropping 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.

Note:

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

Dropping a Table

For the purpose of learning about Flashback Drop, you will create a new table named reg_hist and then drop it. The database will place the table in the recycle bin so that it can be retrieved with the Flashback Drop feature.

To create and then drop a table:

On the Database Home page, click Schema to display the Schema subpage.
Click Tables.

The Tables page appears.
Enter hr in Schema and regions in Object Name. Click Go.

The schema and table are listed in the table at the bottom of the page.
In Actions, select Create Like and click Go.

The General subpage of the Create Table page appears.
Complete the following steps:
1. In the Name field, enter reg_hist.
2. Deselect Not Null for the REGION_ID column.
3. Click Constraints to open the Constraints subpage.
  
  The Constraints subpage appears.
4. Select each constraint and click Delete.
5. Click OK to create the table.
  
  A confirmation message appears.
In the Object Name field, enter reg_hist and click Go.

The Tables page displays information about this table.
Click Delete with Options to delete the table.

The Delete With Options page appears.
Select Delete the table definition, all its data, and dependent objects (DROP), and then click Yes.

A message confirms that the table was deleted.

Retrieving a Dropped Table

This section assumes that you created and then dropped the reg_hist table, as described in "Dropping a Table". The following procedure retrieves reg_hist from the recycle bin.

To perform the Flashback Drop operation:

On the Database Home page, click Availability to display the Availability subpage.
Click Perform Recovery in the Backup/Recovery section.

The Perform Recovery page appears.
In the User-Directed Recovery section, select Tables from the Recovery Scope list.

The page reloads with options appropriate for object level recovery of tables.
Select Flashback Dropped Tables for the Operation Type, and then click Recover.

The Perform Object Level Recovery: Dropped Objects Selection page appears.
Search among the dropped objects in the recycle bin for the objects to retrieve.

For this example, enter hr for the Schema Name and reg_hist in the Table field, and click Go.

The Results section lists the objects matching your search. If needed, click the arrow next to Recycle Bin to expand its contents by one level, showing dropped tables matching your search but not their dependent objects.
Select each table that you want to retrieve with Flashback Drop.

For this example, select reg_hist and then click Next.

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.

The Perform Object Level Recovery: Rename page appears.
If needed, enter new names for dropped objects that you are retrieving. For this scenario, do not specify a new name and click Next.

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.

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.
Review the changes and then click Submit.

A confirmation page should indicate the success of the operation.
Click OK to return to the Database Home page.

Rewinding a Database with Oracle Flashback Database

Unlike the other Flashback features, Oracle Flashback Database operates at a physical level. When you use Flashback Database, your current datafiles 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 datafiles. Also, Flashback Database requires limited application of redo data as compared with media recovery.

Flashback Database uses Flashback logs to access previous versions of data blocks and also uses some data in the archived redo logs. 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:

On the Database Home page, click Availability to display the Availability subpage.
Click Perform Recovery.

The Perform Recovery page appears.
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.
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.
Click Perform Recovery to resume your recovery session.
If you are prompted for host computer and database credentials, then connect with the SYSDBA role, or provide host computer credentials for a user in the DBA group.

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.
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.
Complete the following steps:
1. Select Recover to a prior point-in-time.
2. In Date, select a time 5 minutes before the present time.
3. Click Next.
The Perform Whole Database Recovery: Flashback page appears.
Select Yes to specify that you want to use Flashback Database and then click Next.

The Perform Whole Database Recovery: Review page appears.
Review the options that you selected and click Submit.

When the Flashback operation completes, the Perform Recovery: Result page appears.
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 datafiles, 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:

Perform Step 1 through Step 7 from the procedure in "Rewinding a Database with Oracle Flashback Database".
Do one of the following:
- Select Recover to the current time to recover all transactions to your database up until the present time.
- Select Recover to a prior point-in-time to recover only transactions up through some previous point in time in the past. For more details about point-in-time recovery, see Oracle Database Backup and Recovery User's Guide.
For this example, select Recover to the current time, and then click Next.

The Perform Whole Database Recovery: Rename page appears.
Select No to restore the files to their default locations, and then click Next.

The Perform Whole Database Recovery: Review page appears.
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 will be altered by steps you take during the wizard. Database Control displays warnings each time a significant database change will result from pressing Continue during the recovery process. Pay close attention to these warnings.

When the recovery completes, the Perform Recovery: Result page appears.
Click Open Database and then OK.