Enable Flash Recovery Area In Oracle Database
How To Enable Flash Recovery Area (FRA) In Oracle Database ?
The flash recovery area(FRA) is an Oracle-managed location ( it may be FILE SYSTEM or ASM ) for centralized backup and recovery files. It streamlines backup management.
The Flash Recovery Area contains the following recovery-related files:
- Flashback logs.
- Control file auto backups.
- Datafile and control file copies.
- Backup pieces.
- Archived redo logs.
- Online redo logs.
- Current control file.
DB_RECOVERY_FILE_DEST_SIZE and DB_RECOVERY_FILE_DEST these are 2 initial parameters are required for enabling FRA.
- DB_RECOVERY_FILE_DEST_SIZE: It is the disk quota size for the flash recovery area.
- DB_RECOVERY_FILE_DEST: It is a valid destination Parameter for the Flash Recovery Area. It can be a directory, file system, or ASM disk group.
NOTEÂ : DB_RECOVERY_FILE_DEST_SIZE must be set before DB_RECOVERY_FILE_DEST.
Execute below query to Check whether FRA is enabled or not.
SQL> show parameter db_recovery_file NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_recovery_file_dest string db_recovery_file_dest_size big integer 0 SQL> select * from V$RECOVERY_FILE_DEST; no rows selected
2. Enable FRA.
SQL> alter system set db_recovery_file_dest_size=20G scope=both; System altered. SQL> alter system set db_recovery_file_dest='/data/app/oracle/oradata/FRA' scope=both; System altered. SQL> show parameter db_recovery_file NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_recovery_file_dest string /data/app/oracle/oradata/FRA db_recovery_file_dest_size big integer 20G select * from V$RECOVERY_FILE_DEST; NAME SPACE_LIMIT SPACE_USED SPACE_RECLAIMABLE NUMBER_OF_FILES CON_ID -------------------------------------- ----------- ---------- ----------------- --------------- ---------- /data/app/oracle/oradata/FRA 2.1475E+10 0 0 0 0FRA Enabled.
How to make archivelog destination same as flash recovery area:
SQL> alter system set log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST' scope=both; System altered. SQL> archive log list Database log mode Archive Mode Automatic archival Enabled Archive destination USE_DB_RECOVERY_FILE_DEST Oldest online log sequence 816 Next log sequence to archive 818 Current log sequence 818 SQL> alter system switch logfile; System altered. 1* select * from v$flash_recovery_area_usage SQL> / FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES CON_ID ----------------------- ------------------ ------------------------- --------------- ---------- CONTROL FILE 0 0 0 0 REDO LOG 0 0 0 0 ARCHIVED LOG .01 0 1 0 BACKUP PIECE 0 0 0 0 IMAGE COPY 0 0 0 0 FLASHBACK LOG .49 0 2 0 FOREIGN ARCHIVED LOG 0 0 0 0 AUXILIARY DATAFILE COPY 0 0 0 0 8 rows selected.
FOR RAC : For RAC database, DB_RECOVERY_FILE_DEST_SIZE and DB_RECOVERY_FILE_DEST parameter must be same across all the instances. So it is recommended to put FRA on ASM DISKS.
SQL> alter system set db_recovery_file_dest_size=20G scope=both sid='*'; System altered. SQL> alter system set db_recovery_file_dest='+FRADG' scope=both sid='*'; System altered.
