How To Enable Flash Recovery Area In Oracle Database

How To Enable Flash Recovery Area In Oracle Database

The flash recovery area(FRA) is an Oracle-managed destination( either FILE SYSTEM or ASM ) for centralized backup and recovery files. It simplifies the backup management.

The following recovery-related files are stored in the flash recovery area:
— Current control file
— Online redo logs
— Archived redo logs
— Flashback logs
— Control file auto backups
— Datafile and control file copies
— Backup pieces
— Foreign archived redo log

Below are the steps for enabling flash recovery area.

DB_RECOVERY_FILE_DEST_SIZE and DB_RECOVERY_FILE_DEST 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 – > This initialization parameter is a valid destination 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.

1. 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;
SQL> alter system set db_recovery_file_dest='/u01/oradata/FRA' scope=both;

SQL> show parameter db_recovery_file

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      /u01/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
-------------------------------------- ----------- ---------- ----------------- --------------- ----------
/u01/oradata/FRA               2.1475E+10          0                 0               0          0

Now FRA has been 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;

select * from v$flash_recovery_area_usage

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

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='*';
SQL> alter system set db_recovery_file_dest='+FRADG' scope=both sid='*';