Configure and Use of Flash Recovery Area

Flash recovery area i.e. FRA available since oracle 10g edition and is identified as unified storage location for all recovery related files in oracle database. We will discuss more the about flash recovery area

• The flash recovery area cane reside in single file system or as an ASM diskgroup
• The following permanent items are stores in flash recovery area

Control file :

• Oracle stores one copy of the control file in flash recovery area during installation

Online redo logfile

• You can store one mirrored copy from each redo log file group in flash recovery area

 Following transient items are stored in flash recovery area

• Archived redo log files
• Flashback logs
• Controlfile automatic backup
• Datafile copies (during RMAN backup/cloning as ‘backup as copy’ command)
• RMAN backupsets
• RMAN files

There are three initialization parameters controls location of new control file, online redo logs and datafile are DB_CREATE_FILE_DEST, DB_RECOVERY_FILE_DEST and DB_CREATE_ONLINE_LOG_DEST

• The DB_CREATE_FILE_DEST specifies the default location for oracle managed datafiles (OMF) if we do not explicitly specify destination
• The DB_CREATE_ONLINE_LOG_DEST specifies upto five location for online redo logs files, if this parameter is not specified the oracle uses DB_CREATE_FILE_DEST as destination for online redo log files
• The DB_RECOVERY_FILE_DEST specify the default location for flash recovery are
• Recommended size of flash recovery are is sum of database size , size of incremental backup and size of all archived logs that have not been moved to tape or any other location.

Setting Flash Recovery Area

Setting flash recovery area for use requires below two parameters to be set

DB_RECOVERY_FILE_DEST

DB_RECOVERY_FILE_DEST_SIZE

Make sure you enough space under server file system or ASM diskgroup

Setting parameters

SQL> show parameter db_recovery

NAME                                 TYPE        VALUE

----------------------------------- ----------- -----------

db_recovery_file_dest                string  

db_recovery_file_dest_size           big integer

set recovery location
alter system set DB_RECOVERY_FILE_DEST = '+RECO01' scope = both;

set the size of FRA
alter system set DB_RECOVERY_FILE_DEST_SIZE = 4G scope = both;

SQL> show parameter db_recovery

NAME                                 TYPE        VALUE

---------------------------------- ----------- -----------

db_recovery_file_dest                string      +RECO01

db_recovery_file_dest_size           big integer  4G

Usage of Flash Recovery Area

The database alert log will indicate, if database FRA limit is full though you have enough space on file system or diskgroup.

Usage of FRA can be determined using below queries

Check location and size using v$recovery_file_dest

SQL>  select * from  v$recovery_file_dest;

NAME  SPACE_LIMIT  SPACE_U SPACE_REC NUMBER_OF_FILES       CON_ID
----------------- -------- --------- --------------- ---------------+FLASH 107374182400  2097152       0              2              0

Check usage for areas and purpose

SQL> select * from v$flash_recovery_area_usage;

FILE_TYPE    PERCENT_S_USED PERCENT_S_RECLAIMABLE NUMBER  CON_ID
----------- -------------- ------------------- --------- ---------CONTROL FILE            0               0             0           0

REDO LOG                0               0             0           0
ARCHIVED LOG            0               0             2           0
BACKUP PIECE            0               0             0           0
IMAGE COPY              0               0             0           0
FLASHBACK LOG           0               0             0           0
FOREIGN ARCHIVED LOG    0               0             0           0
AUXILIARY DATAFILE COPY 0               0             0           0

Size of flash recovery area can be modified using alter system command. Database outage will not be required to carry out this change

alter system set db_recovery_dest_size = 6G scope=both;