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
___________________________________________________________________________________________________
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.
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/
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>
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
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: +