Oracle Flashback Concept

FLASHBACK LEVELS

In Oracle Database 10g, you have access to flashback techniques at the row, table, and database levels, as follows:

Row level

We can use Flashback techniques to undo erroneous changes to individual rows. There are three types of row-level Flashback techniques, and all of them rely on undo data stored in the undo tablespace:

• Flashback Query: Allows us to view old row data based on a point in time or an SCN.

we can view the older data and, if necessary, retrieve it and undo erroneous changes.

• Flashback Versions Query: Allows us to view all versions of the same row over a period of time so that you can undo logical errors. It can also provide an audit history of changes, effectively allowing us to compare present data against historical data without performing any DML activity.

• Flashback Transaction Query: Lets us view changes made at the transaction level. This technique helps in analysis and auditing of transactions, such as when a batch job runs twice and you want to determine which objects were affected. Using this technique, we can undo changes made by an entire transaction during a specified period.

Table level

There are two main Flashback features available at the table level:

• Flashback Table: Restores a table to a point in time or to a specified SCN without restoring data files. This feature uses DML changes to undo the changes in a table. The Flashback Table feature relies on undo data.

• Flashback Drop: Allows us to reverse the effects of a DROP TABLE statement, without resorting to a point-in-time recovery. The Flashback Drop feature uses the Recycle Bin to restore a dropped table.

Database level

• Flashback Database: The Flashback Database feature allows us to restore an entire database to a point in time, thus undoing all changes since that time. For example, we can restore a dropped schema or an erroneously truncated table. Flashback Database mainly uses flashback logs to retrieve older versions of the data blocks; it also relies, to a much smaller extent, on archived redo logs to completely recover a database without restoring data files and performing traditional media recovery.

As we can see, Oracle’s Flashback technology employs a variety of techniques. The row-level Flashback techniques and Flashback Table use undo data  Flashback Drop use new concept of Recycle Bin and Flashback Database rely on the new concept of Flashback log data, respectively, to undo errors at various levels. We will focus on these techniques in this chapter.

Flashback vs.Traditional Recovery Techniques

Unlike traditional recovery techniques, the primary use of Flashback techniques isn’t to recover from a media loss, but to recover from human errors. For example, we may accidentally change the wrong set of data or drop a table. Or we may just want to query historical data and perform change analysis. In some extreme cases, we may want to revert the entire database to a previous point in time.

Note: If we have a damaged disk drive, or if there is physical corruption (not logical corruption due to application or user errors) in our database, we must still use the traditional methods of restoring backups and using archived redo logs to perform the recovery.

Traditionally, the only way to recover from human error was to employ traditional backup and restore techniques. The process of restoring the database files and then rolling forward through all the redo logs could often involve significant downtime, however, and Flashback technology offers us a much more efficient and much faster way to recover from logical errors, in most cases while the database is still online and available to users. Furthermore, Flashback techniques allow us to selectively restore certain objects. With traditional techniques, we have no choice but to recover the entire database.

FLASHBACK DATABASE

Before Oracle Database 10g, if we suffered logical database corruption, we would undertake traditional point-in-time recovery techniques, restoring data file backup copies and then using archived redo logs to advance the database forward. This was often time-consuming and cumbersome. No matter how limited the extent of the corruption, we would need to restore entire data files and apply the archived redo logs.

Note: Oracle can check data block integrity by computing checksums before writing the data blocks to disk. When the block is subsequently read again, the checksum for the data block is computed again, and if the two checksums differ, there is likely corruption in the data block. By setting the DB_BLOCK_CHECKSUM initialization parameter to FULL, we can make the database perform the check in the database buffer cache itself, thus eliminating the possibility of corruption at the physical disk level. The DB_BLOCK_CHECKSUM parameter is FALSE by default.

In Oracle Database 10g, the Flashback Database feature restores data files but without requiring backup data files and using just a fraction of the archived redo log information. A Flashback Database operation simply reverts all data files of the database to a specified previous point in time. With Flashback Database, the time it takes to recover is directly proportional to the number of changes that we need to undo. Thus, it is the size of the error and not the size of the database that determines the time it takes to recover. This means that we can recover from logical errors in a fraction of the time—perhaps as little as a hundredth of the time, depending on the size of the database— that it would take using traditional methods.

Note: Flashing back a database is possible only when there is no media failure. If you lose a data file or it becomes corrupted, you’ll have to recover using a restored data file from backups.

We can use Flashback Database in the following situations:

Ø  To retrieve a dropped schema

Ø  When a user error affects the entire database

Ø  When we truncate a table in error

Ø  When a batch job performs only partial changes

The Flashback Database feature uses flashback database logs, which are stored in the new flash recovery area, to undo changes to a point in time just before a specified target time or SCN. Since the specified target time and the actual recovery time may differ slightly, we then use archived redo logs to recover the database over the short period of time between the target time and the actual recovery time.

Once the Flashback Database feature is enabled, we simply use the FLASHBACK DATABASE command to return the database to its state at a previous time, SCN, or log sequence. We can issue the FLASHBACK DATABASE command from either RMAN or SQL*Plus. The only difference is that RMAN will automatically retrieve the necessary archived redo logs, whereas in SQL*Plus we may have to supply the archived redo logs, unless we use the SET AUTORECOVERY ON feature in SQL*Plus. We’ll take a look at the whole Flashback Database process in more detail shortly, but first let’s look at how to enable (and disable) the Flashback Database feature.

Tip Since we need the current data files in order to apply changes to them, we can’t use the Flashback Database feature in cases where a data file has been damaged or lost.