FLASH RECOVERY AREA and FLASHBACK database

on
FLASH RECOVERY AREA and FLASHBACK database (Doc ID 369759.1)


Differences between FLASH RECOVERY AREA and FLASHBACK database.

FLASH RECOVERY AREA
-------------------------------
The flash recovery area is a directory 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 stores the backups in FRA
by default), and it uses it when restoring files during media recovery.
All files necessary to recover the database following a media failure
are located in the flash recovery area ( if defined ).
The flash recovery area also acts as a disk cache for tape. The recommended
strategy is store the backups in the FRA, to have more recent backups
available on disk, then the backup and restore processes will be faster,
Moreover periodically is recommended to backup the FRA to tape, then you
will have the more recent backups on disk and the older one on tape.

 
FLASHBACK DATABASE
-------------------------------
This is a new feature introduced in 10g.
The Flashback Database feature provides a way to quickly revert entire
Oracle database to the state it was in at a past point in time. Also
is possible to bring a single table back in time.
This is different from traditional point in time recovery. To Flashback a
database, Oracle uses older block images to back out changes to the database,
what means that the oracle blocks are being overwritten for its before images
to bring the database back in time.
If flashback database feature is enabled. During normal database operation,
Oracle creates the Flashback logs, where the before image of the database
blocks is stored previously to applying any change in this database blocks.
Oracle automatically creates, deletes, and resizes Flashback logs in
the flash recovery area. You only need to be aware of Flashback logs for
monitoring performance and deciding how much disk space to allocate to
the flash recovery area for Flashback logs.
Flashback Database is not true media recovery, because it does not involve
restoring physical files. However, Flashback is preferable to using the
RESTORE and RECOVER commands in some cases, because it is faster and easier,
and does not require restoring the whole database.
You can limit the number of flashback logs stored by defining the
parameter:
*) DB_FLASHBACK_RETENTION_TARGET (dynamically modifiable) --> upper limit in
   minutes on how far back one can flashback the database.
But you cannot manually delete the flashback logs, you need to turn flashback
database off to delete the flashback logs.
      Note 369755.1 Flashback Logs-Space management

====================================================================



1. What is a Flash Recovery Area ?
2. Why should we use a Flash Recovery Area?
3. What should be the location of Flash Recovery Area ?
4. What should be the size of Flash Recovery Area ?
5. Can I have same Flashback Recovery Area for multiple database ?
6. ASM (Automatic Storage Management) support for Flash Recovery Area :
7. OMF (Oracle Managed Files) support for Flash Recovery Area :
8. Flash Recovery Area for RAC :
9. What type of files can be stored in  Flash Recovery Area ?
9. Initialization Parameters required for Flash Recovery Area

10. Restrictions on Initialization Parameters for Flash Recovery Area
References



APPLIES TO:

Oracle Database - Enterprise Edition - Version 10.1.0.2 and later
Information in this document applies to any platform.
***Checked for relevance on 15-Apr-2014***

PURPOSE

This document explains the basic design and configuration of a Flash Recovery Area.

SCOPE

DBA, Support.

DETAILS

Naming convention:
------------------
  10g: FLASH Recovery Area 
> 11g: FAST  Recovery Area 






















1. What is a Flash Recovery Area ?

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. All the files you need to completely recover a database from a media failure are part of the Flash Recovery Area. Oracle creates archived logs and flashback logs in the flash recovery area. RMAN can store its backup sets and image copies in the flash recovery area, and it uses it when restoring files during media recovery. The flash recovery area also acts as a disk cache for tape.

Flash recovery extends the functionality of Oracle Managed Files to all recovery related files (backup sets, image copies, and archived logs). Oracle Database automatically manages this storage, deleting files that are no longer needed. The user specifies only the location of a Flash Recovery Area, and the amount of disk space that Oracle is allowed to use for recovery related files. This feature is integrated with the 10G MMON feature, so that out of space conditions can be handled through the standard Oracle monitoring framework.

Periodically copying backups to tape frees space in the flash recovery area for other files. Oracle recommends that you enable a recovery area to simplify backup management.

The following recovery-related files are stored in the flash recovery area:
-- Current control file
-- Online redo logs
-- Archived redo logs
-- Flashback logs
-- Control file autobackups
-- Datafile and control file copies
-- Backup pieces
-- Foreign archived redo log ( An archived redo log received by a logical standby database for a LogMiner session.)



2. Why should we use a Flash Recovery Area?

