Flash Recovery Area

The Flash Recovery Area (FRA) (associated with the parameter DB_RECOVERY_FILE_DEST) is a unified storage location for all recovery related files and activities in an Oracle database.
By defining one init.ora parameter, all RMAN backups, archive logs, control file autobackups, and datafile copies are automatically written to a specified file system or ASM Disk Group.
This option is strongly recommended. In RAC environments, the location must be on a cluster file system or shared directory configured through NFS. The files that we save here are PERMANENT (multiplexed copies of current controlfile and online redo logs, they can't be deleted) or TRANSIENT (arch log files, dfiles copies, ctrl file copies, etc).
Ideally you would create this area big enough to save all backups copies, as a minimum it should be large enough to contain archive log files that are not copied to tapes yet.
You can query the V$RECOVERY_FILE_DEST to identify that location.
The flash recovery area should be on a separate disk from the database area, where active database files such as datafiles, control files, and online redo logs are stored. Keeping the flash recovery area on the same disk as the database area exposes you to loss of both your live database files and backups in the event of a media failure.

To enable Flash Recovery Area (FRA) you just need to specify 2 parameters in the following order:
- DB_RECOVERY_FILE_DEST_SIZE (specifies max space to use)
- DB_RECOVERY_FILE_DEST (Location)
You can specify those parameters in the init.ora file or by ALTER SYSTEM SET command with the scope=both option. Examples:
ALTER SYSTEM SET db_recovery_file_dest_size=10G scope=both;
ALTER SYSTEM SET db_recovery_file_dest='/oradata/FRA';

For example, If FRA is in an Automatic Storage Management (ASM) disk group
ALTER SYSTEM SET db_recovery_file_dest='+FLASH' scope=both;

For RAC Perform:
ALTER SYSTEM set db_recovery_file_dest_size=60G scope=both sid='*' ;
ALTER SYSTEM SET db_recovery_file_dest='+FLASH' sid='*';
In a RAC database, all instances must have the same values for these parameters. Even though there are multiple nodes they all share the same controlfiles.

To disable FRA you can use:
ALTER SYSTEM SET DB_RECOVERY_FILE_DEST = '' scope=both;
Note: even after you disable the flash recovery area, the RMAN will continue to access the files located in the flash recovery area for backup and recovery purposes.

Restrictions on Initialization Parameters
Using a flash recovery area has implications for some other initialization parameters:
- Oracle automatically assigns the LOG_ARCHIVE_DEST_10 to USE_DB_RECOVERY_FILE_DEST (meaning that archived redo log files will be sent to the flash recovery area) if you create a recovery area and do not set any other local archiving destinations.
- You cannot use the LOG_ARCHIVE_DEST and LOG_ARCHIVE_DUPLEX_DEST parameters to specify redo log archive destinations. You must instead use the newer LOG_ARCHIVE_DEST_n parameters.
- Oracle allows you to generate archive log files to the FRA and one or more additional location through the use of the LOG_ARCHIVE_DEST_n parameters. To do this, you do not use the standard FRA parameter DB_RECOVERY_FILE_DEST. Rather, you define the various LOG_ARCHIVE_DEST_n parameters as you normally would. Then, you define an additional LOG_ARCHIVE_DEST_n parameter for the FRA using the Oracle-supplied constant USE_DB_RECOVERY_FILE_DEST for the location of that archiving destination, as shown in this example:
SQL> alter system set log_archive_dest_10='LOCATION=USE_DB_RECOVERY_FILE_DEST';
In this case, the LOG_ARCHIVE_DEST_10 parameter will cause the ARCH process to archive to the archive log destination. Note that you still need to set the DB_RECOVERY_FILE_DEST parameter. What happens if you set the DB_RECOVERY_FILE_DEST parameter and you set any of the LOG_ARCHIVE_DEST_n parameters? Oracle will only archive to the LOG_ARCHIVE_DEST_n locations, and will not archive to the FRA unless you have set one of the LOG_ARCHIVE_DEST_n parameters to a location of USE_DB_RECOVERY_FILE_DEST.
- Multiple database can have the same DB_RECOVERY_FILE_DEST only if the DB_NAME are different or if the DB_NAME is same (example the primary and standby database) then the DB_UNIQUE_NAME parameter must be different for the databases
- For RAC the location of Flash Recovery Area must be on a cluster file system, ASM or a shared directory configured through NFS. The location and disk quota must be the same on all instances.

Oracle has a dynamic performance view for monitoring the recovery area:

desc v$recovery_file_dest
Name                          Type
----------------------------- -----------
NAME                          VARCHAR2(513) Recovery area name, indicating location string. This is the value specified in the DB_RECOVERY_FILE_DEST initialization parameter.
SPACE_LIMIT                   NUMBER        Disk space (in bytes) that can be used for FRA (DB_RECOVERY_FILE_DEST_SIZE initialization parameter).
SPACE_USED                    NUMBER        Disk space (in bytes) used by FRA files created in current and all previous FRA. Changing FRA does not reset SPACE_USED to 0.
SPACE_RECLAIMABLE             NUMBER        Total amount of disk space (in bytes) that can be created by deleting obsolete, redundant, and other low priority files from the FRA.
NUMBER_OF_FILES               NUMBER        Number of Files on that Location
CON_ID                        NUMBER

Example:
column Name format a17
SELECT Name, (SPACE_LIMIT/1024/1024/1024) Space_Limit_GB, SPACE_USED/1024/1024/1024 Space_Used_GB, SPACE_RECLAIMABLE, NUMBER_OF_FILES
FROM V$RECOVERY_FILE_DEST;

NAME SPACE_LIMIT_GB SPACE_USED_GB SPACE_RECLAIMABLE NUMBER_OF_FILES
--------------- -------------- ------------- ----------------- ---------------
+FLASH 20 .022460938 0 1

NAME SPACE_LIMIT_GB SPACE_USED_GB SPACE_RECLAIMABLE NUMBER_OF_FILES----------------- -------------- ------------- ----------------- ---------------R:\BACKUPS 150 118.754891 7349836800 53

The size of the flash recovery area at any time is a function of:
• Copies of all datafiles
• Incremental backups
• Online redo logs
• Archived redo logs
• Flashback logs
• Current Controlfile
• Controlfile autobackups and SPFILEs

When sizing, you need to take into account the RMAN backup retention policy, storage device type and number of data block changes.

The following view shows also the file types, consuming space in the Recover Area:
set linesize 100
compute sum of percent_space_used on report
compute sum of percent_space_reclaimable on report
select * from V$RECOVERY_AREA_USAGE;
FILE_TYPE            PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
-------------------- ------------------ ------------------------- ---------------
CONTROL FILE                          0                         0               0
REDO LOG                              0                         0               0
ARCHIVED LOG                          0                         0               0
BACKUP PIECE                      47.04                         0               3
IMAGE COPY                            0                         0               0
FLASHBACK LOG                         0                         0               0
FOREIGN ARCHIVED LOG                  0                         0               0
------------------ ------------------------- sum 47.04 10.16

Sizing FRA
If you want to keep:
–Control file backups and archived logs
        Then estimate total size of all archived logs generated between successive backups on the busiest days x 2 (in case of unexpected redo spikes)
–Flashback logs
        Then add in {Redo rate x Flashback retention target time x 2}
–Incremental backups
        Then add in their estimated sizes
–On-disk image copy
        Then add in size of the database minus size of temporary files

FRA File Retention and Deletion
When FRA space needs exceed quota, automatic file deletion occurs in the following order:
1. Flashback logs = Oldest Flashback time can be affected (with exception of guaranteed restore points)
2. RMAN backup pieces/copies and archived redo logs that are:
• Not needed to maintain RMAN retention policy, or
• Have been backed up to tape (via DEVICE TYPE SBT) or secondary disk location (via BACKUP RECOVERY AREA TO DESTINATION ‘..’)

If archived log deletion policy is configured as:
– APPLIED ON [ALL] STANDBY = Then archived log must have been applied to mandatory or all standby databases
– SHIPPED TO [ALL] STANDBY = Then archived log must have been transferred to mandatory or all standby databases
– BACKED UP <N> TIMES TO DEVICE TYPE [DISK | SBT] = Then archived log must have been backed up at least <N>times
– If [A

Managing the Flash Recovery Area: Resolving flash_recovery_area full
The database issues a warning alert when reclaimable space is less than 15% and a critical alert when reclaimable space is less than 3%.
You can see the alerts in the alert.log and in DBA_OUTSTANDING_ALERTS.
SELECT object_type, message_type, message_level, reason, suggested_action
FROM dba_outstanding_alerts;

If the flash recovery area becomes full, an error is issued. Beware of using the flash recovery area for log_archive_dest_n.
If the flash recovery becomes full and Oracle cannot archive redo logs then the instance will hang.

The following actions can be done to resolve the space issue :
- Add disk space to the Flash Recovery Area or increase DB_RECOVERY_FILE_DEST_SIZE
         alter system set DB_RECOVERY_FILE_DEST_SIZE= <new size>;
- Use the command BACKUP RECOVERY AREA, to back up the contents of the Flash Recovery Area to a tertiary device such as tape.
          RMAN> backup device type 'sbt_tape' recovery area;
     or
         RMAN> backup recovery area;

- Delete the files from the Flash Recovery Area using RMAN.
           The removal is described in the RMAN documentation but this is a quick and dirty way if you don't have an rman repository - but could endanger your ability to recover - so be careful.
       a) delete unwanted archive log files from disk ( rm /del )
       b) connect to rman
       c) rman> crosscheck archivelog all;       - marks the controlfile that the archives have been deleted
       d) rman> delete expired archivelog all;    - deletes the log entries identified above.
