Using Oracle Database Flashback and Creating Restore Point

 Previously I wrote articles about Oracle snapshot standby database and also how to restore a failed over physical standby database back to a physical standby database.  In this article I would explain how to enable and disable oracle database flashback and how to create restore points, especially guaranteed restore points that are very handy in case we need to perform a point-in-time recovery to recover from a user failure. Database must be running in archivelog mode before flashback database feature could be enabled.

Enabling Database Flashback

To enable database flashback, fast recovery area needs to defined on a file system (or ASM diskgroup) with sufficient free space to old the flashback logs. In case of guaranteed restore point for a database with huge activity, fast recovery area may become full very fast. Set fast recovery area and enable flash back using following steps.

-- Check the status of database flashback whether it is enabled to disabled. SQL> select flashback_on from v$database;   FLASHBACK_ON ------------------ NO   -- Set fast recovery are parameters SQL> alter system set db_recovery_file_dest=’+FRA’ sid=’*’;   System altered.   SQL> alter system set db_recovery_file_dest_size=100g sid=’*’;   System altered.   SQL> alter database flashback on;     SQL> select flashback_on from v$database;   FLASHBACK_ON ------------------ NO

At this point, flashback logs would start getting created

db_flashback_retention_target

This parameter has a default value of 1440 minutes which means that database can be flashed back 24 hours in time. This parameter can be modified to any value as per requirement. Please note that there is no guarantee that you will be able to flash back database to 1440 minutes (or the time you have set for your database), because flashback logs may get deleted because of space crunch in fast recovery area.

Creating flashback restore point

A restore point is simply a named marker that we use to simplify our point-in-time recovery if we want to flashback database to some point in time. We use restore point name to do that. For example: Before a database change, we can create a restore point so that if change does not go as expected, we can restore database back to the restore point to bring database back to its original shape.

SQL> create restore point before_patch;   Restore point created.

Creating guaranteed restore point

In case of creating a guaranteed restore point, flashback logs are not automatically deleted from the fast recovery area. If fast recovery area becomes full, your database would go into hung state until you free up space from fast recovery area. Following is the way to create guaranteed restore point.

SQL> create restore point before_upgrade guarantee flashback database;   Restore point created.

V$RESTORE_POINT

Following query can be used to check currently created restore points and current space taken by the flashback logs that are needed for the guaranteed restore point.

SQL> select GUARANTEE_FLASHBACK_DATABASE,STORAGE_SIZE/1024/1024 "STORAGE_MB" ,NAME from  v$restore_point;   GUA   STORAGE_MB                    NAME --- --    ------- ------------ ---------------------------------------- NO                                                   BEFORE_PATCH YES      16384                                 BEFORE_UPGRADE

Flashback database to a restore point

Following command can be used to flashback database to a restore point. Since this is a point-in-time recovery, database open with reset logs would be required to open the database.

SQL> flashback database to restore point before_upgrade;   SQL> SHUTDOWN IMMEDIATE;   SQL> STARTUP MOUNT   SQL> ALTER DATABASE OPEN RESETLOGS;

Flashback database in dataguard environment

In case of dataguard environment, it is a good idea to create restore point on standby database as well, and that restore point should be created just before the restore point creation on primary database. The reason for this is: if standby restore point is created after primary database restore point, standby would not be able to sync with primary database in case primary database is restored to a restore point created before the standby one. After the primary and standby both are restored, standby would be a litter ahead of primary, and thus it would not be able to sync with the primary.