Manage your Database Flash Recovery Area (FRA)

 In the Oracle database, the Flash Recovery Area or FRA is a location on disk where the database can create and manage several kinds of backup and recovery-related files. 
Main file types are archivelog, flashback log, backups, as well as mirrors for your control files and redo log files.
All files in the FRA are Oracle-managed files. Using a Flash Recovery Area simplifies the administration of your database by automatically retaining them for as long as they are needed for restore and recovery activities, and deleting them when they are no longer needed, because the space is needed for another backup and recovery-related purpose.
The FRA size is set with only one parameter for all file types together, but we can do some calculations on the size needed for individual file types.
Flash Recovery Area needs
Before you start configuring your FRA sizing, you need to define your own needs for the recovery windows and retention time. 
How long do you need to keep your backups?
How do you want to use flashback database?
How much archivelog do you want to keep on disk?
Checking the current usage:
You can check the configuration by looking at two parameters.
SQL> SHOW parameter db_recovery_file_dest
 NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      +RECO
db_recovery_file_dest_size           big INTEGER 2300G
SQL>
The current FRA usage can be checked with the views v$recovery_area_usage (for each file type) and v$recovery_file_dest (for overall size and usage).
break on report
compute sum of percent_space_used on report
compute sum of percent_space_reclaimable on report
 select file_type
,      percent_space_used
,      percent_space_reclaimable
,      number_of_files
,      con_id
from   v$recovery_area_usage
order by 1
/
 
FILE_TYPE               PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES     CON_ID
----------------------- ------------------ ------------------------- --------------- ----------
ARCHIVED LOG                         61.57                     61.57             243          0
AUXILIARY DATAFILE COPY                  0                         0               0          0
BACKUP PIECE                             0                         0               0          0
CONTROL FILE                             0                         0               0          0
FLASHBACK LOG                        18.08                       .34              53          0
FOREIGN ARCHIVED LOG                   .12                         0               5          0
IMAGE COPY                               0                         0               0          0
REDO LOG                                 0                         0               0          0
                        ------------------ -------------------------
sum                                  79.77                     61.91
 
8 rows selected.
 
col name format a7
clear breaks
clear computes 
 
select name
,      round(space_limit / 1024 / 1024) size_mb
,      round(space_used  / 1024 / 1024) used_mb
,      decode(nvl(space_used,0),0,0,round((space_used/space_limit) * 100)) pct_used
from v$recovery_file_dest
order by name
/
 
NAME       SIZE_MB    USED_MB   PCT_USED
------- ---------- ---------- ----------
+RECO      1201138     958099         80

The v$recovery_area_usage view gives information about reclaimable files. Files that are reclaimable will be removed by the database when the space is needed for other purposes. This is done when the usage of the FRA is about 80% of the defined size (db_recovery_file_dest_size).
For monitoring your FRA, you need to check your unreclaimable space. Just checking the percentage of the FRA in use is not very helpful, because it will often be around 80%. 
This query combines the two views to calculate the PERCENT_SPACE_NOT_RECLAIMABLE. If it is around (or above) 80% you will need to act, because that is a situation where your actual FRA usage will also rise above 80%. It is an indication that Oracle cannot remove files, because all files need to be kept for recovery purposes. The most common problem with an undersized FRA is that the database will hang when it cannot create an archivelog file at time of a logswitch.
col name format a7
clear breaks
clear computes
 
select name
,      round(space_limit / 1024 / 1024) space_limit_mb
,      round(space_used  / 1024 / 1024) space_used_mb
,      percent_space_used
,      percent_space_reclaimable
,      percent_space_not_reclaimable
from v$recovery_file_dest
,    ( select sum(percent_space_reclaimable)                      percent_space_reclaimable
       ,      sum(percent_space_used)                             percent_space_used
       ,      sum(percent_space_used - percent_space_reclaimable) percent_space_not_reclaimable
       from  v$recovery_area_usage)
order by name
/
 
 
NAME    SPACE_LIMIT_MB SPACE_USED_MB PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE PERCENT_SPACE_NOT_RECLAIMABLE
------- -------------- ------------- ------------------ ------------------------- -----------------------------
+RECO          1201138        958099              79.77                     61.91                         17.86
Configuration FRA size
Configuration of the FRA size is done with the parameter db_recovery_file_dest_size.
SQL> SHOW parameter db_recovery_file_dest
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      +RECO
db_recovery_file_dest_size           big INTEGER 2300G
SQL>
SQL> ALTER system  SET db_recovery_file_dest_size=3500G  scope=BOTH ;
 
