USE_DB_RECOVERY_FILE_DEST
What is USE_DB_RECOVERY_FILE_DEST? The keyword is a constant value which indicates that we would like to use Fast Recovery Area (FRA) space as the destination for storing online archived logs in ARCHIVELOG mode. You can also regard it as a pointer which points to the real path of FRA.
Before you point the archived log destination to FRA, you have to enable Fast Recovery Area (FRA) by setting the following two initialization parameters:
- DB_RECOVERY_FILE_DEST
- DB_RECOVERY_FILE_DEST_SIZE
The real path of FRA.
The size limit of FRA.
Then you are ready to set archived log destination to USE_DB_RECOVERY_FILE_DEST by following approaches.
Implicit Setting of USE_DB_RECOVERY_FILE_DEST
Suppose that the original destination of archived logs is at the default location like this:
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch
...
You can null out the original value of archived log destination within SPFILE scope. Please note that, you cannot reset this parameter with SCOPE=BOTH.
SQL> alter system reset log_archive_dest_1 scope=both;
alter system reset log_archive_dest_1 scope=both
*
ERROR at line 1:
ORA-32029: resetting with SCOPE=MEMORY or SCOPE=BOTH is currently not supported
SQL> alter system reset log_archive_dest_1 scope=spfile;
System altered.
Then bounce the database instance. As a result, the database will implicitly set archived log destination to USE_DB_RECOVERY_FILE_DEST which is kind of a pointer that indicates database to use Fast Recovery Area (i.e. DB_RECOVERY_FILE_DEST).
Here's the mechanism, if you have configured Fast Recovery Area (FRA) well and do not specify any local archive destinations, the database automatically selects FRA as a local archive destination.
Explicit Setting of USE_DB_RECOVERY_FILE_DEST
You can specify any one of LOG_ARCHIVE_DEST_n to USE_DB_RECOVERY_FILE_DEST explicitly. For example, LOG_ARCHIVE_DEST_1 like below:
SQL> alter system set log_archive_dest_1='location=USE_DB_RECOVERY_FILE_DEST';
System altered.
SQL> show parameter log_archive_dest_1;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_1 string location=USE_DB_RECOVERY_FILE_
DEST
...
Later on, both of above ways return the following information about archived log.
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 10
Next log sequence to archive 12
Current log sequence 12
That's how we set USE_DB_RECOVERY_FILE_DEST for archived log destination.
More Considerations
An advantage of USE_DB_RECOVERY_FILE_DEST is worth noting here. When you switched the destination of FRA to another storage, you don't have to change the archived log destination. This is because USE_DB_RECOVERY_FILE_DEST is just a pointer which dynamically point to the real path of DB_RECOVERY_FILE_DEST.
The size of archived logs could grow rapidly sometimes, especially during data migration. You have to watch the usage closely and take actions if necessary. A fulled FRA will throw ORA-19809 and make the instance hang.
Please don't use LOG_ARCHIVE_DEST (without _n) because LOG_ARCHIVE_DEST is mutually exclusive with DB_RECOVERY_FILE_DEST.
Good Notes
Thanks!