Wednesday, April 17, 2013

Snapshot standby database

Oracle 11g has a feature in this area called : Snapshot standby database. A Snapshot Standby Database is a fully update-able standby database that is created by converting a physical standby database into a snapshot standby database for read and write operation for a short period of time thus making it possible to process transactions independently of the primary database


These steps applies to Oracle Server - Enterprise Edition - Version 11.1.0.6 to 11.2.0.2 [Release 11.1 to 11.2]

As per the MOS ID 443720.1  Snapshot database has following characteristics

1. Snapshot standby database continues to receive and archive redo data from primary but does not archive it

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.


Steps to convert the Physical Standby to Snapshot Standby Database

1) If not already configured , configure flash recovery area as given below

SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE=5G;


SQL>  ALTER SYSTEM SET DB_RECOVERY_FILE_DEST='/u01/ora/flashback'

2) Bring the physical standby database to mount stage. 

SQL> SHUTDOWN IMMEDIATE;

SQL> STARTUP MOUNT;

3) Stop managed recovery if it is active. 

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL; 

4) Convert physical standby database to snapshot standby database.

ALTER DATABASE CONVERT TO SNAPSHOT STANDBY; 

The database is dismounted during conversion and must be restarted.


SQL> STARTUP

Once the database is restarted  any transaction can be executed .

SQL> select open_mode,database_role from v$database;

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



Steps to convert Snapshot Standby Database back to Physical Standby



SQL> SHUTDOWN IMMEDIATE;

SQL> STARTUP MOUNT;


SQL>ALTER DATABASE CONVERT TO PHYSICAL STANDBY;

SQL> SHUTDOWN IMMEDIATE;

SQL> STARTUP MOUNT;

SQL> select open_mode,database_role from v$database;

OPEN_MODE  DATABASE_ROLE
---------- ----------------
MOUNTED    PHYSICAL STANDBY 


Start the media recovery process.


SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT; 



Or for Active dataguard


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



Once a snapshot standby database has been converted back into a physical standby database and restarted, Redo Apply can be started and all redo received by the snapshot standby database will be applied to the physical standby database.

Further reading:

BASH-DBA: Managing a Physical Standby Database
BASH-DBA: Monitoring Primary and Physical Standby Databases
BASH-DBA: Open Physical Standby For Read Write Testing and ...
BASH-DBA: Oracle Data Guard : Synchronous vs. Asynchronous ...

No comments:

Post a Comment