As disk storage media is now competitive to tape with respect to purchase costs, disk-based backup is an optimal and preferable storage mechanism. By using disks for storage purposes, you gain significant benefits in terms of mass storage,and you can randomly access your data in milliseconds rather than hours. The previously expensive downtime in traditional recovery is exchanged for rapid data access and recovery times using cheap disk space.

The advantage that we have over tape is that tape is a sequential access device and disk is a random access device. Hence the amount of time needed for restoring from the tape is eliminated or reduced.



3. What should be the location of Flash Recovery Area ?

The flash recovery area should place on a separate disk from the working set of database files. Otherwise, the disk becomes a single point of failure for your database.



4. What should be the size of Flash Recovery Area ?

The larger the flash recovery area is, the more useful it becomes. Ideally, the flash recovery area should be large enough to contain the required files. The recovery area should be able to contain a copy of all datafiles in the database and the incremental backups used by your chosen backup strategy.

If providing this much space is impractical, then 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 on tape. At an absolute minimum, the flash recovery area must be large enough to contain the archived redo logs not yet on tape. If the recovery area has insufficient space to store flashback logs and meet other backup retention requirements, then the recovery area may delete flashback logs to make room.

Formulas for estimating a useful flash recovery area size depend on whether:

-- Your database has a small or large number of data blocks that change frequently .
-- You store backups only on disk, or on disk and tape.
-- You use a redundancy-based backup retention policy, or a recovery window-based retention policy .
-- You plan to use Flashback Database or a guaranteed restore point as alternatives to point-in-time recovery.


If you plan to enable flashback logging, then note that the volume of flashback log generation is approximately the same order of magnitude as redo log generation. For example, if you intend to set DB_FLASHBACK_RETENTION_TARGET to 24 hours, and if the database generates 20 GB of redo in a day, then a rule of thumb is to allow 20 GB to 30 GB disk space for the flashback logs. The same rule applies to guaranteed restore points when flashback logging is enabled. For example, if the database generates 20 GB redo every day, and if the guaranteed restore point will be kept for a day, then plan to allocate 20 to 30 GB.

For an example suppose that you want to determine the size of a flash recovery when the backup retention policy is set to REDUNDANCY 1 and you intend to follow the Oracle Suggested Strategy of using an incrementally updated backup. You use the following formula to estimate the disk quota



Disk Quota =

Size of a copy of database +

Size of an incremental backup +

Size of (n+1) days of archived redo logs +

Size of (y+1) days of foreign archived redo logs (for logical standby) +

Size of control file +

Size of an online redo log member * number of log groups +

Size of flashback logs (based on DB_FLASHBACK_RETENTION_TARGET value)





Where n is the interval in days between incremental updates 



and y is the delay in applying the foreign archived redo logs on a logical standby database:

For Oracle suggested backup kindly refer Note.303861.1 Incrementally Updated Backup In 10G



5. Can I have same Flashback Recovery Area for multiple database ?

Multiple databases can have the same value for DB_RECOVERY_FILE_DEST, but one of the following must be true:

-- No two databases for which the DB_UNIQUE_NAME initialization parameters are 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 recovery area in this way, the location should be large enough to hold the files for all databases. Add the values for DB_RECOVERY_FILE_DEST_SIZE for the databases, then allow for overhead such as mirroring or compression.



6. ASM (Automatic Storage Management) support for Flash Recovery Area :

The Flash Recovery Area can be an ASM disk group. RMAN can back up and restore files that are stored in ASM disk groups.In fact, RMAN is the only way to back up and restore ASM files. Backup sets and image copies can be created in ASM, either by automatically creating them in the Flash Recovery Area, or by explicitly specifying an ASM
disk group for their location.



7. OMF (Oracle Managed Files) support for Flash Recovery Area :

Backup/Restore of OMF files is easier with RMAN as RMAN does not use the long OMF file names in any commands.
Backup sets and image copies can be created as OMF files by creating them in the Flash Recovery Area.
RMAN can be used to migrate existing files to OMF.



8. Flash Recovery Area for RAC :

For RAC database the location and disk quota must be the same on all instances. To accomplish this, Oracle recommends that you place the flash recovery area on the shared ASM disks. In addition, you must set the DB_RECOVERY_FILE_DEST and DB_RECOVERY_FILE_DEST_SIZE parameters to the same values on all instances.
To use the Flash Recovery feature, you must first configure the flash recovery area for each instance in your Oracle RAC cluster.



9. What type of files can be stored in  Flash Recovery Area ?

The files in Flash Recovery Area are classified as permanent or transient.

-- Permanent Files

