Wednesday, April 17, 2013

Open Physical Standby For Read Write Testing and Flashback

Here I am discussing the steps to open the Standby database in read write mode testing purposes and then move it back to standby database using the flashback technology. 

In Standby database 

A ) Set up a flash recovery area.

B )  Make sure to give enough space for to FRA

SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE=5G;

SQL>  ALTER SYSTEM SET DB_RECOVERY_FILE_DEST='/u01/ora/flashback'

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

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL; 

SQL> CREATE RESTORE POINT restore_point_standby GUARANTEE FLASHBACK DATABASE; 

To Confirim the details of restore point, query the below

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

In Primary Database 

Switch logs so the SCN of the restore point is archived on the physical standby database

SQL> ALTER SYSTEM ARCHIVE LOG CURRENT; 

Defer log archive destinations pointing to the standby 

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=DEFER; 

In Standby database 

A ) Activate the physical standby database:

SQL> ALTER DATABASE ACTIVATE STANDBY DATABASE; 

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; 

Revert the active standby database back to Physical standby database 

Once done with your testing

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

SQL> SHUTDOWN IMMEDIATE;

SQL> STARTUP MOUNT;

SQL> FLASHBACK DATABASE TO RESTORE POINT restore_point_standby ; 

SQL> select controlfile_type from v$database;

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

B ) Convert to Standby database

SQL> ALTER DATABASE CONVERT TO PHYSICAL STANDBY; 

SQL> SHUTDOWN IMMEDIATE;

SQL> STARTUP MOUNT;


SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT; 

SQL> select controlfile_type from v$database;
CONTROL
--------------
STANDBY 

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

In Primary database 

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

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

In Standby database 

A ) Open the database in Read only mode

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL; 

SQL> ALTER DATABASE OPEN READ ONLY;

B ) Drop the restore point


SQL> SHUTDOWN IMMEDIATE;

SQL> STARTUP MOUNT;


SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT; 

SQL> DROP RESTORE POINT restore_point_standby ; 



No comments:

Post a Comment