Oracle 26ai: Why nothing under v$flash_recovery_area_usage?

We had someone complaining saying why v$flash_recovery_area_usage is empty when DB is in archive log mode? Should not list everything there?

This has been replicated on 26ai, but it is valid on previous releases.

Answer is simple, log_archive_dest_1 is not set properly, lets replicate on a test db….

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            +FRA
Oldest online log sequence     28
Next log sequence to archive   28
Current log sequence           27

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      +FRA
db_recovery_file_dest_size           big integer 20G

Initially ok, right? well…..

SQL> select FILE_TYPE, PERCENT_SPACE_USED as "% used",PERCENT_SPACE_RECLAIMABLE as "% reclaimable",  NUMBER_OF_FILES from v$flash_recovery_area_usage

FILE_TYPE                   % used % reclaimable NUMBER_OF_FILES
----------------------- ---------- ------------- ---------------
CONTROL FILE                     0             0               0
REDO LOG                         0             0               0
ARCHIVED LOG                     0             0               0
BACKUP PIECE                     0             0               0
IMAGE COPY                       0             0               0
FLASHBACK LOG                    0             0               0
FOREIGN ARCHIVED LOG             0             0               0
AUXILIARY DATAFILE COPY          0             0               0

8 rows selected.

Since this is a standalone db without standby, changed to:

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST';
System altered.


NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_1                   string      LOCATION=USE_DB_RECOVERY_FILE_DEST

We forced a log switch and new logs appear there:

SQL>  select FILE_TYPE, PERCENT_SPACE_USED as "% used",PERCENT_SPACE_RECLAIMABLE as "% reclaimable",  NUMBER_OF_FILES from v$flash_recovery_area_usage;

FILE_TYPE                   % used % reclaimable NUMBER_OF_FILES
----------------------- ---------- ------------- ---------------
CONTROL FILE                     0             0               0
REDO LOG                         0             0               0
ARCHIVED LOG                     0             0               1
BACKUP PIECE                     0             0               0
IMAGE COPY                       0             0               0
FLASHBACK LOG                    0             0               0
FOREIGN ARCHIVED LOG             0             0               0
AUXILIARY DATAFILE COPY          0             0               0

8 rows selected.

That’s it!