- Changing RMAN retention policy.

NOTE= Manually removing fixed files from the FRA can have unexpected consequences. Oracle does not immediately detect the removal of these files, and thus the space is not reclaimed. If you end up manually removing files (or loose a disk perhaps), use the RMAN crosscheck command along with the delete command to cause Oracle to update the current control file information on the FRA. The folks at Oracle recommend that you not manually remove files managed by Oracle if at all possible.

Oracle does not delete eligible files from the Flash Recovery Area until the space must be reclaimed for some other purpose. The effect is that files recently moved to tape are often still available on disk for use in recovery. The recovery area can thus serve as a kind of cache for tape. Once the Flash Recovery Area is full, Oracle automatically deletes eligible files to reclaim space in the Flash Recovery Area as needed.

Identify Different File types and space used in Flash Recovery Area
The following view is very helpful to assist in space management :V$FLASH_RECOVERY_AREA_USAGE

V$FLASH_RECOVERY_AREA_USAGE shows the percentage of the total disk quota used by different types of files, and how much space for each type of file can be reclaimed by deleting files that are obsolete, redundant, or already backed up to tape.

select * from v$flash_recovery_area_usage;
FILE_TYPE    PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
------------ ------------------ ------------------------- ---------------
CONTROLFILE      0                  0                         0
ONLINELOG        0                  0                         0
ARCHIVELOG      39.86              39.86                     24
BACKUPPIECE     59.19              28.93                      4
IMAGECOPY      0                  0                         0
FLASHBACKLOG   0                  0                         0

