Snapshot Standby Database

In Oracle Database 11g, physical standby database can be temporarily converted into an up-datable one called Snapshot Standby Database.

In that mode, you can make changes to database. Once the test is complete, you can rollback the changes made for testing and convert the database into a standby undergoing the normal recovery. This is accomplished by creating a restore point in the database, using the Flashback database feature to flashback to that point and undo all the changes.

Steps:

Configure the flash recovery area, if it is not already done.

SQL> alter system set db_recovery_file_dest_size = 2G;

System altered.

SQL> alter system set db_recovery_file_dest= '+FRADG';

System altered.

Stop the recovery.

SQL> alter database recover managed standby database cancel;

Database altered.

Convert this standby database to snapshot standby using command

SQL> alter database convert to snapshot standby;

Database altered.

Now recycle the database

SQL> shutdown immediate
...

SQL> startup

ORACLE instance started.

Database is now open for read/write operations

SQL> select open_mode, database_role from v$database;

OPEN_MODE DATABASE_ROLE
---------- ----------------
READ WRITE SNAPSHOT STANDBY

After your testing is completed, you would want to convert the snapshot standby database back to a regular physical standby database by following the steps below

SQL> connect / as sysdba
Connected.
SQL> shutdown immediate

SQL> startup mount

...
Database mounted.

SQL> alter database convert to physical standby;

Database altered.

Now shutdown, mount the database and start managed recovery.

SQL> shutdown

ORACLE instance shut down.

SQL> startup mount
ORACLE instance started.
...
Database mounted.

Start the managed recovery process

SQL> alter database recover managed standby database disconnect;

Now the standby database is back in managed recovery mode. When the database was in snapshot standby mode, the archived logs from primary were not applied to it. They will be applied now.