Oracle Flashback and Archivelog Management

Flashback log retention and archivelog management

• New flashback logs are written until the oldest log is no longer required to meet the flashback retention target.  At this point oracle will start to recycle the oldest flashback log files instead of writing a brand new file.
• Flashback log files are only deleted by oracle if under space pressure in the FRA (80% of FRA is reached) at which point obsolete archive logs are deleted and their respective flashback logs.
• Flashback logs can be deleted by turning off and on flashback logging. This can be done dynamically with the database online in oracle 11 and 12 but will lose all written flashback logs and start again.
• Periods of large database activity could generate more flashback logs than usual and this determines the overall number of flash log files (see bullet points above)
• Archivelogs on primary are kept active for as long as the greater of the flashback retention target or rman recovery window.
• On a dataguarded setup the standby archive logs will not be deleted by the 'archivelog deletion policy applied on standby' rman policy. This will only mark archivelogs as being reclaimable by the FRA once they are applied as opposed to 'none' where it will mark archivelogs as reclaimable if they have been backed up or have exceeded the rman recovery window.
• Archivelogs can be deleted automatically by oracle if their respective ARCHIVE LOG section of the control file gets full up.  For example this is occurring on storm 1 and 3 because there is no manual archive log delete task.
• Archivelogs and Flashback logs can be deleted automatically by oracle if the FRA becomes 80% full at which point oracle will start removing files on the obsolete file list. (80% limit now tested by AL and RM).  This will not delete archivelogs within the rman recovery window.
• Flashback retention target is not guaranteed to retain the files (can be deleted if FRA space pressure), whereas RMAN recovery window is.

Policy Recommendations based on above findings

• set "configure archivelog deletion policy to applied on standby" on the standbys.  This will allow automatic deletion of the applied logs if the FRA becomes under space pressure.
• Add jobs into cloud control for standby servers to delete archivelogs < sysdate - x days
• Allow enough FRA for flashback logs as to not hit space pressure.
• Flashback retention target parameter should be set less than or equal to rman recovery window.
• Never delete flashback logs manually.  To do so will result in the database not starting if it is shut down.(switch off flashback dynamically to achieve this)
• Never change flashback retention target unless authorised . These will have been set as part of the build.
• Use flashback only as a complimentary backup method.  Never rely on it as you cannot flashback non logged transactions. Should have supplier confirmation before using after upgrades etc
• Set control_file_record_keep_time parameter to be the same or greater than the rman recovery window.
• Flashback status on monitoring tool ??
• Use flashback where appropriate before application upgrades and bulk changes to large database database
• Can be switched on or off as required
• This should never be a substitute for backup and point in time recovery in any situation. It is a complimentary strategy only
• Need a policy for new and old databases

Useful flashback and FRA views

to obtain oracle FRA space used broken down into file types:
set lines 1000
select * from v$flash_recovery_area_usage;
to return percentage used in FRA that is not reclaimable:
SELECT
ROUND((SPACE_USED - SPACE_RECLAIMABLE)/SPACE_LIMIT * 100, 1)
AS PERCENT_FULL
FROM V$RECOVERY_FILE_DEST;