Convert Physical standby database to active standby for testing

To convert physical standby database to primary for testing scenarios

Step 1 - In Standby database

Set up a flash recovery area.

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

ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE=500G;
ALTER SYSTEM SET DB_RECOVERY_FILE_DEST='+RECO_DG';

Cancel Redo Apply and create a guaranteed restore point.

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
CREATE RESTORE POINT testing GUARANTEE FLASHBACK DATABASE;

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

 select NAME,SCN,TIME from v$restore_point;

NAME                                                               SCN                    TIME
--------------------------------------------------     -------------    ------------------------------
TESTING     2254478210    11-JUN-16 01.10.21.000000000 P


Step 2 - In Primary Database

ALTER SYSTEM ARCHIVE LOG CURRENT;

Optional - Defer log archive destinations pointing to the standby that will be activated.

--ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=DEFER;

Step 3 - In Standby database

Activate the physical standby database:

ALTER DATABASE ACTIVATE STANDBY DATABASE;

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

select CONTROLFILE_TYPE from v$database;

CONTROL
-------
CURRENT


Then open the database.

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

Revert the active standby database back to Physical standby database

Mount the database.
Flashback the database to restore point.

STARTUP MOUNT;

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.

FLASHBACK DATABASE TO RESTORE POINT testing ;


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

select controlfile_type from v$database;

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

Convert to Standby database

ALTER DATABASE CONVERT TO PHYSICAL STANDBY;
STARTUP MOUNT;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;
select controlfile_type from v$database;

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


In Primary database

Optional(If deferred before) - Re-enable archiving to the physical standby database:

ALTER SYSTEM ARCHIVE LOG CURRENT;
--ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;

In Standby database

Drop the restore point

STARTUP FORCE MOUNT;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;
DROP RESTORE POINT testing ;

Note: Archivelogs will not be shipped when the db is in read/write mode from primary. So make sure you are not deleting the archivelogs in primary until they are shipped here. If you have space constraints or archive logs are purged by a job, then flash back to restore point and do a incremental backup on primary and recover using it based on SCN.

Also during this time flashback_on parameter of database is set to NO