The Flash
Recovery Area (FRA) 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 files 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.
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.
By defining one init.ora parameter, all RMAN backups, archive logs, control file autobackups, and datafile copies are automatically written to a specified files 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.
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.
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
- Oracle automatically assings the LOG_ARCHIVE_DEST_10 to the location of the FRA.
- Oracle allows you to archive to both the FRA and one or more additional locations 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 ARCH to archive 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 10g has a new dynamic performance view for monitoring the recovery area:
- Oracle automatically assings the LOG_ARCHIVE_DEST_10 to the location of the FRA.
- Oracle allows you to archive to both the FRA and one or more additional locations 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 ARCH to archive 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 10g has a new 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 Maximum amount of space that can be used for FRA in bytes (value specified in the DB_RECOVERY_FILE_DEST_SIZE initialization parameter).
SPACE_USED NUMBER Amount of 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
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 Maximum amount of space that can be used for FRA in bytes (value specified in the DB_RECOVERY_FILE_DEST_SIZE initialization parameter).
SPACE_USED NUMBER Amount of 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
Example:
SQL> SELECT substr(Name,1,15) 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;
SQL> SELECT substr(Name,1,15) 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
--------------- -------------- ------------- ----------------- ---------------
+FLASH 20 .022460938 0 1
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.
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;
• 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.
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.
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;
- 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 desribed 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.
The removal is desribed 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
Starting with Oracle 10G Release 2, a new view has been made available :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
Starting with Oracle 10G Release 2, a new view has been made available :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
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;
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
Create a parameter file (as above)
Add the following line to the init.ora:
log_archive_dest_n (as above)
Remove the following parameter in the init.ora:
log_archive_dest_10
Restart the instance using the amended pfile and recreate the spfile
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';
Create a parameter file (as above)
Add the following line to the init.ora:
log_archive_dest_n (as above)
Remove the following parameter in the init.ora:
log_archive_dest_10
Restart the instance using the amended pfile and recreate the spfile
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';
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.
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.
