How many times have database recoveries been performed because of incorrect changes made to database data? A user deletes or updates "one too many rows" by mistake and the result is a time-consuming and error prone process to restore and recover the database to a point-in-time before the error occurred.
Let's go back to my days as an Oracle instructor…. A common question in the Oracle DBA backup and recovery class was "why can't I just roll the database back to remove unwanted changes instead of restoring the database from a backup and applying the redo logs to roll forward to a point-in-time before the error occurred?" The question was invariably followed by "don't you think that would be easier?" The answer was always the same "yes, it would be easier, but the Oracle database doesn't have that capability."
That was before Flashback Database came along. Oracle's Flashback Database feature provided a new tool in the DBA's recovery toolbox. Flashback Database allows the DBA to "roll back" a table, set of tables or the entire database to a previous point-in-time. Administrators often used a timestamp during the execution of flashback operations.
10G R2 enhances Flashback Database by allowing administrators to associate a user-defined name with a point-in-time. The user-defined name, called a restore point, can be used in place of a SCN or timestamp when executing a FLASHBACK TABLE or FLASHBACK DATABASE statement.
A SQL statement is used to create the restore point which means it can be embedded in application programs as we as executed on an as-needed basis in SQL*PLUS. The example below shows the creation of a restore point:
CREATE RESTORE POINT batch_daily_cycle_complete;
Now if I execute the following statement:
UPDATE cti.employee_salary_table SET
salary = 200000 WHERE empl_lname = 'FOOT';
My boss can execute this statement to correct the 'mistake' (although I prefer to call it a fully justifiable pay adjustment):
FLASHBACK TABLE cti.employee_salary_table TO batch_daily_cycle_complete
10GR2 also provides guaranteed restore points which ensure that the database can be flashed back to the point-in-time they were created. Guaranteed restore points save disk space because only the flashback logs required to meet the guaranteed restore point need to be retained by the database. The statement below creates a guaranteed restore point:
CREATE RESTORE POINT batch_daily_cycle_complete GUARANTEE FLASHBACK DATABASE
