How far back can Oracle Flashback Database go
Query V$FLASHBACK_DATABASE_LOG and understand db_flashback_retention_target and the fast recovery area.
To find out how far back you can flash back the database, query the V$FLASHBACK_DATABASE_LOG view.
Query
alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS';
select * from v$flashback_database_log;
Sample result (illustrative values):
| OLDEST_FLASHBACK_SCN | OLDEST_FLASHBACK_TIME | RETENTION_TARGET | FLASHBACK_SIZE | ESTIMATED_FLASHBACK_SIZE |
|---|---|---|---|---|
| 907191 | 14-FEB-2009 12:37:20 | 1440 | 8192000 | 73728 |
So the database can be flashed back to any point between 14-FEB-2009 12:37:20 and now, or back to SCN 907191. The condition is that the flashback logs are available and so are the archived logs, because the flashback feature uses archived logs to undo the changes.
Columns of V$FLASHBACK_DATABASE_LOG
| Column | Meaning |
|---|---|
OLDEST_FLASHBACK_SCN |
Oldest SCN to which the database can be flashed back |
OLDEST_FLASHBACK_TIME |
Earliest time to which the database can be flashed back |
RETENTION_TARGET |
Target retention of the flashback logs, in minutes (parameter db_flashback_retention_target) |
FLASHBACK_SIZE |
Current size of the flashback data, in bytes |
ESTIMATED_FLASHBACK_SIZE |
Estimated size of flashback data needed for the current target retention |
DB_FLASHBACK_RETENTION_TARGET
This parameter decides how long the flashback logs should be kept. It behaves like undo_retention: the duration is a target, subject to conditions.
It depends on the size of the fast recovery area (FRA), set by DB_RECOVERY_FILE_DEST_SIZE. DB_FLASHBACK_RETENTION_TARGET tries to keep the logs for the requested time if there is enough space. If not, the FRA deletes the oldest logs to make room for new ones. Conversely, if there is plenty of space, logs can be kept longer than the time set in the parameter.
In the example above, FLASHBACK_SIZE is 8192000 (about 8 MB) and ESTIMATED_FLASHBACK_SIZE is 73728 (72 KB): the configured retention needs 72 KB and 8 MB are available, which is more than enough. On a really active database, the opposite is almost always true.