Oracle Flashback Technology

Oracle Flashback Technology reduces recovery time from hours to minutes.

According to many studies, 40% of application outages are caused by operator or user errors. Part of being human is making mistakes. But these errors are extremely difficult to avoid and can be particularly difficult to recover from without advance planning and the right technology. Such errors can result in "logical" data corruption, or cause downtime of one or more components of the IT infrastructure. While it is relatively simple to rectify the failure of an individual component, detection and repair of logical data corruption, such as accidental deletion of valuable data, is a time consuming operation that causes enormous loss of business productivity. Typical user-errors may include accidental deletion of valuable data, deleting the wrong data, and dropping the wrong table.

Guarding Against Human Errors
The Oracle Database architecture leverages the unique technological advances in the area of database recovery due to human errors. Oracle Flashback Technology provides a set of new features to view and rewind data back and forth in time. The Flashback features offer the capability to query historical data, perform change analysis, and perform self-service repair to recover from logical corruptions while the database is online. With Oracle Flashback Technology, you can indeed undo the past!

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.

Oracle Database 10g extended the Flashback Technology to provide fast and easy recovery at the database, table, row, and transaction level. Flashback Technology revolutionizes recovery by operating just on the changed data. The time it takes to recover the error is now equal to the same amount of time it took to make the mistake. Oracle 10g Flashback Technologies includes Flashback Database, Flashback Table, Flashback Drop, Flashback Versions Query, and Flashback Transaction Query.

Flashback technology can just as easily be utilized for non-repair purposes, such as historical auditing with Flashback Query and undoing test changes with Flashback Database. Oracle Database 11g introduces an innovative method to manage and query long-term historical data with Flashback Data Archive. This release also provides an easy, one-step transaction backout operation, with the new Flashback Transaction capability.

New Features in Oracle Database 11g

Flashback Data Archive

Flashback Data Archive can be used to automatically track and maintain historical changes to all Oracle data in a highly application transparent, secure and efficient manner. Part of the Oracle Total Recall Option, Flashback Data Archive provides enterprises with a quick, centralized and extremely efficient solution to meet all historical data management needs. Flashback Data Archive automatically tracks every single change made to the data stored inside the database and maintains a secure, efficient and easily accessible archive of historical data. The captured historical data can be retained for as long as the business demands and is easily accessible using Flashback SQL queries. Historical data tracking can be enabled on both existing and new tables instantaneously and more importantly, in a completely application transparent manner.

Flashback Data Archive presents a high-performance, storage optimized solution with a centralized management interface for satisfying data retention and change control requirements for organizations. The primary advantages of using Flashback Data Archive for historical data tracking include:

• Application Transparent: Enabling historical data capture on one or more tables can be done instantaneously with no or minimal application changes. Customers can therefore use this feature to capture historical data for both packaged as well as home grown applications.
• Seamless Access: Historical data can be easily accessed using familiar Flashback SQL constructs. Flashback Data Archive includes support for Flashback Queries. Applications can seamlessly query the history of table data, as it existed in different points in time. No special snapshots need to be taken to take advantage of this feature.
• Security: Historical data, once generated, is immutable to all users. This is enabled out-of-the-box and no special or extra setup is required. Access to the internal history tables is restricted to reads only. No DML operations are allowed to users, including administrators. Applications need not query the internal history tables directly as seamless access is provided through the Flashback Query mechanism.
• Minimal performance overhead: Regular user transactions will see negligible impact. Flashback Data Archive employs a lightweight mechanism to mark DML operations on tracked tables for archiving. The actual history generation and archiving is done asynchronously through a background process as explained later.
• Storage Optimized: The history data is internally partitioned and highly compressed to reduce the storage footprint. Flashback Data Archive employs a highly efficient compression scheme to compress the internal history tables. In addition, it automatically partitions the internal history tables based on a range-partitioning scheme. Both compression and partitioning in flashback data archive are managed automatically and require no special administration.
• Centralized Management: Flashback Data Archive provides a centralized and policy-based management interface to automate a number of ongoing administrative tasks. With Flashback Data Archive, you can easily group tables and set a common retention policy. New tables will automatically inherit the retention parameter from the flashback data archive in which it resides. Oracle will automatically purge aged-out history data for all tracked tables based on the specified retention. This frees up the administrator from the repetitive management of history data and avoids costly errors associated with manual maintenance, such as purging incorrect history data.

Flashback Transaction

