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.
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.
The missing row is re-created with its previous contents, with minimal impact to the running database.
Oracle Database Application Developer's Guide - Fundamentals for a more extensive discussion of the use of the SELECT... AS OF SQL statement and extensive examples of its use.
Oracle Database SQL Reference for more details on the syntax of the SELECT... AS OF form of the SELECT statement.
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.
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.
The employee table is restored to its state when the database was at the time specified by the timestamp.
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.
What is 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.
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.
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.
globalUID is a globally unique, 24 character long identifier generated for the object.
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.
You can view the contents of the recycle bin using the SQL*Plus command SHOW RECYCLEBIN.
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.
Lets users see their own dropped objects in the recycle bin. It has a synonym RECYCLEBIN, for ease of use.
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.
You can also use Oracle Flashback Query on tables in the recycle bin (again, assuming that you have the privileges described previously).
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
The PURGE RECYCLEBIN command purges the contents of the recycle bin for the currently logged-in user.
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.
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.
This section summariezes the system privileges required for the operations related to Flashback Drop and the recycle bin.
Any user with drop privileges over the object can drop the object, placing it in the recycle bin.
Privileges are tied to the privileges for DROP. That is, any user who can drop an object can perform Flashback Drop.
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.
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.
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, 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.
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.
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.
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.
Note that if you disable Flashback Database for a tablespace, then you must take its datafiles offline before running FLASHBACK DATABASE.
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.
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.
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.
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.
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.
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.
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 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.
The number of write operations issued for writing to the redo log.
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.
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.
If you are content with the results, you can make the database available by performing an ALTER DATABASE OPEN RESETLOGS.
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.
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.
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.
For more examples of using flashback features to recover from user errors, see Oracle High Availability Architecture and Best Practices.
All Rights Reserved.
