A Comprehensive Guide to Fast Recovery Area (FRA) in Oracle

The Fast Recovery Area (FRA) in Oracle is a dedicated location for storing recovery-related files such as backups, redo logs, and archived logs. It plays a critical role in enhancing database recovery and simplifying storage management. This guide covers FRA in detail, practical scenarios, commands for management, and troubleshooting steps.

What is Fast Recovery Area (FRA)?

FRA is a unified storage location for files required during database recovery. Configuring FRA simplifies database management by centralizing recovery files. Typical components stored in the FRA include:

Control files
Online redo logs
Archived redo logs
Flashback logs
RMAN backups

Configuring FRA

To enable and configure the FRA, you need to set the following parameters:

Key Initialization Parameters

DB_RECOVERY_FILE_DEST: Specifies the location of the FRA.
DB_RECOVERY_FILE_DEST_SIZE: Defines the maximum size of the FRA.

Example Configuration:

ALTER SYSTEM SET DB_RECOVERY_FILE_DEST = '/u01/app/oracle/fast_recovery_area' SCOPE=BOTH;
ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = 20G SCOPE=BOTH;

Verifying FRA Configuration:

SHOW PARAMETER DB_RECOVERY_FILE_DEST;
SHOW PARAMETER DB_RECOVERY_FILE_DEST_SIZE;

Output:

Parameter	Value
db_recovery_file_dest	/u01/app/oracle/fast_recovery_area
db_recovery_file_dest_size	20G

Monitoring FRA Usage

Querying FRA Usage:

SET PAGESIZE 20
SET LINESIZE 100
COLUMN NAME FORMAT A30
COLUMN SPACE_LIMIT_GB FORMAT 999.99
COLUMN SPACE_USED_GB FORMAT 999.99
COLUMN SPACE_RECLAIMABLE_GB FORMAT 999.99
COLUMN NUMBER_OF_FILES FORMAT 99999

SELECT NAME, 
       ROUND(SPACE_LIMIT/1024/1024/1024, 2) AS SPACE_LIMIT_GB, 
       ROUND(SPACE_USED/1024/1024/1024, 2) AS SPACE_USED_GB, 
       ROUND(SPACE_RECLAIMABLE/1024/1024/1024, 2) AS SPACE_RECLAIMABLE_GB, 
       NUMBER_OF_FILES
FROM V$RECOVERY_FILE_DEST;

Output:

NAME	SPACE_LIMIT_GB	SPACE_USED_GB	SPACE_RECLAIMABLE_GB	NUMBER_OF_FILES
/u01/app/oracle/fast_recovery_area	20.00	5.00	1.00	45

Detailed Breakdown:

SPACE_LIMIT_GB: Total size allocated to FRA in GB.
SPACE_USED_GB: Space currently utilized in GB.
SPACE_RECLAIMABLE_GB: Space eligible for reuse in GB.
NUMBER_OF_FILES: Number of files stored in the FRA.

Practical Scenarios and Solutions

Scenario 1: FRA is Full

Issue:

Database operations fail due to lack of space in the FRA.

Steps to Resolve:

-- 1. Check FRA Usage
SET PAGESIZE 20
SET LINESIZE 80
COLUMN SPACE_USED_GB FORMAT 999.99
COLUMN SPACE_LIMIT_GB FORMAT 999.99

SELECT ROUND(SPACE_USED/1024/1024/1024, 2) AS SPACE_USED_GB, 
       ROUND(SPACE_LIMIT/1024/1024/1024, 2) AS SPACE_LIMIT_GB 
FROM V$RECOVERY_FILE_DEST;


-- 2. Identify Old Files
SELECT NAME, ROUND(SPACE_RECLAIMABLE/1024/1024/1024, 2) AS SPACE_RECLAIMABLE_GB 
FROM V$RECOVERY_FILE_DEST;

-- 3. Check and Delete Expired & OBSOLETE backups

-- 1. Crosscheck Archive Logs and Backups
RMAN> CROSSCHECK ARCHIVELOG ALL;
RMAN> CROSSCHECK BACKUP;
-- 2. Delete Expired Archive Logs and Backups from RMAN Catalog
RMAN> DELETE EXPIRED ARCHIVELOG ALL;
RMAN> DELETE EXPIRED BACKUP;
-- 3. Force Delete Obsolete Backups Without Confirmation
RMAN> DELETE FORCE NONPROMPT OBSOLETE;
-- 4. Delete Obsolete Backups Based on Retention Policy
RMAN> DELETE OBSOLETE;