Large-scale database applications rely on complex sequences of transactions, to ensure atomicity and consistency of a group of inserts, updates, or deletes. In the event of a ‘bad’ transaction, the administrator must trail back-in-time to see what changes were effected by the transaction and ascertain any dependencies (e.g. transactions that modified the same data after the ‘bad’ transaction), to ensure that undoing the transaction preserves the original, good state of the data and any related data. Performing this type of transaction analysis can be laborious, especially for very complex applications.

With Flashback Transaction, a single transaction, and optionally, all of its dependent transactions, can be flashed back with a single PL/SQL operation or by using an intuitive EM wizard to identify and flashback the problem transactions. Flashback Transaction relies on the availability of undo data and archived redo logs for the given transaction and its dependents, to backout the changes.

New Features in Oracle Database 10g Release 2

Restore Points

When an Oracle database point-in-time recovery operation is required, a DBA must determine a time or SCN to which the data must be rolled back. Oracle Database 10g Release 2 simplifies point in time recovery with restore points. A restore point is a user-defined name that can be substituted for an SCN or clock time when used in conjunction with Flashback Database, Flashback Table, and Recovery Manager (RMAN), and can be created at the command-line with SQL*Plus or RMAN, or through Enterprise Manager. Restore points eliminate the need to investigate the SCN or time of a transaction and provides users with the ability to bookmark a database transaction event. Guaranteed restore points ensure that sufficient flashback logs are always maintained to get back to that restore point. This means that flashback logs will not be deleted by the Flash Recovery Area, unless they are not needed for the current guaranteed restore points. These special restore points can be created before major database changes, such as a database batch job or schema upgrade, and used for flashback if the changes need to be undone.

Flashback Database Through RESETLOGS

Flashback Database through RESETLOGS allows flashback logs created prior to a RESETLOGS operation to be utilized for Flashback Database operations. In Oracle Database 10g Release 2, flashback logs are preserved after opening the database with RESETLOGS. This new feature is useful when a long-standing logical error is not discovered until after RESETLOGS is performed, and a flashback prior to RESETLOGS is needed. In an Oracle Data Guard environment, this capability allows a physical standby database that has been opened read-write to later flashback the changes and be converted back to a physical standby database. If a logical error is discovered after a switchover operation, the primary and standby databases can be flashed back to an SCN or a point in time prior to the switchover operation.

New Features in Oracle Database 10g Release 1

Flashback Database

Flashback Database quickly rewinds an Oracle database to a previous time, to correct any problems caused by logical data corruptions or user errors. Flashback Database is like a 'rewind button' for your database. It provides database point in time recovery without requiring a backup of the database to first be restored. When you eliminate the time it takes to restore a database backup from tape, database point in time recovery is fast

The Flashback Database capability, accessible from both RMAN and SQL*Plus by using the FLASHBACK DATABASE command, is similar to conventional point-in-time recovery in its effects. It allows you to return a database to its state at a time in the recent past. To enable the Flashback Database capability, a DBA configures the Flash Recovery Area. The Flash Recovery Area is a new feature in Oracle Database 10g that provides a unified storage location for all recovery related files and activities in an Oracle database. Besides Flashback Database logs, the recovery area contains archived redo logs and RMAN backups. For more information on the Flash Recovery Area, consult the Oracle Backup and Recovery documentation.

Oracle automatically creates and manages Flashback Logs within the Flash Recovery Area. Since the Flash Recovery Area is configured with a space quota, the Flashback Logs are subject to those disk space restrictions. The size of Flashback Logs can vary considerably, depending on the read/write ratio of database changes during a given flashback-logging interval. A copy of the old block version is written to the Flashback Log. If, over the course of a day, 10% of the database blocks are updated, then the size of Flashback Logs for 24 hours is approximately one-tenth the size of your database. The DBA may change this disk quota dynamically if more disk space is required to recover the database to an earlier time in the past.

Flashback provides Data Guard with an easy-to-use method to correct user errors. Flashback Database can be used on both the primary and standby database to quickly revert the databases to an earlier point in time to back out user errors. If the administrator decides to failover to a standby database, but those user-errors were already applied to the standby database (for example, because Real Time Apply was enabled), the administrator can simply flashback the standby database to a safe point in time.

