Configure Snapshot Standby

Covert Physical Standby database to Snapshot Standby database R/W mode.

Table of Contents
___________________________________________________________________________________________________

0. Overview
1. Environment
2. Configure Standby Database (DELL_DG)
3. Verify archive log GAP
4. Verify Flash Recovery Area and Flashback database
5. Cancel MRP on Standby
6. Covert to snapshot standby from physical standby
7. Testing
___________________________________________________________________________________________________


0. Overview

Snapshot standby is a feature in Oracle 11g that allows doing a read-write operation on the standby database.

To Configure snapshot standby, first we need to create physical standby and then we will convert from physical standby to Snapshot standby

Developers want to testing on Fresh Live Data, but DBA can't allow them to test on Primary, then how to archive developer requirement.. DBA can convert physical standby to snapshot standby in R/W mode. Hence Developers can make their changes in Snapshot databases.

Whatever changes done on snapshot standby will be flushed out once convert back to physical standby database from snapshot standby.

Primary database changes will not applied to snapshot standby database why because there is no MRP process running snapshot database.

No need to enable flashback database.

Only need to have db_recovery_file_dest and db_recovery_file_dest_size on physical standby.


1. Environment

PRIMARY: (DELL)

Platform          : Linuxx86_64
Server Name       : RAC1.RAJASEKHAR.COM, IP: 192.168.2.101
DB Version        : Oracle 11.2.0.4
File system	  : Normal
DB Name           : DELL
DB_UNIQUE_NAME	  : DELL
Flashback         : Disabled
Oracle Home Path  : /u01/app/oracle/product/11.2.0.4

STANDBY: (DELL_DG)

		
Platform          : Linuxx86_64
Server Name       : RAC2.RAJASEKHAR.COM, IP: 192.168.2.102
DB Version        : Oracle 11.2.0.4
File system       : Normal
DB Name           : DELL
DB_UNIQUE_NAME	  : DELL_DG
Flashback         : Disabled
Oracle Home Path  : /u01/app/oracle/product/11.2.0.4


2. Configure Standby Database (DELL_DG)

This is sample document, database names will be different

http://www.br8dba.com/active-dataguard/


3. Verify archive log GAP

On Primary

SQL> select status,instance_name,database_role,open_mode from v$database,v$Instance;

STATUS       INSTANCE_NAME    DATABASE_ROLE    OPEN_MODE
------------ ---------------- ---------------- --------------------
OPEN         DELL             PRIMARY          READ WRITE

