Flashback Query in Oracle

Flashback Query was introduced in Oracle 9i

Oracle Flashback Query allows us to view and repair historical data. We can perform queries on the database as of a certain time or specified system change number (SCN).

Flashback Query uses Oracle's multiversion read-consistency capabilities to restore data by applying undo as needed.Oracle Database 10g automatically tunes a parameter called the undo retention period. The undo retention period indicates the amount of time that must pass before old undo information, i.e. undo information for committed transactions, can be overwritten. The database collects usage statistics and tunes the undo retention period based on these statistics and on undo tablespace size.

Using Flashback Query, we can query the database as it existed this morning, yesterday, or last week (if undo_retention parameter is set appropriately). The speed of this operation depends only on the amount of data being queried and the number of changes to the data that need to be backed out.

Note: If Oracle’s default locking is overridden at any level, the database administrator or application developer should ensure that the overriding locking procedures operate correctly. The locking procedures must satisfy the following criteria: data integrity is guaranteed, data concurrency is acceptable, and deadlocks are not possible or are appropriately handled.

You set the date and time you want to view. Then, any SQL query you run operates on data as it existed at that time. If you are an authorized user, then you can correct errors and back out the restored data without needing the intervention of an administrator.

SQL> select * from dept as of timestamp sysdate-3/1440;
With the AS OF sql clause, we can choose different snapshots for each table in the query. Associating a snapshot with a table is known as table decoration. If you do not decorate a table with a snapshot, then a default snapshot is used for it. All tables without a specified snapshot get the same default snapshot e.g. suppose you want to write a query to find all the new customer accounts created in the past hour. You could do set operations on two instances of the same table decorated with different AS OF clauses.

DML and DDL operations can use table decoration to choose snapshots within sub queries. Operations such as CREATE TABLE AS SELECT and INSERT TABLE AS SELECT can be used with table decoration in the sub queries to repair tables from which rows have been mistakenly deleted. Table decoration can be any arbitrary expression: a bind variable, a constant, a string, date operations, and so on. You can open a cursor and dynamically bind a snapshot value (a timestamp or an SCN) to decorate a table with.

SQL> create table emp_old select * from emp as of timestamp sysdate-1;

Flashback Query Benefits

■ Application Transparency
Packaged applications, like report generation tools that only do queries, can run in Flashback Query mode by using logon triggers. Applications can run transparently without requiring changes to code. All the constraints that the application needs to be satisfied are guaranteed to hold good, because there is a consistent version of the database as of the Flashback Query time.

■ Application Performance
If an application requires recovery actions, it can do so by saving SCNs and flashing back to those SCNs. This is lot easier and faster than saving data sets and restoring them later, which would be required if the application were to do explicit versioning. Using Flashback Query, there are no costs for logging that would be incurred by explicit versioning.

■ Online Operation
Flashback Query is an online operation. Concurrent DMLs and queries from other sessions are allowed while an object is queried inside Flashback Query. The speed of these operations is unaffected. Moreover, different sessions can flash back to different Flashback times or SCNs on the same object concurrently. The speed of the Flashback Query itself depends on the amount of undo that needs to be applied, which is proportional to how far back in time the query goes.

■ Easy Manageability
There is no additional management on the part of the user, except setting the appropriate retention interval, having the right privileges, and so on. No additional logging has to be turned on, because past versions are constructed automatically, as needed.

Notes:

• Flashback Query does not undo anything. It is only a query mechanism. We can take the output from a Flashback Query and perform an undo in many circumstances.
• Flashback Query does not tell us what changed, LogMiner does that.
• Flashback Query can undo changes and can be very efficient if we know the rows that need to be moved back in time. We can use it to move a full table back in time, but this is very expensive if the table is large since it involves a full table copy.
• Flashback Query does not work through DDL operations that modify columns, or drop or truncate tables.
• LogMiner is very good for getting change history, but it gives changes in terms of deltas (insert, update, delete), not in terms of the before and after image of a row. These can be difficult to deal with in some applications.

When to Use Flashback Query

■ Self-Service Repair
Perhaps you accidentally deleted some important rows from a table and wanted to recover the deleted rows. To do the repair, you can move backward in time and see the missing rows and re-insert the deleted row into the current table.

■ E-mail or Voice Mail Applications
You might have deleted mail in the past. Using Flashback Query, you can restore the deleted mail by moving back in time and re-inserting the deleted message into the current message box.

■ Account Balances
You can view account prior account balances as of a certain day in the month.

■ Packaged Applications
Packaged applications (like report generation tools) can make use of Flashback Query without any changes to application logic. Any constraints that the application expects are guaranteed to be satisfied, because users see a consistent version of the Database as of the given time or SCN.

In addition, Flashback Query could be used after examination of audit information to see the before image of the data. In DSS environments, it could be used for extraction of data as of a consistent point in time from OLTP systems.

Source: 

Sachin's DBA Blog