1. Current data guard configuration is as follows.
DGMGRL> show configuration
Configuration - db_dg
Protection Mode: MaxAvailability
Databases:
prod - Primary database
stdby - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS2. It is not necessary to have flashback enabled for snapshot to work. As per Oracle documentation "if Flashback database is disabled, it is automatically enabled during conversion to a snapshot standby database. The broker automatically restarts the database to the mounted state if it had been opened with Flashback Database disabled. No user action is
required". However, a fast recovery area must be configured on the physical standby. According to Oracle documentation "this is because a guaranteed restore point is created during the conversion process, and guaranteed restore points require a fast recovery area". For this post the flashback feature was deliberately turned off to verify that snapshot conversion can go through without it. However, it's good practice to have flashback enabled in a DG configuration. Turning off flashback is not part of converting to snapshot standby and no need to do the below steps. On primary
SQL> select flashback_on from v$database; FLASHBACK_ON ------------------ YES SQL> alter database flashback off; Database altered. SQL> select flashback_on from v$database; FLASHBACK_ON ------------------ NOOn standby
SQL> select flashback_on from v$database; FLASHBACK_ON ------------------ YES SQL> alter database flashback off; Database altered. SQL> select flashback_on from v$database; FLASHBACK_ON ------------------ NO3. Conversion to snapshot standby is done using the DGMGRL.
DGMGRL> convert database stdby to snapshot standby;
Converting database "stdby" to a Snapshot Standby database, please wait...
Database "stdby" converted successfully
DGMGRL> show configuration
Configuration - db_dg
Protection Mode: MaxAvailability
Databases:
prod - Primary database
stdby - Snapshot standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
Use of DGMGRL automate lot of the steps needed for conversion such as stopping the redo apply and initializing DG after the conversion. These steps could be identified by looking at the standby alert log.
Wed Sep 19 16:22:13 2018 ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL Wed Sep 19 16:22:13 2018 MRP0: Background Media Recovery cancelled with status 16037 Errors in file /opt/app/oracle/diag/rdbms/stdby/stdby/trace/stdby_pr00_2301.trc: ORA-16037: user requested cancel of managed recovery operation Managed Standby Recovery not using Real Time Apply Recovery interrupted! Recovered data files to a consistent state at change 16269612 Wed Sep 19 16:22:13 2018 MRP0: Background Media Recovery process shutdown (stdby) Managed Standby Recovery Canceled (stdby) Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL alter database convert to snapshot standby Starting background process RVWR Wed Sep 19 16:22:14 2018 RVWR started with pid=34, OS id=2429 Wed Sep 19 16:22:15 2018 Archived Log entry 4995 added for thread 1 sequence 399 ID 0x172868bb dest 1: Created guaranteed restore point SNAPSHOT_STANDBY_REQUIRED_09/19/2018 16:22:14 Killing 1 processes with pids 2334 (all RFS) in order to disallow current and fu ture RFS connections. Requested by OS process 2293 Begin: Standby Redo Logfile archival End: Standby Redo Logfile archival RESETLOGS after complete recovery through change 16269612 Resetting resetlogs activation ID 388524219 (0x172868bb) Online log +DATA/stdby/onlinelog/group_1.264.964883451: Thread 1 Group 1 was previously cleared Online log +FRA/stdby/onlinelog/group_1.258.964883451: Thread 1 Group 1 was previously cleared ... Standby became primary SCN: 16269610 Wed Sep 19 16:22:18 2018 Setting recovery target incarnation to 6 CONVERT TO SNAPSHOT STANDBY: Complete - Database mounted as snapshot standby Completed: alter database convert to snapshot standby ALTER DATABASE OPEN Data Guard Broker initializing... Data Guard Broker initialization complete4. After physical standby is converted to snapshot standby it will not carry out redo apply. However, it will continue to receive redo from primary thus protecting the primary in the event of failure. This could be verified by querying managed standby view. On standby run
SQL> select status,sequence#,block# from v$managed_standby where client_process='LGWR'; STATUS SEQUENCE# BLOCK# ------------ ---------- ---------- IDLE 402 409Make a note of block and sequence number. Execute a log switch on primary and query the standby again, which would show change in sequence# and/or block#. This indicate redo shipping is working as expected.
STATUS SEQUENCE# BLOCK# ------------ ---------- ---------- IDLE 403 45. At this point the snapshot standby is open for read/write. Carry out any testing needed. Once done with testing convert back to physical standby. Any changes done on snapshot standby DB will be lost once converted back to physical standby.
6. Connect to DGMGRL from primary and execute the convert command.
DGMGRL> convert database stdby to physical standby; Converting database "stdby" to a Physical Standby database, please wait... Operation requires shutdown of instance "stdby" on database "stdby" Shutting down instance "stdby"... Database closed. Database dismounted. ORACLE instance shut down. Operation requires startup of instance "stdby" on database "stdby" Starting instance "stdby"... ORACLE instance started. Database mounted. Continuing to convert database "stdby" ... Operation requires shutdown of instance "stdby" on database "stdby" Shutting down instance "stdby"... ORA-01109: database not open Database dismounted. ORACLE instance shut down. Operation requires startup of instance "stdby" on database "stdby" Starting instance "stdby"... ORACLE instance started. Database mounted. Database "stdby" converted successfully7. Finally verify the DG status and redo apply has begun.
DGMGRL> show configuration
Configuration - db_dg
Protection Mode: MaxAvailability
Databases:
prod - Primary database
stdby - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
DGMGRL> show database stdby
Database - stdby
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds (computed 0 seconds ago)
Apply Lag: 0 seconds (computed 0 seconds ago)
Apply Rate: 95.00 KByte/s
Real Time Query: OFF
Instance(s):
stdby
Database Status:
SUCCESS
