Data Guard Protection Mode With a High-Latency Standby
FINPRIM is a 19c primary database in New York; FINDR is a physical standby in London with 38 ms round-trip latency. The Data Guard broker is configured, protection mode is MAXIMUM AVAILABILITY, and LOG_ARCHIVE_DEST_2 uses SYNC AFFIRM. During peak processing, users report 20-40 second stalls. AWR shows log file sync averaging 45 ms (normally 2 ms), and the standby lags only slightly. The primary has four 512 MB online redo logs and switches every 12 minutes; the standby has eight 512 MB standby redo logs. The business says financial postings must have zero data loss, and management has approved a far sync instance in the New York data center but not a second standby in London. The DR runbook still requires a manual failover, although an observer host is available in a third region. The network team confirms the 38 ms latency is stable and cannot be reduced.
1. Why does the primary stall, and what evidence confirms the cause?
Read the worked answer
The primary is in MAXIMUM AVAILABILITY, so each commit using SYNC AFFIRM must wait for the London standby to acknowledge redo. The 38 ms round trip adds directly to commit latency. AWR shows log file sync averaging 45 ms instead of 2 ms, which is consistent with one network round trip plus local I/O. At high commit concurrency, sessions queue behind LGWR, producing the user-visible 20-40 second stalls even though individual commits wait only tens of milliseconds.
The slight standby lag and the correlation with peak commit rates support this diagnosis. Check V$DATAGUARD_STATS for transport lag and apply lag, V$LOG_HISTORY for log switch timing, and DBA_HIST_SYSTEM_EVENT for log file sync and log file switch completion. Also confirm that the standby redo logs are correctly sized and that there is no redo gap. If the standby were unavailable, MAXIMUM AVAILABILITY would also stall the primary after NET_TIMEOUT; verify the standby is reachable and applying.
2. Evaluate MAXIMUM PERFORMANCE, a far sync instance, and fast-start failover against the zero-data-loss requirement. Recommend a design.
Read the worked answer
MAXIMUM PERFORMANCE with ASYNC would remove the commit stall, but it allows data loss if the primary fails: committed transactions not yet shipped to London would be lost. That violates the stated financial postings requirement.
A far sync instance in New York receives redo SYNC from the primary and ships ASYNC to London. It removes the London round trip from commits and is the best latency fix, but it does not guarantee zero data loss at the London standby. If the primary fails and the far sync survives, redo may exist only on the far sync, and a far sync cannot be failed over to. If the New York site is lost, both primary and far sync are lost. Therefore a far sync design supports low commit latency, but zero data loss at the DR target still requires SYNC to London or a business decision to relax the guarantee.
Fast-start failover with an observer automates failover to London, but it does not remove SYNC latency. Recommendation: if zero data loss is non-negotiable, keep MAXIMUM AVAILABILITY with SYNC to London and treat the 38 ms commit cost as a business constraint; use far sync only if the business accepts that the guarantee is to the far sync, not the London standby.
3. What changes and validation steps would you perform for the chosen design?
Read the worked answer
For a far sync design, create a far sync instance in New York with a standby control file and standby redo logs, register it with the broker, and configure the primary to ship SYNC AFFIRM to the far sync and ASYNC to London. Conceptually: LOG_ARCHIVE_DEST_2 points to the far sync with SYNC AFFIRM, LOG_ARCHIVE_DEST_3 points to London with ASYNC, and both use VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE). Enable the destinations and verify with DGMGRL VALIDATE DATABASE 'FINPRIM' and SHOW DATABASE VERBOSE 'FINPRIM'.
For fast-start failover, enable FSFO in the broker with the observer in the third region, set the protection mode consistently, and test a switchover during a maintenance window. Check V$DATAGUARD_STATS for transport and apply lag, V$MANAGED_STANDBY for redo transport status, and V$LOG_HISTORY for switches. Perform a failover test on a clone or a planned switchover, and confirm the application reconnects and no committed transaction is lost under the chosen mode. If zero data loss is required, validate that the failover target, not just the far sync, has all redo before cutover.