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 assings 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 Corporation recommends 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. A warning will appear in the alert log if DB_RECOVERY_FILE_DEST is the same as any of the other parameters listed here.
- 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 10g has a new dynamic performance view for monitoring the recovery area:
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
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
--------------- -------------- ------------- ----------------- ---------------
+FLASH 20 .022460938 0 1
The size of the flash recovery area at any time is a
• 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
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
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
If the flash recovery area becomes full, an error is
issued. Beware of using the flash recovery area for
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;
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.
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;
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
another file system location in addition to the Flash Recovery
SQL> create pfile from spfile;
Add the following line to the init.ora:
log_archive_dest_n=’<archivelog directory>’ eg
Restart the instance using the amended pfile and recreate the spfile:
SQL>create spfile from pfile;
to another file system location instead of the Flash Recovery
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
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
Now with RMAN “copy” the controfile from the old
RMAN> RESTORE CONTROLFILE FROM 'oldcontrolfilename';
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.
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.
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';
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
Remove the following parameter in the init.ora:
Restart the instance using the amended pfile and recreate the spfile.