Oracle Fast Recovery Area: Set db_recovery_file_dest

Oracle Fast Recovery Area: Set DB Recovery File Dest and Size

Purpose

The Oracle Fast Recovery Area (FRA) is a disk location where Oracle keeps files related to backup and recovery. Two dynamic initialization parameters, db_recovery_file_dest and db_recovery_file_dest_size, control its location and its disk quota. Oracle documents that setting them enables the FRA with or without shutting down and restarting the instance.

These two commands configure the Oracle Fast Recovery Area at the system level while the database is running. The SCOPE=BOTH clause means the change is made in memory and in the server parameter file (SPFILE). It takes effect immediately and persists after the database is shut down and started up again.

The Backup and Recovery documentation says the FRA can contain control files, online redo logs, archived redo logs, flashback logs, and RMAN backups. Setting the quota and the location correctly is a core task for any DBA who manages backup and recovery.

Code

1-- Set the database recovery size, then the directory
2-- Both parameters are dynamic
3-- The size MUST be set before the path
4
5ALTER SYSTEM SET db_recovery_file_dest_size=<size> SCOPE=BOTH;
6/
7ALTER SYSTEM SET db_recovery_file_dest='<path>' SCOPE=BOTH;
8/

Replace <size> with a value like 100G, 2048G, or 500M, and replace <path> with a file system directory or an ASM disk group.

Code Breakdown

Statement 1 - Set the FRA Size

1ALTER SYSTEM SET db_recovery_file_dest_size=<size> SCOPE=BOTH;

• ALTER SYSTEM SET - changes a database-wide initialization parameter
• db_recovery_file_dest_size - the hard limit, in bytes, on the total space used by recovery files in the fast recovery area
• <size> - an integer, optionally followed by K, M, or G, such as 10G or 500M
• SCOPE=BOTH - applies the change to the running instance and to the SPFILE
• The parameter type is Big integer

Example: ALTER SYSTEM SET db_recovery_file_dest_size=100G SCOPE=BOTH;

Statement 2 - Set the FRA Path

1ALTER SYSTEM SET db_recovery_file_dest='<path>' SCOPE=BOTH;

• db_recovery_file_dest - the default location for the fast recovery area
• '<path>' - a directory or an ASM disk group. Oracle states the location cannot be a raw disk, and that it must be large enough for the disk quota
• The directory must already exist. Oracle documents that the database does not create the directories named by these parameters

Example: ALTER SYSTEM SET db_recovery_file_dest='/u01/app/oracle/fra' SCOPE=BOTH;

Why the Order Matters

The Backup and Recovery guide says of the size parameter: "You must set this parameter before DB_RECOVERY_FILE_DEST." The reference page for the path parameter adds that specifying it without also specifying DB_RECOVERY_FILE_DEST_SIZE is not allowed. Oracle publishes the message for this case as ORA-19802: cannot use DB_RECOVERY_FILE_DEST without DB_RECOVERY_FILE_DEST_SIZE.

That is why the code above, and the working example below, run the size statement first. If a database already has a size set, you can change the path alone, but on a new setup always run the size statement first.

Key Points

• Order matters: Set db_recovery_file_dest_size before db_recovery_file_dest.
• No restart needed: Both parameters are modifiable with ALTER SYSTEM, and Oracle documents that setting them enables the FRA without a restart.
• The path must exist: Oracle does not create the directory for you.
• The size is a quota, not a measurement: Oracle states that DB_RECOVERY_FILE_DEST_SIZE does not indicate the real size occupied on disk when the underlying file system is mirrored, compressed, or affected by overhead not known to Oracle Database. On a two-way mirrored ASM disk group each file of x bytes occupies 2x bytes, so Oracle advises setting the size to no more than half the size of the disks in that group.
• RAC environments: You must set the size for every instance, and multiple instances must have the same value.
• Persistence: If the instance uses a server parameter file, SCOPE=BOTH makes the settings persistent. If it uses a text parameter file, the ALTER SYSTEM change is not persistent, and you must also add both parameters to that file.
• Verify current settings: Run SHOW PARAMETER db_recovery_file or query V$RECOVERY_FILE_DEST.

