Snapshot Standby Database

A snapshot standby database is read-write standby database that is created by converting a physical standby database into a snapshot standby database.

A snapshot Standby is open in the read-write mode and can execute DML transactions independently of the primary database.And also it maintains protection by continuing to receive redo data from the production database, archiving it for later use.

Redo data received from the primary database will apply automatically once it is converted back to a physical standby database.

Steps to Convert Physical Standby to Snapshot Standby:

Step 1: Configure flash recovery area as given below

i) Set the size for recovery area.

Alter system set db_recovery_file_dest_size=<size>

ii) Set Flash recovery area.

Alter system set db_recovery_file_dest=<path>

Step 2: Mount the Physical standby database

Step 3: Stop managed recovery if it is active.

Step 4: Convert physical standby database to snapshot standby database.

ALTER DATABASE CONVERT TO SNAPSHOT STANDBY;

Step 5: The database is dismounted during conversion and must be restarted.

Step 6: Once the database is restarted any transaction can be executed .

5. SQL> select open_mode,database_role from v$database;

OPEN_MODE DATABASE_ROLE

---------- ----------------

READ WRITE SNAPSHOT STANDBY

When we issue ALTER DATABASE CONVERT TO SNAPSHOT STANDBY an implicit guaranteed restore point is created and this GRP is used to flashback a snapshot standby to its original state when it is converted back into a physical standby database.

Steps to Convert Snapshot Standby to Physical Standby:

Step 1: Shutdown the snapshot standby database.

Step 2: Mount the snapshot database.

Step 3: Convert back to physical standby database

ALTER DATABASE CONVERT TO PHYSICAL STANDBY;

Step 4: Shutdown the database and mount it .

SQL> select open_mode,database_role from v$database;

OPEN_MODE DATABASE_ROLE

---------- ----------------

MOUNTED PHYSICAL STANDBY

Step 5: Start the media recovery process.

Once a snapshot standby database converted back to physical standby database and restarted, start Redo Apply and all redo received by the snapshot standby database will be applied to the physical standby database.

FOR DATAGUAR BROKER - DGMGRL

DGMGRL> CONVERT DATABASE <db_unique_name> TO {SNAPSHOT | PHYSICAL} STANDBY;