The performance overhead of enabling Flashback Database is less than 2%. While you may not be willing to sacrifice any performance overhead for your production database, think about the trade-off. If you could recover the database in minutes instead of hours, saving your company millions of dollars in lost revenue, would you then give 2% of the resources to Flashback Database? Enabling Flashback Database functionality provides the following benefits.

• Eliminate the time to restore a backup. When a database is down because it runs into a catastrophic problem, considerable revenue can be lost because the company cannot do business.
• Eliminate standby database redo apply delay. Flashback database is seamlessly integrated with Data Guard. A standby database can now be quickly and easily flashed back to an arbitrary point in time, so a delay in redo apply is not necessary.
• Unanticipated error correction. Flashback Database provides a continuous snapshot of the Oracle database. The database can be rewound back to a SCN or timestamp.

Flashback Table

When a human or application error occurs, you want to be able to restore the state of one or more tables to a point in time before the problem occurred. Flashback Table provides the DBA the ability to recover a table or a set of tables to a specified point in time quickly, easily, and online. Flashback Table restores the tables while automatically maintaining its associated attributes such as - the current indexes, triggers and constraints, not requiring the DBA to find and restore application specific properties. Flashback Table alleviates the need for you to perform more complicated point in time recovery operations. The following command flashes back the ORDERS and ORDER_ITEMS tables to 2:33 PM on July 7.

FLASHBACK TABLE orders, order_items TO TIMESTAMP (JUL-07-2003, 02:33:00);

Like Flashback Query, Flashback Table also relies on the undo data to recover the tables. The undo data, therefore, must be available in order for a Flashback Table to be successful. The Automatic Undo Management feature allows you 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 in time a table can be repaired using Flashback Table.

While a DBA can use the Flashback Table feature to quickly recover from human errors, it also serves as a self-service repair tool to recover from accidental modifications or deletions. An application developer can incorporate the Flashback Table functionality into their customized application. This tool provides significant benefits over media recovery in terms of ease of use, availability and faster restoration with point-in-time object based recovery. Flashback Table

• Performs the restore operation online
• Restores all data in a specified table to a previous point in time described by a timestamp or SCN
• Automatically restores all of the table attributes, such as indexes, triggers, and the like that are necessary for an application to function with the flashed back table
• Maintains any remote state in a distributed environment. For example, all of the table modifications required by replication are flashed back.
• Maintains data integrity as specified by constraints. Oracle preserves all dependent objects and the referential integrity.
• Provides the ability to revert it back to its original state even after a flashback operation.

Flashback Drop
Dropping of objects by accident has always been a problem for users and DBAs alike. Users soon realize their mistake but then it's too late and historically there is no easy way to recover those dropped tables, indexes, constraints, triggers, etc. Flashback Drop provides a safety net when dropping objects in Oracle Database 10g. When a user drops a table, Oracle automatically places it into the Recycle Bin.

What is the Recycle Bin?

The Recycle Bin is a virtual container where all dropped objects reside. Underneath the covers, the objects are occupying the same space as when they were created. If table EMP was created in the USERS tablespace, the dropped table EMP remains in the USERS tablespace. Dropped tables and any associated objects such as indexes, constraints, nested tables, and other dependant objects are not moved, they are simply renamed with a prefix of BIN$$. You can continue to access the data in a dropped table or even use Flashback Query against it. Each user has the same rights and privileges on Recycle Bin objects before they were dropped. You can view your dropped tables by querying the new RECYCLEBIN view. Objects in the Recycle Bin will remain in the database until the owner of the dropped objects decides to permanently remove them using the new PURGE command. The Recycle Bin objects are counted against a user's quota. But Flashback Drop is a non-intrusive feature. Objects in the Recycle Bin will be automatically purged by the space reclamation process if

• a user creates a new table or adds data that causes their quota to be exceeded.
• the tablespace needs to extend its file size to accommodate create/insert operations.

Dropped the wrong table? No problem. Just undrop it with Flashback Drop.

Flashback Query

Introduced with Oracle9i, Flashback Query provides the ability to view the data as it existed in the past. By default, operations on the database use the most recent committed data available. If you want to query the database as it was at some time in the past, you can do so with the Flashback Query feature. It lets you specify either a time or a system change number (SCN) and query using the committed data from the corresponding time. The Flashback Query mechanism is most effective when you use Automatic Undo Management.

