Oracle 10g Availability Enhancements, Part 2: Flashback Database

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:

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.