High availability with Flashback Database

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 performing 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.

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.

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:

 alter system set db_recovery_file_dest= 'C:\oracle\product\10.2.0\flash_recovery_area' scope=both; alter system set db_recovery_file_dest_size = 10G scope=both;

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:

 SQL> startup mount ORACLE instance started. . . . Database mounted. SQL> alter database archivelog; Database altered. SQL> alter database open; Database altered. SQL> archive log list Database log mode Archive Mode Automatic archival Enabled Archive destination USE_DB_RECOVERY_FILE_DEST Oldest online log sequence 2 Next log sequence to archive 4 Current log sequence 4

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.

 SQL> startup mount; ORACLE instance started. . . . Database mounted. SQL> alter system set db_flashback_retention_target = 1440 scope=both; System altered. SQL> alter database flashback on; Database altered. SQL> alter database open; Database altered. SQL> select flashback_on from v$database; FLASHBACK_ON ------------------ YES

Flashback Database example

The following example is for illustratration purposes and is meant to represent corruption beyond a single table.

1. Monitor the Flash Recovery Area

    select name,space_limit,space_used, space_reclaimable from v$recovery_file_dest; NAME SPACE_LIMIT SPACE_USED SPACE_RECLAIMABLE -------------------------------------------- ----------- ---------- ----------------- C:\oracle\product\10.2.0/flash_recovery_area 2147483648 166646272 0 select * from v$flash_recovery_area_usage; FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES ------------ ------------------ ------------------------- --------------- CONTROLFILE 0 0 0 ONLINELOG 0 0 0 ARCHIVELOG 7.38 0 29 BACKUPPIECE 0 0 0 IMAGECOPY 0 0 0 FLASHBACKLOG .38 0 1
   

2. Display the before image of a table

    select c1, ora_rowscn from my_table; C1 ORA_ROWSCN ---------- ---------- 1 1320954
   

3. Determine point-in-time of the database

   In 10gR1 you have two choices to capture the PIT of your database: timestamps and System Change Numbers (SCNs). This information is required as part of the Flashback operation. It is important to capture the commit SCN or later, not the SCN of the DML operation. Oracle provides a clumsy way of capturing the commit SCN with the userenv('commitscn') function. Our example captures this information before the corrupting DML operation.

    select current_scn from v$database; CURRENT_SCN ----------- 1321065
   

   or

    select to_char(sysdate,'YYYY-MM-DD:HH24:MI:SS') "Recover Time" from v$database; Recover Time ------------------- 2006-09-23:20:13:48
   

   In 10gR2, Oracle has simplified this process with restore points. A restore point is a user-defined name associated with a database PIT and can be used in place of timestamps or SCNs. Think of a restore point as a referencable bookmark of redo history. Restore points are kept in the control file until the restore point is dropped or Flashback Logs are deleted. The second example guarantees Flashback Logs are available for recovery.

    create restore point my_restore_point; Operation 206 succeeded.
   

   or

    create restore point my_restrore_point guarantee flashback database;
   

   Note: A restore point does not guarantee all transactions have committed at that point-in-time. This should not be confused with a quiesce point in such RDBMs as DB2.

    

4. Simulate database corruption

    insert into my_table values (2); 1 row created. commit; Commit complete.
   

5. Determine the database has been logically corrupted due to human error

    select c1, ora_rowscn from my_table; C1 ORA_ROWSCN ---------- ---------- 1 1320954 2 1321231
   

   Note: By default Oracle tracks SCNs at the block level. Consequently, all rows in the block will have the same SCN. Enable row-level SCN tracking by using the ROWDEPENDENCIES keyword in the CREATE TABLE command.

    

6. Verify Flashback Database is possible

   Determine the earliest time you can flashback.

    SELECT OLDEST_FLASHBACK_SCN ,to_char(OLDEST_FLASHBACK_TIME,'YYYY-MM-DD:HH24:MI:SS') "OLDEST_FLASHBACK_TIME" FROM V$FLASHBACK_DATABASE_LOG; OLDEST_FLASHBACK_SCN OLDEST_FLASHBACK_TI -------------------- ------------------- 1319629 2006-09-23:19:51:56
   

   Determine if you have a restore point.

    select name, scn, time from v$restore_point; NAME SCN TIME ---------------- ---------- ---------------------------- MY_RESTORE_POINT 1321136 23-SEP-06 08.16.24.000000000 PM
   

   Here are some other views of interest: v$flashback_database_logfile and v$flashback_database_stat.

    

7. Flashback the database

   You can perform Flashback Database with SQL*Plus or RMAN. Flashback Database can be time, change and restore point based. RMAN provides an additional log sequence based option.

   Flashback the Database using our previously created restore point.

    SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> startup mount ORACLE instance started. . . . Database mounted. SQL> flashback database to restore point my_restore_point; Flashback complete.
   

   Review Flashback Database messages in the alert log.

    . . . Sat Sep 23 20:38:11 2006 flashback database to restore point my_restore_point Sat Sep 23 20:38:12 2006 Flashback Restore Start Flashback Restore Complete Flashback Media Recovery Start parallel recovery started with 2 processes Sat Sep 23 20:38:14 2006 Recovery of Online Redo Log: Thread 1 Group 2 Seq 33 Reading mem 0 Mem# 0 errs 0: C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO02.LOG Sat Sep 23 20:38:16 2006 Incomplete Recovery applied until change 1321137 Flashback Media Recovery Complete Completed: flashback database to restore point my_restore_point . . .
   

8. Verify your database is recovered to the desired state

   If you're not satisfied, you can flashback again, bring the database forward with a recover database until or perform complete recovery: recover database.

   Note: Flashback Database can be performed through RESETLOGS.

    SQL> alter database open read only; Database altered. SQL> select c1, ora_rowscn from my_table; C1 ORA_ROWSCN ---------- ---------- 1 1321002
   

9. Open the database for general use

   Satisfied with our recovery, we'll open the database for general use.

    SQL> shutdown; Database closed. Database dismounted. ORACLE instance shut down. SQL> startup mount; ORACLE instance started. . . . Database mounted. SQL> alter database open resetlogs; Database altered.
   

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.