Introduction
Several leading IT studies indicate that up to 40% of database downtime is attributed to human errors. It's not just the end users making the mistakes; it is the well trained IT professional. We are all human and humans make mistakes. The challenge becomes how to find where the error occurred and how to quickly recover from it. If only it were as easy to undo a mistake as it was to make it!

Guarding Against Human Errors
The best way to prevent errors is to restrict a user's access to data and services they truly need to conduct their business. The Oracle Database provides a wide range of security tools to control user access to application data by authenticating users and then allowing administrators to grant users only those privileges required to perform their duties.

When authorized people make mistakes you need the tools to correct these errors. Flashback Query addresses the unavoidable human element, by providing an easy, fast method of querying data, as it existed in the past. But more importantly, Flashback Query provides the ability to recover at the row level while the rest of the data remains online and available. With Flashback the time to correct errors equals the time it took to make the error.

What is Flashback Query?
Oracle9i introduced Flashback Query to provide a simple, powerful and completely non-disruptive mechanism for recovering from human errors. It allows users to view the state of data at a point in time in the past without requiring any structural changes to the database. Using Flashback Query, users can execute queries against a snapshot of data consistent to a point in time in the past either by specifying a wall clock time or a System Change Number (SCN). This capability allows users to correct logical errors, or simply view an old image of data, without any disruption in database services.

Since Flashback Query can be executed without any administrator intervention, application developers can use this feature to build applications equipped with built in self-service error correction capability. Table data can be queried, as it existed at a point in time. This lets applications query, compare, or recover past data without involving the DBA and without an expensive recovery operation. The current table data remains available to other applications throughout.

How does Flashback Query Work?
The 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 (or Rollback Segments). Should the user later decide not to commit the transaction or if the operation was not successful, Oracle uses the data saved in the undo tablespace to recreate the original data. The undo data is also used to generate a snapshot of data consistent to a point in time when a query was started if another user has modified the underlying data. Flashback Query relies on the same mechanism to construct an image of the data as it existed at a time in the past.

The undo data, therefore, must be available in order for a Flashback Query to be successful. Oracle's Automatic Undo Management feature allows administrators to specify how long they wish to retain the undo data using the UNDO_RETENTION initialization parameter. By using this parameter and sizing the undo tablespace appropriately, DBAs can control how far back a Flashback Query can go. By leveraging Oracle's unique multi-versioning read consistency architecture (undo data), Flashback Query offers maximum performance:

• Speed is relative to size of queried data
• No overhead for change tracking
• Standard SQL interface

Enabling Flashback Query
The Oracle database provides a means of explicitly specifying the amount of undo to retain. The system automatically recycles expired undo to make space for new transactions to generate undo. The Flashback Query mechanism is most effective when you use Automatic Undo Management. As a best practice, you should:

• Ensure the database is using an undo tablespace by setting the UNDO-MANAGEMENT initialization parameter to AUTO.
• Set the UNDO-RETENTION initialization parameter to a value defining how far back in time to perform Flashback Query. To estimate the tablespace size required for a given undo retention period, consult the Enterprise Manager Undo Advisor.
• Protect unexpired undo from being overwritten by setting the RETENTION GUARANTEE clause for the undo tablespace. You can use rollback segments with Flashback Query, but for the best results, you should use Automatic Undo Management.

Row Level Recovery with Flashback
Query Most DBAs have encountered the frantic knock on their office door when an end-user issued an incorrect SQL command deleting 100,000 rows instead of the intended 50 rows. Prior to the introduction of Flashback Query, recovering from accidental row deletion was not easy and was usually time consuming. In Oracle9i, you have three recovery options for resolving accidental row deletions:

• Database Point-in-time-recovery (DBPITR)
• Import data from another data source
• Flashback Query

The chart below lists the advantages and disadvantages associated with each of the three recovery options.

Flashback Query is the best choice in recovering deleted rows from specific tables quickly and without data loss. It empowers end-users to correct their own mistakes without involving the DBA and without expensive recovery. Using a single SQL command, the erroneous deleted rows are re-inserted into the table.

INSERT INTO accounts
(SELECT * FROM accounts AS OF TIMESTAMP TO_TIMESTAMP('13-MAR-02 10:59:58','DD-MON-YY HH24:MI:SS')
MINUS
SELECT * FROM accounts);

Tracking Data Changes
While Flashback Query provides an extremely powerful mechanism of recovering from human errors, its use goes beyond undoing incorrect changes. It can also be used to simplify a number of frequently performed daily operations such as tracking incremental data changes and querying the past state of data. Flashback Query could also be used to generate a daily report of all new accounts created on any given day by creating a view as shown below.

CREATE VIEW accounts_created_today AS
SELECT * FROM accounts
MINUS
SELECT * FROM accounts AS OF TIMESTAMP TRUNC(SYSDATE);

Once this view is created, executing the following simple query can produce a list of all accounts created during the day.

SELECT * FROM accounts_created_today;

Similarly, in order to find out an account balance on a certain day in the past, one simply needs execute a query similar to one shown below.

SELECT balance FROM accounts AS OF TIMESTAMP TRUNC(SYSDATE - 3) WHERE account_number = 12345;

The Flashback Query, therefore, simplifies the application design significantly by removing the need to store temporal data. Unlike other database features available for recovering lost data, Flashback Query allows users to view the data as it was in the past and then, lets them choose how to process it. They may wish to analyze the data to determine what exactly caused the change, reverse the change or, simply capture the changed data for further processing.

Recovery Procedures and Testing Exercises
Most enterprises have well documented backup and recovery strategies. Traditional recovery methodologies generally have provisions for single file and full database recovery options but may not be taking advantage of the latest technology enhancements such as Flashback Query. Flashback Query commands should be included in standard recovery procedures and tested during regular recovery testing exercises. The undo retention period should be set to meet recovery time objectives using Flashback Query.

Flashback Technologies in Oracle Database 10g
With Oracle Database 10g, the Flashback Technologies were expanded to include a set of features to quickly view and recover data from the row to the database level while the database is online. The flashback features offer the capability to query past versions of schema objects, query historical data, analyze database changes, or perform self-service repair for online recovery from logical corruptions. A brief description of the Flashback features and key benefits follows:

Summary
Introduced in Oracle9i, Flashback Query has become a key component of enterprise data recovery strategy. Relying on the existing undo architecture of the Oracle database, it provides the fastest, most effective way to correct human error. Flashback Query is a Powerful tool for a DBA and users.

• Empowers end-users to correct their own mistakes without involving the DBA
• Avoids expensive recovery operation
• Simplifies daily operations such as tracking incremental data changes and querying the past state of data
• Reduces system downtime due to human errors

References

1. Oracle Database High Availability web site on OTN
2. Flashback technology explained on OTN
3. Oracle Database 10g High Availability White Paper
4. Flashback technology White Paper
5. Oracle Database Backup and Recovery User's Guide -- Chapter 9: Flashback Technology

Donna Cooksey (Donna.Cooksey@Oracle.com) is a Senior Product Manager with Oracle's High Availability Organization. She is responsible for managing Customer Programs with Oracle's Database High Availability Group. Her primary focus area is Backup and Recovery with years of experience in enterprise data protection.