Question: If I set db_flashback_retention_target=600,
this means i can restore database to any time between sysdate and
sysdate-10/24?
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.
How far back you can flash back also depends on how much flashback
data Oracle has kept in the flashback recovery area.
If you
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.
If you
ever receive the out-of-space warning (85) and critical alerts (97)
because of space pressure in you flash recovery area, you have the
following options:
-
Consider changing your backup retention and archive log retention
policies.
-
Increase the size of the DB_RECOVERY_FILE_DEST_SIZE parameter to
allocate more space to your current flash recovery area.
-
Use
the BACKUP RECOVERY AREA command in the RMAN to back up the contents
of the flash recovery area to a tape device.
-
Use
the RMAN to delete unnecessary backup files. The RMAN commands
CROSSCHECK and DELETE EXPIRED come in handy during this deletion
process.
You can
monitor the flash recovery area with the v$recovery_file_dest
view:
select
name,
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
pct_full
from
v$recovery_file_dest;
See the
Oracle scripts collection
for the full set of flashback monitoring scripts
The flash
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.
-
RMAN files
-
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.
Note:
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.