System altered.
SQL> 

Configuration in a RAC cluster:

For a RAC cluster, you should configure a shared recovery area for all instances, with the same location and size for all instances.
It is good to know that the contents of the v$recovery_area_usage are also about the files from all instances. Queries on this view will give the same results on all instances. There is no gv$recovery_area_usage view.

SQL> SHOW parameter db_recovery_file_dest
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      +RECO
db_recovery_file_dest_size           big INTEGER 2300G
 
SQL> 
SQL> SELECT inst_id , VALUE / (1024 * 1024 * 1024) GB  
     FROM gv$parameter 
     WHERE name = 'db_recovery_file_dest_size'
 /
 
   INST_ID         GB
---------- ----------
         1       2300
         2       2300
 
SQL> 
SQL> ALTER system  SET db_recovery_file_dest_size=3500G  sid='*' scope=BOTH ;
 
System altered.
 
SQL> SELECT inst_id , VALUE / (1024 * 1024 * 1024) GB  
     FROM gv$parameter 
     WHERE name = 'db_recovery_file_dest_size'
 /
 
   INST_ID         GB
---------- ----------
         1       3500
         2       3500
 
SQL>

So, in this case, you would use a maximum of 3500 GB on the +RECO disk group for the database. You do not need 3500 GB for each instance.  

Configuring archivelog deletion:

You can define your archivelog deletion policy in RMAN. If there is no archived redo log deletion policy in RMAN, the files can be deleted when backed up at least once to disk or SBT. Or the logs are obsolete according to the backup retention policy.
If you do create an archivelog deletion policy, they can be deleted after you meet the requirements in the policy. 

Examples are:
CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 2 TIMES TO SBT;
CONFIGURE ARCHIVELOG DELETION POLICY TO SHIPPED TO ALL STANDBY;
CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;

The deletion policy itself does not delete archivelog files. It is a protection; files are not deleted if it conflicts with the policy.  

Two common ways to delete archivelogs are:

• Delete as part of your backup script 
• Do not create any cleanup script and let the database handle it

A common RMAN script for archivelog files is:

backup device type sbt archivelog all not backed up  1 times ;
delete noprompt archivelog until time = 'sysdate-1' backed up 1 times to sbt ;

The delete statement will check the delete policy, so files won't be deleted if that conflicts with the policy. However, you will receive an error during the backup:
archived log file

 

name=+RECO/MYDB/ARCHIVELOG/2020_06_26/thread_1_seq_2.4585.1044140407 thread=1 sequence=2

RMAN-08120: WARNING: archived log not deleted, not yet applied by standby

If you don't run a delete in backup script, the files can just be deleted by the database, based on your configuration. Remember, we are using OMF for the FRA, so these are Oracle managed files.
For example, if you have a data guard standby database and no backup running on the standby, you will find that the database will delete archivelogs that are applied. This is about the files that are "reclaimable"in the v$recovery_area_usage view. So, when the database runs out of space, it will remove some of the reclaimable files, as you can see in the alert log:

Mon May 09 04:55:55 2022
Deleted Oracle managed file +RECO/MYDB/ARCHIVELOG/2020_05_02/thread_1_seq_55913.1742.1044422411
Deleted Oracle managed file

 
Do not create a shell script that will just delete archivelog files. If your database doesn't know you removed them, you can still get an error if you run out of space in your FRA. Always use RMAN to delete archivelog files.

Calculating archivelog size:

How much archivelog is created? You can check the size of your redo log files in v$log and multiply it with the average number of log switches from v$loghist. But that’s not perfect, as you can do a log switch before the redo log file is 100% full.   
It’s better to look at the v$archived_log view. This will have more information about all the created archivelogs:

If you use the gv$archived_log version, you will see all instances of a RAC database.
If you check with gv$archive_dest.destination = 'USE_DB_RECOVERY_FILE_DEST' you will only get files in the FRA (so we will skip remote destinations used by Data Guard) 
If you check the status, you can see if the file is still available in the FRA

select trunc(l.first_time) arch_date
,      l.inst_id
,      l.status
,      n.destination
,      round(sum( blocks * block_size )  / ( 1024 * 1024 * 1024 ) ) total_file_size_gb
,      count(*) file_count
from   gv$archived_log l
,      gv$archive_dest n
where  l.inst_id = n.inst_id
and    l.dest_id = n.dest_id
and    n.destination = 'USE_DB_RECOVERY_FILE_DEST'
group by trunc(l.first_time)
,        l.inst_id
,        l.status
,        n.destination
order by trunc(l.first_time)
,        l.inst_id
,        l.status
,        n.destination
/
 
