Recovering Accidentally Lost Data Using Oracle's Flashback Query
May 9, 2003by Sreeram Surapaneni
Recent studies show that nearly 50% of system outages are due to human errors. The ability to recover from these user errors using a simple, easy to use interface is challenging. Most database products today do not offer any solutions, thus administrators have no option but to recover the database using a previous backup. This not only requires the database to be unavailable during the period of recovery but also leads loss of valid transactions if the database to be restored to a point in time in the past. Oracle 9i's new feature, Flashback Query allows the users to view the old image of data that was modified and committed by DML statement, without requiring any structural changes to the database.
Oracle database uses a version based read consistency mechanism by saving an image of the data prior to making any modifications. These changes are stored in the undo tablespace. If the users decide not to commit the transaction, Oracle uses the data saved in the undo tablespace to recreate the original data. The Flashback Query uses the same mechanism to construct an image of the data, as it existed at a time in the past. Oracle professionals can specify how long they wish to retain the undo data using the UNDO_RETENTION parameter in the init.ora file and thereby can control how far back a Flashback Query can go.
The Flashback query feature in an
application can be utilized by using either of the following two techniques.
Using the AS OF clause in SQL queries to specify a past time is the simplest
way to implement the Flashback query. This method allows you to perform
DDL operations such as creating and truncating tables, and DML operations such
as inserts and deletes, in the same session as queries using the
Before ordinary users can take advantage of this functionality, the Database Administrators must perform the following actions.
Please note that the flashback query is turned off when the session ends, and if there is no explicit call to the disable procedure.