Managing FRA

The following queries are helpful in managing the FRA.

SQL> Select * from v$recovery_file_dest;

Name - Current location

Space Limit - FRA size

Space Reclaimable - Space reclaimable by deleting the obsolete files.

SQL> select * from v$flash_recovery_area_usage;

Used to check the space being used by different files and the space reclaimable by deleting the files that are obsolete.

FRA Errors

When the FRA is full, Oracle issues the following error message,

ORA-19815: Warning: db_recovery_file_dest_size of XXXXXX bytes is 100% used, and has 0 remaining bytes available.

ORA-19809: Limit set by the db_recovery_dest_file_size is exceeded.

ORA-19804: Oracle is unable to reclaim a specified amount of bytes from the limit set by the db_recovery_file_dest_size.

FRA - File Deletion Policy

Automatic File Deletion Policy for FRA

• Multiplexed redo log files and control files (Permanent files) are never deleted.
• Files older than the days set by the retention policy are available for deletion.
• Files that are copied to the tape are eligible for deletion. However they are removed only when there is no space in the FRA.

FRA Parameters

The following parameters are involved in setting up a FRA.

1. DB_CREATE_FILE_DEST - location for all OMF data files.
2. DB_CREATE_ONLINE_LOG_DEST_n - location for control files and online redo log files. If this parameter is not set then oracle creates all three types of files in the DB_CREATE_FILE_DEST location.
3. DB_RECOVERY_FILE_DEST_SIZE - Size of FRA.
4. DB_RECOVERY_FILE_DEST - Location of FRA.
5. LOG_ARCHIVE-DEST_N - Location of Archive log files.

For eg.,

DB_CREATE_FILE_DEST = /oradata/dbfiles/
LOG_ARCHIVE-DEST_1 = 'LOCATION=/export/archive/arc_dest1'
LOG_ARCHIVE-DEST_2 = 'LOCATION=USE_DB_RECOVERY_FILE_DEST'
DB_RECOVERY_FILE_DEST_SIZE =350GB
DB_RECOVERY_FILE_DEST='LOCATION=/fra/rec_area'

one copy of current control file and online redo log files are stored in FRA also.

Control Files, redolog files and FRA

DB_CREATE_ONLINE_DEST_n : Setting this init parameter enable Oracle to create OMF control files and online redolog files in the location specified by the parameter.

DB_RECOVERY_FILE_DEST : Setting this init parameter enables Oracle to create OMF control files and online redolog files in the FRA.

Specifying both the above parameters enable Oracle to create OMF based control and redolog files on both the locations.

Omitting the above parameters will enable Oracle to create non-OMF based control and redolog files in the system specific default location.

Thanks

Setup Flash Recovery Area

To setup the FRA, you need to set two init paramters in the following order.

SQL> Alter system set DB_RECOVERY_FILE_DEST_SIZE=375809638400 scope=spfile;

SQL> Alter system set DB_RECOVERY_FILE_DEST=+ASM_FLASH scope=spfile;

When you configure FRA, LOG_ARCHIVE_DEST and LOG_ARCHIVE_DUPLEX_DEST init parameters become obsolete. You must use the parameters LOG_ARCHIVE_DEST_n.

Log_Archive_dest_10 is by default set to USE_DB_RECOVERY_FILE_DEST. Archived log files are stored in the FRA by default. If you have configured other locations a copy of archived files will also be placed in other locations as well.

USE_DB_RECOVERY_FILE_DEST refers to the FRA.

You can also use OEM and DBCA to configure FRA.

Disable FRA

Set DB_RECOVERY_FILE_DEST=''