The Snapshot standby is an Oracle 11g feature following which a physical standby database can be used in Read-Write mode for testing purposes mainly for short period of time.

Characteristics : (Ref Metalink Note: 443720.1)

1. Snapshot standby database receives and archives, but does not apply the redo data.

2. Redo data received from the primary database is applied automatically once it is converted back into a physical standby database.

3. Data from the primary database is always protected as the archives are being received and stored in place.

4. All local updates will be discarded when snapshot database is converted back to physical standby database.

5. If the primary database moves to new database branch (for example, because of a Flashback Database or an OPEN RESETLOGS), the snapshot standby database will continue accepting redo from new database branch.

6. 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.

7. After a switchover or failover between the primary database and one of the physical or logical standby databases in a configuration, the snapshot standby database can receive redo data from the new primary database after the role transition.

8. Snapshot standby database cannot be the only standby database in a Maximum Protection Data Guard configuration.
Once the snapshot standby is activated this database diverges from its primary database over time because redo data from the primary database is not applied. Again local updates to the snapshot standby database will cause additional divergence.

 

Steps for converting the Physical Standby DB to Snapshot Standby:

 

Login to Physical Standby Database “/ as sysdba”

 

Note: In case of RAC setup of Standby Database, shutdown all standby database instances but one (preferred choice: one on primary Server):

 

—- Start Blackout

emctl start blackout  LIB_DR_TEST –nodeLevel ;

 

1). Generic checks at Standby database:

 

SQL > Select DB_UNIQUE_NAME, OPEN_MODE, DATABASE_ROLE from v$database

2  /

 

DB_UNIQUE_NAME                 OPEN_MODE            DATABASE_ROLE

—————————— ——————– —————-

mibh1                          MOUNTED              PHYSICAL STANDBY

 

SQL> Show parameter db_recovery_file_dest

 

NAME                                 TYPE        VALUE

———————————— ———– ——————————

db_recovery_file_dest                string      +FLASH_TST

db_recovery_file_dest_size           big integer 24800M

SQL>

 

2). Cancel the MRP.

 

SQL> Alter database recover managed standby database cancel ;

 

Database altered.

 

 

3). Convert the standby database to snapshot standby mode and check its role:

 

SQL> alter database convert to snapshot standby ;

 

Database altered.

 

Elapsed: 00:00:03.08

SQL> Select DB_UNIQUE_NAME, OPEN_MODE, DATABASE_ROLE from v$database

2  /

 

DB_UNIQUE_NAME                 OPEN_MODE            DATABASE_ROLE

—————————— ——————– —————-

mibh1                          MOUNTED              SNAPSHOT STANDBY

 

4). Shutdown and start the database in READ-WRITE Mode:

 

SQL> shutdown immediate ;

ORA-01109: database not open

 

 

SQL> startup

ORACLE instance started.

 

SQL> Select DB_UNIQUE_NAME, OPEN_MODE, DATABASE_ROLE from v$database ;

 

DB_UNIQUE_NAME                 OPEN_MODE            DATABASE_ROLE

—————————— ——————– —————-

mibh1                          READ WRITE           SNAPSHOT STANDBY

 

1 row selected.

 

Elapsed: 00:00:00.05

 

5). User activity: connect as some user and perform some DMLs

 

 

Prepare to Convert back to Physical Standby:

 

6). Shutdown and start the Snapshot Standby DB in “mount” state

 

SQL> conn / as sysdba

Connected.

 

SQL> shutdown immediate

Database closed.

 

SQL> startup mount

ORACLE instance started.

 

SQL> Select DB_UNIQUE_NAME, OPEN_MODE, DATABASE_ROLE from v$database

2  /

 

DB_UNIQUE_NAME                 OPEN_MODE            DATABASE_ROLE

—————————— ——————– —————-

mibh1                          MOUNTED              SNAPSHOT STANDBY

 

1 row selected.

 

Elapsed: 00:00:00.02

 

7). Convert the DB back to PHYSICAL STANDBY:

 

SQL> ALTER DATABASE CONVERT TO PHYSICAL STANDBY;

 

Database altered.

 

8). Mount the DB as Standby database and start MRP

 

SQL> shutdown immediate

ORA-01507: database not mounted

 

 

SQL> startup nomount

ORACLE instance started.

 

SQL> Alter database mount standby database ;

 

Database altered.

 

Elapsed: 00:00:05.33

 

 

9). Start MRP, sample command:

 

SQL> alter database recover managed standby database disconnect;

 

Database altered.

 

Elapsed: 00:00:07.05

 

10). Generic checks:

 

SQL> Select DB_UNIQUE_NAME, OPEN_MODE, DATABASE_ROLE from v$database

2  /

 

DB_UNIQUE_NAME                 OPEN_MODE            DATABASE_ROLE

—————————— ——————– —————-

mibh1                          MOUNTED              PHYSICAL STANDBY

 

1 row selected.

 

Elapsed: 00:00:00.01

 

—- Stop Blackout

 

emctl stop blackout MIB_DR_TEST