Getting Ready for Oracle Flashback Database

Use
this article as a primer to setting up an instance’s configuration for making
use of Oracle’s Flashback Database feature.

Oracle’s
flashback database feature is a great feature when you come to think of it. At
any point in time, you can (with proper permissions, configuration, and
administrative steps) recover a database to any point in time with a single
command. Experienced and Guru type DBAs have been doing this for years through
a series of well thought out steps that basically took a database through an
incomplete recovery. Now, with Oracle’s flashback database feature, just about
anyone can perform this highly surgical task of recovering a database to a
point in time quickly.

As with
any feature, especially one with such power, there are going to be a handful of
prerequisites that will be needed before attempting such a powerful task as
incomplete recovery. With the non-Guru in mind, this article looks at
configuring Oracle for Flashback Database using Enterprise Manager (RAC excluded):

1.  Ensure that the database is running in ARCHIVELOG mode,
as archived logs are a primary structure used in the reconstruction of data
during the FLASHBACK DATABASE operation.

The easiest way to confirm
your database is in archive log mode is to issue the ARCHIVE LOG LIST command
from SQL*Plus.


SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 374
Next log sequence to archive 383
Current log sequence 383

You can easily see that
the database is in fact in ARCHIVELOG mode, automatic archival is ENABLED (very
important), and that the archive destination is at USE_DB_RECOVERY_FILE_DEST.
The use of the parameter USE_DB_REZCOVERY_FILE_DEST is actually a redirect for
the DB_RECOVERY_FILE_DEST, which is the default location for the flash recovery
area. This location on disk can be seen through the SHOW PARAMETER command.


SQL> show parameter DB_RECOVERY_FILE_DEST

NAME TYPE VALUE
————————— ———– ——————————
db_recovery_file_dest string /oradata/db11FS/flash_recovery_area
db_recovery_file_dest_size big integer 1G

Now, if after issuing the
ARCHIVE LOG LIST command, you find your database not to be in archive log mode
(normally configured when you first created your database), you can easily configure
ARCHIVELOG mode through Enterprise Manager.

a.  Click on the Availability tab.

b.  Click on Recovery Settings under Setup under
the Backup/Recovery section. This will bring up the Recovery Settings
page.

c.  Check the ARCHIVELOG mode checkbox within the Media
Recovery
section to place this database in ARCHIVELOG mode.

d.  Click the Apply button to save.

e.  Click the Yes button to restart the database
when prompted to restart the database.

f.  
Enter your Host and Database
Credentials
and then click the OK button on the Restart Database:
Specify Host and Target Database Credentials page.

g.  Click Yes on the Restart Database:Confirmation
page to restart the database.

2.  Ensure that you have a flash recovery area defined and
enabled since flashback logs can only be stored in a flash recovery area.

From the ARCHIVE LOG LIST
and SHOW PARAMETER for DB_RECOVERY_FILE_DEST above, you may or may not have had
an actual flash recovery area defined. To enable a flash recovery area, four items
must be configured / enabled.

  • Flash Recovery Area Location
  • Flash Recovery Area Size
  • Enable Flashback Database
    (flashback logging)
  • Flashback Retention Time

a.  Click on the Availability tab

b.  Click on Recovery Settings under Setup under
the Backup/Recovery section. This will bring up the Recovery Settings
page.

c.  Within the Media Recovery section, you can see
that the last archiving location is set to USE_DB_RECOVERY_FILE_DEST—remembering
that USE_DB_RECOVERY_FILE_DEST points to the DB_RECOVERY_FILE_DEST, which is
the default location for the flash recovery area.

d.  Enter a Flash Recovery Area Location (DB_RECOVERY_FILE_DEST)
within the Flash Recovery section.

As a side note, the directory structure under the flash
recovery area will look something like this:


[[email protected] flash_recovery_area]$ tree
.
`– DB11FS
|– archivelog
| |– 2009_06_09
| | |– o1_mf_1_369_52xpslqx_.arc
| | |– o1_mf_1_370_52xpwoyq_.arc
| | |– o1_mf_1_371_52xq1bj2_.arc
| | |– o1_mf_1_372_52xrfjb4_.arc
| | |– o1_mf_1_373_52xt4d5x_.arc
| | |– o1_mf_1_374_52xwd716_.arc
| | |– o1_mf_1_375_52xz5zwq_.arc
| | |– o1_mf_1_376_52y2fsjn_.arc
| | |– o1_mf_1_377_52y32zlk_.arc
| | `– o1_mf_1_378_52y35l4t_.arc
| |– 2009_06_10
| | `– o1_mf_1_379_52zzh914_.arc
| |– 2009_06_17
| | `– o1_mf_1_380_53l8b0dx_.arc
| |– 2009_06_18
| | `– o1_mf_1_381_53nd3w05_.arc
| |– 2009_07_07
| | `– o1_mf_1_382_5570wktf_.arc
| `– 2009_07_08
| |– o1_mf_1_383_55b45l82_.arc
| `– o1_mf_1_384_55b5khpm_.arc
`– onlinelog

e.  You should also specify a Flash Recovery Area Size.

f.  
Check the Enable Flashback
Database
checkbox to enable flashback logging. As another side note, much
of the Oracle documentation mentions issuing the ALTER DATABASE FLASHBACK ON
command to enable flashback for the entire database. Checking this box, through
Enterprise Manager, is what will cause this command to be issued and can be
seen in the alert log.


ALTER DATABASE FLASHBACK ON
db_recovery_file_dest_size of 1024 MB is 76.55% used. This is a
user-specified limit on the amount of space that will be used by this
database for recovery-related files, and does not reflect the amount of
space available in the underlying filesystem or ASM diskgroup.
Allocated 3981204 bytes in shared pool for flashback generation buffer
Starting background process RVWR
Wed Jul 08 16:53:27 2009
RVWR started with pid=17, OS id=10650
Flashback Database Enabled
Turn database flashback on at SCN 2426421
Completed: ALTER DATABASE FLASHBACK ON

g.  Choose a Flashback Retention Time that makes
sense for your database.

h.  Click the Apply button to save.

i. 
Click the Yes button to
restart the database when prompted to restart the database.

j. 
Enter your Host and Database
Credentials
and then click the OK button on the Restart Database; Specify
Host and Target Database Credentials page.

k.  Click Yes on the Restart Database:Confirmation
page to restart the database.

At this point, you can now perform backups, including
archive logs, to the flash recovery area—making storage, recovery, and
persistence of those files much easier. In subsequent articles I’ll begin to
use this newly created flashback area to perform some flashback database
operations and determine some ways in which to monitor and tune the process. Best
of all, just as in this article, I’ll primarily be using Enterprise Manager
with very few breakouts to SQL*Plus or the command line. Should be interesting.

»


See All Articles by Columnist
James Koopmann

James Koopmann
James Koopmann
James Koopmann has fourteen years of database design, development and performance tuning experience. In addition, he has extensive database administration experience in Oracle and other relational databases in production environments, specializing in performance tuning of database engines and SQL based applications. Koopmann is an accomplished author with several technical papers in various Oracle related publications such as Oracle Magazine, Oracle Professional and SQL>UPDATE_RMOUG. He is a featured author and database expert for DatabaseJournal, a member of the editorial review committee for Select Journal (The Magazine for the International Oracle Users Group), an Oracle Certified Professional DBA and noted speaker at local Oracle User Groups around the country.

Latest Articles