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 size of Recovery area
Alter system set db_recovery_file_dest_size=
--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;