Insights and Best Practices

Size the FRA to the retention policy

Size the FRA to hold the backups and archived logs your retention policy needs. Oracle also advises allowing an extra 10% when computing the actual disk usage required for the fast recovery area. When Flashback Database is enabled, the Flashback best practices page says that generally the volume of flashback log generation is similar in magnitude to redo log generation. It gives this formula: Target FRA = Current FRA + DB_FLASHBACK_RETENTION_TARGET x 60 x Peak Redo Rate (MB/sec).

Monitor usage by file type

Query V$RECOVERY_AREA_USAGE to see usage by file type. Its FILE_TYPE column reports values such as ARCHIVED LOG, BACKUP PIECE, IMAGE COPY, and FLASHBACK LOG, and its PERCENT_SPACE_USED and PERCENT_SPACE_RECLAIMABLE columns show how much of the area is in use and how much could be reclaimed.

1SELECT file_type, percent_space_used, percent_space_reclaimable, number_of_files
2FROM V$RECOVERY_AREA_USAGE;

Compare the reclaimable percentage with the used percentage. A high used value with a low reclaimable value means the retention policy is holding most of the area.

Reclaim space with RMAN

If the FRA has no reclaimable space, review the retention policy first. Then use the two RMAN commands for two different situations:

• DELETE OBSOLETE removes backups and copies that the retention policy no longer needs. If you give no options, RMAN uses the options from CONFIGURE RETENTION POLICY. It considers only the backup retention policy, not the archived log deletion policy.
• CROSSCHECK then DELETE EXPIRED handles files that are missing. RMAN marks backups and copies as expired when you run CROSSCHECK and the files are absent or inaccessible. DELETE EXPIRED removes only repository records with the status EXPIRED. Oracle states that if a backup marked EXPIRED still exists as a file, RMAN does not delete the physical file.

These are not the same job. DELETE OBSOLETE frees disk space based on retention. DELETE EXPIRED cleans repository records for files that are already gone, so it does not free any disk space by itself. Oracle describes the best practice as running CROSSCHECK to update the status of backups and copies in the repository, and then running DELETE to remove the files you want gone. The NOPROMPT option deletes the specified files without first listing them or prompting for confirmation.

1RMAN> CROSSCHECK BACKUP;
2RMAN> DELETE EXPIRED BACKUP;
3RMAN> DELETE OBSOLETE;

Consider ASM for larger deployments

The path can be an ASM disk group such as +FRA. If you do, remember the mirroring rule above and size the quota against usable space, not raw space.

Verify after every change

Always confirm your settings with:

1SHOW PARAMETER db_recovery_file;
2SELECT * FROM V$RECOVERY_FILE_DEST;

In V$RECOVERY_FILE_DEST, NAME is the value of DB_RECOVERY_FILE_DEST, and SPACE_LIMIT is the value of DB_RECOVERY_FILE_DEST_SIZE. SPACE_USED counts files created in current and all previous fast recovery areas, so changing the area does not reset it to 0.

Scope Options Explained

The SCOPE clause controls where the parameter change is applied. This table follows the ALTER SYSTEM reference for a non-CDB or a CDB root:

If you started the instance with a text parameter file, MEMORY is the only scope you can specify. For FRA parameters on an instance that uses an SPFILE, SCOPE=BOTH is the usual choice because the setting is live at once and survives a restart.

What Happens When the FRA Fills Up

The size parameter is a quota, and Oracle manages the space inside it. The Backup and Recovery guide gives these facts:

• Oracle does not delete eligible files from the fast recovery area until the space must be reclaimed for some other purpose.
• When the fast recovery area is full, Oracle automatically deletes eligible files to reclaim space as needed.
• If the RMAN retention policy requires keeping a set of backups larger than the disk quota, or if the retention policy is set to NONE, the fast recovery area can fill completely with no reclaimable space.
• Archived redo logs: if the log is archived to an accessible location outside the fast recovery area, instance availability is not affected. Otherwise the database eventually halts because it cannot archive the online redo logs.