The permanent files (assuming these are configured to be stored in the recovery area) are multiplexed copies of the current control file and online redo logs. These cannot be deleted without causing the instance to fail.

-- Transient Files 
Transient files include archived redo logs, datafile copies, control file copies, control file autobackups, backup pieces and flashback logs. Oracle manages these files automatically for deletion whenever space is required in the Flash Recovery Area. They are deleted once they become obsolete under the retention policy or have been backed up to tape. Any transient file in the flash recovery area once backed up to tape even if not deleted are internally placed on a file can be deleted list. Until there is a backup of the file on disk made to a tertiary storage device it cannot be obsolete.



9. Initialization Parameters required for Flash Recovery Area

To enable the Flash Recovery Area, you must set the two initialization parameters:

-- DB_RECOVERY_FILE_DEST_SIZE : It is the disk limit, which is the amount of space the flash recovery area is permitted to use. The minimum size of the Flash Recovery Area should be at least large enough to contain archive logs that have not been copied to tape.

Note: This value does not include certain kinds of disk overhead:
-Block 0 or the OS block header of each Oracle file is not included in this size, so make sure to allow an extra 10% for this data when computing the actual disk usage required for the Flash Recovery Area.

-DB_RECOVERY_FILE_DEST_SIZE does not indicate the real size occupied on disk when the underlying filesystem is mirrored, compressed, or in some other way affected by overhead not known to Oracle.

-- DB_RECOVERY_FILE_DEST: 
This initialization parameter is a valid destination to create the Flash Recovery Area. The destination can be defined as a directory, file system, or ASM disk group.



Note1: The Flash Recovery Area cannot be stored on a raw file system.



Note2 : DB_RECOVERY_FILE_DEST_SIZE must be set before DB_RECOVERY_FILE_DEST.




10. Restrictions on Initialization Parameters for Flash Recovery Area

- You cannot use the LOG_ARCHIVE_DEST and LOG_ARCHIVE_DUPLEX_DEST parameters to specify redo log archive destinations. You must always use the LOG_ARCHIVE_DEST_n parameters in case you have configured flash recovery area.

- LOG_ARCHIVE_DEST_10 is implicitly set to USE_DB_RECOVERY_FILE_DEST if you create a recovery area and do not set any other local archiving destinations.

- Multiple database can have the same DB_RECOVERY_FILE_DEST only if the DB_NAME are different or if the DB_NAME is same (example the primary and standby database) then the DB_UNIQUE_NAME parameter must be different for the databases.

- For RAC the location of Flash Recovery Area 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.



REFERENCES

NOTE:305817.1 - FAQ - Flash Recovery Area feature
NOTE:829755.1 - Space issue in Fast / Flash Recovery Area - FRA Full
NOTE:303861.1 - Incrementally Updated Backup In 10G and higher
NOTE:305812.1 - Flash Recovery area - Space management Warning & Alerts











=============================================================================











Restrictions on Flashback Table
========================

Flashback Table allows you to recover a table or tables to a specific point in time without restoring a backup. When you use the Flashback Table feature to restore a table to a specific point in time, all associated objects, such as indexes, constraints, and triggers will be restored.

Flashback Table operations are not valid for the following object types:
===================================================

- Tables that are part of a cluster
- Materialized views
- Advanced Queuing tables
- Static data dictionary tables
- System tables
- Partitions of a table
- Remote tables (via database link)

Flashback Table is extremely useful when a user accidentally inserts, deletes, or updates the wrong rows in a table. It provides a way for users to easily and quickly recover a table to a previous point in time. 

However, if the following DDL commands are issued, the flashback table command does not work

- ALTER TABLE ... DROP COLUMN
- ALTER TABLE ... DROP PARTITION
- CREATE CLUSTER
- TRUNCATE TABLE
- ALTER TABLE ... MOVE - ALTER TABLE ... ADD PARTITION - ALTER TABLE ... SPLIT PARTITION - ALTER TABLE ... DISABLE / ENABLE PRIMARY KEY





========================================================================



How to recover Table data Using the Flashback Table Feature (Doc ID 246421.1)


















Recovering Tables Using the Flashback Table Feature:
-----------------------------------------------------
The FLASHBACK TABLE statement enables users to recover a table to a previous
point in time. It provides a fast, online solution for recovering a table that has been
accidentally modified or deleted by a user or application. 
Flashback Drop is substantially faster than other recovery mechanisms that 
can be used in this situation, such as point-in-time recovery, and does not lead to any loss of recent transactions or downtime.

