12 January 2010

Flashback Table in time

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.

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, or returned to a previous state. 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:
1.        On the Database Home page, click Schema to display the Schema subpage.
2.        Click Tables in the Database Objects section.
The Tables page appears.
3.        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 have to page through the search results to find your table.
4.        Select the table from the list of tables and click Edit.
In this scenario, select employees.
The Edit Table: table_name page appears.
5.        Click Options to go to the Options subpage.
6.        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.
7.        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:
1.        On the Database Home page, click Availability to display the Availability subpage.
2.        Select Perform Recovery from the Manage section.
The Perform Recovery page appears.
3.        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.
4.        For Operation Type, choose Flashback Existing Tables, and click Recover.
The Perform Object Level Recovery: Point-in-time page appears.
5.        Choose the target time for the Flashback Table operation, and click Next.
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 Version Query enables you to review all recent changes to the target table. Use of this feature is beyond the scope of this documentation.
For this example, assume that rows were accidentally inserted 5 minutes ago. Select Flashback to a timestamp and enter a time 5 minutes before now.
The Perform Object Level Recovery: Flashback Tables page appears.
6.        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.
7.        Do one of the following, and then click Next:
o        Select Cascade to flash back any dependent tables.
o        Select Restrict to flash back only the target table.
o        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.
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.
8.        Click Submit.
When the operation is completed, a Confirmation page displays the results. Click OK.

0 reacties:

Post a Comment