SQL> select max(sequence#) from v$archived_log where archived='YES';

MAX(SEQUENCE#)
--------------
            57  <----

SQL>

On Standby

SQL> select status,instance_name,database_role,open_mode from v$database,v$Instance;

STATUS       INSTANCE_NAME    DATABASE_ROLE    OPEN_MODE
------------ ---------------- ---------------- --------------------
OPEN         DELL_DG          PHYSICAL STANDBY READ ONLY WITH APPLY

SQL> select max(sequence#) from v$archived_log where applied='YES';

MAX(SEQUENCE#)
--------------
            57 <-----

SQL>


4. Verify Flash Recovery Area and Flashback database

Primary

SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
NO <------

SQL>

SQL> show parameter db_recovery_file_dest

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      /u02/oracle/oradata/fast_recovery_area
db_recovery_file_dest_size           big integer 3G
SQL>

Standby

SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
NO

SQL>
SQL> show parameter db_recovery_file_dest

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      /u02/oracle/oradata/fast_recovery_area
db_recovery_file_dest_size           big integer 3G
SQL>


5. Cancel MRP on Standby

SQL> select status,instance_name,database_role,open_mode from v$database,v$Instance;

STATUS       INSTANCE_NAME    DATABASE_ROLE    OPEN_MODE
------------ ---------------- ---------------- --------------------
OPEN         DELL_DG          PHYSICAL STANDBY READ ONLY WITH APPLY

SQL> alter database recover managed standby database cancel;

Database altered.

SQL>


6. Covert to snapshot standby from physical standby

SQL> alter database convert to snapshot standby;

Database altered.

SQL> select status,instance_name,database_role,open_mode from v$database,v$Instance;

STATUS       INSTANCE_NAME    DATABASE_ROLE    OPEN_MODE
------------ ---------------- ---------------- --------------------
MOUNTED      DELL_DG          SNAPSHOT STANDBY MOUNTED

SQL>
SQL> alter database open;

Database altered.

SQL> select status,instance_name,database_role,open_mode from v$database,v$Instance;

STATUS       INSTANCE_NAME    DATABASE_ROLE    OPEN_MODE
------------ ---------------- ---------------- --------------------
OPEN         DELL_DG          SNAPSHOT STANDBY READ WRITE

SQL>

Alert log file:

Sat Dec 29 14:52:49 2018
alter database recover managed standby database cancel
Sat Dec 29 14:52:50 2018
MRP0: Background Media Recovery cancelled with status 16037
Errors in file /u01/app/oracle/diag/rdbms/dell_dg/DELL_DG/trace/DELL_DG_mrp0_3982.trc:
ORA-16037: user requested cancel of managed recovery operation
Recovery interrupted!
MRP0: Background Media Recovery process shutdown (DELL_DG)
Managed Standby Recovery Canceled (DELL_DG)
Completed: alter database recover managed standby database cancel
Sat Dec 29 15:15:16 2018
alter database convert to snapshot standby
Starting background process RVWR
Sat Dec 29 15:15:16 2018
RVWR started with pid=24, OS id=25202
Allocated 4194304 bytes in shared pool for flashback generation buffer
Created guaranteed restore point SNAPSHOT_STANDBY_REQUIRED_12/29/2018 15:15:16
Killing 4 processes with pids 4048,4044,4046,4050 (all RFS) in order to disallow current and future RFS connections. Requested by OS process 8813
All dispatchers and shared servers shutdown
CLOSE: killing server sessions.
CLOSE: all sessions shutdown successfully.
Sat Dec 29 15:15:20 2018
SMON: disabling cache recovery
Begin: Standby Redo Logfile archival
End: Standby Redo Logfile archival
RESETLOGS after incomplete recovery UNTIL CHANGE 1228396
Resetting resetlogs activation ID 3965561545 (0xec5daac9)
Online log /u02/oracle/oradata/DELL_DG/redo01.log: Thread 1 Group 1 was previously cleared
Online log /u02/oracle/oradata/DELL_DG/redo02.log: Thread 1 Group 2 was previously cleared
Online log /u02/oracle/oradata/DELL_DG/redo03.log: Thread 1 Group 3 was previously cleared
Standby became primary SCN: 1228394
Sat Dec 29 15:15:20 2018
Setting recovery target incarnation to 4
AUDIT_TRAIL initialization parameter is changed back to its original value as specified in the parameter file.
CONVERT TO SNAPSHOT STANDBY: Complete - Database mounted as snapshot standby
Completed: alter database convert to snapshot standby
Sat Dec 29 15:16:01 2018
ARC1: Becoming the 'no SRL' ARCH
Sat Dec 29 15:16:22 2018
idle dispatcher 'D000' terminated, pid = (17, 1)
Sat Dec 29 15:16:30 2018
alter database open
Data Guard Broker initializing...
Data Guard Broker initialization complete
Sat Dec 29 15:16:30 2018
Assigning activation ID 3966853654 (0xec716216)
Thread 1 opened at log sequence 1
  Current log# 1 seq# 1 mem# 0: /u02/oracle/oradata/DELL_DG/redo01.log
Successful open of redo thread 1
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Sat Dec 29 15:16:30 2018
SMON: enabling cache recovery
Sat Dec 29 15:16:30 2018
NSA2 started with pid=25, OS id=25215
[8813] Successfully onlined Undo Tablespace 2.
Undo initialization finished serial:0 start:26075724 end:26075874 diff:150 (1 seconds)
Dictionary check beginning
Dictionary check complete
Verifying file header compatibility for 11g tablespace encryption..
Verifying 11g file header compatibility for tablespace encryption completed
SMON: enabling tx recovery
Starting background process SMCO
Database Characterset is WE8MSWIN1252
Sat Dec 29 15:16:30 2018
SMCO started with pid=26, OS id=25217
No Resource Manager plan active
Starting background process QMNC
Sat Dec 29 15:16:31 2018
QMNC started with pid=27, OS id=25219
LOGSTDBY: Validating controlfile with logical metadata
LOGSTDBY: Validation complete
Completed: alter database open


7. Testing

SQL> select status,instance_name,database_role,open_mode from v$database,v$Instance;

STATUS       INSTANCE_NAME    DATABASE_ROLE    OPEN_MODE
------------ ---------------- ---------------- --------------------
OPEN         DELL_DG          SNAPSHOT STANDBY READ WRITE

SQL> create user raj identified by raj default tablespace users temporary tablespace temp;

User created.

SQL> grant connect, resource to raj;

Grant succeeded.

SQL> conn raj/raj;
Connected.
SQL> create table test (a number);

Table created.

SQL> insert into test values (1);

1 row created.

SQL> /

1 row created.

SQL> /

1 row created.

SQL> commit;

Commit complete.

SQL> select count(*) from test;

  COUNT(*)
----------
         3

SQL>

=================================== OR ===================================

How to do perform the same using Dataguard Broker

Using DG Broker – Convert Physical standby to Snapshot standby

DGMGRL> show configuration;

Configuration - DELL

  Protection Mode: MaxPerformance
  Databases:
    DELL    - Primary database
    DELL_DG - Physical standby database <----

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL>


DGMGRL> CONVERT DATABASE "DELL_DG" TO SNAPSHOT STANDBY;
Converting database "DELL_DG" to a Snapshot Standby database, please wait...
Database "DELL_DG" converted successfully
DGMGRL>

DGMGRL> show configuration;

Configuration - DELL

  Protection Mode: MaxPerformance
  Databases:
    DELL    - Primary database
    DELL_DG - Snapshot standby database <----

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL>

Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.

Thank you,
Rajasekhar Amudala
Email: br8dba@gmail.com
WhatsApp No: +65-94693551

2 thoughts on “Configure Snapshot Standby

  1. For converting physical standby database insto snapshot standby, what would be pre-requisite..

    As i read somewhere dataguard broker must be configure without DG broker we can not do this.. Is it true??

    1. For converting physical standby database insto snapshot standby, what would be pre-requisite..?

      Ans:

      1. Configure Physical Standby
      2. Enable Flash recovery area …already mentioned in the document under overview section ..

      Thank you,
      Rajasekhar

Leave a Reply

Your email address will not be published. Required fields are marked *