Flashback Database

What is Flashback Database?
The Flashback Database allows to return the database to its state in the past. It is much faster than the RMAN's point-in-time recovery because it does not require restoring datafiles from backup and it requires applying fewer changes from the archived redo logs.

Configuration
SQL> show parameter db_recovery_file
 * Verify that the db_recovery_file_dest is set. This is the location of the flashback recovery area where the flash recovery files will be stored. Verify that there is sufficient space in the db_recovery_file_dest_size as well. Lastly, set an appropriate retention time (in minutes) in the db_flashback_retention_target.

NAME                                TYPE        VALUE --- - db_recovery_file_dest               string      +DG2 db_recovery_file_dest_size          big integer 100G

SQL> show parameter db_flashback

NAME                                TYPE        VALUE --- - db_flashback_retention_target       integer     2880

[Sample here is for RAC database.] -- Execute this on one of the nodes. SQL> alter system set cluster_database=false scope=spfile;
 * Turn the flashback ON.

System altered.

-- Stop database. $ srvctl stop database -d DB11G

-- Startup in MOUNT mode. SQL> startup mount ORACLE instance started.

Total System Global Area 1.3700E+10 bytes Fixed Size                 2113448 bytes Variable Size           7385194584 bytes Database Buffers        6308233216 bytes Redo Buffers               4440064 bytes Database mounted.

-- Turn flashback ON. SQL> alter database flashback on;

Database altered.

-- Put cluster_database back to TRUE. SQL> alter system set cluster_database=true scope=spfile;

System altered.

SQL> shutdown immediate ORA-01109: database not open Database dismounted. ORACLE instance shut down.

-- Restart database. $ srvctl start database -d DB11G

SQL> select flashback_on from v$database;
 * Verify that flashback is ON.

FLASHBACK_ON -- YES

SQL> create restore point before_flashback;
 * Test flashback database.

Restore point created.

SQL> select * from v$restore_point;

SCN DATABASE_INCARNATION# GUA STORAGE_SIZE -- - --- TIME --- RESTORE_POINT_TIME                                                         PRE --- --- NAME

1.0376E+13                    1 NO             0 10-APR-10 05.10.40.000000000 PM                                                                           NO BEFORE_FLASHBACK

-- Create a test table. SQL> create table a (a number);

Table created.

-- Verify that table is created. SQL> select * from tab where tname='A';

TNAME                         TABTYPE  CLUSTERID -- --- -- A                             TABLE

-- To flashback database, it needs to startup to MOUNT $ srvctl stop database -d DB11G

SQL> startup mount ORACLE instance started.

Total System Global Area 1.3700E+10 bytes Fixed Size                 2113448 bytes Variable Size           7385194584 bytes Database Buffers        6308233216 bytes Redo Buffers               4440064 bytes Database mounted.

-- Let's flashback to a RESTORE POINT created earlier. SQL> flashback database to restore point BEFORE_FLASHBACK;

Flashback complete.

SQL> alter database open resetlogs;

Database altered.

-- Verify that the table created after the restore point is no longer there. SQL> select * from tab where tname='A';

no rows selected

SQL> select * from V$FLASH_RECOVERY_AREA_USAGE;
 * The V$FLASH_RECOVERY_AREA_USAGE view shows what types of files are and their percentages of the space usage in the Flash Recovery Area.

FILE_TYPE   PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES -- - ---       CONTROLFILE                   0                         0               0 ONLINELOG                    0                         0               0 ARCHIVELOG                 .02                       .02               1 BACKUPPIECE              68.98                      1.02              10 IMAGECOPY                    0                         0               0 FLASHBACKLOG               .95                         0               3 To determine the actual space usage, simply query the view V$RECOVERY_FILE_DEST. SQL> select * from V$RECOVERY_FILE_DEST;

NAME -- SPACE_LIMIT SPACE_USED SPACE_RECLAIMABLE NUMBER_OF_FILES --- -- - --- +DG2 2147483648 1502122496         22201856              14


 * Other views
 * v$flashback_database_stat - Monitor logging
 * v$flashback_database_log - Monitor flashback retention target