Percentage of space usable in the flash recovery area can be identified by:
SELECT (100 - sum(percent_space_used)) + sum(percent_space_reclaimable)FROM v$flash_recovery_area_usage;
(100-SUM(PERCENT_SPACE_USED))+SUM(PERCENT_SPACE_RECLAIMABLE)
------------------------------------------------------------
69.74

V$RECOVERY_FILE_DEST shows the current location, disk quota, space in use, space reclaimable by deleting files, and total number of files in the flash recovery area

SQL> select * from v$recovery_file_dest;
NAME           SPACE_LIMIT   SPACE_USED    SPACE_RECLAIMABLE NUMBER_OF_FILES
------------ -------------   ------------ ----------------- ----------------
/dba/backup     4294967296     2647365120                  0               49

Changing the Flash Recovery Area
It is possible to:
a. Archive to another file system location in addition to the Flash Recovery Area
b. Archive to another file system location instead of the Flash Recovery Area
c. Place disk backups in an alternate location
d. Avoid use of the Flash Recovery Area altogether (not recommended)
e. Disable the Flash Recovery Area

a.   Archive to another file system location in addition to the Flash Recovery Area
SQL> create pfile from spfile;
Add the following line to the init.ora:
        log_archive_dest_n=’<archivelog directory>’ eg
        log_archive_dest_1='LOCATION=D:\oracle\product\10.2.0\oradata\V102\Arch'
