Configure Flashback Database

As your database grows larger the length of time required to recover to a point in time grows. In some cases time required to restore all files to perform a point in time recovery becomes prohibitive.

Flashback database provides a solution to point in time recovery issues. Flashback Database using a new type of log file called the Flashback Database log. The Flashback Database log periodically receives before images of database blocks to use for backing out changes to the datafiles in a flashback operation.

The Flashback Database logs are stored in the Flash Recovery Area and are automatically created and managed when Flashback Database is enabled.

This document will detail the configuration of Flashback Database and perform a Flashback.

Preliminaries

The database must be in ARCHIVELOG mode in order to enable Flashback Database. For steps to configure ARCHIVELOG mode see the posts Enable/Disable Archive Log Mode 10g/11g or Enable/Disable Archive Log Mode 10g/11g Using Database Control

It is possible that your database might already be configured for Flashback Database. Use the following query to determine if your database has Flashback Database enabled.

SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
NO

SQL>

Configure the Flash Recovery Area

The Flash Recovery Area is defined by two initialization parameters DB_RECOVERY_FILE_DEST_SIZE and DB_RECOVERY_FILE_DEST.

DB_RECOVERY_FILE_DIST_SIZE – specifies the size of the Flash Recovery Area and must be set before DB_RECOVERY_FILE_DEST. This parameter cannot be unset if the DB_RECOVERY_FILE_DEST parameter is set.

DB_RECOVERY_FILE_DEST – specifies the location of the Flash Recovery Area. If this parameter is not set the Flash Recovery Area is disabled.

You can use the ALTER SYSTEM SET command to modify both of these parameters.

SQL> alter system set db_recovery_file_dest_size=3852M scope=both;

System altered.

SQL> alter system set db_recovery_file_dest='/u01/app/oracle/flash_recovery_area' scope = both;

System altered.

SQL> show parameter db_recovery

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      /u01/app/oracle/flash_recovery
                                                 _area
db_recovery_file_dest_size           big integer 3852M
SQL>

The parameter DB_FLASHBACK_RETENTION_TARGET specifies, in minutes, how far you want to be able to flashback the database. The DB_FLASHBACK_RETENTION_TARGET is not a guarantee. The flashback interval depends on the utilization of the flash recovery area. The default value for DB_FLASHBACK_RETENTION_TARGET is 1440 which is 24 hours.

SQL> show parameter db_flashback_retention_target

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_flashback_retention_target        integer     1440
SQL>

Enable Flashback Database

The last thing that needs to be done is enable Flashback Database. To enable Flashback Database use the ALTER DATABASE FLASHBACK ON command. In order to execute the ALTER DATABASE FLASHBACK ON command the database must be in MOUNT EXCLUSIVE mode.

SQL> shutdown immediate         
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount exclusive
ORACLE instance started.

Total System Global Area  849530880 bytes
Fixed Size                  1339824 bytes
Variable Size             528485968 bytes
Database Buffers          314572800 bytes
Redo Buffers                5132288 bytes
Database mounted.
SQL> alter database flashback on;

Database altered.

SQL> alter database open;

Database altered.

SQL>

Looking at the file system we can see that flashback directory was added and that the Flashback Database log was created.

SQL> host
 [oracle@ora1 ~]$ ls /u01/app/oracle/flash_recovery_area/ORCL
flashback  onlinelog
[oracle@ora1 ~]$ ls /u01/app/oracle/flash_recovery_area/ORCL/flashback
o1_mf_5w9ow2hs_.flb
[oracle@ora1 ~]$ 

Our query we issued early now shows that Flashback Database is enabled.

SQL> select flashback_on from v$database;

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

SQL>

Flashback Database Example

For this example I created four tables T, T1 and T2 using CTAS from DBA_OBJECTS. Each table has 72,646 rows.

SQL> truncate table t;

Table truncated.

SQL> drop table t1 purge;

Table dropped.

SQL> delete from t2 where owner = 'SYS';

30872 rows deleted.

SQL> commit;

Commit complete.

SQL> 

Now to flashback the database to undo the changes made above. You can perform the flashback in either SQL*Plus or RMAN. We want to flashback the database 14-APR-2010 at 9am. We get the SCN that date and time below.

SQL> select timestamp_to_scn(
  2                   to_timestamp('14-APR-10 09:00:00','DD-MON-YY HH24:MI:SS'))
  3  from dual;

TIMESTAMP_TO_SCN(TO_TIMESTAMP('14-APR-1009:00:00','DD-MON-YYHH24:MI:SS'))
-------------------------------------------------------------------------
                                                                  1246828

SQL>

We then verify that we can flashback to that SCN and time by finding the oldest SCN and time in the flashback database log.

SQL> select oldest_flashback_scn, oldest_flashback_time
  2  from v$flashback_database_log;

OLDEST_FLASHBACK_SCN OLDEST_FL
-------------------- ---------
             1241145 13-APR-10

SQL>

The information obtained for V$FLASHBACK_DATABASE_LOG shows that we should be able to flashback the database to the time requested. The database must be mounted and not open in order to perform the flashback database operation.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area  849530880 bytes
Fixed Size                  1339824 bytes
Variable Size             528485968 bytes
Database Buffers          314572800 bytes
Redo Buffers                5132288 bytes
Database mounted.
SQL> flashback database to scn 1246828;

Flashback complete.

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open


SQL> alter database open resetlogs;

Database altered.

SQL>

Note that the database had to be open with the RESETLOGS option. Checking the database we now see that the tables are back in their original state.

SQL> select count(*) from t; <-- table was truncated

  COUNT(*)
----------
     72648

SQL> select count(*) from t1; <-- table was dropped

  COUNT(*)
----------
     72649

SQL> select count(*) from t2; <-- 30872 rows were deleted

  COUNT(*)
----------
     72650

SQL>