Flashback Table

---

Perhaps the most compelling of the Flashback Technologies is the ability to simply revert a table to a previous point in time in a simple and straightforward fashion. The ability to perform point-in-time recovery on a table or group of tables has often been the grounds by which entire clone databases are built-just so that a single table could be extracted and then imported back into production. Those days are long gone, we're happy to say!

Flashback Table employs the same mechanisms as Flashback Query-with information stored in the undo segments, Oracle can rewind a database one transaction at a time to put the table back the way it was at a specified time in the past. Because Flashback Table depends on undo, the same restrictions apply: you can only flashback a table as far back as the undo segments allow you.

In addition to undo, the ability to flashback a table requires you to enable row movement for the table. Row movement was initially put in place as a function of partitioned tables, which allowed an updated row to move to the appropriate partition if the update changed the partition key value. Flashback Table employs row movement to assist in the rewind operations.

alter table woodscrew enable row movement;

Flashback Table cannot save you from all user errors. Certain DDL operations that occur against a table cannot be undone; most importantly, you cannot flashback a table to before a truncate table operation. This is because a truncate does not produce any undo-that is why truncate exists, versus a delete * from table. Also, Flashback Table cannot be used for a dropped table (use Flashback Drop for that-see the section 'Flashback Drop').

Enabling Row Movement and Flashback Table

It is critical that you foresee possible Flashback Table candidates and enable row movement as soon as possible. You cannot enable row movement and then flashback the table to a point prior to enabling row movement. Such an operation will get you this error:

ORA-01466: unable to read data - table definition has changed

In other words, you cannot wait until you need to flashback a table, and then enable row movement as part of the flashback operation. Instead, you would probably want to use Flashback Transaction Query to manually undo each row change.

Performing the Flashback Table from SQL

With row movement enabled, you can move forward with normal operations on the table. Then, when a user-induced corruption occurs in the table, you can use SQL at the command line to perform the Flashback Table:

flashback table sales.woodscrew to timestamp to_timestamp('2003-12-01 13:30:00','YYYY-MM-DD HH24:MI:SS')

Alternatively, you can use SCN if you know the SCN (through investigation via Flashback Query, for example):

flashback table sales.woodscrew to scn 751652;

Like Flashback Query, the performance of a Flashback Table operation is dependent on the amount of data that has to be rewound, and how far back you are rewinding. The more data that has to be undone, the longer the operation will take. But this will always be faster than the performance you can achieve by trying to perform a point-in-time recovery of the table by other methods: you can try tablespace pointin-time recovery, or you can try to restore the tablespaces to a different instance, and then export the table from the clone instance and import it back into production. Nothing can come close to Flashback Table.

Flashback Table with Enterprise Manager

There is functionality inside Enterprise Manager to utilize the Flashback Table feature. The added strength of EM for Flashback Table is the ability to first explore the table via Flashback Row History to determine exactly to what time you want to flash back. If you already know the exact time for flashback, using SQL at the command line would be just as simple as using the Flashback Table Wizard in EM. EM does, however, provide a way to determine what dependencies are at play.

HA Workshop: Explore Flashback Table

Workshop Notes

In this workshop, we will 'accidentally' delete all the rows from the Woodscrew table, and then flashback the entire table to the point in time right before the DELETE transaction took place.

Step 1.  View the data in Woodscrew. Because of previous exercises, it might be worthwhile to truncate the table and then reinsert the records manually using the original population script (as shown earlier in the 'Explore Flashback Versions Query' HA workshop). Make sure you have all eight rows. Also, make sure you enable row movement prior to inserting the fault:

alter table woodscrew enable row movement;

Step 2.  Insert the fault. We will delete all the rows in the table using a SQL*Plus DELETE statement. Afterward, use the View Data action item in EM to view the empty table.

delete from woodscrew; commit;

Step 3.  From EM | Administration | Schema | Tables, choose the Woodscrew table. Then, from the Action list choose Flashback Table and then click Go. This takes you into a Perform Recovery Wizard, which first asks you to 'Specify the point in time to which to recover.' Because we don't already know when the delete took place, choose to evaluate row changes and transactions-the first choice. Click on Next.

Step 4.  You will now see a familiar screen-the Flashback Versions Query interface. Here, we have to set our columns and a WHERE clause to narrow down our search. Because the delete affected all rows, we can choose a single row to review here. We will use the same row as in previous workshops.

where scr_id=1001 and manufactr_id='Tommy Hardware'

Now we can see the information about the DELETE operation that whacked our poor Woodscrew table, as shown in Figure 9-6.

Figure 9-6: The row history for the DELETE operation

Step 5.  You can click on the transaction ID to review the entire delete. However, of more importance to us is the Flashback SCN column, which shows us the SCN to set to undo the DELETE operation. With the appropriate DELETE transaction checked from the list, simply clicking on Next will automatically choose the flashback SCN specified on this screen.

Step 6.  On Step 4 of 7 in the Flashback Table Wizard, Oracle allows you to specify any logically related objects that should be rewound to the same SCN as this table. Oracle will automatically honor all constraints that exist for the table, but you may have logically related tables that should be flashed back. If so, this is your opportunity to specify them. In our example, we do not have any related tables, so click Next.

Step 7. Voila: we are magically transported to Step 7 of 7, where we see the summary of the action that will take place. Most useful here are the Show Row Changes and Show SQL buttons options, which will show what rows will be changed and what SQL will be executed by EM on your behalf. Click on Submit. If there is any problem, or if you just feel better about it, you can cut the EMgenerated SQL into a SQL*Plus session to run the Flashback Table.

FLASHBACK TABLE SALES.WOODSCREW TO SCN 804109; 

---