Restart the instance using the amended pfile and recreate the spfile:
        SQL>shutdown
        SQL>create spfile from pfile;
        SQL>startup;

b. Archive to another file system location instead of the Flash Recovery Area
Connect to SQL*Plus as SYS and execute:
SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST='/u01/flash_recovery_area' SCOPE=BOTH ;
-- or if you use ASM:
SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST='+disk1' SCOPE=BOTH ;

The permanent files (control files and online redolog files), flashback logs and transient files can be left in the old FRA location. The database will delete the transient files from the old FRA location as they become eligible for deletion.

For FLASHBACK logfiles to be able to to pick up the new DB_RECOVERY_FILE_DEST location, the flashback option needs to be restarted like this:
SQL> shutdown immediate;
SQL> startup mount;
SQL> ALTER DATABASE FLASHBACK OFF;
SQL> ALTER DATABASE FLASHBACK ON;
SQL> ALTER DATABASE OPEN;

If you want to move the other permanent files (controlfile and redo log) to the new FRA just follow these steps:

CONTROLFILE
Moving controlfiles from the old to the new FRA requires you to modify (init.ora) the location in the parameter CONTROL_FILES to the new location and restart the instance in NOMOUNT state.

-- Create pfile from the running spfile...
SQL> CREATE pfile FROM spfile;
-- Now edit the pfile created at $ORACLE_HOME/dbs and change the CONTROL_FILES parameter to setup the new location
-- After doing that you can continue your SQLPlus session and startup the database
SQL> CREATE SPFILE FROM PFILE=$ORACLE_HOME/dbs/init.ora
SQL> STARTUP NOMOUNT
SQL> exit

Now with RMAN “copy” the controfile from the old location
RMAN> RESTORE CONTROLFILE FROM 'oldcontrolfilename';

ONLINE REDO LOGS
Add new redo log files to the new FRA to each group member and drop the old ones.
SQL> ALTER DATABASE ADD LOGFILE SIZE 50m;
SQL> ALTER DATABASE DROP LOGFILE 'oldredologfile';

Your FRA old files will be delete automatically as they become unusable for any backup and restore process.

c.Place disk backups in an alternate location
Specify a format on RMAN when backing up eg:
rman> backup database format 'd:\oracle\product \10.2.0\oradata\V102\backups\%U';

Another option is to define the location of your backups on the RMAN settings to it becaomes a permanent location. Example:
configure channel device type disk format '/orabackup/rman/ORA920/backup_%d_set%s_piece%p_%T_%U';
configure controlfile autobackup format for device type disk to '/orabackup/rman/ORA920/rman_backup_controlfile_%d_%F_ctl';

d.   Avoid use of the Flash Recovery Area altogether (not recommended)
Create a parameter file (as above)
Add the following line to the init.ora:
        log_archive_dest_n (as above) or
    log_archive_dest='<arch directory>'
Remove the following parameter in the init.ora:
        log_archive_dest_10
        db_recovery_file_dest
        db_recovery_file_dest_size
Restart the instance using the amended pfile and recreate the spfile.

Managing Errors
ORA-19809: limit exceeded for recovery files
Cause: The limit for recovery files specified by the DB_RECOVERY_FILE_DEST_SIZE was exceeded.
Action: The error is accompanied by 19804. See message 19804 for further details

ORA-19804: cannot reclaim nnnnn bytes disk space from mmmmm limit
Cause: Oracle cannot reclaim disk space of specified bytes from the DB_RECOVERY_FILE_DEST_SIZE limit.
Action: There are five possible solutions:
1) Take frequent backup of recovery area using RMAN.
2) Consider changing RMAN retention policy.
3) Consider changing RMAN archivelog deletion policy.
4) Add disk space and increase DB_RECOVERY_FILE_DEST_SIZE.
5) Delete files from recovery area using RMAN.

ORA-38786: Flash recovery area is not enabled.
Cause: An attempt was made to perform a command that requires Flash recovery area to be enabled.
Action: Set DB_RECOVERY_FILE_DEST to an appropriate location and retry