Download Brave : Secure, Fast & Private Browser with Adblocker

Sometimes the Flash Recovery Area (FRA) is full and the Oracle DBA wants to know what is it use, size and the list of occupants (archives, RMAN backups pieces or image copies, flashback logs).

To Enable /Disable the FRA, it’s here.

-- Use (MB) of FRA
set lines 100
col name format a60

select 
   name,
  floor(space_limit / 1024 / 1024) "Size MB",
  ceil(space_used / 1024 / 1024) "Used MB"
from v$recovery_file_dest;

-- FRA Occupants
SELECT * FROM V$FLASH_RECOVERY_AREA_USAGE;

-- Location and size of the FRA
show parameter db_recovery_file_dest

-- Size, used, Reclaimable 
SELECT 
  ROUND((A.SPACE_LIMIT / 1024 / 1024 / 1024), 2) AS FLASH_IN_GB, 
  ROUND((A.SPACE_USED / 1024 / 1024 / 1024), 2) AS FLASH_USED_IN_GB, 
  ROUND((A.SPACE_RECLAIMABLE / 1024 / 1024 / 1024), 2) AS FLASH_RECLAIMABLE_GB,
  SUM(B.PERCENT_SPACE_USED)  AS PERCENT_OF_SPACE_USED
FROM 
  V$RECOVERY_FILE_DEST A,
  V$FLASH_RECOVERY_AREA_USAGE B
GROUP BY
  SPACE_LIMIT, 
  SPACE_USED , 
  SPACE_RECLAIMABLE ;

-- After that you can resize the FRA with:
-- ALTER SYSTEM SET db_recovery_file_dest_size=xxG;

-- Or change the FRA to a new location (new archives will be created to this new location):
-- ALTER SYSTEM SET DB_RECOVERY_FILE_DEST='/u....';

It is important to monitor regularly space usage in the fast recovery area. The Oracle DBA must make sure that the FRA is large enough to contain backups and other recovery-related files.

If the PERCENT_FULL value (V$RECOVERY_FILE_DEST) is frequently close to 100% after several recent backups; consider allocating more space for your flash recovery area or decrease the retention time of your backups.

Oracle Database provides two views to monitor fast recovery area space usage, V$RECOVERY_FILE_DEST and V$RECOVERY_AREA_USAGE.

A best practice is to be generous on FRA size!

Compatibility: Oracle 18c, 12c, 11g, 10g
Author: Vincent FENOLL – Oracle DBA Montreal

Using a flash recovery area simplifies the ongoing administration of your database by automatically naming recovery-related files, retaining them as long as they are needed for restore and recovery activities, and deleting them when they are no longer needed to restore your database and space is needed for some other backup and recovery-related purpose.

How to enable the Flash Recovery Area?

To enable FRA for the database:

ALTER DATABASE FLASHBACK ON;

ALTER SYSTEM SET db_recovery_file_dest=’my path’ (or ‘+FRA’ if ASM is used and FRA is your diskgroup)
ALTER SYSTEM SET db_recovery_file_dest_size=500G;

You can also set db_flashback_retention_target, default is 1440min or 1 day

To verify if FlashBack is enabled:
SELECT flashback_on, log_mode FROM v$database;

 

How to disable the Flash Recovery Area?

ALTER DATABASE FLASHBACK OFF;

To verify if FlashBack is disabled:
SELECT flashback_on, log_mode FROM v$database;

 

What files can be found in the Flash (Fast) Recovery Area:

Transient Files:
RMAN backups
archived redo logs
flashback logs

Permanent Files:
online redo log copies
control file
Guaranteed Restore Points

 

Flashback must be ON to put your database in ARCHIVELOG mode.

Author: Vincent Fenoll