Question: If I set db_flashback_retention_target=600,
this means i can restore database to any time between sysdate and
Let's assume I set db_flashback_retention_target=600; and
db_recovery_file_dest_size=20g. but my transaction is much more than
20gb in 10 hours. what happens when the destination file becomes full?
When I reach the time limit for db_recovery_file_dest_size; will
oracle continue to add new flashback logs or reuse older ones in
circular manner? Also, Will the size of "flashback recovery area"
ever exceed the size of db_recovery_file_dest_size?
Answers: The db_flashback_retention_target parameter
specifies the upper limit (in minutes) on how far back in time the
database may be flashed back.
Default value – 1440 (minutes)
Modifiable – ALTER SYSTEM
Range of values – 0 to 2 32 - 1 (maximum value
represented by 32 bits)
db_flashback_retention_target specifies the
upper limit (in minutes) on how far back in time the database may be
flashed back. How far back one can flash back a database depends on
how much flashback data Oracle has kept in the recovery area.
The following statement changes the flashback
time from the default of one day to two days:
system set db_flashback_retention_target = 2880;
The following statement changes the flashback time from the default
of 1 day to two days.
SQL> alter system set db_flashback_retention_target =
How far back you can flash back also depends on how much flashback
data Oracle has kept in the flashback recovery area.
exceed the size of your flashback recovery area, the offending task will
fail, and roll back with this error:
ORA-38700: Limit of %s flashback
database logs has been exceeded.
ever receive the out-of-space warning (85) and critical alerts (97)
because of space pressure in you flash recovery area, you have the
Consider changing your backup retention and archive log retention
Increase the size of the DB_RECOVERY_FILE_DEST_SIZE parameter to
allocate more space to your current flash recovery area.
the BACKUP RECOVERY AREA command in the RMAN to back up the contents
of the flash recovery area to a tape device.
the RMAN to delete unnecessary backup files. The RMAN commands
CROSSCHECK and DELETE EXPIRED come in handy during this deletion
monitor the flash recovery area with the v$recovery_file_dest
to_char(space_limit, '999,999,999,999') as space_limit,
to_char(space_limit - space_used + space_reclaimable,
'999,999,999,999') as space_available,
round((space_used - space_reclaimable)/space_limit * 100, 1) as
Oracle scripts collection
for the full set of flashback monitoring scripts
recovery area may contain the following files:
Datafile copies: The new RMAN command BACKUP AS COPY can be used
to create image copies of all datafiles and automatically store in
the flash recovery area.
Control file autobackups: The database places any control file
backups it generates in the flash recovery area.
Archived redo log files: If you store Archived redo log files in
the flash recovery area, Oracle will automatically delete the files.
Online redo log files: Oracle recommends that you save a
multiplexed copy of your online redo log files in the flash recovery
area. The following statements can create online redo logs in the
flash recovery area:
CREATE DATABASE, ALTER DATABASE ADD LOGFILE, ALTER DATABASE ADD
STANDBY LOGFILE, and ALTER DATABASE OPEN RESETLOGS.
Current control files: Oracle also recommends that you store a
multiplexed current control file in the flash recovery area.
Flashback logs: If you enable the flashback database feature,
Oracle copies images of each altered block in every datafile into
flashback logs stored in the flash recovery area.
Oracle calls the multiplexed redo log files and control files in the
flash recovery area permanent files, since they should never be deleted
and are part of the live database. Oracle terms all the other files in
the flash recovery area (recovery related files) transient files, since
Oracle will delete them eventually after they have become obsolete or
have already been copied to tape.
Related flashback errors for RAC include:
ORA-38767: flashback retention target
parameter mismatch Cause: The value of parameters
db_flashback_retention_target must be same in all instances.