Convert SNAPSHOT STANDBY to PHYSICAL STANDBY 12cR1

This article explains the steps to convert back SNAPSHOT STANDBY to PHYSICAL STANDBY 12cR1.

Convert PHYSICAL STANDBY to SNAPSHOT STANDBY 12cR1

Tables of contents:

• Check the Status Of Oracle database Data Guard
• Convert Standby database to PHYSICAL 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: SNAPSHOT STANDBY

a. Check the DG status using SQL command:

• 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           SNAPSHOT STANDBY READ WRITE           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 - Snapshot standby database 
 Fast-Start Failover: DISABLED
 Configuration Status:
 SUCCESS   (status updated 35 seconds ago)

2. Convert Standby database to PHYSICAL STANDBY:

You should connect to the standby database with the DGMGRL client:

Problem, when connected to the primary;

DGMGRL> CONVERT DATABASE wadostby TO PHYSICAL STANDBY;
 Converting database "wadostby" to a Physical Standby database, please wait…
 Operation requires shut down of instance "wadostby" on database "wadostby"
 Shutting down instance "wadostby"…
 ORA-01017: invalid username/password; logon denied
 Warning: You are no longer connected to ORACLE.
 Please complete the following steps and reissue the CONVERT command:
     shut down instance "wadostby" of database "wadostby"
     start up and mount instance "wadostby" of database "wadostby"
 DGMGRL> 

Solution: Connect to the standby database to convert from SNAPSHOT STANDBY to PHYSICAL STANDBY

$ dgmgrl sys/xxxxxxxxxx@wadostby
 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 SYSDBA.
 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 43 seconds ago)
 DGMGRL> CONVERT DATABASE wadostby TO PHYSICAL STANDBY;
 Converting database "wadostby" to a Physical Standby database, please wait…
 Operation requires shut down of instance "wadostby" on database "wadostby"
 Shutting down instance "wadostby"…
 Database closed.
 Database dismounted.
 ORACLE instance shut down.
 Operation requires start up of instance "wadostby" on database "wadostby"
 Starting instance "wadostby"…
 ORACLE instance started.
 Database mounted.
 Continuing to convert database "wadostby" …
 Database "wadostby" converted successfully
 DGMGRL> 
 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           PHYSICAL STANDBY READ ONLY WITH APPLY MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE

Conclusion:

Connect with DGMGRL tools to convert SNAPSHOT STANDBY to PHYSICAL STANDBY 12cR1.