Oracle Database Admin (DBA) Tutorial: FlashBack Database

1.   Flashback database

·       This a new feature introduced in 10g.

·       Flashbacking a database means going back to a previous database state.

·       The Flashback Database feature provides a way to quickly revert entire Oracle database to the state it was in at a past point in time.
This is different from traditional point in time recovery.

·       A new background process RVWR introduced which is responsible for writing flashback logs which stores pre-image(s) of data blocks 

o   One can use Flashback Database to back out changes that:

o   Have resulted in logical data corruptions.

o   Are a result of user error

o   This feature is not applicable for recovering the database in case of media failure.

o   The time required for flashbacking a database to a specific time in past is
DIRECTLY PROPORTIONAL to the number of changes made and not on the size of the database.

2.   How to Configure Flashback database

Prerequisites
Database must be in archivelog mode.
Last clean shutdown.
Enterprise and Personal Edition required

Configuration
Initialization Parameters required: -

·       DB_RECOVERY_FILE_DEST_SIZE (dynamically modifiable) --> Maximum size flashback logs can occupy in DB_RECOVERY_FILE_DEST.

·       DB_RECOVERY_FILE_DEST (dynamically modifiable) --> Physical location where              RVWR background process writes flashback logs.

·       DB_FLASHBACK_RETENTION_TARGET (dynamically modifiable) --> upper limit in minutes on how far back one can flashback the database. 

Note: - After setting these parameters in parameter file (init.ora) or spfile clean shutdown is mandatory.

SQL> Startup mount;
SQL> Alter database flashback on;
SQL> Alter database open;

SQL> select flashback_on from v$database;
FLASHBACK_ON
YES

3.   Lab Examples

•       Flashback Database

•       Flashback Table

•       Flashback Drop

•       Flashback Query

•       Flashback Versions Query

A.    Flashback Database

Using SCN:-

SQL> shutdown immediate
SQL> startup mount
SQL> flashback database to SCN 100;
Flashback complete.

SQL> alter database open resetlogs;
Database altered.

Using Timestamp:-

SQL> shutdown immediate
SQL> startup mount
SQL> flashback database to TIMESTAMP(sysdate - 2/24);
Flashback complete.

SQL> alter database open resetlogs;
Database altered.

B.    Flashback Table

•       This technology allows you to flashback a table to a previous state after “wrong” DML statements, DDL statements are not flashback-able…

•       Enabling row movement for your test table is mandatory for flashback table:

Ex:

SQL> alter table salgrade enable row movement;

You can flashback the tables using two methods

·       Using SCN number

·       Using restore points

·       Using timestamp

C.    Flashback Drop

This technology is no more no less than Windows recycle bin and allows you to restore a wrongly dropped table. In fact objects are not really dropped but just renamed and they remain in tablespace until you need space to create other objects:

Ex:-

Flashback TABLE salgrade TO before DROP;

D.   Flashback Query

This flashback technology has the taste of flashback table except that you can see the past figures without actually restoring them.

Ex:

SELECT * FROM salgrade AS OF TIMESTAMP SYSTIMESTAMP - INTERVAL '10' MINUTE

SELECT * FROM salgrade AS OF TIMESTAMP TO_TIMESTAMP('05-MAR-14 10.30.00 PM');

E.    Flashback Versions Query

This flashback technology is similar to flashback query but you can see different past figures of a column over an interval.