Change protection mode using Oracle Dataguard Broker

Change protection mode using Oracle Dataguard Broker

-- Setting the Configuration Protection Mode
-- Change protection mode from Max Performance (ASYNC) to Max Availability ( SYNC)
-- Fix : ORA-16826: apply service state is inconsistent with the DelayMins property


Asumptions:
Active dataguard has been configured and broker has been set using Oracle database version 11.2.0.3. Primary database name is 'PRIMARY' and standby database name is 'STANDBY'. Protection mode set as Max Performance (ASYNC).


Setting the Configuration Protection Mode:

You can change the protection mode of the configuration at any time. However, it is best if you do this when there is no activity occurring in the configuration if you are moving to the maximum protection or maximum availability modes.

Note: 
If the protection mode to be set is maximum protection mode, the broker automatically restarts the primary database.

This scenario sets the protection mode of the configuration to the MAXAVAILABILITY mode. Note that this protection mode requires that there be at least one standby database configured to use standby redo log files, with its LogXptMode configurable database property set to SYNC.

Pre-Checks: PRIMARY 

DGMGRL> connect sys@PRIMARY
Password:
Connected.
DGMGRL> show database 'PRIMARY';

Database - PRIMARY

  Role:            PRIMARY
  Intended State:  TRANSPORT-ON
  Instance(s):
    PRIMARY

Database Status:
SUCCESS

DGMGRL> show configuration;

Configuration - DR_Config

  Protection Mode: MaxPerformance
  Databases:
    PRIMARY - Primary database
    STANDBY - Physical standby database
      Warning: ORA-16826: apply service state is inconsistent with the DelayMins property

Fast-Start Failover: DISABLED

Configuration Status:
WARNING

DGMGRL> 



Prechecks : STANDBY

DGMGRL> connect sys@STANDBY
Password:
Connected.
DGMGRL> show database 'STANDBY';

Database - STANDBY

  Role:            PHYSICAL STANDBY
  Intended State:  APPLY-ON
  Transport Lag:   0 seconds
  Apply Lag:       19 minutes 21 seconds
  Real Time Query: OFF
  Instance(s):
    STANDBY

  Database Warning(s):
    ORA-16826: apply service state is inconsistent with the DelayMins property

Database Status: WARNING

DGMGRL> show configuration;

Configuration - DR_Config

  Protection Mode: MaxPerformance
  Databases:
    PRIMARY - Primary database
    STANDBY - Physical standby database
      Warning: ORA-16826: apply service state is inconsistent with the DelayMins property

Fast-Start Failover: DISABLED

Configuration Status: WARNING

DGMGRL> 

Steps to Change protection mode from Max Performance (ASYNC) to Max Availability ( SYNC):
( See to fix ORA error and WARNING)

Step 1 : Configure standby redo log files, if necessary.
Because you will be setting the protection mode to the MAXAVAILABILITY mode, it is important to ensure that sufficient standby redo log files are configured on the standby database.

Step 2 : Set the LogXptMode configurable database property appropriately.
Use the EDIT DATABASE (property) command on the standby database to set the redo transport service that corresponds to the protection mode you plan to set. If the protection mode to be set is MAXAVAILABILITY, it is required that the redo transport service of at least one standby database is set to SYNC. For example:

– In Standby:
DGMGRL> EDIT DATABASE 'STANDBY' SET PROPERTY 'LogXptMode'='SYNC';
Property "LogXptMode" updated

Note : The broker will not allow this command to succeed unless the standby database is configured with standby redo log files in the configuration.

Step 3 : Change the overall protection mode for the configuration.
Use the EDIT CONFIGURATION command to upgrade the broker configuration to the MAXAVAILABILITY protection mode:

DGMGRL> EDIT CONFIGURATION SET PROTECTION MODE AS MAXAVAILABILITY;
Succeeded.

DGMGRL> show configuration;

Configuration - DR_Config

  Protection Mode: MaxAvailability
  Databases:
    PRIMARY - Primary database
    STANDBY - Physical standby database
      Warning: ORA-16826: apply service state is inconsistent with the DelayMins property

Fast-Start Failover: DISABLED

Configuration Status:WARNING

Here we are getting ORA-16826. See my troubleshoot mechanism:

In Standby :
– Cancel the MRP:

SQL> alter database recover managed standby database cancel;

Database altered.

– Start MRP with current logfile as you converted the standby database to Max Availability mode:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

Database altered.
SQL> 
SQL> quit
$ dgmgrl

DGMGRL> connect sys@STANDBY;
Password:
Connected.

DGMGRL> show database 'STANDBY';

Database - STANDBY

  Role:            PHYSICAL STANDBY
  Intended State:  APPLY-ON
  Transport Lag:   0 seconds
  Apply Lag:       0 seconds
  Real Time Query: OFF
  Instance(s):     STANDBY

Database Status:
SUCCESS

DGMGRL> show configuration;

Configuration - DR_Config

  Protection Mode: MaxAvailability
  Databases:
    PRIMARY - Primary database
    STANDBY - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status: SUCCESS
DGMGRL> 

If the configuration is disabled when you enter this command, the actual protection mode change is not applied until you enable the configuration with the ENABLE CONFIGURATION command. The broker will not allow you to enable the configuration if it does not find a standby database in the configuration that can support the requirements of the protection mode.

If you have any issues with dataguard setup or broker setup, please feel free post questions / comments.

Cheers !!!