Join this Blog for direct reference of any post into your inbox. To join just click on "Join this Site" under "Follower" then Login with your Email.*** DECLARATION: I Maintain this Blog for Helping Myself While at Work and Welcome any body Needing Help!!!.*** CAUTION: Using any of the script from this Blog may contain at Own Risk. These scripts May or May not have been Tested.***

Sunday, 6 May 2012

Monitor the Flashback area space usage

col ROUND(SPACE_LIMIT/1048576) heading “Space Allocated (MB)” format 999999
col round(space_used/1048576) heading “Space Used (MB)” format 99999
col name format a40
select name, round(space_limit/1048576),round(space_used/1048576)

The flash recovery area, which allows you to centralize storage of all recovery-related files, is new in Oracle 10g. The flash recovery area is an area of disk that is defined for use for recovery-related files. It provides much faster backup and restore operation.
Databases should be in Archive log mode for flashback recovery. If a flash recovery area is configured, archival is enabled automatically and LOG_ARCHIVE_DEST_10 is configured to flash recovery area. If no default location for LOG_ARCHIVE_DEST is provided, then flash recovery area is used to store the archive logs. A single flash recovery area can be shared between more than one databases.

Control file: One copy of the control file is created in the flash recovery area when the database is created.
Archived redo logs: When you configure the flash recovery area, the parameter log_archive_dest_10 is automatically configured, and archived redo logs are archived to that destination, as well as any other archive log destinations.
Flashback logs: Flashback logs are stored in the flash recovery area, if it is defined.
Control file autobackups: The default location for the RMAN control file autobackups is the flash recovery area, if it is defined.
RMAN datafile copies: The default location for the RMAN datafile copies is the flash recovery area, if it is defined.
RMAN backup and other related files: The default location for the RMAN files in general (backup-set pieces, etc.) is the flash recovery area, if it is defined.

Select * from dba_outstanding_alerts;
it allows to check any outstanding issue with the flashback area

ALTER SYSTEM SET DB_RECOVERY_FILE_DEST= '/u01/oradata/o10g/flash';
Flash recovery parameters are dynamic. These parameters can be changed when the database is up and running.
Alter system set db_recovery_file_dest_size=20G scope=both;
Flash recovery file size also can be dynamically changed. Setting the value of db_file_dest_size blank or null disables the flash recovery options.
Alter system set db_recovery_file_dest= '/u01/oracle/flash_recovery' scope=both;

In 10g RMAN has been enhanced with new commands that allow you to back up and restore the flash recovery area.The RMAN command backup recovery area allows you to back up all files required to restore the database via RMAN from a recovery area to an sbt (tape) device.
RMAN> Restore controlfile from autobackup using recovery area 'D:\recovery';
To configure Flashback Area check the other post:


  1. Excellent Summary regarding Flash Recovery Area