Oracle has a number of products and features that provide high availability in cases of unplanned or planned downtime. These include Fast-Start Fault Recovery, Real Application Clusters (RAC), Recovery Manager (RMAN), backup and recovery solutions, Oracle Flashback, partitioning, Oracle Data Guard, LogMiner, multiplexed redolog files and online reorganization.
To correct problems caused by logical data corruptions or user errors, we can use Oracle Flashback.
- Flashback Query (from Oracle 9i)
- Flashback Table (from Oracle 10g)
- Flashback Drop (from Oracle 10g)
- Flashback Version Query (from Oracle 10g)
- Flashback Transaction Query (from Oracle 10g)
- Flashback Database (from Oracle 10g)
- Flashback Data Archive (from Oracle 11g)
- Flashback Transaction (from Oracle 11g)
Flashback Query useful to view the data at a point-in-time in the past. This can be used (only) to view and reconstruct lost data that was deleted or changed by accident.
Flashback Table useful to recover a table to a point-in-time in the past without restoring a backup. Flashback Table is a push button solution to restore the contents of a table to a given point-in-time. An application on top of Flashback Query can achieve this, but with less efficiency.
Flashback Drop provides a way to restore accidentally dropped tables. This will be done with the help of Recyclebin feature.
Flashback Version Query uses undo data stored in the database to view the changes to one or more rows along with all the metadata of the changes.
Flashback Transaction Query useful to examine changes to the database at the transaction level. As a result, we can diagnose problems, perform analysis and audit transactions.
Flashback Database useful to bring database to a prior point in time by undoing all the changes that have taken place since that time. This operation is fast, because we do not need to restore the backups. This in turn results in much less downtime following data corruption or human error. Flashback Database applies to the entire database. It requires configuration and resources, but it provides a fast alternative to performing incomplete database recovery.
Flashback Data Archive – from Oracle 11g, flashback will make use of flashback logs, explicitly created for that table, in FRA (Flash/Fast Recovery Area), will not use undo. Flashback data archives can be defined on any table/tablespace. Flashback data archives are written by a dedicated background process called FBDA so there is less impact on performance. Can be purged at regular intervals automatically.
- DBA must have enabled undo tablespace, with appropriate retention period.
- User must have realized the mistake before the retention period.
- User must not exited from the session.
- Even frontend must be enabled with flashback option (package).
When to Use Oracle Flashback
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.
- A user error affected the whole database.
- A user error affected a table or a small set of tables, but the impact of reverting this set of tables is not clear because of the logical relationships between tables.
- A user error affected a table or a small set of tables, but using Flashback Table would fail because of its DDL restrictions.
- Flashback Database works through all DDL operations, whereas Flashback Table does not. Flashback Database moves the entire database back in time, constraints are not an issue, whereas they are with Flashback Table. Flashback Table cannot be used on a standby database.