Steps to convert PHYSICAL STANDBY TO SNAPSHOT STANDBY in Dataguard Environment

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; 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.