Managing the Oracle Database Flash Recovery Area

Managing the Oracle Database Flash Recovery Area

Published on: Category: Oracle

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.

  1. SQL> SHOW parameter db_recovery_file_dest
  2.  
  3. NAME TYPE VALUE
  4. ------------------------------------ ----------- ------------------------------
  5. db_recovery_file_dest string +RECO
  6. db_recovery_file_dest_size big INTEGER 2300G
  7. 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).

  1. set lines 120
  2. break on report
  3.  
  4. compute sum of percent_space_used on report
  5. compute sum of percent_space_reclaimable on report
  6.  
  7.  
  8. select file_type
  9. , percent_space_used
  10. , percent_space_reclaimable
  11. , number_of_files
  12. , con_id
  13. from v$recovery_area_usage
  14. order by 1
  15. /
  16.  
  17.  
  18.  
  19. FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES CON_ID
  20. ----------------------- ------------------ ------------------------- --------------- ----------
  21. ARCHIVED LOG 61.57 61.57 243 0
  22. AUXILIARY DATAFILE COPY 0 0 0 0
  23. BACKUP PIECE 0 0 0 0
  24. CONTROL FILE 0 0 0 0
  25. FLASHBACK LOG 18.08 .34 53 0
  26. FOREIGN ARCHIVED LOG .12 0 5 0
  27. IMAGE COPY 0 0 0 0
  28. REDO LOG 0 0 0 0
  29. ------------------ -------------------------
  30. sum 79.77 61.91
  31.  
  32. 8 rows selected.
  33.  
  34. col name format a7
  35. clear breaks
  36. clear computes
  37.  
  38. select name
  39. , round(space_limit / 1024 / 1024) size_mb
  40. , round(space_used / 1024 / 1024) used_mb
  41. , decode(nvl(space_used,0),0,0,round((space_used/space_limit) * 100)) pct_used
  42. from v$recovery_file_dest
  43. order by name
  44. /
  45.  
  46. NAME SIZE_MB USED_MB PCT_USED
  47. ------- ---------- ---------- ----------
  48. +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.

  1. col name format a7
  2. clear breaks
  3. clear computes
  4.  
  5. select name
  6. , round(space_limit / 1024 / 1024) space_limit_mb
  7. , round(space_used / 1024 / 1024) space_used_mb
  8. , percent_space_used
  9. , percent_space_reclaimable
  10. , percent_space_not_reclaimable
  11. from v$recovery_file_dest
  12. , ( select sum(percent_space_reclaimable) percent_space_reclaimable
  13. , sum(percent_space_used) percent_space_used
  14. , sum(percent_space_used - percent_space_reclaimable) percent_space_not_reclaimable
  15. from v$recovery_area_usage)
  16. order by name
  17. /
  18.  
  19.  
  20. NAME SPACE_LIMIT_MB SPACE_USED_MB PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE PERCENT_SPACE_NOT_RECLAIMABLE
  21. ------- -------------- ------------- ------------------ ------------------------- -----------------------------
  22. +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.

  1. SQL> SHOW parameter db_recovery_file_dest
  2.  
  3. NAME TYPE VALUE
  4. ------------------------------------ ----------- ------------------------------
  5. db_recovery_file_dest string +RECO
  6. db_recovery_file_dest_size big INTEGER 2300G
  7. SQL>
  8. SQL> ALTER system SET db_recovery_file_dest_size=3500G scope=BOTH ;
  9.  
  10. System altered.
  11. 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.

  1. SQL> SHOW parameter db_recovery_file_dest
  2.  
  3. NAME TYPE VALUE
  4. ------------------------------------ ----------- ------------------------------
  5. db_recovery_file_dest string +RECO
  6. db_recovery_file_dest_size big INTEGER 2300G
  7.  
  8. SQL>
  9. SQL> SELECT inst_id , VALUE / (1024 * 1024 * 1024) GB
  10. FROM gv$parameter
  11. WHERE name = 'db_recovery_file_dest_size'
  12. /
  13.  
  14. INST_ID GB
  15. ---------- ----------
  16. 1 2300
  17. 2 2300
  18.  
  19. SQL>
  20. SQL> ALTER system SET db_recovery_file_dest_size=3500G sid='*' scope=BOTH ;
  21.  
  22. System altered.
  23.  
  24. SQL> SELECT inst_id , VALUE / (1024 * 1024 * 1024) GB
  25. FROM gv$parameter
  26. WHERE name = 'db_recovery_file_dest_size'
  27. /
  28.  
  29. INST_ID GB
  30. ---------- ----------
  31. 1 3500
  32. 2 3500
  33.  
  34. 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:

  1. backup device type sbt archivelog all not backed up 1 times ;
  2. 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:

  1. archived log file name=+RECO/MYDB/ARCHIVELOG/2020_06_26/thread_1_seq_2.4585.1044140407 thread=1 sequence=2
  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:

  1. Mon Jul 06 04:55:55 2020
  2. Deleted Oracle managed file +RECO/MYDB/ARCHIVELOG/2020_06_30/thread_1_seq_55913.1742.1044422411
  3. Deleted Oracle managed file +RECO/MYDB/ARCHIVELOG/2020_06_30/thread_2_seq_63726.1586.1044422409
  4. Mon Jul 06 04:58:12 2020

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
  1. select trunc(l.first_time) arch_date
  2. , l.inst_id
  3. , l.status
  4. , n.destination
  5. , round(sum( blocks * block_size ) / ( 1024 * 1024 * 1024 ) ) total_file_size_gb
  6. , count(*) file_count
  7. from gv$archived_log l
  8. , gv$archive_dest n
  9. where l.inst_id = n.inst_id
  10. and l.dest_id = n.dest_id
  11. and n.destination = 'USE_DB_RECOVERY_FILE_DEST'
  12. group by trunc(l.first_time)
  13. , l.inst_id
  14. , l.status
  15. , n.destination
  16. order by trunc(l.first_time)
  17. , l.inst_id
  18. , l.status
  19. , n.destination
  20. /
  21.  
  22. ARCH_DATE INST_ID S DESTINATION TOTAL_FILE_SIZE_GB FILE_COUNT
  23. --------- ---------- - ------------------------- ------------------ ----------
  24. 08-JUN-20 1 D USE_DB_RECOVERY_FILE_DEST 108 46
  25. 08-JUN-20 2 D USE_DB_RECOVERY_FILE_DEST 108 46
  26. 09-JUN-20 1 D USE_DB_RECOVERY_FILE_DEST 299 116
  27. 09-JUN-20 2 D USE_DB_RECOVERY_FILE_DEST 299 116
  28. 10-JUN-20 1 D USE_DB_RECOVERY_FILE_DEST 441 167
  29. 10-JUN-20 2 D USE_DB_RECOVERY_FILE_DEST 441 167
  30. 11-JUN-20 1 D USE_DB_RECOVERY_FILE_DEST 406 146
  31. 11-JUN-20 2 D USE_DB_RECOVERY_FILE_DEST 406 146
  32. [....]
  33. 08-JUL-20 1 A USE_DB_RECOVERY_FILE_DEST 225 76
  34. 08-JUL-20 2 A USE_DB_RECOVERY_FILE_DEST 225 76
  35.  
  36. 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.

  1. select trunc(l.first_time) arch_date
  2. , round(sum(decode(l.status,'D',blocks * block_size,0)/(1024 * 1024 * 1024))) deleted_gb
  3. , round(sum(decode(l.status,'A',blocks * block_size,0)/(1024 * 1024 * 1024))) available_gb
  4. , round(sum(decode(l.status,'U',blocks * block_size,0)/(1024 * 1024 * 1024))) unavailable_gb
  5. , round(sum(decode(l.status,'X',blocks * block_size,0)/(1024 * 1024 * 1024))) expired_gb
  6. , round(sum(blocks * block_size) /(1024 * 1024 * 1024)) total_size_gb
  7. from gv$archived_log l
  8. , gv$archive_dest n
  9. where l.inst_id = n.inst_id
  10. and l.dest_id = n.dest_id
  11. and n.destination = 'USE_DB_RECOVERY_FILE_DEST'
  12. group by trunc(l.first_time)
  13. order by trunc(l.first_time)
  14. /
  15.  
  16.  
  17. ARCH_DATE DELETED_GB AVAILABLE_GB UNAVAILABLE_GB EXPIRED_GB TOTAL_SIZE_GB
  18. --------- ---------- ------------ -------------- ---------- -------------
  19. 08-JUN-20 217 0 0 0 217
  20. 09-JUN-20 599 0 0 0 599
  21. 10-JUN-20 883 0 0 0 883
  22. 11-JUN-20 812 0 0 0 812
  23. 12-JUN-20 625 0 0 0 625
  24. 13-JUN-20 739 0 0 0 739
  25. 14-JUN-20 618 0 0 0 618
  26. 15-JUN-20 761 0 0 0 761
  27. 16-JUN-20 789 0 0 0 789
  28. 17-JUN-20 852 0 0 0 852
  29. 18-JUN-20 867 0 0 0 867
  30. 19-JUN-20 811 0 0 0 811
  31. 20-JUN-20 697 0 0 0 697
  32. 21-JUN-20 686 0 0 0 686
  33. 22-JUN-20 740 0 0 0 740
  34. 23-JUN-20 781 0 0 0 781
  35. 24-JUN-20 891 0 0 0 891
  36. 25-JUN-20 898 0 0 0 898
  37. 26-JUN-20 806 0 0 0 806
  38. 27-JUN-20 752 0 0 0 752
  39. 28-JUN-20 750 0 0 0 750
  40. 29-JUN-20 776 0 0 0 776
  41. 30-JUN-20 910 0 0 0 910
  42. 01-JUL-20 1048 0 0 0 1048
  43. 02-JUL-20 1021 0 0 0 1021
  44. 03-JUL-20 865 0 0 0 865
  45. 04-JUL-20 333 0 0 0 333
  46. 05-JUL-20 317 37 0 0 355
  47. 06-JUL-20 0 437 0 0 437
  48. 07-JUL-20 0 521 0 0 521
  49. 08-JUL-20 0 450 0 0 450
  50.  
  51. 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.

  1. SQL> SELECT flashback_on FROM v$database ;
  2.  
  3. FLASHBACK_ON
  4. ------------------
  5. NO
  6.  
  7. SQL> ALTER DATABASE flashback ON ;
  8.  
  9. DATABASE altered.
  10.  
  11. SQL> SELECT flashback_on FROM v$database ;
  12.  
  13. FLASHBACK_ON
  14. ------------------
  15. YES
  16.  
  17. SQL>
  18. SQL> ALTER system SET db_flashback_retention_target=1440 scope=BOTH ;
  19.  
  20. System altered.
  21.  
  22. SQL>
  23. SQL> SHOW parameter db_flashback_retention_target
  24.  
  25. NAME TYPE VALUE
  26. ------------------------------------ ----------- ------------------------------
  27. db_flashback_retention_target INTEGER 1440
  28.  
  29. SQL>

