Recovering Accidentally Lost Data Using Oracle's Flashback Query

Extract

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.

Introduction

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 AS OF clause. Alternatively, it can be implemented using the DBMS_FLASHBACK package but we must open a cursor before making a call to disable the Flashback mode. We can fetch results from past data from the cursor, then issue INSERT or UPDATE statements against the current state of the Database. This is illustrated in the next section with examples.

Before ordinary users can take advantage of this functionality, the Database Administrators must perform the following actions.

• Configure for Automatic undo management: set initialization parameter undo_management=auto and undo_tablespace= <undo tablespace name> in the init.ora file.
• Specify undo retention time by setting undo_reention = <time in seconds> in the init.ora.

  For eg:

  		undo_management=auto 
  		undo_retention=1200 
  		undo_tablespace=UNDOTBS
  
  

• Grant execute privilege on dbms_flashback package to the users that need to perform Flashback Query.
• Grant 'FLASHBACK' privilege on the table's that users need to perform flashback query. Alternatively, 'FLASHBACK ANY TABLE' system privilege to allow the user to use this feature on any table except data dictionary tables.

Please note that the flashback query is turned off when the session ends, and if there is no explicit call to the disable procedure.

Notes:

• Flashback Query is supported by the export utility to export data which is consistent as of a specified point in time.
• PL/SQL cursors opened in Flashback mode area available for DML after Flashback mode is disabled.