9
Flashback Technology: Recovering from Logical Corruptions

This chapter describes how to use the flashback features of Oracle to retrieve lost data in data recovery scenarios. This chapter includes the following sections:

• Oracle Flashback Technology: Overview
• Oracle Flashback Query: Recovering at the Row Level
• Oracle Flashback Table: Returning Individual Tables to Past States
• Oracle Flashback Drop: Undo a DROP TABLE Operation
• Oracle Flashback Database: Alternative to Point-In-Time Recovery
• Using Oracle Flashback Features Together in Data Recovery: Scenario

Oracle Flashback Technology: Overview

Oracle Flashback Technology provides a set of features that support viewing and rewinding data back and forth in time. The flashback features offer the capability to query past versions of schema objects, query historical data, analyze database changes, or perform self-service repair to recover from logical corruptions while the database is online.

• Oracle Flashback Query feature lets you specify a target time and then run queries against your database, viewing results as they would have appeared at that time. To recover from an unwanted change like an erroneous update to a table, a user could choose a target time before the error and run a query to retrieve the contents of the lost rows.
• Oracle Flashback Version Query lets you view all the versions of all the rows that ever existed in one or more tables in a specified time interval. You can also retrieve metadata about the differing versions of the rows, including start time, end time, operation, and transaction ID of the transaction that created the version. This feature can be used both to recover lost data values and to audit changes to the tables queried.
• Oracle Flashback Transaction Query lets you view changes made by a single transaction, or by all the transactions during a period of time.
• Oracle Flashback Table returns a table to its state at a previous point in time. You can restore table data while the database is online, undoing changes only to the specified table.

• Oracle Flashback Drop reverses the effects of a DROP TABLE statement.
• Oracle Flashback Database provides a more efficient alternative to database point-in-time recovery. When you use flashback database, your current datafiles revert to their contents at a past time. The result is much like the result of a point-in-time recovery using datafile backups and redo logs, but you do not have to restore datafiles from backup and you do not have to re-apply as many individual changes in the redo logs as you would have to do in conventional media recovery.

Flashback Table, Flashback Query, Flashback Transaction Query and Flashback Version Query all rely on undo data, records of the effects of each update to an Oracle database and values overwritten in the update. Used primarily for such purposes as providing read consistency for SQL queries and rolling back transactions, these undo records contain the information required to reconstruct data as it stood at a past time and examine the record of changes since that past time.

Oracle Flashback Query: Recovering at the Row Level

In a data recovery context, it is useful to be able to query the state of a table at a previous time. If, for instance, you discover that at 12:30 PM, an employee 'JOHN' had been deleted from your EMPLOYEE table, and you know that at 9:30AM that employee's data was correctly stored in the database, you could query the contents of the table as of a time before the deletion to find out what data had been lost, and, if appropriate, re-insert the lost data in the database.

Querying the past state of the table is achieved using the AS OF clause of the SELECT statement. For example, the following query retrieves the state of the employee record for 'JOHN' at 9:30AM, April 4, 2003:

SELECT * FROM EMPLOYEE AS OF TIMESTAMP 
   TO_TIMESTAMP('2003-04-04 09:30:00', 'YYYY-MM-DD HH:MI:SS')
   WHERE name = 'JOHN';

Restoring John's information to the table EMPLOYEE requires the following update:

INSERT INTO employee 
    (SELECT * FROM employee AS OF TIMESTAMP 
     TO_TIMESTAMP('2003-04-04 09:30:00', 'YYYY-MM-DD HH:MI:SS')
     WHERE name = 'JOHN');

The missing row is re-created with its previous contents, with minimal impact to the running database.

Oracle Flashback Table: Returning Individual Tables to Past States

Oracle Flashback Table provides the DBA the ability to recover a table or set of tables to a specified point in time in the past very quickly, easily, and without taking any part of the database offline. In many cases, Flashback Table eliminates the need to perform more complicated point-in-time recovery operations. Flashback Table restores tables while automatically maintaining associated attributes such as current indexes, triggers and constraints, and not requiring the DBA to find and restore application-specific properties. Using Flashback Table causes the contents of one or more individual tables to revert to their state at some past SCN or time.

Flashback Table uses information in the undo tablespace to restore the table. This provides significant benefits over media recovery in terms of ease of use, availability and faster restoration of data.

For more information on Automatic Undo Management, see Oracle Database Administrator's Guide.

Prerequisites for Using Flashback Table

The prerequisites for performing a FLASHBACK TABLE operation are as follows:

• You must have been granted the FLASHBACK ANY TABLE system privilege or you must have the FLASHBACK object privilege on the table.
• You must have SELECT, INSERT, DELETE, and ALTER privileges on the table.
• Undo information retained in the undo tablespace must go far enough back in time to satisfy the specified target point in time or SCN for the FLASHBACK TABLE operation.
• Row movement must be enabled on the table for which you are issuing the FLASHBACK TABLE statement. You can enable row movement with the following SQL statement:

  ALTER TABLE table ENABLE ROW MOVEMENT;
  

