PHYSICAL STANDBY TO SNAPSHOT STANDBY or vice-versa in Dataguard Environment

Snapshot Standby Database
A snapshot standby database is a fully update-able standby database created by converting a physical standby database into a snapshot standby database.
A snapshot database is used for testing like real production and turn it back to Physical Standby. If any major change to application or in database configuration you can check performance of database by using snapshot database.
A snapshot standby database receives and archives, but does not apply, redo data from a primary database.
The redo data received from the primary database is applied once a snapshot standby database is converted back into a physical standby database, after discarding all local updates to the snapshot standby database.

Steps for convert PHYSICAL STANDBY TO SNAPSHOT STANDBY

1. Turn on Flashback

SQL> ALTER DATABASE FLASHBACK ON;

--Set the location of Recovery area
Alter system set db_recovery_file_dest=


2. Stop Redo Log Apply on Standby:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL; 


3. Convert Physical Standby to Snapshot Standby

SQL> ALTER DATABASE CONVERT TO SNAPSHOT STANDBY; 


4. Open & verify the Database in Read/Write Mode

SQL> ALTER DATABASE OPEN;

--Verify
SQL> select open_mode,database_role from v$database;
OPEN_MODE DATABASE_ROLE
---------- ----------------
READ WRITE SNAPSHOT STANDBY 


5. Take a note of the Snapshot if you wish

SQL> SELECT name, scn, time FROM v$restore_point; 

Note: Use the database for testing purpose as needed

Steps for convert the SNAPSHOT STANDBY TO PHYSICAL STANDBY

1. Shutdown the Database

SQL> SHUTDOWN IMMEDIATE; 

2. Mount the Database

SQL> STARTUP MOUNT; 

3. Convert Snapshot Standby to Physical Standby

SQL> ALTER DATABASE CONVERT TO PHYSICAL STANDBY; 

4. Shutdown the Database

SQL> SHUTDOWN IMMEDIATE; 

5. Startup in Nomount mode

SQL> STARTUP NOMOUNT; 

6. Mount the Physical Standby Database

SQL> ALTER DATABASE MOUNT STANDBY DATABASE; 

7. Start Redo Log Apply

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE THROUGH ALL SWITCHOVER DISCONNECT USING CURRENT LOGFILE;