As part of an upgrade project to Oracle 19c, I was analyzing the daily generation of archived redo logs in order to correctly size the Fast Recovery Area, and for one of the databases I came to the conclusion that it was enough to allocate 500 GB instead of its current 4 TB.
It was a big surprise when OEM showed me that almost 3 TB were occupied, and with no possibility of reusing them, while at Disk Group level it showed me that +FRA01 was empty.
I was witnessing a scenario in which the Fast Recovery Area is full and empty at the same time!
The problem
To identify the allocated and used space of the Fast Recovery Area we have the v$recovery_file_dest view, while the v$recovery_area_usage view shows us the detail of each type of file it contains, so I proceed to check them directly in the hope of finding the cause of the unexpected consumption.
SELECT name,
ROUND(space_limit/1024/1024/1024,2) space_limit_gib,
ROUND(space_used/1024/1024/1024,2) space_used_gib,
ROUND(space_reclaimable/1024/1024/1024,2) space_reclaimable_gib,
number_of_files
FROM v$recovery_file_dest;
SELECT file_type, percent_space_used as used,
percent_space_reclaimable as reclaimable,
number_of_files
FROM v$recovery_area_usage;
NAME SPACE_LIMIT_GIB SPACE_USED_GIB SPACE_RECLAIMABLE_GIB NUMBER_OF_FILES
------- --------------- -------------- --------------------- ---------------
+FRA01 4,096.00 2,711.05 1.32 2,143
FILE_TYPE USED RECLAIMABLE NUMBER_OF_FILES
------------------------- ---------- ----------- ---------------
CONTROL FILE 0 0 0
REDO LOG 0 0 0
ARCHIVED LOG 2.29 2.27 18
BACKUP PIECE .02 0 1
IMAGE COPY 0 0 0
FLASHBACK LOG 0 0 0
FOREIGN ARCHIVED LOG 0 0 0
Immediately we notice the discrepancy: the number_of_files column in the v$recovery_file_dest view records more than 2 thousand files, while in v$recovery_area_usage it does not reach 20.
The Solution
After researching in My Oracle Support, a handful of notes are found pointing the blame for this discrepancy to bugs, present in different versions of Oracle.
The causes are many, and the alternative solutions are just as varied, but one of them is always mentioned and seems to be the simplest to implement: resetting the Fast Recovery Area.
This is accomplished by executing the following commands:
SQL> alter session set events 'immediate trace name kra_options level 1';
Session altered.
SQL> execute dbms_backup_restore.refreshagedfiles;
PL/SQL procedure successfully completed.We found that there is now consistency between the data recorded by both views: the number of files is now 19, and the space used went from 2,700 GB to only 46.
NAME SPACE_LIMIT_GIB SPACE_USED_GIB SPACE_RECLAIMABLE_GIB NUMBER_OF_FILES
--------------- --------------- -------------- --------------------- ---------------
+FRA01 4,096.00 46.10 45.32 19
FILE_TYPE USED RECLAIMABLE NUMBER_OF_FILES
------------------------- ---------- ----------- ---------------
CONTROL FILE 0 0 0
REDO LOG 0 0 0
ARCHIVED LOG 4.58 4.53 18
BACKUP PIECE .03 0 1
IMAGE COPY 0 0 0
FLASHBACK LOG 0 0 0
FOREIGN ARCHIVED LOG 0 0 0As everything is now in order, we can reconfigure the allocated space to the 500 GB originally calculated, and with that we close the incident.
To complement the above, I recommend reading the notes:
| 1471471.1 | v$Flash_recovery_area_usage is not Being Updated |
| 2780810.1 | SPACE_USED column in v$recovery_file_dest not Updated After a Change in the FRA Location |
| 2564464.1 | v$recovery_file_dest.number_of_files Shows Incorrect Value after Restoring Controlfile At Standby |