Changing the Data Guard Protection Mode

Oracle Data Guard provides three modes to protect the data in the primary database. While each provide a high degree of data protection they each have a different effect on both the availability and performance of the primary database. Understanding the protection modes will help you design a highly available system that meets the availability needs of your company.

This document provides a brief overview of the Data Guard data protection modes along with details of how to change the protection mode using Oracle Database 11gR2.

Redo Transport Modes

The redo transport services perform the automated transfer of redo data between the primary and the standby database. The transportation can be done in one of two redo transport modes: Synchronous and Asynchronous.

In Synchronous transfer mode (SYNC) the Log Writer process must wait for confirmation that redo data is written on the standby before acknowledging a transaction on the primary database.

The Synchronous transfer mode is a zero loss transfer mode favoring data protection above the availability of the primary database. Any delay in writing the redo data to the standby will result impact performance on the primary and a failure in writing redo data to the standby could result in a shutdown of the primary database. Synchronous transfer mode is required for Maximum Availability and Maximum Protection modes.

In Asynchronous transfer (ASYNC) mode the log writer does not wait for a confirmation that redo data is written on the standby. Delays in transfer of redo data including failure to write redo data on standby do not impact availability of the primary database and provide little if any impact on the performance of the primary database. The Asynchronous transfer mode is not a zero loss transfer mode. Asynchronous transfer mode is required for the Maximum Performance protection mode.

Data Guard Protection Modes

The three protection modes provided in Data Guard listed in order least data protection, least impact potential on the primary to greatest data protection highest impact potential on the primary: Maximum Performance, Maximum Availability and Maximum Performance

Both the Maximum Availability and Maximum Protection mode require Standby Redo logs. All three modes require specific log transport parameters be specified in the LOG_ARCHIVE_DEST_n parameter.

Maximum Performance – In Maximum Performance transactions on the primary database complete a commit as soon as all redo generated is written to the online log of the primary database. Redo data is written to the standby but the log writer does not wait for confirmation of the write, so the performance and the availability of the primary database is not impacted.

In the event that the primary cannot write redo data to the standby the standby is marked as failed and is ignored until it can be it can be connected to once again. Once reconnected any gaps in redo will be sent to resynchronize the standby.

Maximum Performance offers the lowest degree of protection as a failover can result in data loss. Maximum Performance is the default protection mode.

Below is an example of the LOG_ARCHIVE_DEST_n parameter for a Maximum Performance configuration:

LOG_ARCHIVE_DEST_2=’SERVICE=standby ASYNC NOAFFIRM NET_TIMEOUT=30 REOPEN=300 DB_UNIQUE_NAME=standby VALID_FOR=(ALL_LOGFILES,PRIMARY_ROLE)’

Maximum Availability – Maximum Availability is a mixture of Maximum Protection and Maximum Performance. In this mode transactions on the primary database do not complete a commit until the redo data required to recovery the transaction is written to the standby redo log. If the redo data cannot be written to the standbys online redo log then the behavior is that of Maximum Performance. Once the primary database is able to write to the standby, the behavior changes back to that of Maximum Availability.

A data loss occurs in Maximum Availability mode if the standby did not have a chance to resynchronize before the failover. There is a slight performance delay on the primary database before continuing processing in the case of a failed standby.

Requirements for Maximum Availability mode:
Redo Transport mode: SYNC
Disk Write Option: AFFIRM
Standby Redo Logs: Yes
Standby Database Type: Physical and Logical

Below is an example of the LOG_ARCHIVE_DEST_n parameter for a Maximum Availability configuration.

LOG_ARCHIVE_DEST_2=’SERVICE=standby SYNC AFFIRM NET_TIMEOUT=30 REOPEN=300 DB_UNIQUE_NAME=standby VALID_FOR=(ALL_LOGFILES,PRIMARY_ROLE)’

Maximum Protection – Maximum Protection ensures that no data loss will occur in the event of failure of the primary database. The protection comes at the cost of performance and possibly availability of the primary database. In Maximum Protection mode transactions on the primary database do not complete a commit operation until the data to recover the transaction is written to both the primary online redo log and the standby redo log. If the redo data cannot be written to at least one standby the primary database will shutdown. Due to the possibility that the primary database can be shutdown it is recommended to have more than one standby in this configuration.

