Oracle Flashback

ORACLE FLASHBACK

ü  Oracle Flashback Technology provides a set of features available on 9i or higher version that support viewing and rewinding data back and forth in time to recover from Logical Corruptions while the database is online.

ü  Oracle9i introduced Flashback Query to provide a simple, powerful and completely non-disruptive mechanism for recovering from human errors. It allows users to view the state of data at a point in time in the past without requiring any structural changes to the database.

ü  Oracle Database 10g extended the Flashback Technology to provide fast and easy recovery at the database, table, row, and transaction level. Flashback Technology revolutionizes recovery by operating just on the changed data. The time it takes to recover the error is now equal to the same amount of time it took to make the mistake. Oracle 10g Flashback Technologies includes Flashback Database, Flashback Table, Flashback Drop, Flashback Versions Query, and Flashback Transaction Query.

ü  Oracle Flashback Database lets you quickly bring your database to a prior point in time by undoing all the changes that have taken place since that time. This operation is fast, because you do not need to restore the backups. This in turn results in much less downtime following data corruption or human error.

ü  Oracle Flashback Table lets you quickly recover a table to a point in time in the past without restoring a backup.

ü  Oracle Flashback Drop provides a way to restore accidentally dropped tables.

ü  Oracle Flashback Query lets you view data at a point-in-time in the past. This can be used to view and reconstruct lost data that was deleted or changed by accident. Developers can use this feature to build self-service error correction into their applications, empowering end-users to undo and correct their errors.

ü  Oracle 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.

ü  Oracle Flashback Transaction Query lets you examine changes to the database at the transaction level. As a result, you can diagnose problems, perform analysis, and audit transactions.

FLASH RECOVERY AREA

The flash recovery area is an Oracle-managed directory, file system, or Automatic Storage Management disk group that provides a centralized disk location for backup and recovery files. All the files you need to completely recover a database from a media failure are part of the Flash Recovery Area. Oracle creates archived logs and flashback logs in the flash recovery area. RMAN can store its backup sets and image copies in the flash recovery area, and it uses it when restoring files during media recovery. The flash recovery area also acts as a disk cache for tape.

The following recovery-related files are stored in the flash recovery area: 
-- Current control file 
-- Online redo logs 
-- Archived redo logs 
-- Flashback logs 
-- Control file autobackups 
-- Datafile and control file copies 
-- Backup pieces 
-- Foreign archived redo log ( An archived redo log received by a logical standby database for a LogMiner session.)

To enable the Flash Recovery Area, you must set the two initialization parameters: 

-- DB_RECOVERY_FILE_DEST_SIZE : 
It is the disk limit, which is the amount of space the flash recovery area is permitted to use. The minimum size of the Flash Recovery Area should be at least large enough to contain archive logs that have not been copied to tape. 

Note: This value does not include certain kinds of disk overhead: 
-Block 0 or the OS block header of each Oracle file is not included in this size, so make sure to allow an extra 10% for this data when computing the actual disk usage required for the Flash Recovery Area. 

-DB_RECOVERY_FILE_DEST_SIZE does not indicate the real size occupied on disk when the underlying filesystem is mirrored, compressed, or in some other way affected by overhead not known to Oracle. 

-- DB_RECOVERY_FILE_DEST: 

Enabling Flashback & Flash Recovery Area

1.       Shutdown Database

2.       Start the database in mount

3.       Configure parameters for Flashback

4.  

    Verify if Flash back is ON

 

Flashback Query

Flashback Query allows the contents of a table to be queried with reference to a specific point in time, using the AS OF clause

1.     Create the table

2.     Insert the Values

3.     Use the SCN & TIMESTAMP to retrieve number of rows in the table at that time/scn.

Flashback Version Query

Flashback version query allows the versions of a specific row to be tracked during a specified time period using the VERSIONS BETWEEN clauses.

1. Query the table to check the count

2. Update the table with values

3. Use the VERSIONS BETWEEN to query the values

U – Update, I – Insert & D - Delete

Flashback Table

Enabling row movement is must

What is row movement?

When you add the clause "enable row movement" to a create table statement, you are granting permission to Oracle to change the ROWID's. It basically enables rows to move. If table is already created, you can enable row movement by using below command.

Insert the Data.

Flashback the table