What are Oracle Data Guard Protection Modes (redo transport rules) and how to configure them

A Data Guard configuration always runs in one of three data protection modes (also called as redo transport rules):

Maximum Protection
Maximum Availability
Maximum Performance (default mode of operation)

All three modes provide a high degree of data protection, but they differ in terms of the effect that each has on the availability and performance of the primary database.

Data Guard Protection modes

The supported redo transport configurations for the 3 Data Guard Protection Modes are as follows.

1. Maximum Protection

This protection mode guarantees that no data loss will occur if the primary database fails. To provide this level of protection, the redo data needed to recover each transaction must be written to both the local online redo log and to a standby redo log on at least one standby database before the transaction commits. To ensure that data loss cannot occur, the primary database will shut down if a fault prevents it from writing its redo stream to at least one synchronized standby database.

Because this data protection mode prioritizes data protection over primary database availability, Oracle recommends that a minimum of two standby databases be used to protect a primary database that runs in maximum protection mode to prevent a single standby database failure from causing the primary database to shut down.

2. Maximum Availability

This protection mode provides the highest level of data protection that is possible without affecting the availability of the primary database. Like maximum protection mode, transactions do not commit until all redo data needed to recover those transactions has been written to the online redo log and to at least one synchronized standby database. Unlike maximum protection mode, the primary database will not shut down if a fault prevents it from writing its redo stream to a synchronized standby database. Instead, the primary database will operate in RESYNCHRONIZATION until the fault is corrected and all log gaps have been resolved. When all log gaps have been resolved, the primary database automatically resumes operating in maximum availability mode.

This mode ensures that no data loss will occur 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.

3. Maximum Performance

This protection mode provides the highest level of data protection that is possible 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 redo log. The primary database’s redo data stream is also written to at least one standby database, but that redo stream is written asynchronously with respect to the commitment of the transactions that create the redo data.

When network links with sufficient bandwidth and latency are used, this mode provides a level of data protection that approaches that of maximum availability mode with minimal impact on primary database performance.

Prerequisites for the protection modes

To participate in the 3 performance modes the following requirements must be met:

Requirement	Maximum protection	Maximum Availability	Maximum Performance
Redo Archival Process	LGWR	LGWR	LGWR or ARCH
Network Transmission mode	SYNC	SYNC	ASYNC when using LGWR only
Disk Write Option	AFFIRM	AFFIRM	NOAFFIRM
Standby Redo Logs	Yes	Yes	No, but recommended and required for Real-Time Apply
Standby Database Type	Physical (9i), Physical & Logical (10g & above)	Physical and Logical	Physical and Logical

How to change the protection mode (comparison of protection modes)

1. Make sure the LOG_ARCHIVE_DEST_N settings on the primary are configured and the destination enabled to support the target protection mode as documented above. All parameters should be set as needed in the spfile or pfile. For the mode of MAXIMUM PROTECTION, the standby must be up and mounted. For purposes of example, LOG_ARCHIVE_DEST_2 will be used.

SQL> alter system set log_archive_dest_2='service=STBY LGWR SYNC AFFIRM .......... '; 
SQL> alter system set log_archive_dest_state_2=enable;

If using Oracle10g or higher, attributes VALID_FOR and DB_UNIQUE_NAME should also be set as part of the LOG_ARCHIVE_DEST_n.

2. Skip this step if you are on Oracle 11.x or you want to downgrade the Protection Mode in Oracle 10.x Shut down the primary database and restart it in mounted mode. If the primary database is RAC enabled, shut down all of the instances and then start and mount a single instance.

For example:

SQL> shutdown immediate
SQL> startup mount

3. Change the protection mode. If step #2 was done, also open the database and restart the other RAC instances.

SQL> alter database set standby database to maximize {AVAILABILITY | PERFORMANCE | PROTECTION}; 
SQL> alter database open;

4. To confirm the change in protection mode, query v$database on the primary.

SQL> select protection_mode, protection_level from v$database;

Here,
PROTECTION_MODE : Protection mode set for the database
PROTECTION_LEVEL : Actual, real time mode in affect for the database; this will change after outages/faults