Oracle Flashback Technology

Ron Weiss, Server Technologies, Oracle Corporation

A complete high availability solution must handle all the different causes of downtime.  Data loss must be prevented, and the means to quickly recover from it must be provided. Almost any research done on the causes of downtime identifies human error as the single largest cause of downtime.  When authorized people make mistakes you need the tools to correct these errors.  In the Oracle9i Database we introduced Flashback Query.  But that was just the start. In the Oracle Database 10g we are providing a whole family of Flashback technology to handle recovery from human errors. 

In the past it might take minutes to damage a database but hours to repair it.  In many cases the time to correct errors using Flashback equals the time it took to make the error.  Flashback is also extremely easy to use and a single short command can be used to recover the entire database instead of performing complex media recovery.  Flashback provides fine-grained surgical analysis and repair for localized damage – like when the wrong customer order is deleted.  Flashback also allows for correction of more widespread damage yet does it quickly to avoid long downtime – like when all of this month’s customer orders have been deleted.  Flashback is unique to the Oracle Database 10g and supports recovery at any level of granularity including the row, transaction, table, and database wide.

• Flashback Query allows the user to query data at some point-in-time in the past to reconstruct lost data that may have been deleted or changed by accident
• Flashback Versions Query provides a mechanism to view changes made to the database over time at the row level.
• Flashback Transaction Query provides a mechanism to view changes made to the database at the transaction level.
• Flashback Database is a new strategy for doing point-in-time recovery.  It quickly rewinds an Oracle database to a previous time to correct any problems caused by logical data corruption or user error. 
• Flashback Table provides the ability to recover a table, or a set of related tables, to a specified point in time in the past very quickly and easily.
• Flashback Drop provides a safety net when dropping objects as you can very quickly and easily undrop a table and its dependent objects.
• Flash Backup & Recovery to and from inexpensive modular storage to enable very fast media recovery and backups.

Let’s look at just one simple example of the power of Flashback. To bring an Oracle database to a previous point in time, the traditional method was to do point-in-time recovery.  However, point-in-time recovery can take many hours since it requires the whole database be restored from a backup and recovered to the point in time just before the error was introduced into the database.  With the size of databases constantly growing it will take hours, or even days, just to restore the whole database. Let’s compare the two methods of database-wide recovery – Flashback Database versus Classic Point-In-Time Recovery.

The depicted scenario is:

• A bad batch job is run and the customer wants to roll back all changes made to the database over the last 10 minutes to undo the damage caused by the bad batch job.
• A full database backup was taken the previous evening.
• Recovery from this problem using classic point-in-time recovery technology first requires that a restore of the database files be done (identified by “Restore” in blue on the chart).

• High speed tape drives providing 30 MB/sec bandwidth were used for these restores.

• For each database a different number of tape drives was used - i.e., larger databases require more tape drives to backup within the available backup window or restore to meet MTTR goals.

• Then a roll forward through all the redo logs generated since last evenings backups (identified by "Recovery" in yellow on the chart) must be performed.

• Recovery using Flashback Database requires just the use of the FLASHBACK command and takes single-digit minutes  (identified by "Flashback" in red on the chart - look closely since it's so small).

• Bottom Line:

• Flashback Database is 25 to 105 times faster.

• The damage is undone VERY quickly.

• The application is back in service MUCH sooner.

• Recovery time is independent of database size.

Flashback is definitely a technology whose time has come. Other database vendors do not offer anything comparable to the Oracle Flashback technology.    Oracle’s Flashback technology is superior in every way. 

• Oracle Database 10g Flashback is be much simpler to use since it is integrated with the database.

• Single command performs all the operations needed to restore the database to a point in time, recover a dropped table, undo changes to a table, etc.

• Trivial to configure - just supply the disk space and go.

• Can restore a database to an exact and consistent point in time - by specifying time or transaction (SCN).

• Oracle Database 10g Flashback works in every configuration. It works everywhere the database runs.

• Works transparently across any/all storage devices - storage box technologies generally do not.

• Works transparently across file systems and volumes - host based technologies generally do not.

• Works transparently (and scales) on a cluster - host based technologies generally do not.

• Works on any operating system - host based technologies are generally limited to only a few platforms.

• Is designed to scale to exabyte databases, thousands of users, hundreds of CPUs, the Grid, as well as single CPU workstations and blades.

• Oracle Database 10g Flashback is more efficient because it is integrated with the database.

• No need to read old versions of blocks from disk in most cases since they are already in the database buffer cache.

• Integrated with database crash recovery and hence avoids the need for synchronous writes that slow down the database.

• Does not add latency to database writes - writes to flashback logs are scheduled on first change to a block, not when the block is written to disk.

• Uses presence of redo logs to avoid flashback logging in many cases.

• Uses knowledge of data being written to avoid most writes to flashback logs (e.g. temp, redo, archive, loads, etc.).

• Flashback table, drop, query work at the logical level of rows, transactions and tables. External technologies only know about blocks and cannot recover rows or tables.

And all of this functionality is seamlessly integrated into Oracle Database 10g and is included at no extra charge.

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

Ron Weiss (Ron.Weiss@Oracle.com) is Director of Product Management in Oracle’s database kernel development group. He is responsible for OLTP database technology including high availability, disaster recovery, backup and recovery, and storage management. He has worked in the IT industry for over 25 years in the areas of operating systems, hardware and storage systems, and database.