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.
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
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
Image copies of all
Online Redo Logs
Archived Redo Logs retained
for backup to tape
Control File Autobackups
Flash Recovery Logs
Based on these estimates, I will dedicate 2GB of available
disk space so I can demonstrate a complete implementation of the Flash Recovery
Setting Up the Flash Recovery Area. Activation of the
Flash Recovery Area specifying values for two additional initialization
specifies the total size of all files that can be stored in the Flash Recovery
Area. Note that Oracle recommends setting this value first.
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
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.
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.
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
Enabling Flashback Database
As its name implies, Flashback Database offers the
capability to quickly "flash" a database back to its prior state as
of a specified point in time. Oracle does this by retaining a copy of any
modified database blocks in flashback logs in the Flash Recovery Area. A
new flashback log is written to the Flash Recovery Area on a regular basis
(usually hourly, even if nothing has changed in the database), and these logs
are typically smaller in size than an archived redo log. Flashback logs have a
file extension of .FLB.
When a Flashback Database request is received, Oracle then
reconstructs the state of the database just prior to the point in time
requested using the contents of the appropriate flashback logs. Then the
database's archived redo logs are used to fill in the remaining gaps between
the last backup of the datafile and the point in time desired for recovery.
The beauty of this approach is that no datafiles need to
be restored from backups; further, only the few changes required to fill in
the gaps are automatically applied from archived redo logs. This means that
recovery is much quicker than traditional incomplete recovery methods, with
much higher database availability.
It is worth noting the few prerequisites that must be met
before a database may utilize Flashback Database features:
The database must have flashback logging enabled, and therefore a
Flash Recovery Area must have been configured. (For a RAC environment, the
Flash Recovery Area must also be stored in either ASM or in a clustered file
Since archived redo logs are used to "fill in the gaps"
during Flashback Database recovery, the database must be running in ARCHIVELOG
Activating Flashback Database. Once the Flash
Recovery Area has been configured, the next step is to enable Flashback Database
by issuing the ALTER DATABASE FLASHBACK ON;
command while the database is in MOUNT EXCLUSIVE mode, similar to activating a
database in ARCHIVELOG mode.
Setting the Flashback Retention Target. Once
Flashback Database has been enabled, the DB_FLASHBACK_RETENTION_TARGET initialization parameter
determines exactly how far a database can be flashed back. The default value is
1440 minutes (one full day), but this can be modified to suit the needs of your
database. For purposes of illustration, I have set my demonstration database's
setting to 2880 minutes (two full days).
Deactivating Flashback Database. Likewise, issuing
the ALTER DATABASE FLASHBACK OFF; command
deactivates Flashback Backup and Recovery. Just as in the activation process, note
that this command must be issued while the database is in MOUNT EXCLUSIVE mode.
2.3 for queries that display the status of the Flash Recovery Area,
status of the related initialization parameters, and whether the database has
been successfully configured for flashback.
Storing Backups In Flash Recovery Area
Now that I have enabled the Flash Recovery Area and enabled
flashback logging, I can next turn my attention to preparing the database to
use flashback logs during a Flashback Database recovery operation.
2.4 lists the RMAN commands I will need to issue to configure the
database for Flash Recovery Area and Flashback Database use. Notice that I have
not CONFIGUREd a FORMAT directive for the RMAN channels used to create database
backups; for these examples, I am going to let RMAN place all backup components
directly in the Flash Recovery Area.
2.5 implements Oracle's recommended daily RMAN backup scheme using
datafile image copies and incrementally-updated backups. (See the previous
article in this series for a full discussion of this technique.)
2.6 shows the abbreviated results of the first cycle's run of this
backup scheme. Note that Oracle uses OMF naming standards for each backup
component file - in this example, datafiles, the "extra copy" of the
archived redo logs, and control file autobackups - stored in the Flash Recovery
Flashback Database: An Example
Now that I have enabled flashback logging and have created
sufficient backup components that are being managed in the Flash Recovery Area,
it is time to demonstrate a Flashback Database operation.
Let's assume a worst-case scenario: One of my junior developers
has been enthusiastically experimenting with logical units of work on what he thought
was his personal development database, but instead mistakenly applied a
transaction against the production database. He has just accidentally deleted
several thousand entries in the SH.SALES and SH.COSTS tables - just in time to
endanger our end-of-quarter sales reporting schedule, of course! Here is the
DML statements issued, along with the number of records removed:
DELETE FROM sh.sales
WHERE prod_id BETWEEN 20 AND 80;
10455 rows deleted
Executed in 89.408 seconds
DELETE FROM sh.costs
WHERE prod_id BETWEEN 20 AND 80;
6728 rows deleted
Executed in 18.086 seconds
Executed in 0.881 seconds
Flashback Database to the rescue! Since I know the
approximate date and time that this transaction was committed to the database, I
will issue an appropriate FLASHBACK DATABASE command from within an RMAN
session to return the database to that approximate point in time. Here is a
more complete listing of the FLASHBACK DATABASE command set:
FLASHBACK [DEVICE TYPE = <device type>] DATABASE
TO [BEFORE] SCN = <scn>
TO [BEFORE] SEQUENCE = <sequence> [THREAD = <thread id>]
TO [BEFORE] TIME = '<date_string>'
Note that I can return the database to any prior point in
time based on a specific System Change Number (SCN), a specific redo
log sequence number (SEQUENCE), or to a specific date and time (TIME).
If I specify the BEFORE directive, I am telling RMAN to flash the database back
to the point in time just prior to the specified SCN, redo log, or time,
whereas if the BEFORE directive is not specified, the database will be flashed
back to the specified SCN, redo log, or time as of that specified point in
time, i.e., inclusively.
First, I queried my database's Flashback Logs to determine
which ones are available, found the log just prior to the user error and
decided to flash back the database based on that log's starting SCN. Listing 2.7
contains the query I ran against V$FLASHBACK_DATABASE_LOGFILE to obtain this
Just as I would do during a normal point-in-time incomplete
recovery, I then shut down the database by issuing the SHUTDOWN IMMEDIATE command, and then restarted
the database and brought it into MOUNT mode via the STARTUP MOUNT command. Instead of having to
perform a restoration of datafiles as in a normal incomplete recovery, I
instead simply issue the appropriate FLASHBACK DATABASE command to take the
database back to the SCN I desired.
Once the flashback is completed, I could have continued to
roll forward additional changes from the archived redo logs available; however,
I simply chose to open the database at this point in time via the ALTER
DATABASE OPEN RESETLOGS; command. Here are the actual results from the RMAN
C:>rman nocatalog target sys/@zdcdb
Recovery Manager: Release 10.1.0.2.0 - Production
Copyright (c) 1995, 2004, Oracle. All rights reserved.
connected to target database: ZDCDB (DBID=1863541959)
using target database controlfile instead of recovery catalog
RMAN> FLASHBACK DEVICE TYPE = DISK DATABASE TO SCN = 2127725;
Starting flashback at 08-DEC-04
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=158 devtype=DISK
starting media recovery
media recovery complete
Finished flashback at 08-DEC-04
RMAN> alter database open resetlogs;
To see what is really going on during the flashback and
recovery process, I have also included a portion
of the database's alert log. Note that Oracle automatically cleaned up after
itself: Since they are of no use any longer after the RESETLOGS operation,
Oracle even deleted the outmoded Flashback Logs from the Flashback Recovery
Oracle 10g's Flash Recovery Area simplifies the storage
and handling of backup components and flashback logs, and the new Flashback
Database features provide any Oracle DBA with a much improved, faster option
for incomplete database recovery. The next article in this series will delve
into the details of using Oracle 10g's expanded Logical Flashback features,
including some intriguing capabilities for recovering from logical errors at a
much more granular level than Flashback Database provides.
References and Additional Reading
While there is no substitute for direct experience, reading
the manual is not a bad idea, either. I have drawn upon the following Oracle
10g documentation for the deeper technical details of this article:
B10734-01 Oracle Database
Backup and Recovery Advanced User's Guide
B10735-01 Oracle Database
Backup and Recovery Basics
B10750-01 Oracle Database
New Features Guide
B10770-01 Oracle Database
Recovery Manager Refererence
See All Articles by Columnist Jim Czuprynski