Snapshot Standby Database - 11gR2

Prerequsite:
Configuring Oracle 11gR2 Data Guard - Physical Standby (without DG Broker) 

Brief:

Snapshot Standby is a new feature introduced in 11g that allows the standby database to be opened in read-write mode for real time testing. When switched back into standby mode, all changes made whilst in read-write mode are lost. This is achieved using flashback database, but the standby database does not need to have flashback database explicitly enabled to take advantage of this feature. Using the Flashback Database technology restore point is guaranteed to which the database can be later flashed back to.

A Snapshot Standby is opened in the read-write mode and hence it is possible to process transactions independently of the primary database. At the same time, it maintains protection by continuing to receive data from the production database, archiving it for later use.

Snapshot standby database cannot be the target of a switchover or failover. A snapshot standby database must first be converted back into a physical standby database before performing a role transition to it.

Steps: (Only on Standby)

1)  Check the status of flashback on standby.

select name,open_mode,database_role,db_unique_name, flashback_on from v$database;

NAME	OPEN_MODE	DATABASE_ROLE	DB_UNIQUE_NAME	FLASHBACK_ON
DUBAI	MOUNTED	PHYSICAL STANDBY	riyadh	NO

2) Make sure managed recovery is disabled.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Database altered.

3) Convert the standby to a snapshot standb. Observe flashback_on column in v$database also.

SQL> ALTER DATABASE CONVERT TO SNAPSHOT STANDBY;

Database altered.

select name,open_mode,database_role,db_unique_name, flashback_on from v$database;

NAME	OPEN_MODE	DATABASE_ROLE	DB_UNIQUE_NAME	FLASHBACK_ON
DUBAI	MOUNTED	SNAPSHOT STANDBY	riyadh	RESTORE POINT ONLY

 

4) Open the database and check the status of it.

SQL> ALTER DATABASE OPEN;
Database altered.

select name,open_mode,database_role,db_unique_name, flashback_on from v$database;

NAME	OPEN_MODE	DATABASE_ROLE	DB_UNIQUE_NAME	FLASHBACK_ON
DUBAI	READ WRITE	SNAPSHOT STANDBY	riyadh	RESTORE POINT ONLY

5) Check the system generated restore point

select NAME,SCN,TIME from v$restore_point;

NAME	SCN	TIME
SNAPSHOT_STANDBY_REQUIRED_05/22/2013 08:46:14	2037024	5/22/2013 08:46:14.000000000 AM

6) Now do some testing on the standby database 

create table mytest(c number)
insert into mytest values (1)
commit

select * from mytest

C
1

7) Now convert it back to the physical standby, losing all the changes made since the conversion to snapshot standby.
 SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 1068937216 bytes
Fixed Size                  2262048 bytes
Variable Size             281021408 bytes
Database Buffers          780140544 bytes
Redo Buffers                5513216 bytes
Database mounted.

SQL> alter database convert to physical standby;

Database altered.
SQL>  shutdown immediate;
ORA-01507: database not mounted


ORACLE instance shut down.
SQL>  startup nomount;
ORACLE instance started.

Total System Global Area 1068937216 bytes
Fixed Size                  2262048 bytes
Variable Size             281021408 bytes
Database Buffers          780140544 bytes
Redo Buffers                5513216 bytes
SQL> alter database mount standby database;

Database altered.

SQL> alter database recover managed standby database using current logfile disconnect from session;

Database altered.

select name,open_mode,database_role,db_unique_name, flashback_on from v$database;

NAME	OPEN_MODE	DATABASE_ROLE	DB_UNIQUE_NAME	FLASHBACK_ON
DUBAI	MOUNTED	PHYSICAL STANDBY	riyadh	NO

The standby is once again in managed recovery and archivelog shipping is resumed. Notice that flashback database is still not enabled.

try to select your table created in step 6
SQL> select * from mytest;
select * from mytest
              *
ERROR at line 1:
ORA-00942: table or view does not exist