Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Dec 23, 2004

Oracle 10g Availability Enhancements, Part 2: Flashback Database

By Jim Czuprynski

Synopsis. Oracle 10g offers significant enhancements that help insure the high availability of any Oracle database, as well as improvements in the database disaster recovery arena. This article - part two of a series - explores one of the most intriguing new features of Oracle 10g: Flashback Backup and Recovery.

The previous article in this series explored the myriad enhancements to Recovery Manager (RMAN) that Oracle 10g has added to an Oracle DBA's tool belt when constructing a well-planned backup and recovery strategy.

Oracle 9i provided the capability to "flash back" to a prior view of the database via queries performed against specific logical entities. For example, if a user had accidentally added, modified or deleted a large number of rows erroneously, it was now possible to view the state of logical entity just before the operation had taken place. This capability was limited, of course, by the amount of UNDO data retained in the database's UNDO segments and bounded by the time frame specified by the UNDO_RETENTION initialization parameter. Oracle 10g expands these logical flashback capabilities significantly, and I will cover them in detail in the next article in this series.

However, when a DBA needed to return an entire database back to a prior state to recover from a serious logical error - for example, when multiple erroneous transactions within the same logical unit of work have affected the contents of several database tables - a logical option was to perform an incomplete database recovery. Since an incomplete recovery requires that all datafiles are first restored from the latest backup, and then a careful "roll forward" recovery through the appropriate archived and online redo logs until the appropriate point in time was reached, the database would be unavailable until this process was completed.

With the addition of Flashback Database, Oracle 10g has significantly improved the availability of a database while it's restored and recovered to the desired point in time. These new features, however, do take some additional effort to plan for and set up, so let's start at the beginning: configuring the Flash Recovery Area.

Enabling The Flash Recovery Area

Before any Flash Backup and Recovery activity can take place, the Flash Recovery Area must be set up. The Flash Recovery Area is a specific area of disk storage that is set aside exclusively for retention of backup components such as datafile image copies, archived redo logs, and control file autobackup copies. These features include:

Unified Backup Files Storage. All backup components can be stored in one consolidated spot. The Flash Recovery Area is managed via Oracle Managed Files (OMF), and it can utilize disk resources managed by Oracle Automated Storage Management (ASM). In addition, the Flash Recovery Area can be configured for use by multiple database instances if so desired.

Automated Disk-Based Backup and Recovery. Once the Flash Recovery Area is configured, all backup components (datafile image copies, archived redo logs, and so on) are managed automatically by Oracle.

Automatic Deletion of Backup Components. Once backup components have been successfully created, RMAN can be configured to automatically clean up files that are no longer needed (thus reducing risk of insufficient disk space for backups).

Disk Cache for Tape Copies. Finally, if your disaster recovery plan involves backing up to alternate media, the Flash Recovery Area can act as a disk cache area for those backup components that are eventually copied to tape.

Flashback Logs. The Flash Recovery Area is also used to store and manage flashback logs, which are used during Flashback Backup operations to quickly restore a database to a prior desired state.

Sizing the Flash Recovery Area. Oracle recommends that the Flash Recovery Area should be sized large enough to include all files required for backup and recovery. However, if insufficient disk space is available, Oracle recommends that it be sized at least large enough to contain any archived redo logs that have not yet been backed up to alternate media.

Table 1 below shows the minimum and recommended sizes for the Flash Recovery Area based on the sizes of these database files in my current Oracle 10g evaluation database:

Table 1. Sizing The Flash Recovery Area

Database Element

Estimated Size (MB)

Image copies of all datafiles


Incremental backups


Online Redo Logs


Archived Redo Logs retained for backup to tape


Control Files


Control File Autobackups


Flash Recovery Logs


Recommended Size:


Minimum Size:


Based on these estimates, I will dedicate 2GB of available disk space so I can demonstrate a complete implementation of the Flash Recovery Area.

Setting Up the Flash Recovery Area. Activation of the Flash Recovery Area specifying values for two additional initialization parameters:

  • DB_RECOVERY_FILE_DEST_SIZE specifies the total size of all files that can be stored in the Flash Recovery Area. Note that Oracle recommends setting this value first.
  • DB_RECOVERY_FILE_DEST specifies the physical disk location where the Flashback Recovery Area will be stored. Oracle recommends that this be a separate location from the database's datafiles, control files, and redo logs. Also, note that if the database is using Oracle's new Automatic Storage Management (ASM) feature, then the shared disk area that ASM manages can be targeted for the Flashback Recovery Area.

Activating the Flash Recovery Area. It is obviously preferable to set up the Flash Recovery Area when a database is being set up for the first time, as all that needs to be done is to make the changes to the database's initialization parameters. However, if the Flash Recovery Area is being set up for an existing database, all that's required to do is issue the appropriate ALTER SYSTEM commands.

Listing 2.1 shows the changes I have made to the database's initialization parameter file, including an example of how to insure that an additional copy of the database's archived redo logs is created in the Flash Recovery area.

Listing 2.2 shows the commands to issue to set up the Flash Recovery Area when the database is already open before flashback logging has been activated.

Oracle Archives

Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM