Data Guard Protection Modes

Maximum Protection

1. No data loss

2. Redo has to be written to both Primary redo logs and standby redo logs (of atleast one standby database) before transaction commits

3. Primary database shuts down if redo stream is prevented to write at standby redo logs of atleast one standby database

4. Configure standby redo logs on at least one standby database

5. Attribute to use in log_archive_dest_n : LGWR, SYNC and AFFIRM for at least one standby DB

Maximum Availability

1. Redo has to be written to both Primary redo logs and standby redo logs (of atleast one standby database) before transaction commits

2. If redo stream is prevented to write at standby redo logs of at least one standby database then Primary database does not shuts down unlike Maximum protection mode, instead primary database operates in Maximum Performance mode.

4. Primary database automatically resumes to operate in maximum availability mode once all gaps are resolved.

5. Configure standby redo logs on at least one standby database

6. Attribute to use in log_archive_dest_n : LGWR, SYNC and AFFIRM for at least one standby DB

Maximum Performance

1. Default mode

2. Asynchronous redo data is written to at least one standby database

3. Attributes on log_archive_dest_n to set either LGWR and ASYNC or ARCH for standby DB destination

LGWR SYNC and ASYNC in Oracle DataGuard

Oracle Data Guard redo log transport offers synchronous log transport mode (LogXptMode = 'SYNC') or asynchronous log transport mode (LogXptMode = 'ASYNC').  The difference is all about when the COMMIT happens .   

LogXptMode = ('SYNC'):  As the name implies, SYNC mode synchronizes the primary with the standby database and all DML on the primary server will NOT be committed until the logs have been successfully transported to the standby servers.  The synchronous log transport mode is required for the Maximum Protection and Maximum Availability data protection modes. 

LogXptMode = ('ASYNC'): Conversely, asynchronous mode (ASYNC) allows updates (DML) to be committed on the primary server before the log file arrives on the standby servers.  The asynchronous log transport mode is required for the Maximum Performance data protection mode.

As you know, for every committed transaction at the primary database, Oracle generates and writes records to a local online log file.

If you choose REAL TIME redo apply, Data Guard transport services transmit the redo directly from the primary database log buffer to the standby database(s) where it is written to a standby redo log file locally.

This is highly efficient, it bypasses all IO generating processes. All redo travel in memory structures of database.

This method offers two types of replication methods: synchronous and asynchronous.

SYNCHRONOUS REDO TRANSPORT (SYNC) STORY (ZERO DATA LOSS)

Before 11GR2 :

1. PRIMARY DATABASE generates some redo data.

2. It waits the local log file write to be completed before transmitting redo from memory to the remote standby.

3. Then, it sends REDO to STANDBY DATABASE.

4. PRIMARY DATABASE waits until STANDBY DATABASE receives that redo data and even writes to STANDBY REDO LOG FILES.

5. If these conditions are satisfied, PRIMARY DATABASE acknowledges successful commit to application.

6. This provides guarantee of zero data loss in case of any failure happens.

With 11GR2 :

1. PRIMARY DATABASE generates some redo data (again)

2. PRIMARY DATABASE transmits redo to the remote standby in parallel with the local online log file write at the primary database.

ASYNCHRONOUS REDO TRANSPORT (ASYNC) STORY (MAXIMUM PERFORMANCE)

1. PRIMARY DATABASE generates some redo data.

2. PRIMARY DATABASE acknowledges successful commit to application without waiting STANDBY DATABASE's acknowledgment.

3. PRIMARY DATABASE ships directly from the primary log buffer (instead of from disk) to STANDBY DATABASE.

4. PRIMARY DATABASE's performance is at maximum, but there is no guarantee that a small amount of data loss.

You can use these options at PRIMARY DATABASE's configuration :

LOG_ARCHIVE_DEST_2='SERVICE=MYSTANDBY LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=MYUNIQUENAME'

                                                                                                                                           

So, DATA GUARD doesn't need to wait local log files to be written on anymore. This eliminates the  round-trip time required for I/O to complete at the standby database.