SQL> select flashback_on from v$database;
FLASHBACK_ON
------------------
YES
To make flashback off
sql>ALTER DATABASE FLASHBACK OFF;
To make flashback on
sql>shut immediate
sql>startup mount exclusive
sql> alter database flashback on;
Flashback description:
Space issue in Flash Recovery Area( FRA )
"Flash Recovery Area" is used to set up a disk area where the database can create and manage a variety of files related to backup and recovery. Use of the flash recovery area is strongly recommended. Consider configuring a flash recovery area as one of the first steps in implementing a backup strategy. The flash recovery area is an Oracle-managed directory, file system, or Automatic Storage Management disk group.
* Oracle creates archived logs and flashback logs in the flash recovery area.
* RMAN can store its backup sets and image copies in the flash recovery area
Planning Space Usage and Location for the Flash Recovery Area
The amount of disk space to allocate for the flash recovery area depends on the size and activity levels on the database, which determine the size of datafiles and redo logs files in addition to the recovery objectives. The flash recovery area should be large enough for copies of the datafiles, control files, online redo logs, and archived logs needed to recover the database, and also the copies of these backup files that are kept based on the retention policy.
Flash recovery area should be on a separate disk .
How Oracle Manages Disk Space in the Flash Recovery Area ?
Space in the flash recovery area is balanced among backups and archived logs that must be kept according to the retention policy, and other files which may be subject to deletion. Oracle Database does not delete eligible files from the flash recovery area until the space must be reclaimed for some other purpose. Thus, files recently moved to tape are often still available on disk for use in recovery. The recovery area can thus serve as a cache for tape. When the flash recovery area is full, Oracle Database automatically deletes eligible files to reclaim space in the recovery area as needed.
If the RMAN retention policy requires keeping a set of backups larger than the flash recovery area disk quota, or if the retention policy is set to NONE, then the flash recovery area can fill completely with no reclaimable space.
How Oracle Notify space crunch in FRA ?
The database issues a warning alert when reclaimable space is less than 15% and a critical alert when reclaimable space is less than 3%. To warn the DBA of this condition, an entry is added to the alert log and to the DBA_OUTSTANDING_ALERTS table (used by Enterprise Manager). Nevertheless, the database continues to consume space in the flash recovery area until there is no reclaimable space left.
When the recovery area is completely full, the error you will receive is as follows, where nnnnn is the number of bytes required and mmmm is the disk quota:
ORA-19809: limit exceeded for recovery files
ORA-19804: cannot reclaim nnnnn bytes disk space from mmmmm limit
Deletion Rules for the Flash Recovery Area
The following rules govern when files become eligible for deletion from the recovery area:
- Permanent files are never eligible for deletion.
- Files that are obsolete under the retention policy are eligible for deletion.
- Transient files that have been copied to tape are eligible for deletion.
- Archived redo logs are not eligible for deletion until all the consumers of the logs have satisfied their requirements ( If Archived Redo Log Deletion Policy is set ) .
- Foreign archived logs that have been mined by a LogMiner session on a logical standby database are eligible for deletion. Unlike an ordinary archived redo log, a foreign archived redo log has a different DBID.
The safe and reliable way to control deletion of files from the flash recovery area is to configure your retention policy ("Configuring the Backup Retention Policy") and archived log deletion policy ("Configuring an Archived Redo Log Deletion Policy"). To increase the likelihood that files moved to tape are retained on disk, increase the flash recovery area quota.
Requirement :
Configure RMAN retention policy
Configure archived log deletion policy
If retention policy is set to none then no files will be eligible for deletion. If files are never considered obsolete, then a file can only be deleted from the flash recovery area if it has been backed up to some other disk location or to a tertiary storage device such as tape.
How to Monitor Flash Recovery Area Space Usage ?
V$RECOVERY_FILE_DEST : To find out the current location, disk quota, space in use, space reclaimable by deleting files, and total number of files in the flash recovery area.
V$FLASH_RECOVERY_AREA_USAGE : To find out the percentage of the total disk quota used by different types of files. Also, you can determine how much space for each type of file can be reclaimed by deleting files that are obsolete, redundant, or already backed up to tape.
For example
SQL>Select file_type, percent_space_used as used,percent_space_reclaimable as reclaimable,
number_of_files as "number" from v$flash_recovery_area_usage;
FILE_TYPE USED RECLAIMABLE number
------------ ---------- ----------- ----------
CONTROLFILE 0 0 0
ONLINELOG 0 0 0
ARCHIVELOG 4.77 0 2
BACKUPPIECE 56.80 0 10
IMAGECOPY 0 0 0
FLASHBACKLOG 11.68 11.49 63
SQL> select name, space_limit as Total_size ,space_used as Used,
SPACE_RECLAIMABLE as reclaimable ,NUMBER_OF_FILES as "number"
from V$RECOVERY_FILE_DEST;
NAME TOTAL_SIZE USED RECLAIMABLE Number
------------------------ ---------- ---------- ----------- --------
E:\oracle\flash_recovery_area 2147483648 353280000 246841344 75
How to resolve a full flash recovery area when no files are eligible for deletion ?
Files in Flash Recovery Area are auto managed . Whenever there is a space pressure oracle automatically delete files which are out of retention policy. But if no files are eligible for deletion then manual intervention required .
You have a number of choices on how to resolve a full flash recovery area when no files are eligible for deletion:
1. Make more disk space available and increase DB_RECOVERY_FILE_DEST_SIZE
SQL> alter system set db_recovery_file_dest_size=xG SCOPE=BOTH; -- (larger amount)
2. Move backups from the flash recovery area to tertiary storage such as tape.
RMAN>BACKUP RECOVERY AREA;
Note : Flashback logs cannot be backed up outside the recovery area and so are not backed up by BACKUP RECOVERY AREA.
3. Run DELETE for any files that have been removed with an operating system utility. If you use host operating system commands to delete files, then the database will not be aware of the resulting free space.
RMAN>CROSSCHECK BACKUP;
RMAN>CROSSCHECK ARCHIVELOG ALL;
RMAN>Delete expired backup;
RMAN>Delete expired archivelog all;
RMAN>Delete force obsolete;
4. Make sure that your guaranteed restore points are necessary. If not, delete them
SQL>Drop restore point
;
5. If flashback logs are enable then make sure you have enough space for all the flashback logs. If its not required then you can turn off flashback.
SQL>Alter database FLASHBACK OFF;
6. Review your backup retention policy and if required change the RMAN RETENTION POLICY
RMAN>CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;
Exceptions :
- If RMAN is not part of backup strategy and archivelogs are going to FRA then manual intervention required for deletion of archivelogs. Periodically purse old archivelogs
for example
RMAN>Delete archivelog all completed before 'SYSDATE-7';
- By default RMAN backup goes to FRA. While taking RMAN backup if backup location explictly specified to flash recovery area location then those backup pieces are not considered as part of FRA for auto managment.
- For Archivelogs backup to FRA use USE_DB_RECOVERY_FILE_DEST rather than giving explict path of FRA
SQL> alter system set log_archive_dest_10='LOCATION=USE_DB_RECOVERY_FILE_DEST' scope=both;