Performing Flashback Table

The following SQL*Plus statement performs a FLASHBACK TABLE operation on the table employee:

FLASHBACK TABLE employee TO TIMESTAMP 
     TO_TIMESTAMP('2003-04-04 09:30:00', `YYYY-MM-DD HH24:MI:SS');

The employee table is restored to its state when the database was at the time specified by the timestamp.

You can also specify the target point in time for the FLASHBACK TABLE operation using an SCN:

FLASHBACK TABLE employee TO SCN 123456; 

The default for a FLASHBACK TABLE operation is for triggers on a table to be disabled. The database disables triggers for the duration of the operation, and then returns them to the state that they were in before the operation was started. If you wish for the triggers to stay enabled, then use the ENABLE TRIGGERS clause of the FLASHBACK TABLE statement, as shown in this example:

FLASHBACK TABLE t1 TO TIMESTAMP '2003-03-03 12:05:00' ENABLE TRIGGERS;

The following scenario is typical of the kind of logical corruption where Flashback Table could be used:

At 17:00 an HR administrator discovers that an employee "JOHN" is missing from the EMPLOYEE table. This employee was present at 14:00, the last time she ran a report. Someone accidentally deleted the record for "JOHN" between 14:00 and the present time. She uses Flashback Table to return the table to its state at 14:00, as shown in this example:

FLASHBACK TABLE EMPLOYEES TO TIMESTAMP 
      TO_TIMESTAMP('2003-04-04 14:00:00','YYYY-MM-DD HH:MI:SS') 
      ENABLE TRIGGERS;

Oracle Flashback Drop: Undo a DROP TABLE Operation

Oracle Flashback Drop reverses the effects of a DROP TABLE operation. The intention behind this feature is to provide users with a recovery mechanism for an accidental drop of a table. Flashback Drop is substantially faster than other recovery mechanisms (such as point-in-time recovery) and also does not lead to any loss of recent transactions.

When you drop a table, the database does not immediately remove the space associated with the table. Instead, the table is renamed and, along with any associated objects, it is placed in the Recycle Bin of the database. The Flashback Drop operation recovers the table from the recycle bin.

To understand how to use Oracle Flashback Drop, you must also understand how the recycle bin works, and how to access and manage its contents.

This section covers the following topics:

• What is the Recycle Bin?
• How Tables and Other Objects Are Placed in the Recycle Bin
• Naming Convention for Objects in the Recycle Bin
• Viewing and Querying Objects in the Recycle Bin
• Recycle Bin Capacity and Space Pressure
• Purging Objects from the Recycle Bin

What is the Recycle Bin?

The recycle bin is a logical container for all dropped tables and their dependent objects. When a table is dropped, the database will store the table, along with its dependent objects in the recycle bin so that they can be recovered later. Dependent objects which are stored in the recycle bin include indexes, constraints, triggers, nested tables, LOB segments and LOB index segments.

How Tables and Other Objects Are Placed in the Recycle Bin

Tables are placed in the recycle bin along with their dependent objects whenever a DROP TABLE statement is executed. For example, this statement places the EMPLOYEE_DEMO table, along with any indexes, constraints, or other dependent objects listed previously, in the recycle bin:

SQL> DROP TABLE EMPLOYEE_DEMO;
Table Dropped

The table and its dependent objects will remain in the recycle bin until they are purged from the recycle bin. You can explicitly purge a table or other object from the recycle bin with the SQL*Plus PURGE statement, as described in "Purging Objects from the Recycle Bin". If you are sure that you will not want to recover a table later, you can drop it immediately and permanently, instead of placing it in the recycle bin, by using the PURGE option of the DROP TABLE statement, as shown in this example:

DROP TABLE employee_demo PURGE;

Even if you do not purge objects from the recycle bin, the database purges objects from the recycle bin to meet tablespace space constraints. See "Recycle Bin Capacity and Space Pressure" for more details.

Recycle bin objects are not counted as used space. If you query the space views to obtain the amount of free space in the database, objects in the recycle bin are counted as free space.

Dropped objects still appear in the views USER_TABLES, ALL_TABLES, DBA_TABLES, USER_INDEX, ALL_INDEX and DBA_INDEX.A new column, DROPPED, is set to YES for these objects. You can use the DROPPED column in queries against these views to view only objects that are not dropped.

To view only objects in the recycle bin, use the USER_RECYCLEBIN and DBA_RECYCLEBIN views, described later in this chapter.

Naming Convention for Objects in the Recycle Bin

When a table and its dependent objects are moved to the recycle bin, they are assigned unique names, to avoid name conflicts that may arise in the following circumstances:

• A user drops a table, creates another with the same name, then drops the second table.
• Two users have tables with the same name, and both users drop their tables.

The assigned names are globally unique and are used to identify the objects while they are in the recycle bin. Object names are formed as follows:

BIN$$globalUID$version

where:

• globalUID is a globally unique, 24 character long identifier generated for the object.
• version is a version number assigned by the database

The recycle bin name of an object is always 30 characters long.

Note that the globalUID used in the recycle bin name is not readily correlated with any externally visible piece of information about the object or the database.

Viewing and Querying Objects in the Recycle Bin

You can view the contents of the recycle bin using the SQL*Plus command SHOW RECYCLEBIN.

SQL> show recyclebin;
ORIGINAL NAME    RECYCLEBIN NAME                    OBJECT TYPE  DROP TIME
---------------- --------------------------------- ------------ -------------------
EMPLOYEE_DEMO    BIN$gk3lsj/3akk5hg3j2lkl5j3d==$0   TABLE        2003-06-11:17:08:54

The ORIGINAL NAME column shows the orignal name of the object, while the RECYCLEBIN NAME column shows the name of the object as it exists in the recycle bin. Use the RECYCLEBIN NAME when issuing queries against tables in the recycle bin.

The database also provices two views for obtaining information about objects in the recycle bin:

View	Description
USER_RECYCLEBIN	Lets users see their own dropped objects in the recycle bin. It has a synonym RECYCLEBIN, for ease of use.
DBA_RECYCLEBIN	Lets administrators see all dropped objects in the recycle bin

This example uses the views to determine the original names of dropped objects:

SQL> SELECT object_name as recycle_name, original_name, object_type 
     FROM recyclebin;

RECYCLE_NAME                      ORIGINAL_NAME          OBJECT_TYPE
--------------------------------  ---------------------  -------------
BIN$gk3lsj/3akk5hg3j2lkl5j3d==$0  EMPLOYEE_DEMO          TABLE
BIN$JKS983293M1dsab4gsz/I249==$0  I_EMP_DEMO             INDEX
BIN$NR72JJN38KM1dsaM4gI348as==$0  LOB_EMP_DEMO           LOB
BIN$JKJ399SLKnaslkJSLK330SIK==$0  LOB_I_EMP_DEMO         LOB INDEX

You can query objects that are in the recycle bin, just as you can query other objects, if these three conditions are met:

• You must have the FLASHBACK privilege.
• You must have the privileges that were required to perform queries against the object before it was placed in the recycle bin.
• You must use the recycle bin name of the object in your query, rather than the object's original name.

This example shows the required syntax:

SQL> SELECT * FROM "BIN$KSD8DB9L345KLA==$0";

(Note the use of quotes due to the special characters in the recycle bin name.)

You can also use Oracle Flashback Query on tables in the recycle bin (again, assuming that you have the privileges described previously).

Recycle Bin Capacity and Space Pressure

There is no fixed amount of space pre-allocated for the recycle bin. Therefore, there is no guaranteed minimum amount of time during which a dropped object will remain in the recycle bin.

The rules that govern how long an object is retained in the recycle bin and how and when space is reclaimed are explained in this section.

Understanding Space Pressure

Dropped objects are kept in the recycle bin until such time as no new extents can be allocated in the tablespace to which the objects belong without growing the tablespace. This condition is referred to as space pressure. Space pressure can also arise due to user quotas defined for a particular tablespace. A tablespace may have free space, but the user may have exhausted his or her quota on it.

Oracle never automatically reclaims space or overwrites objects in the recycle bin unless forced to do so in response to space pressure.

How the Database Responds to Space Pressure

When space pressure arises, the database selects objects for automatic purging from the recycle bin. Objects are selected for purging on a first-in, first-out basis, that is, the first objects dropped are the first selected for purging.

Actual purging of objects is done only as needed to meet ongoing space pressure, that is, the databases purges the minimum possible number of objects selected for purging to meet immediate needs for space. This policy serves two purposes:

• It minimizes the performance penalty on transactions that encounter space pressure, by not reclaiming more than is required;
• It maximizes the length of time objects remain in the recycle bin, by leaving them there until space is needed.

Dependent objects such as indexes on a table are selected for purging before the associated table (or other required segment).

If space pressure is due to an individual user's quota on a tablespace being exhausted, the recycle bin purges objects belonging to the tablespace which count against that user's space quotas.

For AUTO EXTEND-able tablespaces, objects are purged from the recycle bin to reclaim space before datafiles are extended.

Recycle Bin Objects and Segments

The recycle bin operates at the object level, in terms of tables, indexes, and so on. An object may have multiple segments associated with it, such as partitioned tables, partitioned indexes, lob segments, nested tables, and so on. Because the database reclaims only the segments needed to immediately satisfy space pressure, it can happen that some but not all segments of an object are reclaimed. When this happens, any segments of the object not reclaimed immediately are marked as temporary segments. These temporary segments are the first candidates to be reclaimed the next time space pressure arises.

In such a case, the partially-reclaimed object can no longer be removed from the recycle bin with Flashback Drop. (For example, if one partition of a partitioned table is reclaimed, the table can no longer be the object of a Flashback Drop.)

Performing Flashback Drop on Tables in the Recycle Bin

Use the FLASHBACK TABLE ... TO BEFORE DROP statement to recover objects from the recycle bin. You can specify either the name of the table in the recycle bin or the original table name. This can be obtained from either the DBA_RECYCLEBIN or USER_RECYCLEBIN view as shown in "Viewing and Querying Objects in the Recycle Bin". To use the FLASHBACK TABLE ... TO BEFORE DROP statement, you need the same privileges you need to drop the table.

The following example restores the BIN$KSD8DB9L345KLA==$0 table, changes its name back to hr.int_admin_emp, and purges its entry from the recycle bin:

FLASHBACK TABLE "BIN$KSD8DB9L345KLA==$0" TO BEFORE DROP;

You can also use the table's original name in the Flashback Drop operation:

FLASHBACK TABLE HR.INT_ADMIN_EMP TO BEFORE DROP;

You can assign a new name to the restored table by specifying the RENAME TO clause. For example:

FLASHBACK TABLE "BIN$KSD8DB9L345KLA==$0" TO BEFORE DROP 
     RENAME TO hr.int2_admin_emp;

Flashback Drop of Multiple Objects With the Same Original Name

You can create, and then drop, several objects with the same original name, and they will all be stored in the recycle bin. For example, consider these SQL statements:

CREATE TABLE EMP ( ...columns ); # EMP version 1
DROP TABLE EMP;
CREATE TABLE EMP ( ...columns ); # EMP version 2
DROP TABLE EMP;
CREATE TABLE EMP ( ...columns ); # EMP version 3
DROP TABLE EMP;

In such a case, each table EMP is assigned a unique name in the recycle bin when it is dropped. You can use a FLASHBACK TABLE... TO BEFORE DROP statement with the original name of the table, as shown in this example:

FLASHBACK TABLE EMP TO BEFORE DROP;

The most recently dropped table with that original name is retrieved from the recycle bin, with its original name. You can retrieve it and assign it a new name using a RENAME TO clause. The following example shows the retrieval from the recycle bin of all three dropped EMP tables from the previous example, with each assigned a new name:

FLASHBACK TABLE EMP TO BEFORE DROP RENAME TO EMP_VERSION_3;
FLASHBACK TABLE EMP TO BEFORE DROP RENAME TO EMP_VERSION_2;
FLASHBACK TABLE EMP TO BEFORE DROP RENAME TO EMP_VERSION_1;

Note that the last table dropped is the first one to be retrieved.

You can also retrieve any table you want from the recycle bin, regardless of any such name collisions, by using the table's unique recycle bin name.

Purging Objects from the Recycle Bin

The PURGE command is used to permanently purge objects from the recycle bin. Once purged, objects can no longer be retrieved from the bin using Flashback Drop.

There are a number of forms of the PURGE statement, depending on exactly which objects you want to purge from the recycle bin

PURGE TABLE: Purging a Table and Dependent Objects

The PURGE TABLE command purges an individual table and all of its dependent objects from the recycle bin. This example shows the syntax, using the table's original name:

PURGE TABLE EMP;

You can also use the recycle bin name of an object with PURGE TABLE:

PURGE TABLE "BIN$KSD8DB9L345KLA==$0";

If you have created and dropped multiple tables with the same orignal name, then when you use the PURGE TABLE statement the first table dropped will be the one to be purged.

For example, consider the following series of CREATE TABLE and DROP TABLE statements:

CREATE TABLE EMP;   # version 1 of the table
DROP TABLE EMP;     # version 1 dropped
CREATE TABLE EMP;   # version 2 of the table
DROP TABLE EMP;     # version 2 dropped
CREATE TABLE EMP;   # version 3 of the table
DROP TABLE EMP;     # version 3 dropped

There are now three EMP tables in the recycle bin. If you execute PURGE TABLE EMP several times, the effect is as described here:

PURGE TABLE EMP;    # version 1 of the table is purged
PURGE TABLE EMP;    # version 2 of the table is purged
PURGE TABLE EMP;    # version 3 of the table is purged

Note that this is the opposite of the behavior of FLASHBACK TABLE... TO BEFORE DROP, where using the original name of the table retrieves the most recently dropped version from the recycle bin.

PURGE INDEX: Freeing Space in the Recycle Bin

You can use PURGE INDEX to purge just an index for a table, while keeping the base table in the recycle bin. The syntax for purging an index is as follows:

PURGE INDEX "BIN$GTE72KJ22H9==$0";

By purging indexes from the recycle bin, you can reduce the chance of space pressure, so that dropped tables can remain in the recycle bin longer. If you retrieve a table from the recycle bin using Flashback Drop, you can rebuild the indexes after you retrieve the table.

PURGE TABLESPACE: Purging All Objects in a Tablespace

You can use the PURGE TABLESPACE command to purge all dropped tables and other dependent objects from a specific tablespace. The syntax is as follows:

PURGE TABLESPACE hr;

You can also purge only objects from a tablespace belonging to a specific user, using the following form of the command:

PURGE TABLESPACE hr USER scott;

PURGE RECYCLEBIN: Purging All Objects in a User's Recycle Bin

The PURGE RECYCLEBIN command purges the contents of the recycle bin for the currently logged-in user.

PURGE RECYCLEBIN;

It purges all tables and their dependent objects for this user, along with any other indexes owned by this user but not on tables owned by the user.

PURGE DBA_RECYCLEBIN: Purging All Recycle Bin Objects

If you have the SYSDBA privilege, then you can purge all objects from the recycle bin, regardless of which user owns the objects, using this command:

PURGE DBA_RECYCLEBIN;

Dropping a Tablespace, Cluster, User or Type and the Recycle Bin

When a tablespace is dropped including its contents, the objects in the tablespace are dropped immediately, and not placed in the recycle bin. Any objects in the recycle bin from the dropped tablespace are purged from the recycle bin.

If all objects from a tablespace have been placed in the recycle bin, then dropping the tablespace causes the objects to be purged, even if you do not use the INCLUDING CONTENTS clause with DROP TABLESPACE.

When a user is dropped, any objects belonging to the user that are not in the recycle bin are dropped immediately, not placed in the recycle bin. Any objects in the recycle bin that belonged to the user are purged from the recycle bin.

When you drop a cluster, all tables in the cluster are purged. When you drop a user-defined data type, all objects directly or indirectly dependent upon that type are purged.

Privileges and Security

This section summariezes the system privileges required for the operations related to Flashback Drop and the recycle bin.

• DROP

  Any user with drop privileges over the object can drop the object, placing it in the recycle bin.

• FLASHBACK TABLE... TO BEFORE DROP

  Privileges are tied to the privileges for DROP. That is, any user who can drop an object can perform Flashback Drop.

• PURGE

  Privileges are tied to the DROP privileges. Any user having DROP TABLE or DROP ANY TABLE privileges can purge the objects from the recycle bin.

• SELECT for objects in the Recycle Bin

  Users must have SELECT and FLASHBACK privileges over an object in the recycle bin to be able to query the object in the recycle bin. Any users who had the SELECT privilege over an object before it was dropped continue to have the SELECT privilege over the object in the recycle bin.Users must have FLASHBACK privilege to query any object in the recycle bin, because these are objects from a past state of the database.

Limitations and Restrictions on Flashback Drop

• The recycle bin functionality is only available for non-system, locally managed tablespaces. If a table is in a non-system, locally managed tablespace, but one or more of its dependent segments (objects) is in a dictionary-managed tablespace, then these objects are protected by the recycle bin.
• There is no fixed amount of space allocated to the recycle bin, and no guarantee as to how long dropped objects remain in the recycle bin. Depending upon system activity, a dropped object may remain in the recycle bin for seconds, or for months.
• While Oracle permits queries against objects stored in the recycle bin, you cannot use DML or DDL statements on objects in the recycle bin.
• You can perform Flashback Query on tables in the recycle bin, but you must use the recycle bin name. You cannot use the original name of the table.
• A table and all of its dependent objects (indexes, LOB segments, nested tables, triggers, constraints and so on) go into the recycle bin together, when you drop the table. Likewise, when you perform Flashback Drop, the objects are generally all retrieved together.

  It is possible, however, that some dependent objects such as indexes may have been reclaimed due to space pressure. In such cases, the reclaimed dependent objects are not retrieved from the recycle bin.

• Due to security concerns, tables which have Fine-Grained Auditing (FGA) and Virtual Private Database (VPD) policies defined over them are not protected by the recycle bin.
• Partitioned index-organized tables are not protected by the recycle bin.
• The recycle bin does not preserve referential constraints on a table (though other constraints will be preserved if possible). If a table had referential constraints before it was dropped (that is, placed in the recycle bin), then you may re-create any referential constraints after you perform Flashback Drop to retrieve the table from the recycle bin.

Oracle Flashback Database: Alternative to Point-In-Time Recovery

Oracle Flashback Database, accessible from both RMAN (by means of the FLASHBACK DATABASE command) and SQL*Plus (by means of the FLASHBACK DATABASE statement), lets you quickly recover the entire database from logical data corruptions or user errors.

It is similar to conventional point in time recovery in its effects, allowing you to return a database to its state at a time in the recent past. Flashback Database is, however, much faster than point-in-time recovery, because it does not require restoring datafiles from backup and it requires applying fewer changes from the archived redo logs.

To enable Flashback Database, you set up a flash recovery area, and set a flashback retention target, to specify how far back into the past you want to be able to restore your database with Flashback Database.

From that time on, at regular intervals, the database copies images of each altered block in every datafile into flashback logs stored in the flash recovery area. These block images can later be re-used to reconstruct the datafile contents as of any moment at which logs were captured.

To restore a database to its state at some past target time using Flashback Database, each block is restored to its contents as of the flashback logging time most immediately prior to the desired target time, and then changes from the redo logs are applied to fill in changes between the time captured by the flashback logs and the target time. Redo logs must be available for the entire time period spanned by the flashback logs, whether on tape or on disk. In practice, however, redo logs are often kept much longer than flashback logs, so this requirement is not a real limitation.

The time required to perform Flashback Database is largely a function of how far back the target time is and the number of blocks changed, rather than the volume of individual updates to the database.

Limitations of Flashback Database

Because Flashback Database works by undoing changes to the datafiles that exist at the moment that you run the command, it has the following limitations:

• Flashback Database cannot by itself recover from media failure, that is, the corruption or deletion of a datafile. The datafile to be reverted to a past state with flashback database must be present and must not be corrupted due to media failure. However, you can combine Flashback Database and point-in-time recovery in some situations for more efficient recovery. Tablespaces affected by the deletion of a datafile must be restored from backup and recovered to a point in time before the deletion. Tablespaces not affected by the deletion, however, can be reverted to that same point in time using Flashback Database.
• You cannot use Flashback Database if the database's control file has been restored from backup or re-created after Flashback Database was turned on.
• You cannot use Flashback Database to undo a shrink datafile operation.
• You cannot use Flashback Database to return the database to an SCN prior to the earliest SCN available in the flashback logs that currently exist in the flash recovery area. Because flashback logs can be deleted from the flash recovery area when space is needed, it is important to size your flash recovery area appropriately.

Finally, it is important to note that 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 required files such as archived redo logs and other backups, flashback logs may be deleted to make room in the flash recovery area for these required files. If you discover that Oracle has discarded flashback logs required to reach your desired target time for Flashback Database, you can always use traditional point-in-time recovery instead to achieve a similar result.

Requirements for Flashback Database

The requirements for enabling Flashback Database are:

• Your database must be running in ARCHIVELOG mode, because archived logs are used in the Flashback Database operation.
• You must have a flash recovery area enabled, because flashback logs can only be stored in the flash recovery area.
• For Real Application Clusters databases, the flash recovery area must be stored in a clustered file system or in ASM.

Enabling Flashback Database

To enable Flashback Database, set the DB_FLASHBACK_RETENTION_TARGET initialization parameter and issue the ALTER DATABASE FLASHBACK ON statement. Follow the process outlined here.

1. Start SQL*Plus and ensure that the database is mounted, but not open. For example:

   SQL> SELECT STATUS FROM V$INSTANCE;
   
   

2. By default the flashback retention target is set to one day (1440 minutes). If you wish, you can change the retention target. For example, if you want to retain enough flashback logs to be able to perform a 72 hour flashback, set the retention target to 4320 minutes (3 days x 24 hours/day x 60 minutes/hour):

   SQL> ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET=4320;
   
   

3. Enable the Flashback Database feature for the whole database:

   SQL> ALTER DATABASE FLASHBACK ON;
   
   

By default, flashback logs are generated for all permanent tablespaces. If you wish, you can reduce overhead by disabling flashback logging specific tablespaces:

SQL> ALTER TABLESPACE test1 FLASHBACK OFF;

You can re-enable flashback logging for a tablespace later with this command:

SQL> ALTER TABLESPACE test1 FLASHBACK ON;

Note that if you disable Flashback Database for a tablespace, then you must take its datafiles offline before running FLASHBACK DATABASE.

You can disable flashback logging for the entire database with this command:

SQL> ALTER DATABASE FLASHBACK OFF;

You can enable Flashback Database not only on a primary database, but also on a standby database. Enabling Flashback Database on a standby database allows one to perform Flashback Database on the standby database. Flashback Database of standby databases has a number of applications in the Data Guard environment. See Oracle Data Guard Concepts and Administration for details.

Sizing the Flash Recovery Area for Flashback Logs

The setting of the DB_FLASHBACK_RETENTION_TARGET initialization parameter determines, indirectly, how much flashback log data the database should keep. This limit is contingent upon sufficient space existing in the flash recovery area. The size of flashback logs can vary considerably, however, depending on the locality of database changes during a given flashback logging interval.

Estimating Flashback Database Storage Requirements

The V$FLASHBACK_DATABASE_LOG view can help you decide how much space to add to your flash recovery area for flashback logs. After you have enabled the Flashback Database feature and allowed the database to generate some flashback logs, run the following query:

SQL> SELECT ESTIMATED_FLASHBACK_SIZE FROM V$FLASHBACK_DATABASE_LOG;

An estimate of disk space needed to meet the current flashback retention target is calculated, 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 dataabase 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 like flashback logs and backups already moved 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 for other required files. In such situations you will still be able to use point-in-time recovery to revert your database to a previous state.

Determining the Current Flashback Database Window

At any given time, the earliest point in time to which you can actually rewind your database by using Flashback Database can be determined by querying the V$FLASHBACK_DATABASE_LOG view as shown in this example:

SELECT OLDEST_FLASHBACK_SCN, OLDEST_FLASHBACK_TIME 
      FROM V$FLASHBACK_DATABASE_LOG;

If the results of this query indicate that you cannot reach your intended flashback retention target, increase the size of your flash recovery area to accomodate more flashback logs than value indicated by V$FLASHBACK_DATABASE_LOG.ESTIMATED_FLASHBACK_SIZE. Also, when you are deciding whether to use Flashback Database instead of point-in-time recovery, this value will tell you whether you can reach your desired target time before you start the Flashback Database operation.

Performance Tuning for Flashback Database

Maintaining flashback logs imposes comparatively limited overhead on an Oracle database instance. Changed blocks are written from memory to the flashback logs at relatively infrequent, regular intervals, to limit processing and I/O overhead.

To achieve good performance for large production databases with Flashback Database enabled, Oracle Corporation recommends the following:

• Use a fast file system for your flash recovery area, preferably without operating system file caching. Files the database creates in the flash recovery area, including flashback logs, are typically large. Operating system file caching is typically not effective for these files, and may actually add CPU overhead for reading from and writing to these files. Thus, it is recommended to use a file system that avoids operating system file caching, such as ASM, or the Solaris 2.8 file system with direct I/O.
• Configure enough disk spindles for the file system that will hold the flash recovery area. For large production databases, multiple disk spindles may be needed to support the required disk throughput for the database to write the flashback logs effectively.
• If the storage system used to hold the flash recovery area does not have non-volatile RAM, try to configure the file system on top of striped storage volumes, with a relatively small stripe size such as 128K. This will allow each write to the flashback logs to be spread across multiple spindles, improving performance
• For large, production databases, set the init.ora parameter LOG_BUFFER to be at least 8MB. This makes sure the database allocates maximum memory (typically 16MB) for writing flashback database logs.

The overhead of turning on logging for Flashback Database depends on the read-write mix of the database workload. The more write-intensive the workload, the higher the overhead caused by turning on logging for Flashback Database. (Queries do not change data and thus do not contribute to logging activity for Flashback Database.)

Monitoring Flashback Database

The best way to monitor system usage due to flashback logging is to take performance statistics using the Oracle Statspack. For example, if you see "flashback buf free by RVWR" as the top wait event, it indicates that Oracle cannot write flashback logs very quickly. In such a case, you may want to tune the file system and storage used by the flash recovery area, possibly using one of the methods described in "Performance Tuning for Flashback Database".

The V$FLASHBACK_DATABASE_STAT view (described in Oracle Database Reference) shows the bytes of flashback data logged by the database. Each row in the view shows the statistics accumulated (typically over the course of an hour). The FLASHBACK_DATA and REDO_DATA columns describe bytes of flashback data and redo data written respectively during the time interval, while the DB_DATA column describe bytes of data blocks read and written. Note that FLASHBACK_DATA and REDO_DATA correspond to sequential writes, while DB_DATA corresponds to random reads and writes.

Because of the difference between sequential I/O and random I/O, a better indication of I/O overhead is the number of I/O operations issued for flashback logs. The following statistics in V$SYSSTAT can tell you the number of I/O operations your instance has issued for various purposes:

Column Name	Column Meaning
Physical write I/O request	The number of write operations issued for writing data blocks
physical read I/O request	The number of read operations issued for reading data blocks
redo writes	The number of write operations issued for writing to the redo log.
flashback log writes	The number of write operations issued for writing to flashback logs.

See Oracle Database Reference for more details on columns in the V$SYSSTAT view.

Running the FLASHBACK DATABASE Command from RMAN

1. Query the target database to determine the range of possible Flashback Database SCNs. The following queries show you the the latest and earliest SCN in the flashback window:

   SQL> SELECT CURRENT_SCN FROM V$DATABASE;
   
   SQL> SELECT OLDEST_FLASHBACK_SCN, OLDEST_FLASHBACK_TIME 
           FROM V$FLASHBACK_DATABASE_LOG;
   
   

2. Start RMAN and connect to the target database. For example:

   rman TARGET /
   
   

3. Run the FLASHBACK DATABASE command to return the database to a prior TIME, SCN, or archived log SEQUENCE number. If you configured sbt channels, RMAN automatically restores archived logs from tape as needed during the Flashback Database operation. For example:

   RMAN> FLASHBACK DATABASE TO SCN 46963;
   
   RMAN> FLASHBACK DATABASE TO SEQUENCE 5304;
   
   RMAN> FLASHBACK DATABASE TO TIME (SYSDATE-1/24);
   
   RMAN> FLASHBACK DATABASE TO TIME timestamp('2002-11-05 14:00:00');
   
   RMAN> FLASHBACK DATABASE 
     TO TIME to_timestamp('2002-11-11 16:00:00', 'YYYY-MM-DD HH24:MI:SS');
   
   

When the Flashback Database operation completes, you can evaluate the results by opening the database read-only and run some queries to check whether your Flashback Database has returned the database to the desired state.

RMAN> SQL 'ALTER DATABASE OPEN READ ONLY';

At this point you have several options:

• If you are content with the results, you can make the database available by performing an ALTER DATABASE OPEN RESETLOGS.

  RMAN> ALTER DATABASE OPEN RESETLOGS
  
  

• If you discover that you have chosen the wrong target time for your Flashback Database operation, you can use RECOVER DATABASE UNTIL to bring the database forward, or perform FLASHBACK DATABASE again with an SCN further in the past. You can completely undo the effects of your flashback operation by performing complete recovery of the database:

  RMAN> RECOVER DATABASE;
  
  

• If you only want to retrieve some lost data from the past time, you can open the database read-only, then perform a logical export of the data using an Oracle export utility (Data Pump Export or Original Export), then run RECOVER DATABASE to return the database to the present time and re-import the data using the Oracle import utility that corresponds to the export utility you used.

Note that, as with point-in-time recovery, you lose all updates to the database after the target SCN for the Flashback Database operation.

Running the FLASHBACK DATABASE Command from SQL*Plus

The FLASHBACK DATABASE command in SQL*Plus takes essentially the same options and performs essentially the same behavior as FLASHBACK DATABASE as performed in RMAN. The chief difference is that RMAN, being aware of backups of your database files, can restore from backup automatically any needed archived logs required for the Flashback Database process. When using FLASHBACK DATABASE in SQL*Plus, all files required to complete the operation must already be present on disk.

Using Oracle Flashback Features Together in Data Recovery: Scenario

The following scenario shows how one might use the various flashback features of Oracle (Oracle Flashback Query, Oracle Flashback Transaction Query, Oracle Flashback Table, and Oracle Flashback Database) to recover from a data loss due to a user or application error.

At 17:00 an HR administrator discovers that an employee "JOHN" is missing from the EMPLOYEE table. This employee was present in the table at 14:00, when she last checked. This means that someone accidentally deleted "JOHN" from the table between 14:00 and 17:00. The HR administrator re-inserts the missing employee row into the EMPLOYEE table with the following use of Flashback Query:

INSERT INTO employee 
    SELECT * FROM employee AS OF TIMESTAMP 
        TO_TIMESTAMP('2003-04-04 14:00:00', 'YYYY-MM-DD HH:MI:SS')
        WHERE name = 'JOHN';

She can find out more information about when "JOHN" was deleted, the transaction which deleted "JOHN", and the user who deleted "JOHN" by using Flashback Version Query and Flashback Transaction Query as follows:

SELECT commit_timestamp , logon_user FROM FLASHBACK_TRANSACTION_QUERY 
     WHERE xid IN 
        (SELECT versions_xid FROM employee VERSIONS BETWEEN 
         TIMESTAMP TO_TIMESTAMP('2003-04-04 14:00:00', 'YYYY-MM-DD HH:MI:SS')
         and TO_TIMESTAMP('2003-04-04 17:00:00', 'YYYY-MM-DD HH:MI:SS') 
         WHERE name = 'JOHN');

If at this time she discovers many other logical data errors in the EMPLOYEE table, she can recover the whole table to the state at 14:00 by using Flashback Table:

FLASHBACK TABLE employee TO TIMESTAMP TO_TIMESTAMP('2003-04-04 14:00:00', 
'YYYY-MM-DD HH:MI:SS');

Finally, if many other tables also contain errors due to transactions during the same interval, flashback database can return the entire database to its state before the errors:

FLASHBACK DATABASE 
  TO TIME to_timestamp('2003-04-04 14:00:00', 'YYYY-MM-DD HH:MI:SS');

For more examples of using flashback features to recover from user errors, see Oracle High Availability Architecture and Best Practices.