Introduction
Flashback Database is a new approach to point-in-time (PIT) database recovery. This incomplete recovery strategy can be used to recover a database that has been logically corrupted due to human error. Introduced in 10g, it is designed to maximize availability by reducing recovery time. This article will explore Flashback Database, compare it against traditional recovery methods and demonstrate how to configure and perform a flashback recovery.
Traditional recovery vs. Flashback Database
It is widely accepted that logical corruption due to human error is the number one reason for downtime. Examples of logical corruption range from users erroneously updating data and truncating tables to batch jobs mistakenly run twice or out of order. The result is the same -- database corruption that is extensive or difficult to identify. Oracle offers two strategies to return a database to a prior point-in-time: traditional recovery and flashback database.
Incomplete recovery is recovery of the database to a prior state. There are two steps to this process: restoring data and forward-recovering transaction activity to a desired time. The major difference between traditional recovery and Flashback Database is that traditional recovery begins by restoring all data files prior to the desired recovery time while Flashback Database works backwards by restoring blocks changed after the corruption. To put this in perspective, let's consider a 10 TB database with 1 MB of corruption. Traditional recovery begins by restoring 10 TB of application data while Flashback Database backs out 1 MB of application data to arrive at a point before the corruption. Let's take a look at each of these strategies.
Traditional recovery
Prior to Oracle 10g, the only option to return a database back to a prior a point-in-time due to user error is traditional recovery. This strategy involves restoring all database data files from backup before perfor
To continue reading for free, register below or login
To read more you must become a member of SearchOracle.com
ming forward recovery to a desired point-in-time. Media recovery is either server-based (RMAN) or user-managed (OS utilities).
The following diagram illustrates this complex, costly and inefficient multi-step recovery strategy.
[IMAGE]
Here we see a user has executed SQL and corrupted the database. The user notifies the command center and reports the error. A system analyst manages the incident by coordinating the recovery with several individuals from different parts of the organization. Recovery is accomplished by restoring all data files from a backup and rolling forward redo logs to the desired point-in-time. Recovery time is proportional to the size of the database, not the amount of changes that need to be backed out. The mean time to recover (MTTR) actually increases as the database grows in size.
Flashback Database
In Oracle 10g, a new Flashback technology feature called Flashback Database has been introduced as an alternative to traditional recovery. Flashback Database lets you quickly recover an entire database to a prior point-in-time without restoring the database from a backup. Often described as a rewind button for the database, it works by restoring just changed blocks to a point just prior to the desired recovery time. Redo change records are then applied to reach the desired point-in-time. The changed data blocks are called Flashback Logs.
Flashback Database provides obvious benefits for transactional databases. Not so obvious are the advantages for analytical databases. In data warehousing, bulk operations are often done in a nologging mode. With Flashback Database, as long as the database is running in archive log mode, it can be returned to a state prior to the bulk operation because the changed blocks will be undone by the recovery.
Note: Although Flashback Database is integrated into the database, it is disabled in Oracle's Express Edition (XE).
The following diagram illustrates this simple, cheap and efficient self-service repair of the database.
[IMAGE]
Here we see a user has executed SQL and corrupted the database. The user notifies the application DBA who issues a Flashback Database command, automatically returning the database to a point prior to the corruption. Flashback Database is fast because it operates on only changed data. The flashback time is equal to the time it took to make the mistake, not the size of the database.
Configuring Flashback Database
The following example demonstrates a command line configuration. This can also be accomplished with Enterprise Manager.
Before we configure Flashback Database we will need to take care of some prerequisites.
Flash Recovery Area
First we need to configure a Flash Recovery Area (FRA). New in 10g, FRA is nothing more than a disk location for holding recovery-related files. For Flashback Database, a new background process called Recovery Writer (RVWR) periodically writes before images of data blocks from the SGA flashback buffer to disk as Flashback Logs in the FRA. Flashback Logs are automatically managed within the FRA by Oracle.
The cost of Flashback Logs is measured in space and performance. Space will be a factor of the write intensity of the database. A 24-hour retention with 5% of the database blocks being written as Flashback Logs will translate into a 5% increase in overall disk space. Because blocks are written at regular intervals rather than part of a transaction, impact on performance is negligible.
To configure FRA you will need to set the following initialization parameters:
Archiving
Next, we will need to configure archiving. Again, we can use the FRA as our archive log destination. Similar to traditional recovery, Flashback Database needs archiving to forward recover committed transactions after the Flashback Logs have been restored to a point just prior to the desired time.
To minimally configure archiving, execute the following command sequence:
Flashback Database
With the prerequisites out of the way, we're ready to configure Flashback Database.
First, we'll need to set the flashback retention target. This initialization parameter, measured in minutes, represents how far we can bring the database back in time. Its value translates into the amount and duration of Flashback Logs in the FRA. Our example below sets a retention target of 24 hours. It is important to understand that this retention is not guaranteed. If the FRA is in need of space, Flashback Logs may be automatically deleted before their target retention. We'll see a little later how we can guarantee our Flashback Logs are maintained in the FRA. With the retention set, Flashback Database can be enabled.
Flashback Database example
The following example is for illustratration purposes and is meant to represent corruption beyond a single table.
Conclusion
Flashback Database continues to be one of my favorite features of Oracle 10g. Whether you're correcting user errors, just looking at a prior state of the database or returning your test environment back after regression testing, this feature is the best strategy to reduce recovery time. We've seen this technology is easy to use, faster than traditional recovery and best of all, it's free! I hope you'll agree that Flashback Database is an essential component of an availability architecture.
About the author
Jeff McCormick is a senior data architect at a financial services company and executive director of the Connecticut Oracle User Group. Jeff has worked in IT for almost 20 years as a system, storage and database architect/administrator and has over 15 years of experience with DB2, Sybase, SQL Server, MySQL and Oracle relational database technology. He holds several certifications, including Oracle Database 10g Administrator Certified Professional, Microsoft Certified Product (SQL Server) and Certified Sybase Professional Database Administrator. Jeff has performed extensive work in the area of high availability and disaster recovery, speaking and authoring several papers on availability architecture.
