Flashback Database

What is Flashback Database?

Coming soon

Configuration

• 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.

SQL> show parameter db_recovery_file

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

• Turn the flashback ON.

[Sample here is for RAC database.]

-- Execute this on one of the nodes.
SQL> alter system set cluster_database=false scope=spfile;

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

• Verify that flashback is ON.

SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
YES

• Test flashback database.

SQL> create restore point before_flashback;

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

• 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.

SQL> select * from V$FLASH_RECOVERY_AREA_USAGE;

        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