ARCH_DATE    INST_ID S DESTINATION               TOTAL_FILE_SIZE_GB FILE_COUNT
--------- ---------- - ------------------------- ------------------ ----------
08-MAY-22          1 D USE_DB_RECOVERY_FILE_DEST                108         46
08-MAY-22          2 D USE_DB_RECOVERY_FILE_DEST                108         46
09-MAY-22          1 D USE_DB_RECOVERY_FILE_DEST                299        116
09-MAY-22          2 D USE_DB_RECOVERY_FILE_DEST                299        116
10-MAY-22          1 D USE_DB_RECOVERY_FILE_DEST                441        167
10-MAY-22          2 D USE_DB_RECOVERY_FILE_DEST                441        167
11-MAY-22          1 D USE_DB_RECOVERY_FILE_DEST                406        146
11-MAY-22          2 D USE_DB_RECOVERY_FILE_DEST                406        146
[....]
64 rows selected.

The status can have several values: 
A - Available
D - Deleted
U - Unavailable
X - Expired

So, based on this column we can calculate how much archivelog is created and how much is still available in the FRA.

select trunc(l.first_time) arch_date
,      round(sum(decode(l.status,'D',blocks * block_size,0)/(1024 * 1024 * 1024))) deleted_gb
,      round(sum(decode(l.status,'A',blocks * block_size,0)/(1024 * 1024 * 1024))) available_gb
,      round(sum(decode(l.status,'U',blocks * block_size,0)/(1024 * 1024 * 1024))) unavailable_gb
,      round(sum(decode(l.status,'X',blocks * block_size,0)/(1024 * 1024 * 1024))) expired_gb
,      round(sum(blocks * block_size) /(1024 * 1024 * 1024))                       total_size_gb
from   gv$archived_log l
,      gv$archive_dest n
where  l.inst_id = n.inst_id
and    l.dest_id = n.dest_id
and    n.destination = 'USE_DB_RECOVERY_FILE_DEST'
group by trunc(l.first_time)
order by trunc(l.first_time)
/
 
 
ARCH_DATE DELETED_GB AVAILABLE_GB UNAVAILABLE_GB EXPIRED_GB TOTAL_SIZE_GB
--------- ---------- ------------ -------------- ---------- -------------
....
08-MAY-22        217            0              0          0           217
09-MAY-22        599            0              0          0           599
10-MAY-22        883            0              0          0           883
11-MAY-22        812            0              0          0           812
31 rows selected.

So, now we know how much archivelog is created daily in the FRA.
We only need to decide how long we want to keep the archivelogs. This depends on several factors:
How often do you make a backup? Making a backup can make the space used by the archivelog reclaimable

Even after you made a backup, you might want to keep the archivelog on disk for some time, because recovering your database is faster when you don't need to restore all the files first
In a data guard environment, you might want to keep files longer, because you can't remove them before they are shipped to the standby databases

Now we can calculate how much FRA space we need for archivelog.
Configuring flashback log
Before you can use flashback, you should turn the option on and configure the retention target.

SQL> SELECT flashback_on FROM v$database ;
 
FLASHBACK_ON
------------------
NO
 
SQL> ALTER DATABASE flashback ON ;
 
DATABASE altered.
 
SQL> SELECT flashback_on FROM v$database ;
 
FLASHBACK_ON
------------------
YES
 
SQL>
SQL> ALTER system SET db_flashback_retention_target=1440 scope=BOTH ;
 
System altered.
 
SQL>
SQL> SHOW parameter db_flashback_retention_target
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_flashback_retention_target        INTEGER     1440
 
SQL>
The flashback sizing can be viewed in v$flashback_database_log (or gv$flashback_database_log for a RAC database).
SQL>
SELECT inst_id
,      to_char(oldest_flashback_time,'dd-mm-yyyy-hh24:mi')    oldest_flashback_time
,      retention_target
,      round((sysdate - oldest_flashback_time ) * (60 * 24))  actual_retention_possible
,      round(flashback_size / (1024 * 1024 * 1024))           flashback_size_gb
,      round(estimated_flashback_size / (1024 * 1024 * 1024)) estimated_flashback_size_gb
FROM   gv$flashback_database_log
/
 
   INST_ID OLDEST_FLASHBACK RETENTION_TARGET ACTUAL_RETENTION_POSSIBLE FLASHBACK_SIZE_GB ESTIMATED_FLASHBACK_SIZE_GB
