LLI Wiki — My Digital Knowledge Base

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.

Mastodon LLIMastodon SocialFosstodon