Oracle Snapshot Standby Database

Starting 11g, we can convert a physical standby database into a snapshot standby database. It actually means that we can open a standby database in read-write mode for transactions processing and then later we can convert this database back to standby database. This is accomplished by enabling the database flashback on standby database;
as this will be the key to rollback the opened (in read-write mode) standby database back to its physical standby state. Snapshot Standby feature is beneficial in the scenarios when we need to perform some testing on the production database and we don’t have enough time or resources to build a clone of the production database. So, we can open the standby as snapshot standby and then convert it back to standby after out testing.

Steps to convert a physical standby database to a snapshot standby and then convert it back to a physical standby are explained in the following. In case of RAC, all these steps can be performed from any node, and flash recovery area should be on a shared location and accessible to all RAC instances.

1) First of all we would need to enable the flashback if it is not enabled already. Prior to do this, we would also need to add flash recovery area with sufficient free space which should be enough to store flashback logs as long as standby database remains open for testing/transactions.. For this example, I am using an ASM diskgroup ORAFRA for flash recovery area.

SQL> ALTER SYSTEM SET db_recovery_file_dest_size=40g sid='*'; SQL> ALTER SYSTEM SET db_recovery_file_dest= '+ORAFRA' sid='*';

2) Enable flashback with sufficient amount of retention period. This period should be at least equal to the amount of time this database will remain open for testing and transactions. For RAC, execute these from

SQL> ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET=4320 sid='*'; # 3 days (in minutes) SQL> ALTER DATABASE FLASHBACK ON;

3) Cancel the managed recovery and open the snapshot standby database. In case of RAC, shutdown all instances except 1 from where you execute following command

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL; SQL> ALTER DATABASE CONVERT TO SNAPSHOT STANDBY; Database altered. SQL>  select open_mode,database_role from v$database; OPEN_MODE            DATABASE_ROLE -------------------- ---------------- MOUNTED              SNAPSHOT STANDBY SQL> SHUTDOWN IMMEDIATE; ORA-01109: database not open Database dismounted. ORACLE instance shut down. SQL>STARTUP ORACLE instance started. Total System Global Area 1610612736 bytes Fixed Size                  2066080 bytes Variable Size             385878368 bytes Database Buffers         1207959552 bytes Redo Buffers               14708736 bytes Database mounted. Database opened.

Now database is open and any kind of testing or transactions can be done on this database. Once testing completes, we can use following steps to convert this database back to a physical standby database.

1) Convert database back to standby. In case of RAC, shutdown all instances except 1 where you execute following command. Startup instance in mount mode first before executing this statement.

SQL> SHUTDOWN IMMEDIATE SQL> STARTUP MOUNT SQL> ALTER DATABASE CONVERT TO PHYSICAL STANDBY;

2) Shutdown the instance and then startup mount (also mount all other instances in case of RAC)

SQL> SHUTDOWN IMMEDIATE ORA-01109: database not open Database dismounted. ORACLE instance shut down. SQL> STARTUP ORACLE instance started. Total System Global Area 1610612736 bytes Fixed Size                  2066080 bytes Variable Size             385878368 bytes Database Buffers         1207959552 bytes Redo Buffers               14708736 bytes Database mounted. Database open.

3) Start managed recovery.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

Related Article

·       Converting a FailedOver Standby Database Back to Standby Database