The manual is not very clear so what is the difference between the following Data Guard Redo Transport parameters
LGWR SYNC AFFIRM
LGWR SYNC NOAFFIRM
Our parameter is set to LGWR SYNC NOAFFRIM but how can the asynchronous NOAFFIRM be used with SYNC?
The manual states that with the SYNC option the log writer process waits for the I/O to complete before continuing.
It also states that for NOAFFIRM the log writer process does NOT wait until the disk I/O completes before continuing.
(1) So what is happening when SYNC and NOAFFIRM are used together?
(2) What is the difference between SYNC and AFFIRM parameters. The manual seems to suggest they do the same thing. If so what are they both available?
LGWR SYNC AFFIRM in Oracle Data Guard is used for zero data loss. How does one ensure zero data loss? Well, the redo block generated at the primary has to reach the standby across the network (that's where the SYNC part comes in - i.e. it is a synchronous network call), and then the block has to be written on disk on the standby (that's where the AFFIRM part comes in) - typically on a standby redo log.
Can you have LGWR SYNC NOAFFIRM? Yes sure. Then you will have synchronous network transport, but the only thing you are guaranteed is that the block has reached the remote standby's memory. It has not been written on to disk yet. So not really a zero data loss solution (e.g. what if the standby instance crashes before the disk I/O).
To sum up -> LGWR SYNC AFFIRM means primary transaction commits are waiting for ntk I/O + disk I/O acks. LGWR SYNC NOAFFIRM means primary transaction commits are waiting for ntk I/O only.