Requirements for Maximum Protection mode:
Redo Transport mode: SYNC
Disk Write Option: AFFIRM
Standby Redo Logs: Yes
Standby Database Type: Physical and Logical (10g and above)

Below is an example of the LOG_ARCHIVE_DEST_n parameter for a Maximum Protection configuration.

LOG_ARCHIVE_DEST_2=’SERVICE=standby SYNC AFFIRM NET_TIMEOUT=30 REOPEN=300 DB_UNIQUE_NAME=standby VALID_FOR=(ALL_LOGFILES,PRIMARY_ROLE)'

Below are brief descriptions of the parameters used in the LOG_ARCHIVE_DEST_n examples presented for each protection mode.

SERVICE – Specifies a valid service name for the standby database.

SYNC | ASYNC – Specifies whether the redo data is to be received at the destination before the transaction is committed. ASYNC is default.

AFFIRM | NOAFFIRM – Control whether the redo destination acknowledges received redo data. NOAFFIRM is the default for ASYNC if not specified. AFFIRM is the default for SYNC for if not specified.

NET_TIMEOUT – Specifies the time in seconds that the primary database log writer will wait for a response from the Log Network Service (LNS) before terminating the connection and marking the standby (destination) as failed. The default value is 30 seconds.

REOPEN – Specifies the time in seconds that the log writer should wait before attempting to access a previously failed standby (destination). The default is 300 seconds.

DB_UNIQUE_NAME – Unique name for the standby database

VALID_FOR(logifile,role) –Specifies that a specific log destination is valid only when the database is in a particular role.

Prior to changing the protection there are a few preliminary steps that should be taken that are documented below.

Determining the current Protection Mode

SQL> select protection_mode from v$database;

PROTECTION_MODE
--------------------
MAXIMUM PERFORMANCE

SQL>

Determine the current value of LOG_ARCHIVE_DEST_n that is used to send redo to the standby

SQL> show parameter log_archive_dest_2

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2                   string      SERVICE=standby ASYNC NOAFFIRM
                                                  NET_TIMEOUT=30 REOPEN=300 DB_
                                                 UNIQUE_NAME=standby VALID_FOR=
                                                 (ALL_LOGFILES,PRIMARY_ROLE)

Ensure that the primary database and standby database both have DB_UNIQUE_NAME set to a unique value.

Primary:

SQL> show parameter db_unique_name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_unique_name                       string      proddb
SQL>

Standby:

SQL> show parameter db_unique_name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_unique_name                       string      standby
SQL>

Verify that LOG_ARCHIVE_CONFIG parameter contains the DG_CONFIG value that lists DB_UNIQUE_NAME of the primary and each standby database used in the Data Guard configuration.

SQL> show parameter log_archive_config

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_config                   string      dg_config=(proddb,standby)
SQL>

Next set the LOG_ARCHIVE_DEST_n parameter to reflect the redo transport requirements for the new protection mode. In this example we are going to change Maximum Performance to Maximum Availability. Note: When changing to Maximum Availability or Maximum Protection mode standby redo logs must be present on the standby.

SQL> alter system set log_archive_dest_2='SERVICE=standby SYNC AFFIRM NET_TIMEOUT=30 REOPEN=300 DB_UNIQUE_NAME=standby VALID_FOR=(ALL_LOGFILES,PRIMARY_ROLE)' scope = both;

System altered.

SQL>

Next we set the protection mode.

SQL> alter database
  2  set standby database to maximize availability;

Database altered.

SQL>

We can verify that the protection mode has changed by query

SQL> select protection_mode from v$database;

PROTECTION_MODE
--------------------
MAXIMUM AVAILABILITY

SQL>

If you decide to change the protection mode to Maximum Protection you will need to change the protection while the primary is mounted and not open.

SQL> alter system set log_archive_dest_2='SERVICE=standby SYNC AFFIRM NET_TIMEOUT=30 REOPEN=300 DB_UNIQUE_NAME=standby VALID_FOR=(ALL_LOGFILES,PRIMARY_ROLE)' scope = both;

System altered.

SQL> alter database
  2  set standby database to maximize protection;
alter database
*
ERROR at line 1:
ORA-01126: database must be mounted in this instance and not open in any
instance

SQL>