Numerous studies have shown that human errors account for nearly 40% of system outages. In Oracle9i, a new feature has been introduced called Flashback Query that provides database users the ability to recover from human errors through a simple, easy to use interface.

Under normal circumstances, database operations are performed on most recently committed data. However, there can be situations where a user performs an erroneous DML function, such as, an update, delete, or insert, and then commits the change. In the past, if the user was not able to manually fix the mistake, a situation encountered quite frequently, the only recourse was to ask the database administrator to perform point-in-time database recovery, a non-trivial activity thus results in database outage and loss of data. With Oracle 9i's Flashback Query feature users can query data at a point in time in the past by specifying either the particular time or the System Change Number (SCN). This gives users the ability to easily recover from DML errors without requiring any structural changes to the database or intervention from database administrators. Using this feature users can recover from mistakes by simply restoring the data prior to the erroneous transaction.

The Oracle database ensures read consistency of the data by storing the image of the data prior to any change in undo or rollback segments. When a user makes a change to some data and then decides not to commit it, the change can be undone by issuing the rollback command. This undo is implemented by retrieving the image of the data prior to the change from the undo segments and recreating the old data. Flashback Query mechanism is based on this feature. Oracle 9i's Automatic Undo Management feature lets database administrators specify how long they want to retain old data in the database. This is done by setting the UNDO_RETENTION initialization parameter. By setting this parameter and allocating sufficient storage space for the undo tablespace, database administrators can control how far back data can be queried using the Flashback Query feature.

Before Flashback Query functionality can be used by ordinary users, some actions are required from the database administrators:

• Initialization parameter UNDO_MANAGEMENT=AUTO must be set in the init.ora file.
• Another initialization parameter UNDO_RETENTION must specify a time period (in seconds) for which old data has to be preserved in the database.
• EXECUTE privilege must be granted on the DBMS_FLASHBACK package to the users, roles, and applications that need to perform Flashback Queries.

Once these actions have been performed, users can access the old image of data that was modified by a DML statement. Before this data can be accessed, users have to call the package DBMS_FLASHBACK.ENABLE_AT_TIME(param) or DBMS_FLASHBACK.ENABLE_AT_SYSTEM_CHANGE_NUMBER(param) to enable flashback query. Once flashback query is enabled, all queries performed will apply to the state of the database at the time specified by the input parameter to the package. After all the required queries have been performed on the past data, Flashback Query feature is disabled by calling package DBMS_FLASHBACK.DISABLE. From this point on all queries will access the current data in the database.

Flashback Query Applications

The Flashback Query feature can be used for:

• Undoing users errors, such as, incorrect deletes or updates after they have been committed without necessitating intervention from the database administrators or causing any database outage.
• Creating applications with built-in end user self-service error correction capabilities.

These applications of Flashback Query liberate database administrators from the task of correcting user errors and empower the users to recover from their errors by giving access to previously unavailable data through a simple, completely non-intrusive interface. This not only enhances database administrator and user productivity but also reduces overall database administration costs.