How do I check flash recovery area?

The flash recovery area or FRA in the oracle database is a location on the disk where the oracle database stores the backup files and archive files.

Read: How to Add New Disk in ASM instance?

How to configure FRA size

Before starting configuration you must decide on the below points.

• Backup keep time?
• How much archive log do you want to keep on disk?
• How do you want to use the flashback database?

1. Check current FRA size

SQL> show parameter db_recovery

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      /u01/app/oracle/fast_recovery_
area
db_recovery_file_dest_size           big integer 4122M

2. The Current FRA size we can check with the help of view v$recovery_area_usage for each file usage.

SQL> break on report
SQL> compute sum of percent_space_used on report
SQL> compute sum of percent_space_reclaimable on report

SQL>  select * from v$recovery_area_usage;

FILE_TYPE            PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
-------------------- ------------------ ------------------------- ---------------
CONTROL FILE                          0                         0               0
REDO LOG                              0                         0               0
ARCHIVED LOG                        .11                       .11               5
BACKUP PIECE                      42.26                      1.44              26
IMAGE COPY                         8.61                      8.61               4
FLASHBACK LOG                         0                         0               0
FOREIGN ARCHIVED LOG                  0                         0               0
                     ------------------ -------------------------
sum                               50.98                     10.16

3. Check overall size and usages of FRA with the help of v$recovery_file_dest view.

SQL> 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
---------------------------------------- ---------- ---------- ----------
/u01/app/oracle/fast_recovery_area             4122       2102         51

The below query combines the two views to calculate the percentage of not reclaimable space and reclaimable space. If not reclaimable space percentage around (or above) 80% you will need to take action because that is a situation where your actual FRA usage will also rise above 80%. That is an indication, 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 archive log file at the time of a log switch.

SQL> select name
  2  ,      round(space_limit / 1024 / 1024) space_limit_mb
  3  ,      round(space_used  / 1024 / 1024) space_used_mb
  4  ,      percent_space_used
  5  ,      percent_space_reclaimable
  6  ,      percent_space_not_reclaimable
  7  from v$recovery_file_dest
  8  ,    ( select sum(percent_space_reclaimable)                      percent_space_reclaimable
  9         ,      sum(percent_space_used)                             percent_space_used
 10         ,      sum(percent_space_used - percent_space_reclaimable) percent_space_not_reclaimable
 11         from  v$recovery_area_usage)
 12  order by name;

OutPut from the above query:

The FRA size is configured with help of the db_recovery_file_dest_size parameter.

SQL> show parameter db_recovery

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      /u01/app/oracle/fast_recovery_area
db_recovery_file_dest_size           big integer 4122M



SQL> ALTER system  SET db_recovery_file_dest_size=4200M  scope=BOTH ;

System altered.

I hope now you understand how to manage Flash Recovery Area or FRA, if yes please write in the comment box.

Subscribe to our YouTube channel.