The flashback sizing can be viewed in v$flashback_database_log (or gv$flashback_database_log for a RAC database).

  1. SQL>
  2. SELECT inst_id
  3. , to_char(oldest_flashback_time,'dd-mm-yyyy-hh24:mi') oldest_flashback_time
  4. , retention_target
  5. , round((sysdate - oldest_flashback_time ) * (60 * 24)) actual_retention_possible
  6. , round(flashback_size / (1024 * 1024 * 1024)) flashback_size_gb
  7. , round(estimated_flashback_size / (1024 * 1024 * 1024)) estimated_flashback_size_gb
  8. FROM gv$flashback_database_log
  9. /
  10.  
  11. INST_ID OLDEST_FLASHBACK RETENTION_TARGET ACTUAL_RETENTION_POSSIBLE FLASHBACK_SIZE_GB ESTIMATED_FLASHBACK_SIZE_GB
  12. ---------- ---------------- ---------------- ------------------------- ----------------- ---------------------------
  13. 2 07-07-2020-12:44 1440 1512 301 208
  14. 1 07-07-2020-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: 

  1. SQL> SELECT * FROM v$recovery_area_usage WHERE FILE_TYPE = 'FLASHBACK LOG' ;
  2.  
  3. FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES CON_ID
  4. ----------------------- ------------------ ------------------------- --------------- ----------
  5. FLASHBACK LOG 6.85 1.55 76 0
  6.  
  7. 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. 

  1. SQL> CREATE restore point my_rp guarantee flashback DATABASE;
  2.  
  3. Restore point created.
  4.  
  5. SQL> SELECT name , storage_size FROM v$restore_point
  6.  
  7. NAME STORAGE_SIZE
  8. ---------- ------------
  9. MY_RP 4294967296
  10.  
  11. SQL> DROP restore point my_rp ;
  12.  
  13. Restore point dropped.
  14.  
  15. 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:

  1. CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 14 DAYS;
  2. CONFIGURE DEFAULT DEVICE TYPE TO DISK;
  3. 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.

  1. RMAN> show DEFAULT DEVICE TYPE;
  2.  
  3. RMAN configuration parameters for database with db_unique_name RCAT are:
  4. CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
  5.  
  6.  
  7. RMAN> show RETENTION POLICY;
  8.  
  9. RMAN configuration parameters for database with db_unique_name RCAT are:
  10. CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
  11.  
  12. RMAN> backup tablespace cattbs;
  13. RMAN> backup tablespace cattbs;

And then check the FRA again:

  1. SQL> SELECT * FROM v$recovery_area_usage WHERE file_type = ‘BACKUP PIECE’;
  2.  
  3. FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES CON_ID
  4. ----------------------- ------------------ ------------------------- --------------- ----------
  5. 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.

Bastiaan Bak
About the author Bastiaan Bak

DBA with over 15 years of experience. Experience in various branches, with several modules. Including: Oracle database, Oracle RAC, Oracle EBS and PL/SQL.

More posts by Bastiaan Bak
Comments (2)
  1. om 14:02

    Thanks for this blog Bastiaan!

    A small tip: you can copy the FRA to another share: backup recovery area to destination '/oracle_backups; (but first investigate what this copies exactly ;-))

    Thanks again, and keep up the good work!

    1. om 14:02

      Thank you Pierre !

Reply