7.1 About Point-in-Time Recovery and Flashback Features

The most basic solution after unwanted database changes is database point-in-time recovery, in which you restore the database from backup and then apply redo logs to recreate all changes up to a point in time before the unwanted change.

Oracle Flashback Technology provides several alternatives to a set of features that support viewing past states of data, and winding data back and forth in time, without requiring the restore of the database from backup. Depending upon the changes to your database, Flashback Technology can often reverse the unwanted changes more quickly and with less impact on the availability of the rest of your database.

7.1.1 About Database Point-in-Time Recovery

Database point-in-time recovery (DBPITR) is a process that works at the physical level to return the datafiles to their state at a desired target time in the past. In an RMAN DBPITR operation, you specify a target point in time, and RMAN restores the database from backups prior to that time, and then applies incremental backups and performs media recovery to recreate all changes between the time of the datafile backups and the target time. If your backup strategy is properly designed and your database is running in ARCHIVELOG mode then DBPITR is an option in nearly all circumstances.

RMAN simplifies DBPITR greatly compared to user-managed DBPITR. Given a target SCN, datafiles are restored from backup and recovered efficiently using incremental backups and archived logs available on disk or tape, with no intervention from the user. However, there are some disadvantages to point-in-time recovery:

You cannot return only selected objects to their earlier state, only the entire database.
Your entire database is unavailable during the database point-in-time recovery process.
Point-in-time recovery can be time-consuming, because all datafiles must be restored, and redo logs and incremental backups must be restored from backup and used to recover the datafiles. If needed backups are on tape, this process can take even longer.

Note:

If you know that unwanted database changes are extensive but confined to certain tablespaces in your database, tablespace point-in-time recovery (TSPITR) can return a subset of your tablespaces to an earlier SCN while the unaffected tablespaces of your database continue to be available. TSPITR is an advanced technique described in Oracle Database Backup and Recovery Advanced User's Guide.

7.1.2 Oracle Flashback Technology:Alternatives to Point-in-Time Recovery

The flashback features of Oracle are more efficient than media recovery in most circumstances in which they are available. They can also be used to investigate past states of Most Flashback Technology features operate at the logical level, viewing and manipulating database objects, as follows:

Oracle Flashback Query 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.

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.

Flashback Drop is built around a mechanism called the Recycle Bin, which Oracle uses to manage dropped database objects until the space they occupied is needed to store new data.

Oracle Flashback Database provides a more efficient direct alternative to database point-in-time recovery. It is unlike the other flashback features in that it operates at a physical level. When you use Flashback Database, your current datafiles revert to their contents at a past time. The end product is much like the result of a database point-in-time recovery, but can be much faster because it does not require you to restore datafiles from backup, and requires only limited application of redo compared to media recovery.

Flashback Database uses flashback logs to access past versions of data blocks, as well as some information from the archived redo log. To have the option of using Flashback Database to repair your database, you must either configure your database to generate flashback logs, or use the related capability of guaranteed restore points to protect the contents of your database at a fixed point in time, such as immediately before a risky database change.