How to do Flashback Database in oracle 12c active data guard environment

Here are the easy steps-

There was a requirement to run stress testing (provably it may run 24 hours) in production active data guard database environment (DB version - Enterprise Edition Release 12.1.0.2.0 – 64bit). 

Stress testing will create lots of testing data and after that they want to rollback all testing transactions.  Means they want to go back to the state where it was before stress testing start. 

So for that, I recommended to use flashback Database over point-in-time recovery. because it is very easy ,quick and with less impact on database availability. 

I suggested following two step by step Methods as per their environment.

you guys can use any of them. both worked fine for me.

Two methods are below-  

Method #1:   

May use if Data guard’s  protechtion mode is -  MAXIMUM PERFORMANCE.
else use Method #2 .

So in this case, we can plan to shutdown standby server before stress testing and start after flashback database on primary database.  Steps are below-

   1)      Confirming flashback enabled on both primary and standby databases.

SQL> select database_role,flashback_on from v$database;

   2)   Just before stress testing –  Cancel Managed recovery on standby server and shutdown    standby server.

     SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL; 

          3)   Create a guaranteed restore point in primary.

           

              SQL> CREATE RESTORE POINT before_stress_test_pr GUARANTEE FLASHBACK DATABASE;

 

          4) After stress testing ,

 On primary –     

                      SQL>   SHUTDOWN IMMEDIATE  ;

                       SQL> STARTUP MOUNT ;

                         SQL>  FLASHBACK DATABASE TO RESTORE POINT before_stress_test_pr;

                         SQL> ALTER DATABASE OPEN RESETLOGS ;

         Checking SCN number in primary –(just for confirmation it is greater that standby by SCN number later) 

                 SQL>  SELECT CURRENT_SCN FROM V$DATABASE;

             5)    Start the standby server .

6)      Start Managed recovery on the standby –

                 SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

             7)     Drop guaranteed restore point from primary.

             SQL> DROP RESTORE POINT before_stress_test_pr;

               

Method #2

This one can be used any situation.

1)      Confirming flashback enabled on both primary and standby databases.

SQL> select database_role,flashback_on from v$database;

2)      Create a guaranteed restore point in standby  ( just before stress testing start)

CREATE RESTORE POINT before_stress_test_st GUARANTEE FLASHBACK DATABASE;

3)      After few minutes, create a guaranteed restore point in primary (just before stress testing start)

CREATE RESTORE POINT before_stress_test_pr GUARANTEE FLASHBACK DATABASE;

 After stress testing ,

4)       On primary –     

           SQL>   SHUTDOWN IMMEDIATE  ;

           SQL> STARTUP MOUNT ;

                         SQL>  FLASHBACK DATABASE TO RESTORE POINT before_stress_test_pr;

                         SQL> ALTER DATABASE OPEN RESETLOGS ;

  Checking SCN number in primary –(just for confirmation it is greater that standby by SCN number later) 

             SQL>  SELECT CURRENT_SCN FROM V$DATABASE;

5)      On Standby – 

     SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL; 

Checking SCN number in standby – for confirmation it is lesser than primary SCN number.

     SQL> SELECT CURRENT_SCN FROM V$DATABASE;

     SQL> FLASHBACK STANDBY DATABASE TO RESTORE POINT before_stress_test_st  ;

6)       Start Managed recovery on the standby –

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

7)      Drop both guaranteed restore point.

More Usability of Flashback Database-

Typically, in following situations you can use Flashback Database. 

• A user error or corruption removes needed data or introduces corrupted data. For example, a user or DBA might erroneously delete or update the contents of one or more tables, drop database objects that are still needed during an update to an application, or run a large batch update that fails midway.
  
• A database upgrade fails or an upgrade script goes awry.
  
• A complete database recovery after a media failure cannot succeed because you do not have all of the needed redo logs or incremental backups.