Convert PHYSICAL STANDBY to SNAPSHOT STANDBY 12cR1

This article explains the steps to convert Oracle database Data Guard Brocker configuration from PHYSICAL STANDBY to SNAPSHOT STANDBY on Oracle Database 12cR1 on Oracle Linux 7u5.

Tables of contents:

• Check the Status Of Oracle database Data Guard
• Convert Standby database to SNAPSHOT STANDBY
• Check the new Status of Standby database

1. Check the Status Of Oracle database Data Guard

• OS Version: Oracle Linux 7u5
• Database Version: 12.1.0.2 EE
• Name: WADO
• Protection Mode: MAXIMUM PERFORMANCE
• Primary Database
  • DB_UNAQUE_NAME: wado
• Standby Database:
  • DB_UNAQUE_NAME: wadostby
  • Database Role: PHYSICAL STANDBY

a. Check the DG status using SQL command:

• On the Primary Database

SQL> select NAME,DB_UNIQUE_NAME,DATABASE_ROLE,OPEN_MODE,PROTECTION_MODE,PROTECTION_LEVEL from v$database;
 NAME      DB_UNIQUE_NAME         DATABASE_ROLE    OPEN_MODE        PROTECTION_MODE      PROTECTION_LEVEL
 
 WADO      wado               PRIMARY      READ WRITE           MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE

• On the Standby Database:

SQL> select NAME,DB_UNIQUE_NAME,DATABASE_ROLE,OPEN_MODE,PROTECTION_MODE,PROTECTION_LEVEL from v$database;
 NAME      DB_UNIQUE_NAME         DATABASE_ROLE    OPEN_MODE        PROTECTION_MODE      PROTECTION_LEVEL
 
 WADO      wadostby           PHYSICAL STANDBY READ ONLY WITH APPLY MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE

b. Check the DG status using DGMGRL command:

$ dgmgrl /
 DGMGRL for Linux: Version 12.1.0.2.0 - 64bit Production
 Copyright (c) 2000, 2013, Oracle. All rights reserved.
 Welcome to DGMGRL, type "help" for information.
 Connected as SYSDG.
 DGMGRL> show configuration;
 Configuration - wado_ha
 Protection Mode: MaxPerformance
   Members:
   wado     - Primary database
     wadostby - Physical standby database 
 Fast-Start Failover: DISABLED
 Configuration Status:
 SUCCESS   (status updated 7 seconds ago)
 DGMGRL> 

2. Convert Standby database to SNAPSHOT STANDBY:

a. Why SNAPSHOT STANDBY:

A snapshot standby database is a fully updatable standby database. A snapshot standby database receives and archives, but does not apply, redo data from a primary database. Redo data received from the primary database is applied when a snapshot standby database is converted back into a physical standby database, after discarding all local updates to the snapshot standby database. More Info.

b. Convert Step to SNAPSHOT STANDBY:

$ dgmgrl /
 DGMGRL for Linux: Version 12.1.0.2.0 - 64bit Production
 Copyright (c) 2000, 2013, Oracle. All rights reserved.
 Welcome to DGMGRL, type "help" for information.
 Connected as SYSDG.
DGMGRL> CONVERT DATABASE wadostby TO SNAPSHOT STANDBY;
 Converting database "wadostby" to a Snapshot Standby database, please wait…
 Database "wadostby" converted successfully
 DGMGRL> 

3. Check the new Status of Standby database:

a. Check the DG status using SQL command:

SQL> select NAME,DB_UNIQUE_NAME,DATABASE_ROLE,OPEN_MODE,PROTECTION_MODE,PROTECTION_LEVEL from v$database;
 NAME      DB_UNIQUE_NAME         DATABASE_ROLE    OPEN_MODE        PROTECTION_MODE      PROTECTION_LEVEL
 
 WADO      wadostby           SNAPSHOT STANDBY READ WRITE           MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE

Note: The database is opened to READ WRITE status, the standby database is ready to you modifications test.

b. Check the DG status using DGMGRL command:

DGMGRL> show configuration;
 Configuration - wado_ha
 Protection Mode: MaxPerformance
   Members:
   wado     - Primary database
     wadostby - Snapshot standby database 
 Fast-Start Failover: DISABLED
 Configuration Status:
 SUCCESS   (status updated 37 seconds ago)
 DGMGRL> 

Conclusion:

This is a list of commands to check the protection mode and the type of ORacle dataguard configuration and how to convert a physical standby database to snapshot standby database.