FRA Management

Oracle Database FRA (Flash Recovery Area) Management:

This document provides comprehensive guidance on managing the Flash Recovery Area (FRA) in
Oracle databases. It covers essential concepts, configuration, monitoring, and troubleshooting, including detailed
SQL queries for various management tasks.

1. Introduction to FRA

The Flash Recovery Area (FRA) is a central storage location for Oracle database recovery-related files.

These files include:

• Archived Redo Logs: Essential for database recovery.
• RMAN Backups: Full, incremental, and control file backups.
• Control File Autobackups: Automatic backups of the control file.
• Online Redo Logs (if multiplexed to FRA): Copies of online redo logs.
• Flashback Logs: Logs used for Flashback Database operations.

Benefits of using FRA:

• Simplified backup and recovery management.
• Automated space management.
• Centralized recovery file storage.
• Reduced risk of data loss.

2. Configuring the FRA

The FRA is configured using the following initialization parameters:

• DB_RECOVERY_FILE_DEST: Specifies the location of the FRA (directory path)
• DB_RECOVERY_FILE_DEST_SIZE: Specifies the total size allocated to the FRA.

2.1. Setting FRA Parameters

-- Check current FRA settings

SHOW PARAMETER DB_RECOVERY_FILE_DEST;
SHOW PARAMETER DB_RECOVERY_FILE_DEST_SIZE;

-- Set FRA location and size (replace with your desired values)
ALTER SYSTEM SET DB_RECOVERY_FILE_DEST = '/u02/oradata/recovery_area' SCOPE=BOTH;
ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = 50G SCOPE=BOTH;

2.2. Verifying FRA Configuration

-- Verify FRA configuration
SELECT name, value
FROM v$parameter
WHERE name LIKE 'db_recovery_file_dest%';

-- Check space usage in FRA
SELECT name, space_limit, space_used, number_of_files
FROM v$recovery_file_dest;

3. Monitoring FRA Usage

Regular monitoring of FRA usage is crucial to prevent it from filling up, which can impact database operations.

3.1. Checking FRA Space Usage

-- Detailed FRA space usage

SELECT FILE_TYPE, PERCENT_SPACE_USED, PERCENT_SPACE_RECLAIMABLE, NUMBER_OF_FILES
FROM V$FLASH_RECOVERY_AREA_USAGE;

-- More detailed space usage.
SELECT FILE_TYPE,
      ROUND(PERCENT_SPACE_USED, 2) "Percent Used",
      ROUND(PERCENT_SPACE_RECLAIMABLE, 2) "Percent Reclaimable",
      NUMBER_OF_FILES,
      ROUND(SPACE_LIMIT / 1024 / 1024 / 1024, 2) "Limit (GB)",
      ROUND(SPACE_USED / 1024 / 1024 / 1024, 2) "Used (GB)",
      ROUND(SPACE_RECLAIMABLE / 1024 / 1024 / 1024, 2) "Reclaimable (GB)"
FROM V$FLASH_RECOVERY_AREA_USAGE;

3.2. Monitoring Archived Redo Log Usage

-- Archived redo log usage in FRA

SELECT name, space_limit, space_used, space_reclaimable, number_of_files
FROM v$recovery_file_dest
WHERE file_type = 'ARCHIVED LOG';

3.3. Monitoring RMAN Backup Usage

-- RMAN backup usage in FRA

SELECT name, space_limit, space_used, space_reclaimable, number_of_files
FROM v$recovery_file_dest
WHERE file_type = 'BACKUPPIECE';

3.4. Monitoring Flashback Log Usage

-- Flashback log usage in FRA

SELECT name, space_limit, space_used, space_reclaimable, number_of_files
FROM v$recovery_file_dest
WHERE file_type = 'FLASHBACK LOG';

4. Managing FRA Space

When the FRA is nearing its capacity, you need to manage the space by deleting or moving files.

4.1. Deleting Obsolete RMAN Backups

-- Configure RMAN retention policy (example: redundancy 1)

CONFIGURE RETENTION POLICY TO REDUNDANCY 1;

-- Delete obsolete backups

RMAN> DELETE OBSOLETE;

4.2. Deleting Archived Redo Logs

• Using RMAN:

RMAN> DELETE ARCHIVELOG ALL COMPLETED BEFORE 'SYSDATE-7'; -- Delete logs older than 7 days

• Using SQL (with caution):

-- Only if RMAN is not used for archivelog deletion.

-- First, ensure the logs are backed up.

-- Then, delete logs older than a specific date.

SELECT name FROM v$archived_log WHERE completion_time < SYSDATE - 7;

-- then using OS commands, delete those files.

4.3. Increasing FRA Size

-- Increase FRA size (replace with your desired value)

ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = 75G SCOPE=BOTH;

4.4. Moving Files Outside the FRA

• Archived Redo Logs:

-- Use RMAN to backup archivelogs to another location.
RMAN> BACKUP ARCHIVELOG ALL FORMAT '/new_location/%U';
-- Then delete the archivelogs from FRA.
RMAN> DELETE ARCHIVELOG ALL;

• RMAN Backups:

-- Use RMAN to backup backups to another location using copy backup.
RMAN> BACKUP BACKUPSET ALL FORMAT '/new_location/%U';
-- Then delete the backups from FRA.
RMAN> DELETE BACKUPSET ALL;

5. Troubleshooting FRA Issues

5.1. FRA Full Issues

• Symptom: Database errors related to archiving or backups.
• Solution:

1. Delete obsolete backups and archived redo logs.
2. Increase FRA size.
3. Move files outside the FRA.

5.2. FRA Location Issues

• Symptom: Database startup errors or RMAN errors.
• Solution:

1. Verify the DB_RECOVERY_FILE_DEST parameter.
2. Ensure the directory exists and has proper permissions.

5.3. Permissions Issues

• Symptom: RMAN errors or database errors related to file creation.
• Solution:

1. Verify the Oracle user has read/write permissions on the FRA directory.

5.4. RMAN Retention Policy Issues

• Symptom: Unexpectedly large or small numbers of backups.
• Solution:

1. Review and adjust the RMAN retention policy.

6. RMAN Queries related to FRA

--List all backups in FRA.

RMAN> LIST BACKUP;

--List archivelogs in FRA.

RMAN> LIST ARCHIVELOG ALL;

--List controlfile backups in FRA.

RMAN> LIST CONTROLFILECOPY;

--List all backupsets.

RMAN> LIST BACKUPSET;

--List all backup pieces.

RMAN> LIST COPY OF DATABASE;

--Shows the current configured retention policy.

RMAN> SHOW RETENTION POLICY;

7. Best Practices

• Allocate sufficient space to the FRA.
• Regularly monitor FRA usage.
• Implement a proper RMAN retention policy.
• Automate FRA space management tasks.
• Back up archived redo logs and backups to a separate location for long-term storage.
• Test your backup and recovery strategies regularly.