Set Oracle Flashback Database Retention Target with ALTER SYSTEM

calendar Mar 6, 2026 / · 5 min read · Oracle Flashback DBA Recovery Database Administration ALTER SYSTEM Fast Recovery Area Point In Time Recovery  ·
Share on: linkedin copy

Set Oracle Flashback Database Retention Target with ALTER SYSTEM

Oracle Flashback Database lets you rewind your entire database to a past point in time without a full restore. The command ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET controls how far back in time that rewind can go.

What This Command Does

The DB_FLASHBACK_RETENTION_TARGET parameter sets the upper limit, in minutes, on how far back you can flashback your database. Oracle uses this setting to decide how much flashback log data to keep inside the Fast Recovery Area (FRA). Think of it as a time window — anything older than the target may be cleaned up to free space.

The Code

1-- Set the flashback database retention target
2ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET=<minutes> SCOPE=BOTH;
3/

Replace <minutes> with the number of minutes you want Oracle to retain flashback data.

Common examples:

Retention WindowMinutes Value
1 day (default)1440
2 days2880
3 days4320
7 days10080

Breakdown of the Code

ALTER SYSTEM This is an Oracle DDL statement that changes a system-level initialization parameter. It affects the entire database instance, not just your current session.

SET DB_FLASHBACK_RETENTION_TARGET=<minutes> This sets the parameter value. The value is always in minutes (integer). For example, to retain 7 days of flashback data, use 10080 because 7 × 24 × 60 = 10080. The valid range is 0 to 4,294,967,295 (the maximum value of a 32-bit integer).

SCOPE=BOTH This keyword applies the change in two places at once:

  • MEMORY — takes effect immediately for the running instance
  • SPFILE — persists the change so it survives a database restart

Without SCOPE=BOTH, the change is temporary and lost after a restart.

/ The forward slash at the end executes the SQL statement in SQL*Plus or SQL Developer. It is a standard SQL*Plus execution terminator.

Key Points

  • The default value is 1440 minutes (1 day).
  • This parameter is modifiable online using ALTER SYSTEM — no database restart is required.
  • The parameter is NOT modifiable inside a Pluggable Database (PDB) in a multitenant environment; it must be set at the CDB (Container Database) level.
  • Oracle treats this as a target, not a guarantee. If the Fast Recovery Area runs out of space, Oracle will delete older flashback logs even if they are still within the retention window.
  • You cannot manually delete flashback logs stored in the FRA. Oracle manages them automatically based on space and the retention target.

How It Works Inside Oracle

When Flashback Database is enabled, Oracle writes block images from every changed data file into the flashback logs at regular intervals. These block images allow Oracle to reconstruct the exact state of the database at any past moment within the retention window.

The DB_FLASHBACK_RETENTION_TARGET parameter tells Oracle how long to keep these logs. The actual amount of disk space consumed depends heavily on your redo generation rate — a write-heavy workload generates much more flashback data than a read-heavy one.

Storage Planning

Before you increase this parameter, you must plan your Fast Recovery Area size carefully. Oracle provides this formula:

Target FRA = Current FRA + (DB_FLASHBACK_RETENTION_TARGET × 60 × Peak Redo Rate in MB/sec)

For example, with a retention of 360 minutes and a peak batch redo rate of 30 MB/sec over 4 hours, you could need up to ~443 GB of additional FRA space. Always query V$FLASHBACK_DATABASE_LOG to check your oldest available flashback time after configuration.

Best Practices

  • Set DB_FLASHBACK_RETENTION_TARGET to at least 60 minutes if you use Oracle Data Guard, so you can reinstate a failed primary database after a failover.
  • For user error recovery, set the target equal to the longest time window from which you ever expect to recover.
  • In a Data Guard environment, set the same value on both the primary and standby databases.
  • Verify the current setting with:
1SHOW PARAMETER DB_FLASHBACK_RETENTION_TARGET;
  • Verify Flashback is actually enabled on the database with:
1SELECT FLASHBACK_ON FROM V$DATABASE;

Important Insights

  • A lower retention target saves FRA space but limits your recovery options. Balance your business recovery time objectives (RTO) against your storage budget.
  • Reducing the target value is safe. Oracle will simply age out older flashback logs the next time it needs space, with no data corruption risk.
  • Flashback Database is not a replacement for RMAN backups. It handles short-term, fast point-in-time rewinds; RMAN handles long-term archival recovery.
  • In Oracle 23ai, new parameters like DB_FLASHBACK_LOG_DEST allow you to store flashback logs outside the FRA for the first time, giving more storage flexibility.

References

Posts in this series