Snapshot Standby - Step by Step Configuration

Snapshot standby database is a fully updateable standby database created by converting a physical standby database into a snapshot standby 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.

1. Configured flash recovery area, check database flashback enable, must enable it if not.

SQL> alter system set db_recovery_file_dest_size = 2G;
SQL> alter system set db_recovery_file_dest= 'D:\app\db_recov';

SQL> SELECT FLASHBACK_ON FROM V$DATABASE;

2. Cancel managed recovery mode on physical standby database

SQL> alter database recover managed standby database cancel;

3. Convert physical standby database to snapshot standby database

SQL> alter database convert to snapshot standby;

4. Recycle the database:

SQL> shutdown immediate
SQL> startup
SQL> select open_mode, database_role from v$database;

You can see database enable for read/write operations

5. Convert snapshot standby database back to physical standby database:

SQL> connect / as sysdba
SQL> shutdown immediate
SQL> startup mount
SQL> alter database convert to physical standby;
SQL> shutdown

Start managed recovery process:

SQL> alter database recover managed standby database disconnect;
SQL> select open_mode, database_role from v$database;