How to manage flash recovery area and restore point

Oracle
oracle 10.2.0.2

Restore point and flash recovery area space management

Definition
-------------

The flash recovery area is an Oracle-managed directory, file system, or Automatic Storage Management disk group that
provides a centralized disk location for backup and recovery files. Oracle creates archived logs in the flash recovery area.
RMAN can store its backups in the flash recovery area, and it uses it when restoring files during media recovery.

Creating a normal restore point assigns the restore point name to a specific point in time or SCN, as a kind of bookmark
or alias you can use with commands that recognize a RESTORE POINT clause as a shorthand for specifying an SCN.

Like normal restore points, guaranteed restore points can be used as aliases for SCNs in recovery operations. However,
they also provide specific functionality related to the use of the Flashback Database feature.


About the Flashback Database Window
---------------------------------------------------------

DB_FLASHBACK_RETENTION_TARGET specifies the upper limit (in minutes) on how far back in time the database may
be flashed back. How far back one can flashback a database depends on how much flashback data Oracle has kept in the
flash recovery area.

The flashback retention target is a target, not an absolute guarantee that Flashback Database will be available.
If your flash recovery area is not large enough to hold both the flashback logs and files that must be retained to meet the
retention policy, such as archived redo logs and other backups, then the flashback logs from the earliest SCNs may be
deleted to make room in the flash recovery area for other files.

When flashback logging is enabled, the earliest SCN in the flashback database window can be determined by querying V$FLASHBACK_DATABASE_LOG.OLDEST_FLASHBACK_SCN and
V$FLASHBACK_DATABASE_LOG.OLDEST_FLASHBACK_TIME as shown in this example:

SELECT OLDEST_FLASHBACK_SCN, OLDEST_FLASHBACK_TIME
FROM V$FLASHBACK_DATABASE_LOG;


Requirements for Using Guaranteed Restore Points
--------------------------------------------------------------------------

The COMPATIBLE initialization parameter must be set to 10.2 or greater.

The database must be running in ARCHIVELOG mode. The FLASHBACK DATABASE operation used to return your
database to a guaranteed restore point requires the use of archived redo logs from around the time of the restore point.

A flash recovery area must be configured, as described in "Setting Up a Flash Recovery Area for RMAN". Guaranteed
restore points use a mechanism similar to flashback logging, and as with flashback logging, Oracle must store the required
logs in the flash recovery area.

If flashback database is not enabled, then the database must be mounted, not open, when creating the first guaranteed
restore point (or if all previously created guaranteed restore points have been dropped).

Note: There are no special requirements for using normal restore points.


Age Out Rules of Restore Points
-----------------------------------------------

Normal restore points eventually age out of the control file, even if not explicitly dropped.
The rules governing retention of restore points in the control file are:
-The most recent 2048 restore points are always kept in the control file, regardless of their age.
-Any restore point more recent than the value of CONTROL_FILE_RECORD_KEEP_TIME is retained,
regardless of how many restore points are defined.
Normal restore points that do not meet either of these conditions may age out of the control file.

Guaranteed restore points never age out of the control file. They remain until they are explicitly dropped.


About Logging for Flashback Database and Guaranteed Restore Points
-------------------------------------------------------------------------------------------------------

If no files are eligible for deletion from the flash recovery area 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.

・Logging for Guaranteed Restore Points With Flashback Logging Disabled

The available block images can be used to re-create the datafile contents at the time of a guaranteed restore point using
FLASHBACK DATABASE, but you cannot use FLASHBACK DATABASE to reach points in time between the guaranteed
restore points and the current time, as is possible when logging for Flashback Database is enabled. If you need to return
the database to an intermediate point in time, your only option is database point-in-time recovery.

Because each block that changes is only logged once, disk space usage for logging for guaranteed restore points when
flashback logging is disabled is generally considerably less than normal flashback logging. You could maintain a guaranteed
restore point for days or even weeks without concern over the ongoing growth of flashback logs that occurs if logging for
Flashback Database is enabled. The performance overhead of logging for a guaranteed restore point without flashback
database logging is generally lower as well.

・Logging for Flashback Database With Guaranteed Restore Points Defined

If Flashback Database is enabled and one or more guaranteed restore points is defined, then the database performs normal
flashback logging, which causes some performance overhead and, depending upon the pattern of activity on your database,
can cause signifcant space pressure in the flash recovery area. However, unlike normal logging for Flashback Database,
the flash recovery area always retains the flashback logs required to allow FLASHBACK DATABASE to any time as far back
as the earliest currently defined guaranteed restore point. Flashback logs are not deleted in response to space pressure,
if they are required to satisfy the guarantee.


Estimating 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:

SQL> SELECT ESTIMATED_FLASHBACK_SIZE FROM V$FLASHBACK_DATABASE_LOG


Rules for Retention and Deletion of Flashback Logs
--------------------------------------------------------------------------

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.

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.


Obtain Information, Space Management Rules and How to Resolve Space Pressure
------------------------------------------------------------------------------------------------------------------------

You can 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;

Oracle does not delete eligible files from the Flash Recovery Area until the space must be reclaimed for some other purpose.
The following rules apply for files to become eligible for deletion from the Flash Recovery Area:

-Any backups which have become obsolete as per the retention policy.
-Any files in the Flash Recovery Area which has been already backed up to a tertiary device such as tape.
-Flashback logs may be deleted from the Flash Recovery Area to make space available for other required files.

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.
-Use the command BACKUP RECOVERY AREA, to back up the contents of the Flash Recovery Area to a tertiary
device such as tape.
-Delete unnecessary files from the Flash Recovery Area using the RMAN delete command.
-You may also need to consider changing your backup retention policy
-When using Data Guard, consider changing your archivelog deletion policy.


Reference:

Oracle® Database Backup and Recovery Basics
10g Release 2 (10.2)
Part Number B14192-03
(Data Protection with Restore Points and Flashback Database)

Oracle® Database Reference
10g Release 2 (10.2)
Part Number B14237-02
(DB_FLASHBACK_RETENTION_TARGET)

MetaLink Note:305812.1 Flash Recovery area - Space management Warning & Alerts

MetaLink Note:315098.1 How is the space pressure managed in the Flash Recovery Area - An Example.