So the first response to a full FRA is automatic deletion of eligible files. Trouble starts only when nothing is eligible, because the retention policy protects everything that is left.

Quick Reference: Common Size Formats

1-- 10 Gigabytes
2ALTER SYSTEM SET db_recovery_file_dest_size=10G SCOPE=BOTH;
3
4-- 100 Gigabytes
5ALTER SYSTEM SET db_recovery_file_dest_size=100G SCOPE=BOTH;
6
7-- 2 Terabytes
8ALTER SYSTEM SET db_recovery_file_dest_size=2048G SCOPE=BOTH;

Complete Working Example

 1-- Step 1: Set the FRA size FIRST (required before setting the path)
 2ALTER SYSTEM SET db_recovery_file_dest_size=100G SCOPE=BOTH;
 3/
 4
 5-- Step 2: Set the FRA directory path
 6ALTER SYSTEM SET db_recovery_file_dest='/u01/app/oracle/fra' SCOPE=BOTH;
 7/
 8
 9-- Step 3: Verify the settings
10SHOW PARAMETER db_recovery_file;
11
12-- Step 4: Confirm space allocation
13SELECT name, space_limit, space_used, space_reclaimable, number_of_files
14FROM V$RECOVERY_FILE_DEST;
15
16-- Optional: Send archived logs to the FRA
17ALTER SYSTEM SET log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST' SCOPE=BOTH;

When to Run This

• Enabling the FRA on a new database or after a restore
• Raising the quota after V$RECOVERY_AREA_USAGE shows little reclaimable space
• Moving the FRA to a larger file system or disk group
• Checking a RAC cluster to confirm every instance has the same size value

Troubleshooting Common Issues

• ORA-19802: You set the path before the size. Set db_recovery_file_dest_size first, then repeat the path statement.
• The command fails on the path: Check that the directory exists and that the Oracle software owner can write to it. Oracle does not create it.
• Settings vanish after a restart: The instance probably uses a text parameter file. SCOPE=BOTH only persists with a server parameter file, so add both parameters to the text file.
• FRA stays full after deletes: Check SPACE_RECLAIMABLE in V$RECOVERY_FILE_DEST. If it is low, the retention policy is protecting the files. Review CONFIGURE RETENTION POLICY, then run DELETE OBSOLETE.
• Space reported by Oracle does not match the disk: The quota is not the real size occupied on disk when the storage is mirrored or compressed. Reduce the quota to match usable space.

References

• Configuring the RMAN Client - Oracle 19c Backup and Recovery User's Guide - Official guidance on enabling the fast recovery area, the setting order, the disk quota, and what happens when the area is full
• DB_RECOVERY_FILE_DEST_SIZE - Oracle 19c Database Reference - Syntax, Big integer type, ALTER SYSTEM modifiability, and the RAC requirement
• DB_RECOVERY_FILE_DEST - Oracle 19c Database Reference - Syntax and the rule that the path cannot be specified without the size
• V$RECOVERY_FILE_DEST - Oracle 19c Database Reference - Columns that show the disk quota and current usage of the fast recovery area
• V$RECOVERY_AREA_USAGE - Oracle 19c Database Reference - Usage by file type, with used and reclaimable percentages
• ALTER SYSTEM - Oracle 19c SQL Language Reference - Definition of the MEMORY, SPFILE, and BOTH scope values
• DELETE - Oracle 19c Backup and Recovery Reference - RMAN DELETE OBSOLETE, DELETE EXPIRED, and NOPROMPT behavior, and the CROSSCHECK best practice
• Using Oracle Managed Files - Oracle 19c Database Administrator's Guide - States that the directories named by these parameters must already exist
• Oracle Flashback Best Practices - Oracle 23ai High Availability Overview - Flashback log volume and the FRA sizing formula
• RMAN Enhancements in Oracle Database 10g - ORACLE-BASE - Community walkthrough that shows ALTER SYSTEM examples for both FRA parameters and describes the area as managed by the database