Implementing Snapshot Standby

What is a Snapshot Standby database?

In simple words, it is an updatable copy of physical standby database. To configure snapshot standby, we need to configure physical standby, which can be converted to snapshot standby and vice versa. This is typically helpful in a scenario where we need to test an application with actual production data, without impacting the production environment.

A snapshot standby database receive archive logs from primary database , but it does not apply them. Redo data received from the primary database is applied when a snapshot standby database is converted back into a physical standby database. All local updates to the snapshot standby database are discarded.

Current physical standby database configuration details

DGMGRL> show configuration
Configuration - dg1
  Protection Mode: MaxPerformance
  Databases:
    DWHDB - Primary database
    STDBY - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS

Before converting, make sure flashback & FRA is configured on standby database. 

SQL> select flashback_on from v$database;
FLASHBACK_ON
------------------
NO

Following steps can be used to enable flashback

SQL> alter database recover managed standby database cancel;
OR
DGMGRL> edit database 'STDBY' set state='LOG-APPLY-OFF';
SQL> alter database flashback on;
Database altered.
SQL> alter database recover managed standby database disconnect;
OR
DGMGRL> edit database 'STDBY' set state='ONLINE';

SQL> select name, database_role, flashback_on, open_mode from v$database;
NAME      DATABASE_ROLE    FLASHBACK_ON       OPEN_MODE
--------- ---------------- ------------------ ---------
DWHDB     PHYSICAL STANDBY YES                MOUNTED
Steps to convert Physical Standby Database to the Snapshot Standby Database

1) Stop managed recovery if it is active. 

SQL> alter database recover managed standby database cancel;
OR
DGMGRL> edit database 'STDBY' set state='LOG-APPLY-OFF';

2) Convert physical standby database to snapshot standby database. 

SQL> alter database convert to snapshot standby;  
OR
DGMGRL> convert database 'STDBY' to snapshot standby;

3) Verify the configuration

DGMGRL>  show configuration
Configuration - dg1
  Protection Mode: MaxPerformance
  Databases:
    DWHDB - Primary database
    STDBY - Snapshot standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS

4) Check the database mode

SQL> select name, database_role, flashback_on, open_mode from v$database;
NAME      DATABASE_ROLE    FLASHBACK_ON       OPEN_MODE
--------- ---------------- ------------------ --------------------
DWHDB     PHYSICAL STANDBY YES                READ WRITE

5) Check the incarnation status

SQL> select  incarnation#, resetlogs_change#, status, resetlogs_id, 
flashback_database_allowed from  v$database_incarnation;
INCARNATION# RESETLOGS_CHANGE# STATUS  RESETLOGS_ID FLASHBACK_DATABASE_ALLOWED
------------ ----------------- ------- ------------ --------------------------
           1                 1 PARENT     737324718 YES
           2           2500798 CURRENT    739715228 YES

One can perform read/write operations on the snapshot standby. Once done, we can convert it back to the physical standby. At this point physical standby will start applying the accumulated received archive log files.

It revert back the changes we made, using the Flashback feature.  Before converting the database to the snapshoot standby, it creates a guaranteed restore point, which will be used to revert back the database.

SQL> select  name, preserved, storage_size, guarantee_flashback_database 
from v$restore_point;
NAME                                               PRE STORAGE_SIZE GUA
-------------------------------------------------- --- ------------ ---
SNAPSHOT_STANDBY_REQUIRED_01/06/2011 12:27:05      YES     31875072 YES

 

Steps to convert Snapshot Standby Database to the Physical Standby Database

1) Convert snapshot standby database to physical standby database. 

SQL> alter database convert to physical standby; 
OR
DGMGRL> convert database 'STDBY' to physical standby;

2) Verify the configuration

DGMGRL> show configuration
Configuration - dg1
  Protection Mode: MaxPerformance
  Databases:
    DWHDB - Primary database
    STDBY - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS

3) Check the database mode

SQL> select name,database_role, flashback_on, open_mode from v$database;
NAME      DATABASE_ROLE    FLASHBACK_ON       OPEN_MODE
--------- ---------------- ------------------ --------------------
DWHDB     PHYSICAL STANDBY YES                MOUNTED

 

4) Check the incarnation status

SQL> select  incarnation#, resetlogs_change#, status, resetlogs_id, 
flashback_database_allowed from  v$database_incarnation;
INCARNATION# RESETLOGS_CHANGE# STATUS  RESETLOGS_ID FLASHBACK_DATABASE_ALLOWED
------------ ----------------- ------- ------------ --------------------------
           1                 1 CURRENT    737324718 YES
           2           2500798 ORPHAN     739715228 YES

5) Start managed recovery (if not active)

SQL> alter database recover managed standby database disconnect;
OR
DGMGRL> edit database 'STDBY' set state='ONLINE';

DGMGRL> show database 'STDBY'
Database - STDBY
  Role:            PHYSICAL STANDBY
  Intended State:  APPLY-ON
  Transport Lag:   0 seconds
  Apply Lag:       0 seconds
  Real Time Query: OFF
  Instance(s):
    STDBY
Database Status:
SUCCESS
 
Advertisements
This entry was posted in Oracle Data Guard, Oracle Snapshot Standby and tagged . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s