Use USE_DB_RECOVERY_FILE_DEST for self-managing deletion rules of the Archived Log files

When using the Fast Recovery Area for storing archive logs, one probably wants to use this location to make use of the self-managing deletion rules of files.

One of these rules is that files are obsolete under the retention policy are eligible for deletion. When for instance RMAN needs space for backup (without delete obsolete option), Oracle will do a similar ‘delete obsolete’ command to make space in the Fast Recovery Area.

For archived logs to be eligible, one MUST use the USE_DB_RECOVERY_FILE_DEST setting for log_archive_dest_x. If the actual location is used, Oracle does not include archived logs in the self-managing deletion rules of files!!!

Not using USE_DB_RECOVERY_FILE_DEST

In the next example we will use the +FRADG location and do a logswitch. You can see ARCHIVELOG is not increased in V$FLASH_RECOVERY_AREA_USAGE.

SQL> alter system set log_archive_dest_1='LOCATION=+FRADG';

System altered.

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            +FRADG
(...)

SQL> alter system switch logfile;

System altered.

SQL> SELECT FILE_TYPE "Type",
PERCENT_SPACE_USED "% Used",
PERCENT_SPACE_RECLAIMABLE "% Reclaim",
NUMBER_OF_FILES "# Files"
FROM V$FLASH_RECOVERY_AREA_USAGE;

Type             % Used  % Reclaim    # Files
------------ ---------- ---------- ----------
CONTROLFILE          .1          0          1
ONLINELOG             0          0          0
ARCHIVELOG            0          0          0
BACKUPPIECE       71.09      48.04         21
IMAGECOPY          8.05          0          6
FLASHBACKLOG          0          0          0

6 rows selected.

Using USE_DB_RECOVERY_FILE_DEST

In the next example we will use the ‘LOCATION=USE_DB_RECOVERY_FILE_DEST’ location and do a logswitch. You can see ARCHIVELOG is now increased in V$FLASH_RECOVERY_AREA_USAGE.

SQL> alter system set log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST';

System altered.

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
(...)

SQL> alter system switch logfile;

System altered.

SQL> SELECT FILE_TYPE "Type",
PERCENT_SPACE_USED "% Used",
PERCENT_SPACE_RECLAIMABLE "% Reclaim",
NUMBER_OF_FILES "# Files"
FROM V$FLASH_RECOVERY_AREA_USAGE;

Type             % Used  % Reclaim    # Files
------------ ---------- ---------- ----------
CONTROLFILE          .1          0          1
ONLINELOG             0          0          0
ARCHIVELOG          .01          0          1
BACKUPPIECE       71.09      48.04         21
IMAGECOPY          8.05          0          6
FLASHBACKLOG          0          0          0

6 rows selected.

Conclusion

Use ‘LOCATION=USE_DB_RECOVERY_FILE_DEST’ when you want Oracle to clear your Archived Logs in the Fast Recovery Area. When you just point to that location, the DBA must take care of the cleanup!