-- 4. Manually Back Up and Delete Logs (if needed)
HOST rm -rf /u01/app/oracle/fast_recovery_area/archivelog/*;

-- 5. Increase FRA Size
ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = 50G SCOPE=BOTH;

Explanation of Step 3. 1, 2,3,4 points

Delete Obsolete Backups Based on Retention Policy: Deletes backups that are considered obsolete based on the retention policy defined in RMAN.
Crosscheck Archive Logs and Backups: Crosschecking ensures RMAN verifies the existence of the archive logs and backups in the RMAN catalog and physical storage.
Delete Expired Archive Logs and Backups: Removes any expired archive logs and backups from the RMAN catalog.
Force Delete Obsolete Backups Without Confirmation: Deletes backups that are obsolete based on retention policies, forcing the removal without prompting for confirmation.

Scenario 2: Unable to Create New Archived Logs

Steps to Resolve:

-- 1. Clear Archived Logs
RMAN> CROSSCHECK ARCHIVELOG ALL;
RMAN> DELETE EXPIRED ARCHIVELOG ALL;

-- 2. Relocate Archived Logs to Another Disk
ALTER SYSTEM SET LOG_ARCHIVE_DEST_1 = 'LOCATION=/u02/app/oracle/archive_logs' SCOPE=BOTH;

Scenario 3: Flashback Logs Consuming Excessive Space

Steps to Resolve:

-- 1. Disable Flashback Logging
ALTER DATABASE FLASHBACK OFF;

-- 2. Purge Flashback Logs
RMAN> DELETE NOPROMPT FLASHBACK LOG ALL;

-- 3. Adjust Flashback Retention Target
ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET = 60 SCOPE=BOTH;

Scenario 4: Slow Performance Due to FRA Contention

Steps to Resolve:

- 1. Spread FRA Across Multiple Disks
ALTER SYSTEM SET DB_RECOVERY_FILE_DEST = '+DATA_FRA' SCOPE=BOTH;

-- 2. Optimize Backup Schedule
-- Run backups during non-peak hours to reduce contention.

Best Practices for Managing FRA

Monitor Usage Regularly: Use the V$RECOVERY_FILE_DEST view.
Set Alerts: Configure thresholds for space usage using Oracle Enterprise Manager.
Implement Retention Policies:

RMAN> CONFIGURE RETENTION POLICY TO REDUNDANCY 2;

Archive Logs Periodically: Ensure archived logs are backed up and removed from the FRA.

Useful Queries for FRA Management

List Files in FRA:

SET PAGESIZE 20
SET LINESIZE 80
COLUMN FILE_TYPE FORMAT A20
COLUMN PERCENT_SPACE_USED FORMAT 999.99
COLUMN PERCENT_SPACE_RECLAIMABLE FORMAT 999.99

SELECT FILE_TYPE, 
       ROUND(PERCENT_SPACE_USED, 2) AS PERCENT_SPACE_USED, 
       ROUND(PERCENT_SPACE_RECLAIMABLE, 2) AS PERCENT_SPACE_RECLAIMABLE 
FROM V$FLASH_RECOVERY_AREA_USAGE;

Output:

FILE_TYPE	PERCENT_SPACE_USED	PERCENT_SPACE_RECLAIMABLE
ARCHIVED LOG	40.00	10.00
BACKUP PIECE	30.00	5.00
FLASHBACK LOG	20.00	15.00

Identify Old Backups:

SET PAGESIZE 20
SET LINESIZE 80
COLUMN HANDLE FORMAT A50
COLUMN COMPLETION_TIME FORMAT A20

SELECT HANDLE, COMPLETION_TIME
FROM V$BACKUP_PIECE
WHERE COMPLETION_TIME < SYSDATE - 30;

Verify RMAN Configuration:

RMAN> SHOW ALL;

Conclusion

The Fast Recovery Area is a critical component in Oracle database management, offering centralized storage for recovery-related files. By following the best practices and troubleshooting techniques outlined here, you can ensure that your FRA operates efficiently, minimizing downtime and improving recovery readiness. Regular monitoring and proactive management are essential for leveraging the full benefits of FRA.