Sign in or 
| Version | User | Scope of changes |
|---|---|---|
| Sep 12 2008, 5:56 PM EDT (current) | juancarlosreyesp | 6928 words added |
| Sep 12 2008, 5:55 PM EDT | juancarlosreyesp |
Changes
Key: Additions Deletions
1 Flash Recovery Area
Is an optional disk location that you can use to store recovery-related files:
· Control file
· Online redo log copies
· Archived logs
· Flashback logs
· RMAN backups
Is required for Oracle Flashback Database and Guaranteed Restore Points.
Oracle and RMAN based on a set of policies, manages the files in the flash recovery area automatically, backed up files are retained until disk space is needed for other files, then deletes files automatically reducing the risk that a DBA accidentally deletes redundancy set files, or not backed up files.
Note.- Oracle-generated logs used to perform flashback database operations can only be written to the flash recovery area. They cannot be backed up to disk.
Note: When storing backup files, using OMF on top of Automatic Storage Management without using a flash recovery area is supported but discouraged. It is awkward to directly manipulate files under Automatic Storage Management.
In a RAC database, all instances must have the same values forthese parameters.
This initialization parameter specifies the maximum storage in bytes of data to be used by the flash recovery area for this database.
DB_RECOVERY_FILE_DEST_SIZE must be set before DB_RECOVERY_FILE_DEST.
overhead:
· Block 0 or the OS block header of each Oracle file is not included in this size. Allow an extra 10% for this data when computing actual disk usage required for the flash recovery area.
· Does not indicate the real size occupied on disk when the underlying file system is mirrored, compressed, or in some other way affected by overhead not known to Oracle. For example, if you can configure the flash recovery area on a normal redundancy (2-way mirrored) ASM disk group, each file of X bytes occupies 2X bytes on the ASM disk group. In such a case, DB_RECOVERY_FILE_DEST_SIZE must be set to no more than 1/2 the size of the disks for the ASM disk group. Likewise, when using a high redundancy (three-way mirrored) ASM disk group, DB_RECOVERY_FILE_DEST_SIZE must be no more than 1/3 the size of the disks in the disk group, and so on.
For example
ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = 10G SCOPE=BOTH SID='*';
The V$RECOVERY_FILE_DEST and V$FLASH_RECOVERY_AREA_USAGE views can help you determine whether you have allocated enough space for your flash recovery area.
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.
SQL> SELECT * FROM V$RECOVERY_FILE_DEST;
NAME SPACE_LIMIT SPACE_USED SPACE_RECLAIMABLE NUMBER_OF_FILES
-------------- ----------- ---------- ----------------- ---------------
mydisk/rcva 5368709120 109240320 256000 28
Query the V$FLASH_RECOVERY_AREA_USAGE view to find out 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.
SQL> 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
For example:
ALTER SYSTEM SET DB_RECOVERY_FILE_DEST = '/disk1/flash_recovery_area' SCOPE=BOTH SID='*';
If the flash recovery area location is an Automatic Storage Management disk group named disk1, for example, then you can do the following:
SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST = '+disk1' SCOPE=BOTH SID='*';
These cannot be deleted without causing the instance to fail.
Oracle will generally eventually delete these files, at some point after they become obsolete under the retention policy or have been backed up to tape.
Note: The Oracle Flashback Database feature, which provides an convenient alternative to point-in-time recovery, generates flashback logs, which are also considered transient files and must be stored in the flash recovery area. However, unlike other transient files, flashback logs cannot be backed up to other media. They are automatically deleted as space is needed for other files in the flash recovery area. Location
The flash recovery area should be on a separate disk from the database area, where
active database files such as datafiles, control files, and online redo logs are stored.
Keeping the flash recovery area on the same disk as the database area exposes you to
loss of both your live database files and backups in the event of a media failure.
The flash recovery area cannot be stored on a raw file system.
In a RAC environment, the location 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.
· No two databases for which DB_UNIQUE_NAME is specified have the same value for DB_UNIQUE_NAME.
· For those databases where no DB_UNIQUE_NAME is provided, no two databases have the same value for DB_NAME.
When databases share a single flash recovery area in this fashion, the file system or
ASM disk group holding the flash recovery area should be large enough to hold all of the recovery files for all of the databases. Add all the values for DB_RECOVERY_FILE_DEST_SIZE for the different databases, and then allow for any overhead such as mirroring or compression in allocating actual disk space.
You must calculate to have at least one redundancy set on the flash recovery area.
Space used for flashback logs is managed automatically by the database, and balanced against space required for other files in the flash recovery area.
If insufficient space is allocated for the flash recovery area, then flashback logs may be deleted to make room for backups and archived log files.
The minimum recommended flash recovery area size on disk is dependent on the how frequent the backups are taken and retention policy used.
If the disk quota is not sufficient to keep a copy of database, then the flash recovery area should be used only as an archived log destination.
No file in the flash recovery area is eligible for deletion if it is required to satisfy the guarantee. Thus, retention of flashback logs and other files required to satisfy the guarantee, as well as files required to satisfy your backup retention policy, can cause the flash recovery area to fill completely.
Caution: If no files are eligible for deletion from the flash recoveryarea because of the requirements imposed by your retention policy and the guaranteed restore point, then the database behaves as if it
has encountered a disk full condition. In many circumstances, this causes your database to halt.
Disk Quota =
Size of X copies of database +
Size of one copy of incremental backups +
Size of (Y+1) days of archived logs
If you configure your retention policy to a recovery window of X days and execute your backup script once in Y days then you can calculate your required disk quota by one of two formulas. If X>=Y then the formula for the recommended flash recovery area disk quota is:
Disk Quota =
Size of one copy of database +
Size of (floor(X/Y) + 1) copies of incremental backups +
Size of (X * ceil(X/Y) +1) days of archived logs
where ceil(X/Y) is the smallest integer greater than or equal to X/Y and
floor(X/Y) is the largest integer less than or equal to X/Y.
If X <Y then the recommended formula is:
Disk Quota =
Size of one copy of database +
Size of 2 copies of incremental backups +
Size of (Y +1) days of archived logs
from the flash recovery area:
· Permanent files are never eligible for deletion.
· Files that are obsolete under the configured retention policy are eligible for deletion.
· Transient files that have been copied to tape are eligible for deletion.
· In a Data Guard environment, archived redo log deletion policy governs when archived redo log files can be deleted from the flash recovery area.
Note: Exactly which of the eligible files will be deleted to satisfy a space request is unpredictable. The rules governing the selection of specific files for deletion are likely to change between releases and are dependent upon your configuration. The safe and reliable way to control deletion of files from the flash recovery area is to change your retention policy. If you wish to increase the likelihood that files moved to tape are also retained on disk to minimize expected restore and recovery times, increase the flash recovery area quota.
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 recovery area is completely full, the error you will receive is:
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 database handles a flash recovery area with insufficient reclaimable space just as it handles a disk full condition. Often, the result is an database hang, but not always. For example, if the flash recovery area is one of your mandatory redo log archiving destinations, and the database cannot archive a new log because the recovery area is full, then the archiver may, depending on your configuration, retry archiving periodically until space is freed in the recovery area.
· A copy of all datafiles
· Incremental backups, as used by your chosen backup strategy
· Online redo logs
· Archived redo logs not yet backed up to tape
· Control files
· Control file autobackups (which include copies of the control file and SPFILE)
If providing this much space is impractical, it is best to create an area large enough to keep a backup of the most important tablespaces and all the archived logs not yet copied to tape. At an absolute minimum, the flash recovery area must be large enough to contain the archived logs that have not been copied to tape. To determine the disk quota and current disk usage in the flash recovery area, query the view V$RECOVERY_FILE_DEST.
Formulas for estimating a useful flash recovery area size depend upon several factors in your backup and recovery strategy:
· Whether your database has a small or large number of data blocks that change frequently;
· Whether you store backups only on disk, or on disk and tape;
· Whether you use a redundancy-based retention policy, or a recovery window-based retention policy;
· Whether you plan to use Flashback Database or guaranteed restore points as alternatives to point-in-time recovery to recover from logical errors.
Specific formulas are provided for many different backup scenarios in "RMAN-Based Disk and Tape Backup Strategies: Scenarios".
When using Flashback Database, you must add extra space to the flash recovery area to hold the flashback logs, along with the other files stored in the flash recovery area. The setting of the DB_FLASHBACK_RETENTION_TARGET initialization parameter determines, indirectly, how much flashback log data the database retains. The size of flashback logs generated by the database for a given time period can vary considerably, however, depending on the specific database workload. If more blocks are affected by database updates during a given interval, then more disk space is used by the flashback log data generated for that interval.
To estimate disk space requirements for flashback database logs, the V$FLASHBACK_DATABASE_LOG view can help you estimate how much space to add to your flash recovery area for flashback logs. After you have enabled logging for Flashback Database and set a flashback retention target, allow the database to run under a normal workload for a while, to generate a representative sample of flashback logs. Then run the following query:
SELECT ESTIMATED_FLASHBACK_SIZE FROM V$FLASHBACK_DATABASE_LOG;
The result is an estimate of the disk space needed to meet the current flashback retention target, based on the database workload since Flashback Database was enabled. Add the amount of disk space specified in $FLASHBACK_DATABASE_ LOG.ESTIMATED_FLASHBACK_SIZE to your flash recovery area size, to hold the expected database flashback logs. Space usage in the flash recovery area is always balanced among backups and archived logs which must be kept according to the retention policy, and other files which may be subject to deletion, such as flashback logs and backups already backed up to tape but still cached on disk. If you have not allocated enough space in your flash recovery area to store your flashback logs and still meet your other backup retention requirements, flashback logs may be deleted from the recovery area to make room.
The following rules govern the flash recovery area's creation, retention, overwriting and deletion of flashback logs:
· A flashback log is created whenever necessary to satisfy the flashback retention target, as long as there is enough space in the flash recovery area.
· A flashback log can be reused, once it is old enough that it is no longer needed to satisfy the flashback retention target.
· If the database needs to create a new flashback log and the flash recovery area is full or there is no disk space, then the oldest flashback log is reused instead.
Note: Re-using the oldest flashback log shortens the flashback database window. If enough flashback logs are reused due to a lack of disk space, the flashback retention target may not be satisfied.
· If the flash recovery area is full, then an archived redo log may be automatically deleted by the flash recovery area to make space for other files. In such a case, any flashback logs that would require the use of that redo log file for the use of FLASHBACK DATABASE are also deleted.
kccwnc: trying to expand control file section nnnn for Oracle Managed Files where nnnn is the number of the control file record type.
If the control file is at the maximum size supported under the host operating system, then the database cannot expand the control file. In such a situation, this warning appears in the alert log:
WARNING: Oracle Managed File filename is unknown to control file. This is the result of limitation in control file size that could not keep all recovery area files.
This means that the control file cannot hold a record of all flash recovery area files needed to satisfy the configured retention policy. There are several ways to avoid or alleviate this problem:
· Use a control file of larger block size, preferably one with 32K block size. To achieve this, you must set the SYSTEM tablespace block size to be greater than or equal to the control file block size, and re-create the control file after changing DB_BLOCK_SIZE.
· Make the files in the flash recovery area eligible for deletion, by backing them up to tertiary storage such as tape. For example, you can use the RMAN command BACKUP RECOVERY AREA to specifically back up files in the flash recovery area to your media manager.
· Make more files in the flash recovery area eligible for deletion, by changing the retention policy to a shorter recovery window or lower degree of redundancy.
· Make more disk space available, and increase DB_RECOVERY_FILE_DEST_SIZE to reflect the new space.
· Move backups from the flash recovery area to a tertiary device such as tape. One convenient way to back up all of your flash recovery area files to tape at once is the BACKUP RECOVERY AREA command and after RMAN DELETE command.
· Change your guaranteed restore point policy
Flashback logs, by design, cannot be backed up outside the flash recovery area. Therefore, in a BACKUP RECOVERY AREA operation the flashback logs are not backed up to tape. Flashback logs are deleted automatically to satisfy the need for space for other files in the flash recovery area. However, a guaranteed restore point can force the retention of flashback logs required to perform Flashback Database to the restore point SCN.
· Delete unnecessary files from the flash recovery area using the RMAN DELETE command.
Note.- if you use host operating system commands to delete files, then the database will not be aware of the resulting free space. You can run the RMAN CROSSCHECK command to have RMAN re-check the contents of the flash recovery area and identify expired files, and then use the DELETE EXPIRED command to remove missing files from the RMAN repository.
· You may also need to consider changing your backup retention policy
· If using Data Guard, consider changing your archivelog deletion policy.
can follow this procedure:
1. change the DB_RECOVERY_FILE_DEST initialization parameter.
For example:
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.
2. The permanent files (control files and online redo log 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.
non-ASM disk storage and you want to start using ASM to store your flash recovery
area. You must set the initialization parameters related to the flash recovery area to
refer to the ASM disk location. Then you can migrate existing backups, online logs, to the new ASM flash recovery area 1.9.3.1 Setting Initialization Parameters for Flash Recovery Area in ASM The following process moves the flash recovery area from non-ASM disk storage to an ASM disk group named +FRA.
Note: Flashback logs cannot be migrated. All data in the flashbacklogs is lost.
Specify the location of the flash recovery area by setting DB_RECOVERY_FILE_DEST and DB_RECOVERY_FILE_DEST_SIZE.
For example
SQL> alter system set DB_RECOVERY_FILE_DEST_SIZE=100G SID=’*’;
SQL> alter system set DB_RECOVERY_FILE_DEST=’+FRA’ SID=’*’; 1.9.3.2 Migrating the Control File to an ASM Flash Recovery Area It is assumed that one control file is already stored in +DISK/ct1.f, the other in non-ASM storage. The goal is to move the non-ASM control file to the flash recovery area and store it as +FRA/ct2.f.
Change the CONTROL_FILES initialization parameter to refer to the new location.
alter system set control_files='+DISK/ct1.f','+FRA/ct2.f' scope=spfile sid='*';
In SQL*Plus, bring the database to NOMOUNT:
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP NOMOUNT;
Use RMAN to actually create the new control files in ASM.
RMAN> restore controlfile from '+DISK/ct1.f';
RMAN> alter database mount;
Note: If you have already migrated all of these files to ASM storage using the procedure in "Disk-Based Migration of a Database to ASM" do not need to perform this step.
Before changing the location of the flash recovery area, you should drop any guaranteed restore points. Flashback logs used to support guaranteed restore points are stored in the flash recovery area.
If you were using flashback logging before to support flashback database, you can
re-enable it now. For example:
SQL> ALTER DATABASE FLASHBACK ON;
The following procedure changes the database configuration so that the flash recovery area is used for all future backups.
1. The first step is to change the initialization parameters for the database
2. If this database is a primary database and your online logs, control file or archived redo logs are in the flash recovery area, then perform a consistent shutdown of your database. For example:
SQL> SHUTDOWN IMMEDIATE
If this database is a standby database and your standby online logs, control file, or archive logs are in recovery area, then stop managed recovery mode and shut down the database.
3. Modify the initialization parameter file of the target database as follows:
Set DB_RECOVERY_FILE_DEST to the desired ASM disk group.
Modify DB_RECOVERY_FILE_DEST_SIZE if you need to change the size of
the flash recovery area.
4. If you shut down the database in step 2, then bring the database to a NOMOUNT
state. For example:
RMAN> STARTUP NOMOUNT
If the old flash recovery area has copy of the current control file, then restore control file from the old DB_RECOVERY_FILE_DEST and mount the database again.
RMAN> RESTORE CONTROLFILE FROM 'filename_of_old_control_file';
RMAN> ALTER DATABASE MOUNT;
5. The next step is to migrate the control file from the old flash recovery area to the
new flash recovery area. In this example, one control file is stored as
+DISK/ct1.f, the other as +FRA/ct2.f.
In SQL*Plus, change the CONTROL_FILES initialization parameter using the following command:
SQL> alter system set control_files='+DISK/ct1.f','+FRA/ct2.f' scope=spfile sid='*';
6. If you were using flashback logging before to support flashback database, you can
re-enable it now. For example:
SQL> ALTER DATABASE FLASHBACK ON;
At this point, all future files that are directed to the flash recovery area are created in
the new ASM flash recovery area location. 1.9.3.3 Changing Flashback Log Location to ASM Flash Recovery Area Because the actual flashback logs cannot be migrated, the only step required to move the location of flashback logs to the new ASM flash recovery area is to disable and then enable flashback logging. After a clean shutdown, mount the database and run the following commands in SQL*Plus:
SQL> ALTER DATABASE FLASHBACK OFF;
SQL> ALTER DATABASE FLASHBACK ON;
Future flashback logs will be created in the new flash recovery area. The old flashback logs are automatically deleted from non-ASM storage.
Migrating Online Logs to ASM Flash Recovery Area
For a primary database, migrating the online logs is performed by adding new log group members in ASM, and then dropping the old members. The database must be open to perform this task.
The easiest way to perform this step is to use a PL/SQL script
declare
cursor rlc is
select group# grp, thread# thr, bytes/1024 bytes_k, 'NO' srl
from v$log
union
select group# grp, thread# thr, bytes/1024 bytes_k, 'YES' srl
from v$standby_log
order by 1;
stmt varchar2(2048);
swtstmt varchar2(1024) := 'alter system switch logfile';
ckpstmt varchar2(1024) := 'alter system checkpoint global';
begin
for rlcRec in rlc loop
if (rlcRec.srl = 'YES') then
stmt := 'alter database add standby logfile thread ' ||
rlcRec.thr || ' size ' ||rlcRec.bytes_k || 'K';
execute immediate stmt;
stmt := 'alter database drop standby logfile group ' || rlcRec.grp;
execute immediate stmt;
else
stmt := 'alter database add logfile thread ' ||rlcRec.thr || ' size ' ||
rlcRec.bytes_k || 'K';
execute immediate stmt;
begin
stmt := 'alter database drop logfile group ' || rlcRec.grp;
dbms_output.put_line(stmt);
execute immediate stmt;
exception
when others then
execute immediate swtstmt;
execute immediate ckpstmt;
execute immediate stmt;
end;
end if;
end loop;
end;
For a standby database, you can follow similar steps to the script to drop the old standby redo logs and add new ones in the +FRA disk group, but the online redo logs cannot be migrated until the database is opened as a primary.
Once you have run your script, the migration of online logs is complete. Migrating Existing Backups to ASM Flash Recovery Area
To back up the existing archived redo log files to the new flash recovery area, use this command:
RMAN> BACKUP AS COPY ARCHIVELOG ALL DELETE INPUT;
To move backup sets to the new flash recovery area, use this command:
RMAN> BACKUP DEVICE TYPE DISK BACKUPSET ALL DELETE INPUT;
To move all datafile copies to the new flash recovery area location, use this command:
RMAN> BACKUP AS COPY DATAFILECOPY ALL DELETE INPUT;
At this point, all backups have been moved from the old flash recovery area to the new one.
Note: After you configure the database to change the location of the flash recovery area, backups created in the old flash recovery area location remain in their old location, still count against the total disk quota of the flash recovery area, are deleted from the old flash recovery area as space is required for other files, and can still be managed by RMAN and used in RMAN recovery operations. There is no need to move existing backups to the new ASM flash recovery area, unless you need the disk space used by those files for other purposes.
If you do need to free the space taken up by leftover non-ASM flash recovery area files, your options include backing them up to tape (for example, by using BACKUP RECOVERY AREA DELETE INPUT) or moving the backups from the old flash recovery area location to the new one, as described in this section.
ORA-19816: WARNING: Files may exist in location that are not known to database. Where location is the location of the flash recovery area.
In such a situation, you should use the RMAN command CATALOG RECOVERY AREA to re-catalog any such files so that they appear in the RMAN repository. If the file header of the file in question is corrupted, then you will have to delete the file manually using an operating system-level utility.
· Archived logs and RMAN backups are stored in the flash recovery area.
· The control file and online redo log copies are stored in directories in the file system outside the flash recovery area.
· Datafiles are expected to be no larger than 3GB in size. No more than 4GB of archived redo log files should be retained.
The backup strategy in this example is based on incremental backups. The control file will be automatically backed up to the flash recovery area.
The flash recovery area is sized to 10GB, room enough for control file autobackups, a whole database level 0 incremental backup (which consists of image copies of the 3GB of datafiles), plus several incremental level 1 backups. To implement this strategy, the parameter file contains the following entries:
DB_NAME=sample
# set location for current datafiles:
DB_CREATE_FILE_DEST = '/u02/oradata/wrk_area'
# set location for control files and online redo logs:
DB_CREATE_ONLINE_LOG_DEST_1 = '/u03/oradata/wrk_area'
DB_CREATE_ONLINE_LOG_DEST_2 = '/u04/oradata/wrk_area'
# set flash recovery area location and size
DB_RECOVERY_FILE_DEST = '/u01/oradata/rcv_area'
DB_RECOVERY_FILE_DEST_SIZE = 10G
Because the parameter file does not set any of the LOG_ARCHIVE_DEST_n parameters, Oracle sends archived logs to the flash recovery area only.
Once the target database is started, the following RMAN commands configure the
retention policy, backup optimization, and the control file autobackup:
RMAN> CONFIGURE RETENTION POLICY TO REDUNDANCY 1;
RMAN> CONFIGURE BACKUP OPTIMIZATION ON;
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;
Any disk-based backups are now directed to your flash recovery area.
· The control files, datafiles, and online redo logs are stored as Oracle managed files in a single file system directory.
· One multiplexed copy of the control file is kept in the flash recovery area
· Multiplexed copies of the online redo logs are kept in the flash recovery area
· Redo log files are archived both to the flash recovery area and another file system,
· separate from the work area
· RMAN backups are directed to the flash recovery area by default
To create a database with a flash recovery area:
1. Create a PFILE for the database, including the initialization parameters required to use a flash recovery area and direct online and archived logs, a copy of the control file, . Assume that you set the following:
# set DB_NAME
DB_NAME=sample
# set destination for OMF datafiles, control file and online redo logs
DB_CREATE_FILE_DEST = /u01/oradata/wrk_area/
# set log archiving destinations to a file system location
# and the flash recovery area
LOG_ARCHIVE_DEST_1 = 'LOCATION=/arc_dest1'
LOG_ARCHIVE_DEST_2 = 'LOCATION=USE_DB_RECOVERY_FILE_DEST'
# multiplexed copies of control file and online logs in flash recovery area
# rman backups also go here
DB_RECOVERY_FILE_DEST = 'LOCATION=/u01/oradata/rcv_area'
DB_RECOVERY_FILE_DEST_SIZE = 10G
The DB_CREATE_FILE_DEST parameter sets the default directory for all datafiles, online logs, and control files. Another copy of the control file and online logs is created in the flash recovery area.
2. After you set the initialization parameters, create the database. For example, start
SQL*Plus and enter:
SQL> CREATE DATABASE sample;
The preceding statement has the following effects:
· Datafiles are created as Oracle managed files in DB_CREATE_FILE_DEST.
· Because no LOGFILE clause was included, online log groups are created by default. Each group has two members, one in DB_CREATE_FILE_DEST, the other in DB_RECOVERY_FILE_DEST.
· Because the CONTROL_FILES parameter was not set, Oracle creates a control file in DB_CREATE_FILE_DEST (primary) and DB_RECOVERY_FILE_DEST (multiplexed copy).
On a Linux system, the filenames might look like the following:
/u02/oradata/wrk_area/SAMPLE/controlfile/o1_mf_3ajeikm_.ctl #primary ctlfile
/u01/oradata/rcv_area/SAMPLE/controlfile/o1_mf_6adjkid_.ctl #ctl file copy
/u02/oradata/wrk_area/SAMPLE/logfile/o1_mf_0_orrm31z_.log #log grp1, mem 1
/u01/oradata/rcv_area/SAMPLE/logfile/o1_mf_1_ixfvm8w9).log #log grp 1 mem 2
/u02/oradata/wrk_area/SAMPLE/logfile/o1_mf_2_2xyz16am_.log # log grp2, mem1
/u01/oradata/rcv_area/SAMPLE/logfile/o1_mf_2_q89tmp28_.log #log grp 2, mem 2
· Oracle uses LOG_ARCHIVE_DEST_1 and LOG_ARCHIVE_DEST_2 as destinations for archiving the redo logs. Archived redo log files are created in the flash recovery area because LOG_ARCHIVE_DEST_2 is configured to be the flash recovery area.
Because you enabled a local redo log archiving destination, LOG_ARCHIVE_DEST_10 is not implicitly set to the flash recovery area.
The archived redo log files in the flash recovery area are given Oracle-managed filenames that are not based on the LOG_ARCHIVE_FORMAT parameter. For example, if you generate an archived log:
ALTER SYSTEM ARCHIVE LOG CURRENT;
An archived log file is created in the primary archiving location, as well as the following flash recovery area subdirectory: /u01/oradata/rcv_area/SAMPLE/archivelog/YYYY_MM_DD where YYYY_MM_DD is the creation date format.
3. You may want to create more online redo log groups for this database. To do so, use the ALTER DATABASE ADD LOGFILE statement in SQL*Plus. When no file name is specified, it creates another log file member in the destinations already specified for online logs, including the flash recovery area. For example, the following statement creates a new log group with two members: one in DB_CREATE_FILE_DEST and another in DB_RECOVERY_FILE_DEST: ALTER DATABASE ADD LOGFILE;
· Archive each redo log to the flash recovery area (and only to the flash recovery area)
· Send RMAN backups to the flash recovery area by default.
To create a database with a flash recovery area:
1. Set the relevant initialization parameters. Assume that you set the following:
DB_NAME=sample
DB_CREATE_FILE_DEST = '/u02/oradata/wrk_area'
DB_RECOVERY_FILE_DEST = '/u01/oradata/rcv_area'
DB_RECOVERY_FILE_DEST_SIZE = 10G
# if you set the following parameters, then the online redo logs *and*
# current control file are located here
DB_CREATE_ONLINE_LOG_DEST_1 = '/u03/oradata/wrk_area'
DB_CREATE_ONLINE_LOG_DEST_2 = '/u04/oradata/wrk_area'
The DB_CREATE_FILE_DEST parameter sets the default file system directory for the datafiles. The DB_CREATE_ONLINE_LOG_DEST_n parameter sets the default file system directories for the online redo logs and control files. DB_RECOVERY_FILE_DEST sets the file system directory for archived logs.
2. After you set the initialization parameters, create the database. For example, start SQL*Plus and enter:
CREATE DATABASE sample;
No multiplexed copies of the online redo logs or control files are created in the flash recovery area.
3. Because you enabled a flash recovery area, Oracle automatically sets LOG_ARCHIVE_DEST_10 to the flash recovery area. The filenames in the flash recovery area are given Oracle managed filenames that are not based on the LOG_ARCHIVE_FORMAT parameter. For example, generate an archived log:
ALTER SYSTEM ARCHIVE LOG CURRENT;
On Linux, the preceding statement creates an archived log in a flash recovery area subdirectory: /u01/oradata/rcv_area/SAMPLE/archivelog/YYYY_MM_DD, where YYYY_MM_DD is the creation date format.
4. If you need to add more online redo log groups, execute the ALTER DATABASE ADD LOGFILE statement. When no file name is specified, it creates another log file member in each DB_CREATE_ONLINE_LOG_DEST_n location (but not the flash recovery area). For example, enter the following:
ALTER DATABASE ADD LOGFILE;
On Linux, the preceding statement creates one member in /u03/oradata/wrk_area/SAMPLE/logfile and another member in /u04/oradata/wrk_
area/SAMPLE/logfile. On other platforms, the specific file and directory names are platform-dependent.
· 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.
· LOG_ARCHIVE_DEST_10 is implicitly set 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.
· 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.
For example: CATALOG RECOVERY AREA;
RMAN> CATALOG START WITH '+disk1'; # all files from flash recovery area # (stored on ASM disk)
RMAN> CATALOG START WITH '/arch_dest2'; # all files from second arch dest
Note that files in the flash recovery area cannot be marked as UNAVAILABLE.
Note that you cannot set KEEP attributes on files stored in the flash recovery area.
If you use Automatic Storage Management (ASM), an Oracle Managed Files framework, or the flash recovery area, then you may need a way to recatalog files that are known to the disk management system but are no longer listed in the RMAN repository. This situation can occur when the intended mechanisms for tracking filenames fails due to media failure, software bug, or user error.
BACKUP RECOVERY AREA does not include the flashback logs when backing up flash recovery area contents to tape.
# delete obsolete backups on tape
DELETE OBSOLETE DEVICE TPE sbt;
ALTER SYSTEM SET DB_RECOVERY_FILE_DEST='' SCOPE=BOTH SID="*";
The database will no longer provide the space management features of the flash recovery area for the files stored in the old DB_RECOVERY_FILE_DEST location. The files will still be known to the RMAN repository, however, and available for backup and restore activities.
Note: If you are using a flash recovery area, then you should not run your database with the retention policy disabled. 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. It is quite likely that all of the space in your recovery area will be used.
1.1 What it’s for?
Is recommended to store redundancy set, keeping backup files as much times as possible to speed up recover process.Is an optional disk location that you can use to store recovery-related files:
· Control file
· Online redo log copies
· Archived logs
· Flashback logs
· RMAN backups
Is required for Oracle Flashback Database and Guaranteed Restore Points.
1.2 How it works
You specify the location and maximum size of the flash recovery area.Oracle and RMAN based on a set of policies, manages the files in the flash recovery area automatically, backed up files are retained until disk space is needed for other files, then deletes files automatically reducing the risk that a DBA accidentally deletes redundancy set files, or not backed up files.
Note.- Oracle-generated logs used to perform flashback database operations can only be written to the flash recovery area. They cannot be backed up to disk.
1.3 Used in conjunction with ASM and OMF
1.3.1 Oracle Managed Files and Automatic Storage Management.
The flash recovery area is built on top of OMF, so the flash recovery area can be stored anywhere Oracle-managed files can. Oracle Managed Files can be used on top of a traditional file system supported by the host operating system (for example, VxFS or ODM).Note: When storing backup files, using OMF on top of Automatic Storage Management without using a flash recovery area is supported but discouraged. It is awkward to directly manipulate files under Automatic Storage Management.
1.3.2 Oracle's Automatic Storage Management (ASM).
Even if you choose not to set up the flash recovery area in ASM storage, you can still use Oracle Managed Files to manage your backup files in an ASM disk group. You will lose one of the major benefits of the flash recovery area, the automatic deletion of files no longer needed to meet your recoverability goals as space is needed for more recent backups. However, the other automatic features of OMF will still function.1.4 Database Configuration
To enable the flash recovery area, you must set the two initialization parameters DB_RECOVERY_FILE_DEST_SIZE and DB_RECOVERY_FILE_DESTIn a RAC database, all instances must have the same values forthese parameters.
This initialization parameter specifies the maximum storage in bytes of data to be used by the flash recovery area for this database.
DB_RECOVERY_FILE_DEST_SIZE must be set before DB_RECOVERY_FILE_DEST.
1.4.1 DB_RECOVERY_FILE_DEST_SIZE
Note: The value specified does not include certain kinds of diskoverhead:
· Block 0 or the OS block header of each Oracle file is not included in this size. Allow an extra 10% for this data when computing actual disk usage required for the flash recovery area.
· Does not indicate the real size occupied on disk when the underlying file system is mirrored, compressed, or in some other way affected by overhead not known to Oracle. For example, if you can configure the flash recovery area on a normal redundancy (2-way mirrored) ASM disk group, each file of X bytes occupies 2X bytes on the ASM disk group. In such a case, DB_RECOVERY_FILE_DEST_SIZE must be set to no more than 1/2 the size of the disks for the ASM disk group. Likewise, when using a high redundancy (three-way mirrored) ASM disk group, DB_RECOVERY_FILE_DEST_SIZE must be no more than 1/3 the size of the disks in the disk group, and so on.
For example
ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = 10G SCOPE=BOTH SID='*';
The V$RECOVERY_FILE_DEST and V$FLASH_RECOVERY_AREA_USAGE views can help you determine whether you have allocated enough space for your flash recovery area.
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.
SQL> SELECT * FROM V$RECOVERY_FILE_DEST;
NAME SPACE_LIMIT SPACE_USED SPACE_RECLAIMABLE NUMBER_OF_FILES
-------------- ----------- ---------- ----------------- ---------------
mydisk/rcva 5368709120 109240320 256000 28
Query the V$FLASH_RECOVERY_AREA_USAGE view to find out 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.
SQL> 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
1.4.2 DB_RECOVERY_FILE_DEST
This parameter specifies a valid disk location for file creation, which can be a directory on a file system, or Automatic Storage Management disk group.For example:
ALTER SYSTEM SET DB_RECOVERY_FILE_DEST = '/disk1/flash_recovery_area' SCOPE=BOTH SID='*';
If the flash recovery area location is an Automatic Storage Management disk group named disk1, for example, then you can do the following:
SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST = '+disk1' SCOPE=BOTH SID='*';
1.5 File Classification
The files in the flash recovery area can be classified as1.5.1 Permanent
The only permanent files (assuming these are configured to be stored in the flash recovery area) are multiplexed copies of the current control file and online redo logs.These cannot be deleted without causing the instance to fail.
1.5.2 Transient.
All other files are transient: archived redo logs, datafile copies, control file copies, control file autobackups, and backup pieces.Oracle will generally eventually delete these files, at some point after they become obsolete under the retention policy or have been backed up to tape.
Note: The Oracle Flashback Database feature, which provides an convenient alternative to point-in-time recovery, generates flashback logs, which are also considered transient files and must be stored in the flash recovery area. However, unlike other transient files, flashback logs cannot be backed up to other media. They are automatically deleted as space is needed for other files in the flash recovery area. Location
The flash recovery area should be on a separate disk from the database area, where
active database files such as datafiles, control files, and online redo logs are stored.
Keeping the flash recovery area on the same disk as the database area exposes you to
loss of both your live database files and backups in the event of a media failure.
The flash recovery area cannot be stored on a raw file system.
In a RAC environment, the location 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.
1.5.3 Sharing a Flash Recovery Area Among Multiple Databases
Multiple database can have the same value for DB_RECOVERY_FILE_DEST, if one of the following conditions is met:· No two databases for which DB_UNIQUE_NAME is specified have the same value for DB_UNIQUE_NAME.
· For those databases where no DB_UNIQUE_NAME is provided, no two databases have the same value for DB_NAME.
When databases share a single flash recovery area in this fashion, the file system or
ASM disk group holding the flash recovery area should be large enough to hold all of the recovery files for all of the databases. Add all the values for DB_RECOVERY_FILE_DEST_SIZE for the different databases, and then allow for any overhead such as mirroring or compression in allocating actual disk space.
1.6 Disk quota
Is the user-specified limit to the size of the flash recovery area.You must calculate to have at least one redundancy set on the flash recovery area.
Space used for flashback logs is managed automatically by the database, and balanced against space required for other files in the flash recovery area.
If insufficient space is allocated for the flash recovery area, then flashback logs may be deleted to make room for backups and archived log files.
The minimum recommended flash recovery area size on disk is dependent on the how frequent the backups are taken and retention policy used.
If the disk quota is not sufficient to keep a copy of database, then the flash recovery area should be used only as an archived log destination.
1.6.1 Guaranteed Restore Points
When you create a guaranteed restore point, with or without enabling full flashback database logging, you must monitor the space available in your flash recovery area.No file in the flash recovery area is eligible for deletion if it is required to satisfy the guarantee. Thus, retention of flashback logs and other files required to satisfy the guarantee, as well as files required to satisfy your backup retention policy, can cause the flash recovery area to fill completely.
Caution: If no files are eligible for deletion from the flash recoveryarea because of the requirements imposed by your retention policy and the guaranteed restore point, then the database behaves as if it
has encountered a disk full condition. In many circumstances, this causes your database to halt.
1.6.2 Formulas
If your configured retention policy is REDUNDANCY X and you plan to take a backup every Y days, then the formula for the recommended flash recovery area disk quota is:Disk Quota =
Size of X copies of database +
Size of one copy of incremental backups +
Size of (Y+1) days of archived logs
If you configure your retention policy to a recovery window of X days and execute your backup script once in Y days then you can calculate your required disk quota by one of two formulas. If X>=Y then the formula for the recommended flash recovery area disk quota is:
Disk Quota =
Size of one copy of database +
Size of (floor(X/Y) + 1) copies of incremental backups +
Size of (X * ceil(X/Y) +1) days of archived logs
where ceil(X/Y) is the smallest integer greater than or equal to X/Y and
floor(X/Y) is the largest integer less than or equal to X/Y.
If X <Y then the recommended formula is:
Disk Quota =
Size of one copy of database +
Size of 2 copies of incremental backups +
Size of (Y +1) days of archived logs
1.7 How Oracle Manages Disk Space in Recovery Area
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.1.7.1 When Files are Eligible for Deletion from the Flash Recovery Area
There are relatively simple rules governing when files become eligible for deletionfrom the flash recovery area:
· Permanent files are never eligible for deletion.
· Files that are obsolete under the configured retention policy are eligible for deletion.
· Transient files that have been copied to tape are eligible for deletion.
· In a Data Guard environment, archived redo log deletion policy governs when archived redo log files can be deleted from the flash recovery area.
Note: Exactly which of the eligible files will be deleted to satisfy a space request is unpredictable. The rules governing the selection of specific files for deletion are likely to change between releases and are dependent upon your configuration. The safe and reliable way to control deletion of files from the flash recovery area is to change your retention policy. If you wish to increase the likelihood that files moved to tape are also retained on disk to minimize expected restore and recovery times, increase the flash recovery area quota.
1.7.2 When Space is Not Available in the Flash Recovery Area
If, for instance, 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.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 recovery area is completely full, the error you will receive is:
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 database handles a flash recovery area with insufficient reclaimable space just as it handles a disk full condition. Often, the result is an database hang, but not always. For example, if the flash recovery area is one of your mandatory redo log archiving destinations, and the database cannot archive a new log because the recovery area is full, then the archiver may, depending on your configuration, retry archiving periodically until space is freed in the recovery area.
1.7.3 Planning Size
The larger the flash recovery area is, the more useful it becomes. Ideally, the flash recovery area should be large enough to contain all of the following files:· A copy of all datafiles
· Incremental backups, as used by your chosen backup strategy
· Online redo logs
· Archived redo logs not yet backed up to tape
· Control files
· Control file autobackups (which include copies of the control file and SPFILE)
If providing this much space is impractical, it is best to create an area large enough to keep a backup of the most important tablespaces and all the archived logs not yet copied to tape. At an absolute minimum, the flash recovery area must be large enough to contain the archived logs that have not been copied to tape. To determine the disk quota and current disk usage in the flash recovery area, query the view V$RECOVERY_FILE_DEST.
Formulas for estimating a useful flash recovery area size depend upon several factors in your backup and recovery strategy:
· Whether your database has a small or large number of data blocks that change frequently;
· Whether you store backups only on disk, or on disk and tape;
· Whether you use a redundancy-based retention policy, or a recovery window-based retention policy;
· Whether you plan to use Flashback Database or guaranteed restore points as alternatives to point-in-time recovery to recover from logical errors.
Specific formulas are provided for many different backup scenarios in "RMAN-Based Disk and Tape Backup Strategies: Scenarios".
1.7.4 Sizing the Flash Recovery Area to Include Flashback Logs
You cannot manage the flashback logs in the flash recovery area directly, other than by setting the flashback retention target or using guaranteed restore points. You can, however, manage flash recovery area space as a whole, in order to maximize space available for retention of flashback database logs. In this way you increase the likelihood of achieving the flashback retention target.When using Flashback Database, you must add extra space to the flash recovery area to hold the flashback logs, along with the other files stored in the flash recovery area. The setting of the DB_FLASHBACK_RETENTION_TARGET initialization parameter determines, indirectly, how much flashback log data the database retains. The size of flashback logs generated by the database for a given time period can vary considerably, however, depending on the specific database workload. If more blocks are affected by database updates during a given interval, then more disk space is used by the flashback log data generated for that interval.
To estimate disk space requirements for flashback database logs, the V$FLASHBACK_DATABASE_LOG view can help you estimate how much space to add to your flash recovery area for flashback logs. After you have enabled logging for Flashback Database and set a flashback retention target, allow the database to run under a normal workload for a while, to generate a representative sample of flashback logs. Then run the following query:
SELECT ESTIMATED_FLASHBACK_SIZE FROM V$FLASHBACK_DATABASE_LOG;
The result is an estimate of the disk space needed to meet the current flashback retention target, based on the database workload since Flashback Database was enabled. Add the amount of disk space specified in $FLASHBACK_DATABASE_ LOG.ESTIMATED_FLASHBACK_SIZE to your flash recovery area size, to hold the expected database flashback logs. Space usage in the flash recovery area is always balanced among backups and archived logs which must be kept according to the retention policy, and other files which may be subject to deletion, such as flashback logs and backups already backed up to tape but still cached on disk. If you have not allocated enough space in your flash recovery area to store your flashback logs and still meet your other backup retention requirements, flashback logs may be deleted from the recovery area to make room.
The following rules govern the flash recovery area's creation, retention, overwriting and deletion of flashback logs:
· A flashback log is created whenever necessary to satisfy the flashback retention target, as long as there is enough space in the flash recovery area.
· A flashback log can be reused, once it is old enough that it is no longer needed to satisfy the flashback retention target.
· If the database needs to create a new flashback log and the flash recovery area is full or there is no disk space, then the oldest flashback log is reused instead.
Note: Re-using the oldest flashback log shortens the flashback database window. If enough flashback logs are reused due to a lack of disk space, the flashback retention target may not be satisfied.
· If the flash recovery area is full, then an archived redo log may be automatically deleted by the flash recovery area to make space for other files. In such a case, any flashback logs that would require the use of that redo log file for the use of FLASHBACK DATABASE are also deleted.
1.8 Interaction of Flash Recovery Area and CONTROL_FILE_RECORD_KEEP_TIME
When a control file record containing information about a file created in the flash recovery area is about to be reused (because the record is older than CONTROL_FILE_RECORD_KEEP_TIME), if the file is eligible for deletion then the database attempts to delete the file from the flash recovery area. Otherwise, the database expands the size of the control file section containing the record for this file, logging the expansion in the alert log with a message like this example:kccwnc: trying to expand control file section nnnn for Oracle Managed Files where nnnn is the number of the control file record type.
If the control file is at the maximum size supported under the host operating system, then the database cannot expand the control file. In such a situation, this warning appears in the alert log:
WARNING: Oracle Managed File filename is unknown to control file. This is the result of limitation in control file size that could not keep all recovery area files.
This means that the control file cannot hold a record of all flash recovery area files needed to satisfy the configured retention policy. There are several ways to avoid or alleviate this problem:
· Use a control file of larger block size, preferably one with 32K block size. To achieve this, you must set the SYSTEM tablespace block size to be greater than or equal to the control file block size, and re-create the control file after changing DB_BLOCK_SIZE.
· Make the files in the flash recovery area eligible for deletion, by backing them up to tertiary storage such as tape. For example, you can use the RMAN command BACKUP RECOVERY AREA to specifically back up files in the flash recovery area to your media manager.
· Make more files in the flash recovery area eligible for deletion, by changing the retention policy to a shorter recovery window or lower degree of redundancy.
1.9 Maintenance Tasks
As a rule, the flash recovery area is self-maintaing, but there are exceptions:1.9.1 Full Flash Recovery Area and no files eligible for deletion
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.
· Move backups from the flash recovery area to a tertiary device such as tape. One convenient way to back up all of your flash recovery area files to tape at once is the BACKUP RECOVERY AREA command and after RMAN DELETE command.
· Change your guaranteed restore point policy
Flashback logs, by design, cannot be backed up outside the flash recovery area. Therefore, in a BACKUP RECOVERY AREA operation the flashback logs are not backed up to tape. Flashback logs are deleted automatically to satisfy the need for space for other files in the flash recovery area. However, a guaranteed restore point can force the retention of flashback logs required to perform Flashback Database to the restore point SCN.
· Delete unnecessary files from the flash recovery area using the RMAN DELETE command.
Note.- if you use host operating system commands to delete files, then the database will not be aware of the resulting free space. You can run the RMAN CROSSCHECK command to have RMAN re-check the contents of the flash recovery area and identify expired files, and then use the DELETE EXPIRED command to remove missing files from the RMAN repository.
· You may also need to consider changing your backup retention policy
· If using Data Guard, consider changing your archivelog deletion policy.
1.9.2 Changing location without moving old files
If you need to move the flash recovery area of your database to a new location, youcan follow this procedure:
1. change the DB_RECOVERY_FILE_DEST initialization parameter.
For example:
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.
2. The permanent files (control files and online redo log 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.
1.9.3 Migrating the Flash Recovery Area to ASM/Changing location moving old files
This section describes procedures to use if you already have backups on disk innon-ASM disk storage and you want to start using ASM to store your flash recovery
area. You must set the initialization parameters related to the flash recovery area to
refer to the ASM disk location. Then you can migrate existing backups, online logs, to the new ASM flash recovery area 1.9.3.1 Setting Initialization Parameters for Flash Recovery Area in ASM The following process moves the flash recovery area from non-ASM disk storage to an ASM disk group named +FRA.
Note: Flashback logs cannot be migrated. All data in the flashbacklogs is lost.
Specify the location of the flash recovery area by setting DB_RECOVERY_FILE_DEST and DB_RECOVERY_FILE_DEST_SIZE.
For example
SQL> alter system set DB_RECOVERY_FILE_DEST_SIZE=100G SID=’*’;
SQL> alter system set DB_RECOVERY_FILE_DEST=’+FRA’ SID=’*’; 1.9.3.2 Migrating the Control File to an ASM Flash Recovery Area It is assumed that one control file is already stored in +DISK/ct1.f, the other in non-ASM storage. The goal is to move the non-ASM control file to the flash recovery area and store it as +FRA/ct2.f.
Change the CONTROL_FILES initialization parameter to refer to the new location.
alter system set control_files='+DISK/ct1.f','+FRA/ct2.f' scope=spfile sid='*';
In SQL*Plus, bring the database to NOMOUNT:
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP NOMOUNT;
Use RMAN to actually create the new control files in ASM.
RMAN> restore controlfile from '+DISK/ct1.f';
RMAN> alter database mount;
Note: If you have already migrated all of these files to ASM storage using the procedure in "Disk-Based Migration of a Database to ASM" do not need to perform this step.
Before changing the location of the flash recovery area, you should drop any guaranteed restore points. Flashback logs used to support guaranteed restore points are stored in the flash recovery area.
If you were using flashback logging before to support flashback database, you can
re-enable it now. For example:
SQL> ALTER DATABASE FLASHBACK ON;
The following procedure changes the database configuration so that the flash recovery area is used for all future backups.
1. The first step is to change the initialization parameters for the database
2. If this database is a primary database and your online logs, control file or archived redo logs are in the flash recovery area, then perform a consistent shutdown of your database. For example:
SQL> SHUTDOWN IMMEDIATE
If this database is a standby database and your standby online logs, control file, or archive logs are in recovery area, then stop managed recovery mode and shut down the database.
3. Modify the initialization parameter file of the target database as follows:
Set DB_RECOVERY_FILE_DEST to the desired ASM disk group.
Modify DB_RECOVERY_FILE_DEST_SIZE if you need to change the size of
the flash recovery area.
4. If you shut down the database in step 2, then bring the database to a NOMOUNT
state. For example:
RMAN> STARTUP NOMOUNT
If the old flash recovery area has copy of the current control file, then restore control file from the old DB_RECOVERY_FILE_DEST and mount the database again.
RMAN> RESTORE CONTROLFILE FROM 'filename_of_old_control_file';
RMAN> ALTER DATABASE MOUNT;
5. The next step is to migrate the control file from the old flash recovery area to the
new flash recovery area. In this example, one control file is stored as
+DISK/ct1.f, the other as +FRA/ct2.f.
In SQL*Plus, change the CONTROL_FILES initialization parameter using the following command:
SQL> alter system set control_files='+DISK/ct1.f','+FRA/ct2.f' scope=spfile sid='*';
6. If you were using flashback logging before to support flashback database, you can
re-enable it now. For example:
SQL> ALTER DATABASE FLASHBACK ON;
At this point, all future files that are directed to the flash recovery area are created in
the new ASM flash recovery area location. 1.9.3.3 Changing Flashback Log Location to ASM Flash Recovery Area Because the actual flashback logs cannot be migrated, the only step required to move the location of flashback logs to the new ASM flash recovery area is to disable and then enable flashback logging. After a clean shutdown, mount the database and run the following commands in SQL*Plus:
SQL> ALTER DATABASE FLASHBACK OFF;
SQL> ALTER DATABASE FLASHBACK ON;
Future flashback logs will be created in the new flash recovery area. The old flashback logs are automatically deleted from non-ASM storage.
Migrating Online Logs to ASM Flash Recovery Area
For a primary database, migrating the online logs is performed by adding new log group members in ASM, and then dropping the old members. The database must be open to perform this task.
The easiest way to perform this step is to use a PL/SQL script
declare
cursor rlc is
select group# grp, thread# thr, bytes/1024 bytes_k, 'NO' srl
from v$log
union
select group# grp, thread# thr, bytes/1024 bytes_k, 'YES' srl
from v$standby_log
order by 1;
stmt varchar2(2048);
swtstmt varchar2(1024) := 'alter system switch logfile';
ckpstmt varchar2(1024) := 'alter system checkpoint global';
begin
for rlcRec in rlc loop
if (rlcRec.srl = 'YES') then
stmt := 'alter database add standby logfile thread ' ||
rlcRec.thr || ' size ' ||rlcRec.bytes_k || 'K';
execute immediate stmt;
stmt := 'alter database drop standby logfile group ' || rlcRec.grp;
execute immediate stmt;
else
stmt := 'alter database add logfile thread ' ||rlcRec.thr || ' size ' ||
rlcRec.bytes_k || 'K';
execute immediate stmt;
begin
stmt := 'alter database drop logfile group ' || rlcRec.grp;
dbms_output.put_line(stmt);
execute immediate stmt;
exception
when others then
execute immediate swtstmt;
execute immediate ckpstmt;
execute immediate stmt;
end;
end if;
end loop;
end;
For a standby database, you can follow similar steps to the script to drop the old standby redo logs and add new ones in the +FRA disk group, but the online redo logs cannot be migrated until the database is opened as a primary.
Once you have run your script, the migration of online logs is complete. Migrating Existing Backups to ASM Flash Recovery Area
To back up the existing archived redo log files to the new flash recovery area, use this command:
RMAN> BACKUP AS COPY ARCHIVELOG ALL DELETE INPUT;
To move backup sets to the new flash recovery area, use this command:
RMAN> BACKUP DEVICE TYPE DISK BACKUPSET ALL DELETE INPUT;
To move all datafile copies to the new flash recovery area location, use this command:
RMAN> BACKUP AS COPY DATAFILECOPY ALL DELETE INPUT;
At this point, all backups have been moved from the old flash recovery area to the new one.
Note: After you configure the database to change the location of the flash recovery area, backups created in the old flash recovery area location remain in their old location, still count against the total disk quota of the flash recovery area, are deleted from the old flash recovery area as space is required for other files, and can still be managed by RMAN and used in RMAN recovery operations. There is no need to move existing backups to the new ASM flash recovery area, unless you need the disk space used by those files for other purposes.
If you do need to free the space taken up by leftover non-ASM flash recovery area files, your options include backing them up to tape (for example, by using BACKUP RECOVERY AREA DELETE INPUT) or moving the backups from the old flash recovery area location to the new one, as described in this section.
1.9.4 Instance crash during file creation
When an instance crashes during the creation of a file in the flash recovery area, Oracle may leave the file in the flash recovery area. When this occurs, you will see the following error in the alert log:ORA-19816: WARNING: Files may exist in location that are not known to database. Where location is the location of the flash recovery area.
In such a situation, you should use the RMAN command CATALOG RECOVERY AREA to re-catalog any such files so that they appear in the RMAN repository. If the file header of the file in question is corrupted, then you will have to delete the file manually using an operating system-level utility.
1.10 Privileges
1.11 Examples on Configuration
1.11.1 Configure Flash Recovery Area for Disk-Based Backups: Example
In this example the database is configured as follows:· Archived logs and RMAN backups are stored in the flash recovery area.
· The control file and online redo log copies are stored in directories in the file system outside the flash recovery area.
· Datafiles are expected to be no larger than 3GB in size. No more than 4GB of archived redo log files should be retained.
The backup strategy in this example is based on incremental backups. The control file will be automatically backed up to the flash recovery area.
The flash recovery area is sized to 10GB, room enough for control file autobackups, a whole database level 0 incremental backup (which consists of image copies of the 3GB of datafiles), plus several incremental level 1 backups. To implement this strategy, the parameter file contains the following entries:
DB_NAME=sample
# set location for current datafiles:
DB_CREATE_FILE_DEST = '/u02/oradata/wrk_area'
# set location for control files and online redo logs:
DB_CREATE_ONLINE_LOG_DEST_1 = '/u03/oradata/wrk_area'
DB_CREATE_ONLINE_LOG_DEST_2 = '/u04/oradata/wrk_area'
# set flash recovery area location and size
DB_RECOVERY_FILE_DEST = '/u01/oradata/rcv_area'
DB_RECOVERY_FILE_DEST_SIZE = 10G
Because the parameter file does not set any of the LOG_ARCHIVE_DEST_n parameters, Oracle sends archived logs to the flash recovery area only.
Once the target database is started, the following RMAN commands configure the
retention policy, backup optimization, and the control file autobackup:
RMAN> CONFIGURE RETENTION POLICY TO REDUNDANCY 1;
RMAN> CONFIGURE BACKUP OPTIMIZATION ON;
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;
Any disk-based backups are now directed to your flash recovery area.
1.11.2 Create a Database with Multiplexed Files in the Flash Recovery Area: Scenario
Assume that you want to create a database with the following properties:· The control files, datafiles, and online redo logs are stored as Oracle managed files in a single file system directory.
· One multiplexed copy of the control file is kept in the flash recovery area
· Multiplexed copies of the online redo logs are kept in the flash recovery area
· Redo log files are archived both to the flash recovery area and another file system,
· separate from the work area
· RMAN backups are directed to the flash recovery area by default
To create a database with a flash recovery area:
1. Create a PFILE for the database, including the initialization parameters required to use a flash recovery area and direct online and archived logs, a copy of the control file, . Assume that you set the following:
# set DB_NAME
DB_NAME=sample
# set destination for OMF datafiles, control file and online redo logs
DB_CREATE_FILE_DEST = /u01/oradata/wrk_area/
# set log archiving destinations to a file system location
# and the flash recovery area
LOG_ARCHIVE_DEST_1 = 'LOCATION=/arc_dest1'
LOG_ARCHIVE_DEST_2 = 'LOCATION=USE_DB_RECOVERY_FILE_DEST'
# multiplexed copies of control file and online logs in flash recovery area
# rman backups also go here
DB_RECOVERY_FILE_DEST = 'LOCATION=/u01/oradata/rcv_area'
DB_RECOVERY_FILE_DEST_SIZE = 10G
The DB_CREATE_FILE_DEST parameter sets the default directory for all datafiles, online logs, and control files. Another copy of the control file and online logs is created in the flash recovery area.
2. After you set the initialization parameters, create the database. For example, start
SQL*Plus and enter:
SQL> CREATE DATABASE sample;
The preceding statement has the following effects:
· Datafiles are created as Oracle managed files in DB_CREATE_FILE_DEST.
· Because no LOGFILE clause was included, online log groups are created by default. Each group has two members, one in DB_CREATE_FILE_DEST, the other in DB_RECOVERY_FILE_DEST.
· Because the CONTROL_FILES parameter was not set, Oracle creates a control file in DB_CREATE_FILE_DEST (primary) and DB_RECOVERY_FILE_DEST (multiplexed copy).
On a Linux system, the filenames might look like the following:
/u02/oradata/wrk_area/SAMPLE/controlfile/o1_mf_3ajeikm_.ctl #primary ctlfile
/u01/oradata/rcv_area/SAMPLE/controlfile/o1_mf_6adjkid_.ctl #ctl file copy
/u02/oradata/wrk_area/SAMPLE/logfile/o1_mf_0_orrm31z_.log #log grp1, mem 1
/u01/oradata/rcv_area/SAMPLE/logfile/o1_mf_1_ixfvm8w9).log #log grp 1 mem 2
/u02/oradata/wrk_area/SAMPLE/logfile/o1_mf_2_2xyz16am_.log # log grp2, mem1
/u01/oradata/rcv_area/SAMPLE/logfile/o1_mf_2_q89tmp28_.log #log grp 2, mem 2
· Oracle uses LOG_ARCHIVE_DEST_1 and LOG_ARCHIVE_DEST_2 as destinations for archiving the redo logs. Archived redo log files are created in the flash recovery area because LOG_ARCHIVE_DEST_2 is configured to be the flash recovery area.
Because you enabled a local redo log archiving destination, LOG_ARCHIVE_DEST_10 is not implicitly set to the flash recovery area.
The archived redo log files in the flash recovery area are given Oracle-managed filenames that are not based on the LOG_ARCHIVE_FORMAT parameter. For example, if you generate an archived log:
ALTER SYSTEM ARCHIVE LOG CURRENT;
An archived log file is created in the primary archiving location, as well as the following flash recovery area subdirectory: /u01/oradata/rcv_area/SAMPLE/archivelog/YYYY_MM_DD where YYYY_MM_DD is the creation date format.
3. You may want to create more online redo log groups for this database. To do so, use the ALTER DATABASE ADD LOGFILE statement in SQL*Plus. When no file name is specified, it creates another log file member in the destinations already specified for online logs, including the flash recovery area. For example, the following statement creates a new log group with two members: one in DB_CREATE_FILE_DEST and another in DB_RECOVERY_FILE_DEST: ALTER DATABASE ADD LOGFILE;
1.11.3 Creating a Database with Only Archived Logs in the Flash Recovery Area: Scenario
Assume that you want to create a database in which the control files, datafiles, and online redo logs are Oracle managed files in a single file system directory. Additionally, you want to do the following:· Archive each redo log to the flash recovery area (and only to the flash recovery area)
· Send RMAN backups to the flash recovery area by default.
To create a database with a flash recovery area:
1. Set the relevant initialization parameters. Assume that you set the following:
DB_NAME=sample
DB_CREATE_FILE_DEST = '/u02/oradata/wrk_area'
DB_RECOVERY_FILE_DEST = '/u01/oradata/rcv_area'
DB_RECOVERY_FILE_DEST_SIZE = 10G
# if you set the following parameters, then the online redo logs *and*
# current control file are located here
DB_CREATE_ONLINE_LOG_DEST_1 = '/u03/oradata/wrk_area'
DB_CREATE_ONLINE_LOG_DEST_2 = '/u04/oradata/wrk_area'
The DB_CREATE_FILE_DEST parameter sets the default file system directory for the datafiles. The DB_CREATE_ONLINE_LOG_DEST_n parameter sets the default file system directories for the online redo logs and control files. DB_RECOVERY_FILE_DEST sets the file system directory for archived logs.
2. After you set the initialization parameters, create the database. For example, start SQL*Plus and enter:
CREATE DATABASE sample;
No multiplexed copies of the online redo logs or control files are created in the flash recovery area.
3. Because you enabled a flash recovery area, Oracle automatically sets LOG_ARCHIVE_DEST_10 to the flash recovery area. The filenames in the flash recovery area are given Oracle managed filenames that are not based on the LOG_ARCHIVE_FORMAT parameter. For example, generate an archived log:
ALTER SYSTEM ARCHIVE LOG CURRENT;
On Linux, the preceding statement creates an archived log in a flash recovery area subdirectory: /u01/oradata/rcv_area/SAMPLE/archivelog/YYYY_MM_DD, where YYYY_MM_DD is the creation date format.
4. If you need to add more online redo log groups, execute the ALTER DATABASE ADD LOGFILE statement. When no file name is specified, it creates another log file member in each DB_CREATE_ONLINE_LOG_DEST_n location (but not the flash recovery area). For example, enter the following:
ALTER DATABASE ADD LOGFILE;
On Linux, the preceding statement creates one member in /u03/oradata/wrk_area/SAMPLE/logfile and another member in /u04/oradata/wrk_
area/SAMPLE/logfile. On other platforms, the specific file and directory names are platform-dependent.
1.12 Restrictions and Errors
1.12.1 Restrictions on Initialization Parameters When Using Flash Recovery Area
Using a flash recovery area has implications for some other initialization parameters:· 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.
· LOG_ARCHIVE_DEST_10 is implicitly set 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.
· 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.
1.13 Oracle Dictionary
1.13.1 V$RECOVERY_FILE_DEST
Displays information about the disk quota and current disk usage in the flash recovery area.| Column | Datatype | Description |
| NAME | VARCHAR2(513) | Location name. This is the value specified in the DB_RECOVERY_FILE_DEST initialization parameter. |
| SPACE_LIMIT | NUMBER | Maximum amount of disk space (in bytes) that the database can use for the flash recovery area. This is the value specified in the DB_RECOVERY_FILE_DEST_SIZE initialization parameter. |
| SPACE_USED | NUMBER | Amount of disk space (in bytes) used by flash recovery area files created incurrent and all previous flash recovery areas. Changing flash recovery areas 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 flash recovery area |
| NUMBER_OF_FILES | NUMBER | Number of files in the flash recovery area |
1.13.2 V$FLASH_RECOVERY_AREA_USAGE
Displays usage information about flashback recovery areas.| Column | Datatype | Description |
| FILE_TYPE | VARCHAR2(12) | File type |
| PERCENT_SPACE_USED | NUMBER | Percent of recovery area that is in use |
| PERCENT_SPACE_ RECLAIMABLE | NUMBER | Percent of recovery area that is reclaimable |
| NUMBER_OF_FILES | NUMBER | Number of files in the recovery area |
1.14 RMAN
1.14.1 CATALOG RECOVERY AREA
The CATALOG RECOVERY AREA command will catalog all files in the flash recovery area. During this operation, any files in the recovery area that are not listed in the RMAN repository are added to the RMAN repository.For example: CATALOG RECOVERY AREA;
1.14.2 CATALOG START WITH
Catalog all backups that are associated with the database. For example, the following commands catalogs files in the flash recovery area, and then in a secondary archiving destination:RMAN> CATALOG START WITH '+disk1'; # all files from flash recovery area # (stored on ASM disk)
RMAN> CATALOG START WITH '/arch_dest2'; # all files from second arch dest
Note that files in the flash recovery area cannot be marked as UNAVAILABLE.
Note that you cannot set KEEP attributes on files stored in the flash recovery area.
If you use Automatic Storage Management (ASM), an Oracle Managed Files framework, or the flash recovery area, then you may need a way to recatalog files that are known to the disk management system but are no longer listed in the RMAN repository. This situation can occur when the intended mechanisms for tracking filenames fails due to media failure, software bug, or user error.
1.14.3 Backup Recovery Area
BACKUP RECOVERY AREA;BACKUP RECOVERY AREA does not include the flashback logs when backing up flash recovery area contents to tape.
# delete obsolete backups on tape
DELETE OBSOLETE DEVICE TPE sbt;
1.14.4 Disk channels
By configuring an explicit format for disk channels, you direct backups away from the flash recovery area, if you have configured one. You lose the disk space management capabilities of the flash recovery area.1.15 Disabling the Flash Recovery Area
To disable the flash recovery area, set the DB_RECOVERY_FILE_DEST initialization parameter to a null string. For example, use this SQL*Plus statement to change the parameter on a running database:ALTER SYSTEM SET DB_RECOVERY_FILE_DEST='' SCOPE=BOTH SID="*";
The database will no longer provide the space management features of the flash recovery area for the files stored in the old DB_RECOVERY_FILE_DEST location. The files will still be known to the RMAN repository, however, and available for backup and restore activities.
Note: If you are using a flash recovery area, then you should not run your database with the retention policy disabled. 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. It is quite likely that all of the space in your recovery area will be used.
