What are Oracle Data Guard Protection Modes and how to configure them

Data Protection Modes and Redo Transport

When you define a redo transport mode, you are configuring the shipment of log files from a redo source to a redo destination. The redo source is usually a primary database or a Far Sync instance. A redo destination is usually a physical or logical standby database. It is possible for a standby database to be considered a redo source to cascaded standby databases. You must set your redo transport mode to support the protection mode that you want for your configuration. However, configuring the redo transport mode alone does not set up the protection mode.

After setting up the redo transport mode, you can put the configuration into a data protection mode. The data protection mode setting causes internal rules to be implemented, ensuring that your configuration is protected at the necessary level.

Data Protection Modes

Oracle Data Guard offers 3 data protection modes to help enterprises balance data availability against system performance requirements. The mode are:

  • Maximum protection
  • Maximum availability
  • Maximum performance

In some situations, a business cannot afford to lose data. In other situations, the availability of the database may be more important than the loss of data. Some applications require maximum database performance and can tolerate the potential loss of data.

Maximum Protection Mode

This protection mode ensures that no data loss occurs if the primary database fails. To provide this level of protection, the redo data that is needed to recover each transaction must be written to both the local online redo log and the standby redo log on at least one standby database before the transaction commits. To ensure that data loss cannot occur, the primary database shuts down if a fault prevents it from writing its redo stream to at least one remote standby redo log. For multiple-instance RAC databases, Data Guard shuts down the primary database if it is unable to write the redo records to at least one properly configured database instance

Maximum protection mode requirements:

  • Configure standby redo log files on at least one standby database Configure standby redo log files on at least one standby database.
  • Set the SYNC and AFFIRM attributes of the LOG_ARCHIVE_DEST_n parameter for at least one standby database destination.

Note: Oracle recommends a minimum of two standby databases for maximum protection mode

Far Sync does not support the maximum protection mode.

Maximum Availability Mode

This protection mode provides the highest possible level of data protection without compromising the availability of the primary database. A transaction does not commit until the redo that is needed to recover that transaction is written to the local online redo log and has been received at the standby. The standby must initiate I/O to the standby redo log and send an acknowledgment back to the primary. It does not have to wait on the I/O to complete. This feature is known as FastSync in Oracle Database 12c. The primary database does not shut down if a fault prevents communicating with a standby instance. Instead, the primary database operates in an unsynchronized mode until the fault is corrected and all gaps in redo log files are resolved. When all gaps are resolved and the standby database is synchronized, the primary database automatically resumes operating in maximum availability mode. This mode guarantees that no data loss occurs if the primary database fails, but only if a second fault does not prevent a complete set of redo data from being sent from the primary database to at least one standby database.

Maximum availability mode requirements:

  • Configure standby redo log files on at least one standby database.
  • Set the SYNC and NOFFIRM attributes of the LOG ARCHIVE DEST_n parameter for at least one standby database. The SYNC and AFFIRM attributes can also be used.

Maximum Performance Mode

Maximum performance is the default protection mode and provides the highest possible level of data protection without affecting the performance of the primary database. This is accomplished by allowing a transaction to commit as soon as the redo data needed to recover that transaction is written to the local online redo log. The primary database’s redo data is also written to at least one standby database, but that redo data is written asynchronously with respect to the commitment of the transactions that create the redo data.

When network links with sufficient bandwidth are used, this mode provides a level of data protection that approaches that of maximum availability mode with minimal impact on primary database performance. Maximum performance mode requirement: Set the ASYNC and NOAFFIRM redo transport attributes of the LOG_ARCHIVE_DEST_n parameter on at least one standby database.

Comparing Data Protection Modes

Mode Risk of Data Loss Transport If no acknowledgment is received:
Maximum Protection Zero data loss. Double failure protection SYNC Stall primary until an acknowledgement is received.
Maximum Availability Zero data loss with single site failure SYNC (AFFIRM) Stall primary until threshold period expires, then resume processing.
Maximum Availability Potential for data loss when multiple sites fail SYNC (NOAFFIRM) Stall primary until threshold period expires, then resume processing.
Maximum Performance Potential for minimal data loss ASYNC Primary never waits for standby acknowledgement.

Consider the characteristics of each protection mode. You must balance cost, availability, performance, and transaction protection when choosing the protection mode.

Setting the Data Protection Mode by Using Enterprise Manager Enterprise Manager

Setting the Data Protection Mode by Using Enterprise Manager

If the data protection mode that you need requires a standby database to use the SYNC or ASYNC redo transport mode, Enterprise Manager automatically sets the redo transport mode for the primary database and the selected standby databases.

Enterprise Manager automatically determines the correct number and size of standby redo log files that are needed for all databases in the configuration and adds those log files using the directory locations that you specify.

set Data Protection Mode by Using Enterprise Manager

To set the data protection mode with Enterprise Manager:

  1. Navigate to the Data Guard page.
  2. Click the link in the Protection Mode field to access the Change Protection Mode: Select Mode page.
  3. Select Maximum Protection, Maximum Availability, or Maximum Performance, and click Continue.
  4. If prompted, enter the username and password of a user with SYSDBA privileges. Click Login.
  5. Select one or more standby databases to support the protection mode that you selected. (If standby redo log files are needed, verify the names of the log files.) Click OK.
  6. On the Confirmation page, click Yes.

Setting the Data Protection Mode by Using DGMGRL

1. You must configure standby redo log files for the primary database or another standby database in the configuration to ensure that it can support the chosen protection mode after a switchover.

2. Use the EDIT DATABASE SET PROPERTY command to set the redo transport mode for the standby database. For example, if you are changing the data protection mode to maximum availability, use the EDIT DATABASE command to specify SYNC for redo transport services:

DGMGRL> EDIT DATABASE 'london' SET PROPERTY 'LogXptMode'='SYNC';

You must also set the redo transport services for the primary database or another standby database in the configuration to ensure that it can support the chosen protection mode after a switchover.

3. Use the EDIT CONFIGURATION SET PROTECTION MODE AS command to set the overall configuration protection mode. To set the protection mode to maximum availability, use the following command:

DGMGRL> EDIT CONFIGURATION SET PROTECTION MODE AS MAXAVAILABILITY;

Setting the Data Protection Mode by Using SQL

If you are not using the Data Guard broker, then you can use SQL to set the data protection mode. You must manually set the redo transport attributes correctly prior to setting the data protection mode. Note that the data protection mode can be set to MAXIMUM PROTECTION on an open database only if the current data protection mode is MAXIMUM AVAILABILITY and if there is at least one synchronized standby database. The V$DATABASE view can show the current protection mode.

Set the data protection mode on the primary database:

SQL> ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE AVAILABILITY;
Database Altered.

Confirm that the primary database is operating in the new protection mode:

SQL> SELECT protection_mode FROM v$database;