Flashback database

• Redo logs on disk or tape must be available for the entire time period spanned by the flashback logs. For example, if the flashback retention target is 1 week, then you must ensure that online and archived redo logs that contain all changes for the past week are accessible. In practice, redo logs are typically needed much longer than the flashback retention target to support point-in-time recovery.
• To ensure successful operation of Flashback Database and guaranteed restore points, you must first set several key database options.

Flashback Database

Configure the following database settings before enabling Flashback Database:

    Your database must be running in ARCHIVELOG mode, because archived logs are used in the Flashback Database operation.

    You must have a fast recovery area enabled, because flashback logs can only be stored in the fast recovery area.

    For Oracle Real Application Clusters (Oracle RAC) databases, the fast recovery area must be in a clustered file system or in ASM.

Limitations of Flashback Database

• Flashback Database can only undo changes to a data file made by Oracle Database. It cannot be used to repair media failures, or to recover from accidental deletion of data files.
• You cannot use Flashback Database to undo a shrink data file operation. However, you can take the shrunken file offline, flash back the rest of the database, and then later restore and recover the shrunken data file.
• You cannot use Flashback Database alone to retrieve a dropped data file. If you flash back a database to a time when a dropped data file existed in the database, only the data file entry is added to the control file. You can only recover the dropped data file by using RMAN to fully restore and recover the data file.
• If the database control file is restored from backup or re-created, all accumulated flashback log information is discarded. You cannot use FLASHBACK DATABASE to return to a point in time before the restore or re-creation of a control file.
• When you create a guaranteed restore point, with or without enabling full flashback database logging, you must monitor the space available in your fast recovery area.
• If possible, avoid using Flashback Database with a target time or SCN that coincides with a NOLOGGING operation.

Creating Normal and Guaranteed Restore Points

To create a restore point:

• Ensure that the database is open or mounted. If the database is mounted, then it must have been shut down cleanly (unless it is a physical standby database).
• The following example shows how to create a normal restore point in SQL*Plus:

SQL> CREATE RESTORE POINT before_upgrade;

• This example shows how to create a guaranteed restore point:

SQL> CREATE RESTORE POINT before_upgrade GUARANTEE FLASHBACK DATABASE;

List RESTORE POINT

LIST RESTORE POINT ALL;

• To see a list of all currently defined restore points (normal and guaranteed), use the V$RESTORE_POINT control file view with the following query:

SQL> SELECT NAME, SCN, TIME, DATABASE_INCARNATION#,GUARANTEE_FLASHBACK_DATABASE,STORAGE_SIZE FROM V$RESTORE_POINT;

Flashback Example

• The following is the step-by-step process of Flashback of RAC database to a Restore Point.

Set the environment to correct instance

ps -ef | grep pmon
. oraenv

Get restore point name and make note of it from sqlplus

SQL> select name, host_name, status from gv$instance;
SQL> select log_mode,flashback_on from v$database;
SQL> select name,time from v$restore_point;

Stop the database and put it in mount state using srvctl

srvctl status database -d MYTESTDB
srvctl stop database -d MYTESTDB
srvctl status database -d MYTESTDB
srvctl start instance -d MYTESTDB -i MYTESTDB1 -o mount

Flashback to restore point using sqlplus

SQL> select instance_name,status from gv$instance;

SQL> flashback database to restore point REST_POINT;

SQL> alter database open resetlogs;

Database altered.

Stop and start the database and make sure all instances are up, using srvctl

srvctl stop database -d MYTESTDB
srvctl start database -d MYTESTDB
srvctl status database -d MYTESTDB