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>

30 thoughts on “Changing the Data Guard Protection Mode”

  1. In your example the primary and the standby both have the same db_unique_name.
    Shouldn’t the db_unique_name for the two databases be unique:

    — primary

    SQL> show parameter db_unique_name

    NAME TYPE VALUE
    ———————————— ———– ——————————
    db_unique_name string proddb

    — standby

    SQL> show parameter db_unique_name

    NAME TYPE VALUE
    ———————————— ———– ——————————
    db_unique_name string standby

  2. What happens if I use “sync” at max performance mode? Will the primary hang if there is a network failure that the redo cannot be written at the standby site?

  3. Hi Eric Jenkinson,
    Thanks for your lucid explanation of the Protection Modes. It’s excellent.
    I have a small question:
    Below is the setting for log_archive_dest_2
    log_archive_dest_2 = ‘service=”stby”,LGWR SYNC AFFIRM delay=0 OPTIONAL compression=DISABLE max_failure=0 max_connections=1 reopen=300 db_unique_name=”orclstby” net_timeout=30 valid_for=(online_logfile,primary_role)’ scope=both;
    The protection mode is “Maximum Availability” and having only one Physical standby.

    Suppose, there is a network disconnect between the Primary(orclprmy) and the Standby (orclstby) for a duration of 2 minutes (120 Seconds), then the above setting (REOPEN=300) mean that, though the network is back after 2 minutes, Will the System wait for a complete (300 seconds, 5 minute) to resume it’s log shipping?
    Will the Primary not start it’s log shipping, the moment the network is back?

  4. Q:1 What happens if I use “sync” at max performance mode? Will the primary hang if there is a network failure that the redo cannot be written at the standby site?

    Q:2 I have max performance mode with sysn affirm.I want to change it to asysn with no affirm with max performance mode :Could yopu please provide step by step procedure.should the primary Dataabse be in mount mode?
    What are the steps for the standby database?Thanks in advance.

  5. Hi Eric

    Nice article and nicely explained.
    There is a small typo in the article

    It says that …
    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

    The it should read as : Maximum Performance, Maximum Availability and Maximum PROTECTION (it says Performance again).

  6. hi all,

    can you please provide me the steps for converting from max performance to max protection. currently my database is in max performance without standby redologs. provide steps to convert to max protection with standby redologs

  7. Hi Eric,

    First of all Merry Christmas to you n your family.
    Your site is really helping hand for oracle DBAs. Thanks to you.

    Regards

  8. 北京慕恩尚服饰有限公司是一家专业设计、 研发、生产、销售、承接T恤 Polo衫 翻领T恤 卫衣 文化衫 活动服] 加工定制印刷 以及售后服务于一体的综合性中高档服装企业。

  9. Eric,

    Do you have any suggestions for why a production server would be stalling when it loses connection with the standby database if it is in maximum performance mode?

    NET_TIMEOUT – 180
    REOPEN_SECS – 300

    I have the above parameters setup, and i think they need to be changed.

    Regards,

    Kieron

  10. Can we use Asynch Affirm mode in case of logical standby dataguard setup?? Will that work in the max. performance mode??

  11. One question,

    My DG setup is in Maximum Availability mode. what will happen to my user connection on primary db if the standby db is not reachable? will it wait until commit/hung/disconnect

  12. nice script, is it special attention when both the primary and standby are in RAC ? or just add sid =’*’ at the end of the alter command ?

    best regards Eric

  13. Hello dear
    I try two ALTER statements for changing protection mode to MAXIMUM AVAILABILITY
    But my standby database does not display new object until I use below statement to see my new object
    “Alter system switch logfile;”

    What do you suggest to see my new objects concurrently in standby database?

    Thanks in advance

  14. what would be the standby status while converting the protection mode in primary, it should be in mount only or need to down?

  15. Hi there,I check your blog named “Changing the Data Guard Protection Mode” like every week.Your humoristic style is awesome, keep doing what you’re doing! And you can look our website about free ip proxy.

Leave a Reply

Your email address will not be published. Required fields are marked *