What is Flash Recovery Area?
> To set up a location on disk where the database can create and manage a variety of backup and recovery-related files on your behalf.
> Simplifies the ongoing administration of your database by :
1) automatically naming recovery-related files
2) retaining them as long as they are needed for restore and recovery activities
3) deleting them when they are no longer needed to restore your database and space is needed for some other backup
> Use of the flash recovery area is strongly recommended
How to choose a location for Flash Recovery Area?
> The flash recovery area cannot be stored on a raw file system.
> You must choose a location (a directory or Automatic Storage Management disk group) to hold the files.
> You must also determine a disk quota for the flash recovery area, the maximum space to be used for all files stored there.
> 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.
For RAC database :
The location 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.
About Oracle Flashback Database feature
Flashback Database feature, which provides an convenient alternative to point-in-time recovery, generates flashback logs, which are also considered transient files and must be stored in the flash recovery area.
> To determine the disk quota and current disk usage in the flash recovery area, query the view V$RECOVERY_FILE_DEST.
Setting Initialization Parameters for Size and Location of the Flash Recovery Area
=====================================================================
To enable the flash recovery area, you must set the two initialization parameters
1) DB_RECOVERY_FILE_DEST_SIZE (which specifies the disk quota, or maximum space to use for flash recovery area files for this database)
2) DB_RECOVERY_FILE_DEST (which specifies the location of the flash recovery area).
> DB_RECOVERY_FILE_DEST_SIZE must be set before DB_ RECOVERY_FILE_DEST.
> In a RAC database, all instances must have the same values for these parameters.
> Set the value of both of these parameters in init file.
> It is recommended that DB_RECOVERY_FILE_DEST not be the same as DB_CREATE_FILE_DEST or any of
the DB_CREATE_ONLINE_LOG_DEST_n parameters.
>
How to change the value of DB_RECOVERY_FILE_DEST_SIZE
In our RAC database on ASM the value of DB_RECOVERY_FILE_DEST_SIZE was set to 2G and location is set to +RECOVERYDEST diskgroup
We planned to use ~10 GB approx of space for backup activities.
1) Added one more disk of 10 GB to +RECOVERYDEST diskgroup
2) changed the upper limit of space usage by updating parameter DB_RECOVERY_FILE_DEST_SIZE in all RAC instances by below command :
BEFORE :
SQL> show parameter db_reco
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string +RECOVERYDEST
db_recovery_file_dest_size big integer 2G
SQL> ALTER SYSTEM SET db_recovery_file_dest_size=10G SCOPE=BOTH SID='*';
System altered.
AFTER :
SQL> show parameter db_reco
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string +RECOVERYDEST
db_recovery_file_dest_size big integer 10G
What will happen if you try to cross upper limit db_recovery_file_dest_size
You will receive a warning like below in Alert log :
Mon Nov 7 18:18:35 2011
Errors in file /u01/app/oracle/admin/brij/bdump/brij1_mmon_17596.trc:
ORA-19815: WARNING: db_recovery_file_dest_size of 2147483648 bytes is 90.03% used, and has 214059520 remaining bytes available.
Mon Nov 7 18:18:35 2011
************************************************************************
You have following choices to free up space from flash recovery area:
1. Consider changing RMAN RETENTION POLICY. If you are using Data Guard,
then consider changing RMAN ARCHIVELOG DELETION POLICY.
2. Back up files to tertiary device such as tape using RMAN
BACKUP RECOVERY AREA command.
3. Add disk space and increase db_recovery_file_dest_size parameter to
reflect the new space.
4. Delete unnecessary files using RMAN DELETE command. If an operating
system command was used to delete files, then use RMAN CROSSCHECK and
DELETE EXPIRED commands.
************************************************************************
Monitor Flash Recovery Area space utilization
=======================================
Below two views can be queried to find the current status of Flash Recovery Area space utilization
SQL> SELECT * FROM V$RECOVERY_FILE_DEST;
NAME SPACE_LIMIT SPACE_USED SPACE_RECLAIMABLE NUMBER_OF_FILES
-------------------- ------------------ ---------- ----------------- -----------------------------------------------------------------------------------------------------
+RECOVERYDEST 10737418240 1541652480 0 7
SQL> SELECT * FROM V$FLASH_RECOVERY_AREA_USAGE;
FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
------------ ------------------ ------------------------- ------------------------------------------------------------------------------------------------------------------
CONTROLFILE .15 0 1
ONLINELOG 1.95 0 2
ARCHIVELOG 0 0 0
BACKUPPIECE 12.24 0 4
IMAGECOPY 0 0 0
FLASHBACKLOG 0 0 0
6 rows selected.
Disabling the Flash Recovery Area
=============================
> Set the DB_RECOVERY_FILE_DEST initialization parameter to a null string
For example, use this SQL*Plus statement to change the parameter on a running database:
ALTER SYSTEM SET DB_RECOVERY_FILE_DEST='' SCOPE=BOTH SID="*";
The database will no longer provide the space management features of the flash recovery area for the files stored in the old DB_RECOVERY_FILE_DEST location. The files will still be known to the RMAN repository, however, and available for backup and restore activities.