Restores all data in a specified table to a previous point in time described by a
timestamp or SCN. An exclusive DML lock is held on a table while it is being
restored.

Performs the restore operation online.

Note: You must be using automatic undo management to use the
flashback table feature. It is based on undo information stored in an
undo tablespace.

Automatically restores all of the table attributes, such as indexes, triggers, and
the likes that are necessary for an application to function with the flashed back
table.

Maintains any remote state in a distributed environment. For example, all of the
table modifications required by replication if a replicated table is flashed back.

Maintains data integrity as specified by constraints. Tables are flashed back
provided none of the table constraints are violated. This includes any referential
integrity constraints specified between a table included in the FLASHBACK
TABLE statement and another table that is not included in the FLASHBACK
TABLE statement.

Even after a flashback, the data in the original table is not lost. You can later
revert to the original state.

To use the FLASHBACK TABLE statement you must have been granted the
FLASHBACK ANY TABLE system privilege or you must have the FLASHBACK object
privilege on the table. Additionally, you must have SELECT, INSERT, DELETE, and
UPDATE privileges on the table. The table that you are performing the flashback
operation on must have row movement enabled.

Example:

SQL>alter tablespace UNDOTBS1 retention guarantee;

SQL>select tablespace_name,retention from dba_tablespaces;

TABLESPACE_NAME                RETENTION
------------------------------ -----------
SYSTEM                         NOT APPLY
UNDOTBS1                       GUARANTEE
SYSAUX                         NOT APPLY
TEMP                           NOT APPLY
EXAMPLE                        NOT APPLY
USERS                          NOT APPLY
HISTORY                        NOT APPLY

7 rows selected.

SQL> ALTER TABLE flash_test_table enable row movement;

Table altered.


SQL> select * from flash_test_table;

     EMPNO EMPNAME
---------- ------------------------------
         1 Kiran
         2 Scott
         3 Tiger
         4 Jeff

SQL> select current_scn from v$database;

CURRENT_SCN
----------------
332348



SQL> connect scott/tiger
Connected.
SQL> insert into flash_test_table values(5,'Jane');

1 row created.

SQL> insert into flash_test_table values(6,'John');

1 row created.

SQL> commit;

Commit complete.

SQL> connect / as sysdba
Connected.
SQL> select current_scn from v$database;

CURRENT_SCN
----------------
332376

SQL> connect scott/tiger
Connected.

SQL> select * from flash_test_table;

     EMPNO EMPNAME
---------- ------------------------------
         1 Kiran
         2 Scott
         3 Tiger
         4 Jeff
         5 Jane
         6 John

6 rows selected.

SQL> flashback table flash_test_table to scn 332348;

Flashback complete.

SQL> select * from flash_test_table;

     EMPNO EMPNAME
---------- ------------------------------
         1 Kiran
         2 Scott
         3 Tiger
         4 Jeff

SQL> flashback table flash_test_table to scn 332376;

Flashback complete.

SQL> select * from flash_test_table;

     EMPNO EMPNAME
---------- ------------------------------
         1 Kiran
         2 Scott
         3 Tiger
         4 Jeff
         5 Jane
         6 John

6 rows selected.



Additional comment:
------------------------
Adding a example for using flashback table with timestamp (to_timestamp) 
.
SQL> flashback table xxx to timestamp to_timestamp('2012-09-01 11:00:00', 'YYYY-MM-DD HH24:MI:SS') ;

--











=========================================================
Use Flashback Table Feature and Resolve errors (Doc ID 270060.1)











What is the Flashback Table feature:

------------------------------------------------------


    

  • This feature introduces the FLASHBACK TABLE statement in SQL, which enables to quickly recover a table to a point in time in the past without restoring a backup.
    • 



    

  • The time in the past to which the table can be flashed back is dependent on the amount of undo data in the system. Also, Oracle Database cannot restore a table to an earlier state across any DDL operations that change the structure of the table.
    • 



    

  • You cannot roll back a FLASHBACK TABLE statement. However, you can issue another FLASHBACK TABLE statement and specify a time just prior to the current time. Therefore, it is advisable to record the current SCN before issuing a FLASHBACK TABLE clause.
    • 






How to Enable and use this feature:

-----------------------------------------------------



To flash back a table to an earlier SCN or timestamp, you must have either the


    

  1. FLASHBACK object privilege on the table or the FLASHBACK ANY TABLE system privilege.
    2. 

  2. SELECT, INSERT, DELETE, and ALTER object privileges on the table.
    3. 

  3. Row movement must be enabled for all tables in the Flashback list
    4. 






