Oracle 10g/11g:Open(READ-WRITE) physical standby database in flashback mode

Oracle 10g/11g:Open(READ-WRITE) physical standby database in flashback mode 

 In this post, We will do DR Drill activity by open physical standby database in flashback mode.

Sometimes it is require when we need to check our dr is sufficient to handle the load same as production and also before switchover we can check if any problems in physical standby.

It is very simple and handy by enabling flashback mode on physical standby database.Once task of testing (DR DRILL) finishes we flash back database to guarantee restore point.
After testing we can start real time sync from production to DR Server.

Follow the easy steps on primary and standby database.

Please note that there is no rollback if any error on DR.


 1.On Standby Database

A) Set up a flash recovery area. (Skip this step if FRA already configured)

 If Flash Recovery Area (FRA ) is not configured in the standby then enable it and make sure to give enough space for to FRA

SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE=100G;
SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST='/oracle/backup/rman’;

B) Cancel Redo Apply and create a guaranteed restore point.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
SQL> CREATE RESTORE POINT Standby_flashback_testing GUARANTEE FLASHBACK DATABASE; 

To confirm the details of restore point and its scn and time stamp run

SQL> select NAME,SCN,TIME from v$restore_point;

NAME                                                               SCN                    TIME
--------------------------------------------------     -------------    ------------------------------
STANDBY_FLASHBACK_TESTING     22607810    12-APR-09 01.10.21.000000000 P 

 2.On Primary Database

A) On the primary database, switch logs so the SCN of the restore point will be archived on the physical standby database. When using standby redo log files, this step is essential to ensure the database can be properly flashed back to the restore point.

SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;

B) Defer log archive destinations pointing to the standby that will be activated.

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=DEFER;(Check the correct dest)

3.On Standby Database.

A) Activate the physical standby database:

SQL> ALTER DATABASE ACTIVATE STANDBY DATABASE;

Once its done you can check the controlfile status will be changed from Standby to Current

SQL> select CONTROLFILE_TYPE from v$database;

CONTROL
-------
CURRENT

B) Then open the database.

SQL> ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE;
SQL> ALTER DATABASE OPEN;

 4.On Standby Database.

Test Application by pointing standby database.

After completion of testing follow next steps for revert to physical standby.

5.On Standby Database.

A) Revert the active standby database back to Physical standby database

    A1. Mount the database.
    A2. Flashback the database to restore point.

SQL> STARTUP MOUNT FORCE;

ORACLE instance started.
Total System Global Area  289406976 bytes
Fixed Size                  1290208 bytes
Variable Size             159383584 bytes
Database Buffers          125829120 bytes
Redo Buffers                2904064 bytes
Database mounted.

SQL> FLASHBACK DATABASE TO RESTORE POINT Standby_flashback_testing ;

You can confirm the same by checking the controlfile status. It will be now backup controlfile

SQL> select controlfile_type from v$database;

CONTROL
--------------
BACKUP

B ) Convert to Standby database

SQL> ALTER DATABASE CONVERT TO PHYSICAL STANDBY;
SQL> STARTUP MOUNT FORCE;
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT; 

SQL> select controlfile_type from v$database;

CONTROL
--------------
STANDBY

6.On Standby Database.

A) Put the standby database in managed recovery mode. Let archive gap resolution fetch all missing archived redo log files and allow Redo Apply to apply the gap. 

 7.On Primary Database.

A ) Re-enable archiving to the physical standby database:

SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE; 

8.On Standby Database.

A ) Open the database in Read only mode and ensure that all the transaction done in active mode are no more

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
SQL> ALTER DATABASE OPEN READ ONLY;  

B ) Drop the restore point

SQL> STARTUP FORCE MOUNT;
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;
SQL> DROP RESTORE POINT Standby_flashback_testing ;