---------- ---------------- ----------------  ------------------------- ----------------- ---------------------------
         2 07-07-2021-12:44             1440                       1512               301                         208
         1 07-07-2021-12:44             1440                       1512               301                         208

The db_flashback_retention_target is exactly what the name implies: a retention target. If the FRA is large, you can have more; because these are OMF files, flashback files are only removed if the database needs the storage for other things. If the FRA is too small, you may not have enough to reach the target.
In this case, the target is 1440 minutes (1 day), but we have storage in use for a possible flashback of 1512 minutes. We have 301 GB in use, but only need about 208 to reach the target. So, some of the space is reclaimable: 

SQL>  SELECT * FROM v$recovery_area_usage WHERE FILE_TYPE = 'FLASHBACK LOG' ;
 
FILE_TYPE               PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES     CON_ID
----------------------- ------------------ ------------------------- --------------- ----------
FLASHBACK LOG                         6.85                      1.55              76          0
 
SQL>

If you want to be sure you can flashback your database, you need to make a guaranteed restore point (aka a snapshot). The database will then keep the needed flashback files until you drop the restore point, even if you exceed the db_flashback_retention_target. 

SQL>  CREATE restore point my_rp guarantee flashback DATABASE;
 
Restore point created.
 
SQL>  SELECT name , storage_size FROM v$restore_point
 
NAME       STORAGE_SIZE
---------- ------------
MY_RP        4294967296
 
SQL> DROP restore point my_rp ;
 
Restore point dropped.
 
SQL>

Configuring backups

Definition of your retention policy should be done in RMAN. Your policy can be a recovery window or the number of backups you want to save.
The actual usage of storage in the FRA can of course be checked again in the v$recovery_area_usage view, looking at the file types IMAGE COPY, AUXILIARY DATAFILE COPY and BACKUP PIECE.
In RMAN, you can define:

CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 14 DAYS;
CONFIGURE DEFAULT DEVICE TYPE TO DISK;
CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 1 TIMES TO DISK;

If you keep the defaults, the backup files will be stored in the FRA with retention policy redundancy 1. Meaning that if I make two backups, 1 will be reclaimable.

RMAN> show DEFAULT DEVICE TYPE;
 
RMAN configuration parameters for database with db_unique_name RCAT are:
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
 
 
RMAN> show RETENTION POLICY;
 
RMAN configuration parameters for database with db_unique_name RCAT are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
 
RMAN> backup tablespace cattbs;
RMAN> backup tablespace cattbs;
And then check the FRA again:
SQL> SELECT * FROM v$recovery_area_usage WHERE file_type = ‘BACKUP PIECE’;
 
FILE_TYPE               PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES     CON_ID
----------------------- ------------------ ------------------------- --------------- ----------
BACKUP PIECE                            .8                       .06               9          0

Alter a "Delete obsolete" in RMAN. The first backup is removed, and the reclaimable space is back to 0.
RMAN's crosscheck command will resolve the inconsistency between the FRA usage in the database and on disk.

Be aware that the most common problem with an undersized FRA is a database hang, because the database cannot create an archivelog file at time of a logswitch. The most common solution is to create a backup of the archivelogs, which will also delete these archivelogs.
This doesn't make much sense if both the archivelogs and the backups are stored in the FRA. You can't create a backup in the FRA, when the FRA runs out of space.

So, always investigate if you can locate backups outside the FRA. See also this Oracle support note: How to disable use of Flash Recovery Area for Archivelogs and Backups (Doc ID 297397.1).
Compression

If you are licensed to use the advanced compression option, you can compress RMAN backups while they are created.
Automatic compression of archivelog files is still not implemented by Oracle; see Oracle support Doc ID 2449903.1.

You should not write your own scripts to compress archivelogs in the FRA. If you want to save space using your own scripts, you should create an archive location outside the FRA.

Conclusion
A full FRA can cause a hanging database, so a system down. When monitoring the FRA, focus on the space that is unreclaimable and not just on the actual usage. Make sure you know your needs for your recovery window and flashback target.
Store the RMAN backups outside the FRA, if possible. If you want to make sure you can use a flashback database, you should create a guaranteed restore point. And don't forget to drop it, when it’s no longer needed.
Always let the database or your RMAN backup script handle files in the FRA, because they are all Oracle Managed Files.