Examples that illustrate the usage and common errors encountered:

---------------------------------------------------------------------------------------------------



CANNOT FLASHBACK "SYS" TABLES:



SQL> select dbms_flashback.get_system_change_number from dual;



GET_SYSTEM_CHANGE_NUMBER

------------------------

521237



SQL> flashback table ban to scn 521237

2 ;

flashback table ban to scn 521237

*

ERROR at line 1:

ORA-08185: Flashback not supported for user SYS



CANNOT FLASHBACK A TABLE WHICH DOESN'T HAVE ROW MOVEMENT ENABLED



SQL> flashback table banchu to timestamp(systimestamp - interval '1' minute);

flashback table banchu to timestamp(systimestamp - interval '1' minute)

*

ERROR at line 1:

ORA-08189: cannot flashback the table because row movement is not enabled





A USER "SOMU" TRYING TO FLASHBACK A TABLE OF USER "SOM" WITHOUT PROPER PRIVILEGES:



SQL> flashback table som.banchu to timestamp(systimestamp - interval '1' minute);

flashback table som.banchu to timestamp(systimestamp - interval '1' minute)

*

ERROR at line 1:

ORA-01031: insufficient privileges



GRANTING PROPER PRIVILEGES TO USER "SOMU"



Connected as "SOM":



SQL>grant flashback on banchu to somu;



SQL> grant select,insert,delete,alter on banchu to somu;



Grant succeeded.



SQL> select * from banchu;



ID

----------

100

200

300



Connected as "SOMU"



SQL> flashback table som.banchu to timestamp(systimestamp - interval '3' minute);



Flashback complete.





SQL> select * from som.banchu;



ID

----------

100

200



Please note that '300' is missing.



Connected as 'SOM':

-------------------

SQL> select * from banchu;



ID

----------

100

200



DROPPING A TABLE AND THEN FLASHBACK:



SQL> drop table banchu;



Table dropped.



SQL> flashback table banchu to scn 522748;

flashback table banchu to scn 522748

*

ERROR at line 1:

ORA-00942: table or view does not exist



SQL> select * from banchu;



ID

----------

100

200



If you specify the user-specified name, and if the recycle bin contains more

than one object of that name, then the database retrieves the object that

was moved to the recycle bin most recently



SQL> select * from banchu;



ID

----------

100

200



SQL> DROP TABLE BANCHU;



Table dropped.



SQL> CREATE TABLE BANCHU(PHONE NUMBER);



Table created.



SQL> INSERT INTO BANCHU VALUES(9999);



1 row created.



SQL> COMMIT;



Commit complete.



SQL> DROP TABLE BANCHU;



Table dropped.



SQL> FLASHBACK TABLE BANCHU TO BEFORE DROP;



Flashback complete.



SQL> SELECT * FROM BANCHU;



PHONE

----------

9999



Note: The above query retreives the table that was DROPPED MOST RECENTLY.



TO RETREIVE THE OLD TABLE,AGAIN DO A FLASHBACK WITH RENAME TO OPTION:



SQL> flashback table banchu to before drop rename to banchu1;



Flashback complete.



SQL> select * from banchu1;



ID

----------

100

200



When using the system generated name to retreive the old table, it gave the error:

----------------------------------------------------------------------------------------------------------------------



SQL> select object_name,original_name from user_recyclebin;



OBJECT_NAME ORIGINAL_NAME

------------------------------ -----------------------------

BIN$2LsYTE+bSF/gNAgAIKT8Ng==$0 BANCHU

BIN$2K3a5EGlDtrgNAgAIKT8Ng==$0 TEST1



SQL> flashback table BIN$2LsYTE+bSF/gNAgAIKT8Ng==$0 to before drop;

flashback table BIN$2LsYTE+bSF/gNAgAIKT8Ng==$0 to before drop

*

ERROR at line 1:

ORA-00905: missing keyword



This error is because the generated name is not enclosed with double quotes



SQL> flashback table "BIN$2LsYTE+bSF/gNAgAIKT8Ng==$0" to before drop;







Make sure the user has the correct privileges else you will get ORA-1031







=============================================================







hat are the SELECT ANY TRANSACTION / FLASHBACK ANY TABLE Privileges ? (Doc ID 266536.1)







Purge, Flashback Before Drop While Multiple Tables in the Recyclebin With Same Name (Doc ID 266477.1)







Flashback Version Query & Flashback Transaction Query - Oracle 10G Enhancement (Doc ID 270270.1)







==========================================================














































Comments











Post a Comment


Oracle DBA Information