If you need to move the Flash Recovery Area of your database to a new location,
invoke SQL*Plus to change the DB_RECOVERY_FILE_DEST initialization parameter.
For example:
SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST='+disk1' SCOPE=BOTH SID='*';
After you change this parameter, all new Flash Recovery Area files will be
created in the new location.
The permanent files (control files and online redolog files), flashback logs
and transient files can be left in the old Flash Recovery Area location. The
database will delete the transient files from the old Flash Recovery Area
location as they become eligible for deletion.
For the FLASHBACK logfiles to be able to to pick up the new 'db_recovery_file_dest' location,
the flashback option needs to be toggled off and on.
This can be done like this:
- Shutdown the Database
SQL> shutdown immediate
- Startup mount the Database:
SQL> startup mount;
- Drop the garanteed restore points
SQL> select * from v$restore_point;
drop restore point <name>;
- Toggle the Flashback off:
SQL> alter database flashback off;
- Toggle the Flashback on:
SQL> alter database flashback on;
- Open the Database:
SQL> alter database open;
If you need to actually move your current permanent files, transient files, to the new Flash Recovery Area, then follow the following steps:
1) To move the existing backupsets and archived redo log files,use the following command:
RMAN> BACKUP AS COPY ARCHIVELOG ALL DELETE INPUT;
RMAN> BACKUP DEVICE TYPE DISK BACKUPSET ALL DELETE INPUT;
2) To move the datafile copies. Run the below command for each datafile copy:
RMAN> BACKUP AS COPY DATAFILECOPY <name> DELETE INPUT;
where the <name> is the datafilecopy name in the old recovery area.
3) To move the controlfile from the old Flash Recovery Area to new one.
Change the location in the parameter CONTROL_FILES to the new location
and restart the instance in NOMOUNT.
RMAN> RESTORE CONTROLFILE FROM 'filename_of_old_control_file';
4) To move the online redo logs. Use the commands to add a a log file stored in
the new Flash Recovery Area and drop the logfile in the old Flash Recovery Area
for each redo log group.
SQL> alter database add logfile size 100M;
SQL> alter database drop logfile '<name of the old redo log>';
Oracle will clean up transient files remaining in the old Flash Recovery Area
location as they become eligible for deletion.
GOAL
- Benefits of using Flash Recovery Area.
- Explains how to Create FRA using DB_RECOVERY_FILE_DEST_SIZE and DB_RECOVERY_FILE_DEST
- Database Files that can reside in Flash Recovery Area.
- How to Disable FRA
SOLUTION
Definition: The flash recovery area can be defined as a single, unified storage area that keeps all the database recovery related files and performs recovery activities in an Oracle database.
The flash recovery area is the most powerful tool available in Oracle 10g that plays a vital role in performing database recovery operations. The flash recovery area serves as the default storage area for all files related to backup and restore operations.
The flash recovery area provides the following benefits:
+ Single storage location for all recovery-related files.
+ Automatic management of recovery-related disk space.
+ Faster backup and restore operations, since you don’t need to restore tape backups.
+ Increased reliability of backups, since disks are generally safer storage devices than tapes.
+ Flash database is only possible when the Flash Recovery Area is configured.
Following are the various entities that can be considered as the flash recovery area:
+ A single directory
+ An entire file system
+ Automatic storage management (ASM)
Note: The flash recovery area is shared among databases in order to optimize the usage of disk space for database recovery operations. In case of a media failure or a logical error, the flash recovery area is referred to retrieve all the files needed to recover a database.
What’s in the Flash Recovery Area?
The flash recovery area may contain the following files:
+ Datafile copies: The new RMAN command BACKUP AS COPY can be used to create image copies of all datafiles and automatically store in the flash recovery area.
+ Control file autobackups: The database places any control file backups it generates in the flash recovery area.
+ Archived redo log files: If you store Archived redo log files in the flash recovery area, Oracle will automatically delete the files.
+ Online redo log files: Oracle recommends that you save a multiplexed copy of your online redo log files in the flash recovery area. The following statements can create online redo logs in the flash recovery area: CREATE DATABASE, ALTER DATABASE ADD LOGFILE, ALTER DATABASE ADD STANDBY LOGFILE, and ALTER DATABASE OPEN RESETLOGS.
+ Current control files: Oracle also recommends that you store a multiplexed current control file in the flash recovery area.
+ RMAN files: All the RMAN backups can be stored in Flash Recovery Area.
+ Flashback logs: If you enable the flashback database feature, Oracle copies images of each altered block in every datafile into flashback logs stored in the flash recovery area.
Note: Oracle calls the multiplexed redo log files and control files in the flash recovery area permanent files, since they should never be deleted and are part of the live database. Oracle terms all the other files in the flash recovery area (recovery related files) transient files, since Oracle will delete them eventually after they have become obsolete or have already been copied to tape.
Creating a Flash Recovery Area :
You use the DB_RECOVERY_FILE_DEST and DB_RECOVERY_FILE_DEST_SIZE initialization parameters to configure a flash recovery area in your database. When you use the DB_RECOVERY_FILE_DEST parameter to specify the destination of your flash recovery area, you can use a directory, file system, or ASM disk group as your destination.
Dynamically Defining the Flash Recovery Area :
ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = 2G SCOPE=BOTH;
ALTER SYSTEM SET DB_RECOVERY_FILE_DEST = 'E:\RECOVERY_AREA' SCOPE=BOTH;
You must always specify the size parameter before specifying the location parameter.
Disabling the Current Flash Recovery Area :
ALTER SYSTEM SET DB_RECOVERY_FILE_DEST = '';
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.
PURPOSE
Purpose of the article is to discuss
1. How Oracle Manages Disk Space in the Flash Recovery Area
2. Space management Warning & Alerts
3. Impact of retention policy
4. How to resolve full Flash Recovery Area conditions.
5. BACKUP PERFORMANCE SLOWS DOWN WHEN FRA (Flash/Fast Recovery Area) IS NEARLY FULL (~80%)
SCOPE
Space management in the Flash Recovery Area for DBAs, support
DETAILS
1. How Oracle Manages Disk Space in the Flash Recovery Area ?
The flash recovery area is largely self-managing, some situations may require DBA intervention. Oracle Database and RMAN create files in the flash recovery area until the space used reaches the recovery area disk limit. When it needs to make room for new files, Oracle Database deletes files from the flash recovery area that are obsolete, redundant, or backed up to tertiary storage. Oracle Database prints a warning when available disk space is less than 15%, but it continues to fill the disk to 100% of the disk limit.
Files in the recovery area are permanent or transient. Permanent files are active files used by the database instance. Control file , Online Redo log files are part of permanent files. These files wont get deleted at all. All files that are not permanent are transient. Archived redo log, Foreign archived redo log, Image copies of datafiles and control files, Backup pieces, Flashback logs are part of transient files. In general, Oracle Database eventually deletes transient files after they become obsolete.
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.
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.
- 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 and archived log deletion policy. Archivelog deletion policy is only applicable for oracle 11g onward.
Query the V$RECOVERY_FILE_DEST view 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. For example :
SQL> SELECT * FROM V$RECOVERY_FILE_DEST;
NAME SPACE_LIMIT SPACE_USED SPACE_RECLAIMABLE NUMBER_OF_FILES
-------------------------------- ----------- ---------- ----------------- ---------------
/<Path>/flash_recovery_area 838860800 292490752 0 44
Query the V$FLASH_RECOVERY_AREA_USAGE view 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 :
SELECT * FROM V$FLASH_RECOVERY_AREA_USAGE;
FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
------------ ------------------ ------------------------- ---------------
CONTROLFILE 0 0 0
ONLINELOG 2 0 22
ARCHIVELOG 4.05 2.01 31
BACKUPPIECE 3.94 3.86 8
IMAGECOPY 15.64 10.43 66
FLASHBACKLOG .08 0 1
Note :
If the flash recovery area is not large enough to hold the flashback logs then the database may start overwriting flashback logs from the earliest SCNs. Consequently, the flashback database window can be shorter than the flashback retention target, depending on the size of the flash recovery area, other backups that must be retained, and how much flashback logging data is needed. The flashback retention target is a target, not a guarantee that Flashback Database will be available.
2. Flash Recovery area - Space management Warning & Alerts
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). However, the database continues to consume space in the Flash Recovery Area until there is no reclaimable space left. When the Flash Recovery Area is completely full, the following error will be reported. For example :
ORA-19809: limit exceeded for recovery files
ORA-19804: cannot reclaim <nnnnn> bytes disk space from <mmmmm> limit
where <nnnnn> is the number of bytes required and <mmmm> is the disk quota for the Flash Recovery Area.
The following Error would be reported in alert.log
ORA-19815: WARNING: db_recovery_file_dest_size of <size of FRA configured> bytes is 100.00% used, and has 0 remaining bytes available.
Issue the following query to see the message:
SQL> SELECT object_type, message_type, message_level, reason, suggested_action
FROM dba_outstanding_alerts ;
The following actions can be done to resolve the space issue :
- Add disk space to the Flash Recovery Area.
- Back up your files to a tertiary device.
- Delete the files from the Flash Recovery Area using RMAN.
- Changing RMAN retention policy.
The database handles a Flash Recovery Area with insufficient reclaimable space just as it handles a disk full condition. Often, the result is a hang of the database.
3. Retention Policy / Obsolete Files in Flash Recovery Area
Correct use of a Flash Recovery Area, requires a retention policy. No retention policy will cause files in the Flash Recovery Area never become obsolete, causing major issues on the database.
The RMAN status OBSOLETE is always determined in reference to a retention policy. For example, if a database backup is OBSOLETE in the RMAN repository, it is because it is either not needed for recovery to a point within the recovery window, or it is redundant.
If a Flash Recovery Area is configured, then the database uses an internal algorithm to delete files from the Flash Recovery Area that are no longer needed because they are redundant, orphaned, and so forth. The backups with
status OBSOLETE form a subset of the files deemed eligible for deletion by the disk quota rules.
When space is required in the Flash Recovery Area, then the following files are deleted:
a) Any backups which have become obsolete as per the retention policy.
b) Any files in the Flash Recovery Area which has been already backed up to a tertiary device such as tape.
c) Flashback logs may be deleted from the Flash Recovery Area to make space available for other required files.
A safe and reliable way to control deletion of files from the Flash Recovery Area is to change the retention policy.
4. Resolving a Full Flash Recovery Area
There are a number of choices on how to resolve a full Flash Recovery Area when there are NO files eligible for deletion:
- Make more disk space available, and increase DB_RECOVERY_FILE_DEST_SIZE to reflect the new space. For example :
SQL> 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. For Example:
RMAN> backup device type 'sbt_tape' recovery area;
Delete unnecessary files from the Flash Recovery Area using the RMAN delete command.
NOTE: If a host operating system command is used to delete files, then the database will not be aware of the resulting free space. Run the RMAN CROSSCHECK command to have RMAN re-check the contents of the Flash Recovery Area and identify the deleted files. Afterwards run DELETE EXPIRED to remove deleted files from the RMAN repository.
- You may also need to consider changing your backup retention policy . For Example :
RMAN> configure retention policy to recovery window of 5 days;
- When using Data Guard, consider changing your archivelog deletion policy.
- In case of 11g if Archivelog deletion policy is set to a higher value, conside changing it to a lower value.
RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 1 TIMES TO SBT;
5. BACKUP PERFORMANCE SLOWS DOWN WHEN FRA (Flash/Fast Recovery Area) IS NEARLY FULL (~80%)
Issue:
Unpublished BUG 11907498 - BACKUP SLOWS DOWN WHEN FRA IS NEARLY FULL (~80%) (Doc ID 11907498.8)
Fixed Ver: 11.2.0.3
Cause:
When the disk space usage reaches 80%, we try to reclaim space pro-actively.
But, when there is no disk space to reclaim, we should not refresh the aging rules which could cause a performance problem.
Comment/Info:
We print a message when a file is purged/deleted from FRA when reclaim space in alert log.
Look for
'Deleted Oracle Managed File ...'
Workaround:
SQL> alter system set events '19823 trace name context forever, level 100' ;
.
REFERENCES
NOTE:11907498.8 - Bug 11907498 - RMAN backups slow down when the Flash Recovery Area (FRA) is used 85%.
NOTE:829755.1 - Space issue in Fast / Flash Recovery Area - FRA Full
NOTE:305817.1 - FAQ - Flash Recovery Area feature
GOAL
This note is intended to help you determine which files are in the FRA file system directory or FRA diskgroup and what files are or are not controlled by the self cleaning mechanism of the FRA.
SOLUTION
NOTE: In the images and/or the document content below, the user information and environment data used represents fictitious data from the Oracle sample schema(s), Public Documentation delivered with an Oracle database product or other training material. Any similarity to actual environments, actual persons, living or dead, is purely coincidental and not intended in any manner.
You are submitting the following queries for a summarized view of FRA contents:
select * from v$flash_recovery_area_usage;
select * from v$recovery_file_dest;
If you compare used space from the view above to what is shown in output from v$asm_diskgroup or an operating system command like "du -s -b /oracle/fra", you may see the results from v$asm_diskgroup or "du -s -b /oracle/fra" is showing more space is used as compared to what the two views are showing.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
These queries will tell you what files are in the FRA and under the control of the self cleaning mechanism for the FRA:
* For the last query below, change +FLASH to the name of your FRA as seen by the db_recovery_file_dest parameter setting.
set linesize 120
set pagesize 45
select name from V$CONTROLFILE where is_recovery_dest_file = 'YES' AND name is not NULL;
select member from V$LOGFILE where is_recovery_dest_file = 'YES' AND member is not NULL;
select name from V$ARCHIVED_LOG where is_recovery_dest_file = 'YES' AND name is not NULL;
select name from V$BACKUP_COPY_DETAILS where is_recovery_dest_file = 'YES' AND name is not NULL;
select handle from V$BACKUP_PIECE where is_recovery_dest_file = 'YES' AND handle is not NULL;
select name from V$DATAFILE_COPY where is_recovery_dest_file = 'YES' AND name is not NULL;
select name from V$FOREIGN_ARCHIVED_LOG where is_recovery_dest_file = 'YES' AND name is not NULL;
select name from V$FLASHBACK_DATABASE_LOGFILE where name like '%+FLASH%' ;
** If the last query above, the one for V$FLASHBACK_DATABASE_LOGFILE, shows more rows than what is shown by V$FLASH_RECOVERY_AREA_USAGE, there may be flashback logs in the FRA not under the control of the self cleaning mechanism for the FRA.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
These queries will show some, possibly all, files which are in the FRA but are NOT under the control of the self cleaning mechanism for the FRA. Note, these files are NOT tracked by v$recovery_file_dest or v$flash_recovery_area_usage.
* For each query below, change +FLASH to the name of your FRA as seen by the db_recovery_file_dest parameter setting.
set linesize 120
set pagesize 45
select name from V$CONTROLFILE where is_recovery_dest_file = 'NO' and name like '%+FLASH%';
select member from V$LOGFILE where is_recovery_dest_file = 'NO' and member like '%+FLASH%';
select name from V$ARCHIVED_LOG where is_recovery_dest_file = 'NO' and name like '%+FLASH%';
select name from V$BACKUP_COPY_DETAILS where is_recovery_dest_file = 'NO' and name like '%+FLASH%';
select handle from V$BACKUP_PIECE where is_recovery_dest_file = 'NO' and handle like '%+FLASH%';
select name from V$DATAFILE_COPY where is_recovery_dest_file = 'NO' and name like '%+FLASH%';
select name from V$FOREIGN_ARCHIVED_LOG where is_recovery_dest_file = 'NO' and name like '%+FLASH%';
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Using asmcmd, cd into the +FLASH diskgroup then use "find / *" or for an operating system directory use "ls -r" to list all the files in the FRA. What you see in the results should match the queries above. You may find there are files in the FRA directory or diskgroup which are in neither set of queries above. Those files, along with files in the second set of queries, will take extra space in the FRA directory or diskgroup possibly leading to the false belief the v$recovery_file_dest and v$flash_recovery_area_usage views are incorrect.
By comparing the first set of queries, the second set of queries, and what is found by either the asmcmd "find" command or the operating system "ls -r" command, you will be in a position to know which files are showing in the v$recovery_file_dest and v$flash_recovery_area_usage views and if there are other files in the FRA location taking up space not shown by those two views. You will also be equipped to know what files are or are not controlled by the self cleaning mechanism of the FRA.
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