The Oracle database treats undo as a first class database object. Undo is persistent and can survive database system crash or, shutdown. It also shares the database buffer cache with other database objects for better performance. The Oracle database uses undo beyond transaction commit to provide read consistency for long running queries and also, to recover from logical corruptions.

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 choice of undo retention value depends upon the length of the long running queries and the logical corruption recovery requirements. Users can, however, choose not to specify the undo retention and allow the system to provide the best retention for the given undo space. This best retention allows for best possible coverage for the long running queries and also, to recover from logical corruptions. The default undo retention is not guaranteed. The system can use oldest un-expired undo if it runs out of expired undo to use for an ongoing transaction.

New in Oracle Database 10g Release 1 is the ability to query data in the past for more than 5 days if the UNDO_RETENTION is set for greater then 5 days. Oracle will maintain the undo for that period of time as long as the Undo Tablespace datafiles are allocated enough disk space The following describes the steps required to ensure a database is enabled to use the Flashback Query and other flashback features that are dependent upon undo information:

1. Ensure that the database is using an undo tablespace. Setting the UNDO_MANAGEMENT initialization parameter to AUTO specifies this.
2. Set the UNDO_RETENTION initialization parameter to a value that causes undo to be kept for a length of time that allows success of your longest query back in time or to recover from human errors.
3. To guarantee that unexpired undo will not be overwritten, set the RETENTION GUARANTEE clause for the undo tablespace.

The unique feature of Flashback Query allows you to see the data as it was in the past, then choose exactly how to process the information. You might perform an analysis and then undo the changes, or capture changed data for further processing. The Flashback Query mechanism is flexible enough to be used in many situations. You can:

• query data as it existed in the past.
• compare current data with past data. You can compare individual rows or do more complex comparisons such as finding the intersection or union.
• recover deleted or changed data.

Flashback Versions Query

Flashback Versions Query provides a way to audit the rows of a table and retrieve information about the transactions that changed the rows. It retrieves all committed versions of the rows that exist or ever existed between the time the query was issued and a point in time in the past. It accomplishes this by utilizing Automatic Undo Management.

The Flashback Versions Query is an extension to SQL that allows you to retrieve the different versions of rows in a given table that existed in a specific time interval. For any given table, a new row version is created every time the COMMIT statement is executed. The Flashback Versions Query returns a row for each version of the row that existed in the time interval you specify. You invoke the Flashback Versions Query functionality by using the VERSIONS BETWEEN clause of the SELECT statement.

Flashback Versions Query offers new additional columns that provide transaction details on the row data that allows a DBA to pinpoint when and how data is changed in the Oracle database.

• VERSIONS_XID - The transaction id that created this version of the row
• VERSIONS_OPERATION - The action that created this version of the row (such as delete, insert, and update)
• VERSIONS_STARTSCN - The SCN in which this row version first occurred
• VERSIONS_ENDSCN -- The SCN in which this row version was changed.

The Flashback Versions Query is a powerful tool for the DBA to run analysis and answer the question, 'How did this happen?' Not only can the DBA run manual analysis, but this is a powerful tool for the application's developer as well. You can build customized applications for auditing purposes. Now everyone really is accountable for his or her actions.

Flashback Transaction Query

You may discover that somehow data in a table has been inappropriately changed. To research this change, you can use multiple flashback queries to view row data at specific points in time. More efficiently, you can use Flashback Versions Query feature to view all changes to a row over a period of time and the associated transaction id's. This feature allows you to append VERSIONS BETWEEN clause to a SELECT statement that specifies an SCN or timestamp range between which you want to view changes to row values.

Once you identify an erroneous transaction, you can then use the Flashback Transaction Query feature to identify other changes that were done by the transaction, and to request the undo SQL to reverse those changes. The FLASHBACK_TRANSACTION_QUERY view is the means by which you obtain transaction history and undo SQL.

If you need to reverse the effects of the erroneous transaction, the undo SQL statements can be manually executed, allowing easy recovery from user or application errors. Flashback Transaction Query can increase online diagnosability of problems in your database and facilitate analysis and audits of transactions.

Summary

Human errors are one of the predominant causes of system failure. These errors are extremely difficult to avoid and can be particularly difficult to recover without advance planning and the right technology. The 'right' technology is here: Oracle Database 11g. Why should an error that takes seconds to execute take hours or days to recovery from? It shouldn't and now it doesn't. Flashback revolutionizes recovery by operating on just the changed data. A single command surgically repairs corruptions from human errors. Flashback technology removes the complexity of recovery while decreasing the time it takes to recover from unpredictable human errors.