Oracle Tips by Burleson Consulting
is an excerpt from "Oracle 10g New Features for Administrators" by
How Flashback Database Works
• Once you enable the flashback database feature, at
regular intervals, a new process RVWR
(RecoveryWriter) copies images of each altered block in the
datafiles from memory (flashback buffer) to the new
• Oracle stores these flashback logs in the
flashback recovery area.
• If you want to flashback to 8:00 A.M., it may turn
out that the flashback logs nearest to the target time were
written at 7:56 A.M. To cover this gap, you must apply the changes
from archived or online redo log files pertaining to that period.
• Always remember that Oracle doesn’t guarantee that
you can flashback your database to the flashback retention target.
If Oracle is running low on free space in the flash recovery area
for newly arriving archived redo log files, it will remove some
flashback logs to make room.
Flashback Database Considerations
• If a datafile was resized during the time span
covered by the Flashback Database operation, you can’t flashback
that datafile. Instead, you must offline that particular datafile
before you start the flashback database operation.
• If a control file has been restored or re-created
during the time span you want to flashback over, you can’t use the
Flashback Database feature.
• You can’t flashback a database to before a
• You can’t flashback a datafile that was dropped or
shrunk during the time span covered by the flashback table
Configuring Flashback Database
1. Ensure that your database is in the archivelog
ARCHIVE LOG LIST (in SQL*Plus)
2. Your database must be using the flash recovery
SELECT VALUE FROM
V$PARAMETER WHERE NAME = 'db_recovery_file_dest'
3. You must set the initialization parameter
DB_FLASHBACK_RETENTION_TARGET to set your flashback retention target
ALTER SYSTEM SET
4. Shut down the database and restart in the MOUNT
5. Turn the flashback database feature on with the
6. Use the ALTER DATABASE OPEN command to open the
FLASHBACK_ON FROM V$DATABASE;
Note: You can turn the feature off by using the
ALTER DATABASE FLASHBACK OFF command while the database in the MOUNT
EXCLUSIVE mode. When you do so, Oracle deletes all flashback
database logs in the flash recovery area.
Note: If you don’t want certain tablespaces to
be part of your flashback operations, issue the following command
after setting the tablespace offline:
USERS FLASHBACK OFF
Flashbacking a Database
1. Restart the database in the MOUNT (exclusive)
mode then issue one of the commands:
TO SCN 5964663
FLASHBACK DATABASE TO BEFORE SCN 5964663
FLASHBACK DATABASE TO TIMESTAMP (SYSDATE -1/24)
FLASHBACK DATABASE TO SEQUENCE 12345
2. Open the database with READ ONLY option to check
that the database flashed back to the correct time.
3. If you decide to go back further in time, you can
flashback the database again.
4. If you determine that you flashed back too far
into the past, you can use redo logs to roll forward.
5. Open the database with RESETLOGS option:
6. If you want to completely undo the effects of the
flashback database operation, just use the command RECOVER DATABASE
to perform a complete recovery of the database.
Displaying Flashback Storage Information
In order to estimate the space you need to add to
your flash recovery area for accommodating the flashback database
RETENTION_TARGET, FLASHBACK_SIZE FROM
To really know how far back you can flashback your
database at any given time, you must query the
V$FLASHBACK_DATABASE_LOG in the following manner:
The view V$FLASHBACK_DATABASE_STATS helps you
monitor the I/O overhead of logging flashback data.
BEGIN_TIME and END_TIME - stands for the beginning
and ending hourly time intervals for which the view’s statistics
were collected. Oracle collects flashback data on an hourly basis
for a maximum of 24 hours. If you issue a query on the table,
however, it may return 25 rows, the 25th row being for the most
recent fraction of time after the last (24th) row was logged in the
FLASHBACK_DATA - stands for the number of bytes of
flashback data written during the interval.
DB_DATA - stands for the number of bytes of database
data read and written during the interval.
REDO_DATA - stands for the number of bytes of redo
data written during the interval.
ESTIMATED_FLASHBACK_SIZE - is identical to the value
of the ESTIMATED_FLASHBACK_SIZE column in the
If you like Oracle tuning, see the
Tuning: The Definitive Reference", with 950 pages of tuning
tips and scripts.
You can buy it direct from the publisher for 30%-off and get
instant access to the code depot of Oracle tuning scripts.