Infrastructure at your Service

Franck Pachot

DataGuard wait events have changed in 12c

By Franck Pachot

.
There are several new features in 12c about Data Guard: cascaded standby, far sync instance. But there are also some architecture changes: new processes and new wait events.
Here is an example of an AWR report of a LogXptMode=’SYNC’ DataGuard configuration in 11g – which means that the log_archive_dest is defined with: ‘LGWR SYNC AFFIRM’
That report comes from a period of time where the primary database was stuck because the standby server had a problem. It’s 11.2.0.2 and the foreground events show that the user sessions are waiting on LGWR:

11g Top 5 Timed Foreground Events

Event Waits Time(s) Avg wait (ms) % DB time Wait Class
log file sync 17,002 21,113 1242 46.08 Commit
log buffer space 1,322 8,583 6493 18.73 Configuration
buffer busy waits 1,869 4,376 2342 9.55 Concurrency
enq: TX – row lock contention 997 2,105 2111 4.59 Application
DB CPU 1,773 3.87

then we have to go to the background event section in order to see what the LGWR was waiting on:

Background Wait Events

Event Waits %Time -outs Total Wait Time (s) Avg wait (ms) Waits /txn % bg time
LNS wait on SENDREQ 17,674 0 2,103 119 1.04 37.50
LGWR-LNS wait on channel 153,447 88 2,094 14 9.03 37.34
log buffer space 17 0 114 6723 0.00 2.04
db file parallel write 52,931 0 105 2 3.12 1.87
enq: CF – contention 16 0 78 4865 0.00 1.39
log file switch completion 4 0 63 15640 0.00 1.12
log file sync 6 0 55 9102 0.00 0.9

In 11g the LNS processes  (Log Network Server) is responsible to send redo to the standby.
Because we are in SYNC we wait for remote acknowlegement.
Nothing new here. But let’s see what has changed in 12c.

12c

In 12c the name of the processes have changed. It’s not LNS anymore, but:

  • NSS ships the redo from LGWR to remote standby when we are in SYNC
  • NSA ships the redo from online redo logs to standby when we are in ASYNC

and you will see new wait events for them:

  • ‘SYNC Remote Write’ for all redo transport waits done by NSS processes
  • ‘ASYNC Remote Write’ for all redo tranport waits done by NSA processes
Note that since 11gR2 the writing to online redo logs and to standby are done in parallel. Then, if your network latency is smaller than your disk latency on primary, then you have absolutely no overhead to be in SYNC. You can be in SYNC NOAFFIRM so that the disk latency at standby site do not increase the ‘SYNC Remote Write’ wait. In 12c you can have that in a MaxAvailability configuration with Far Sync Standby.

SYNC Remote Write

So what is the 12c equivalent of the following waits that are symptomatic of SYNC latency:

  • LGWR waiting on LNS – wait event ‘LGWR-LNS wait on channel’
  • LNS waiting on standby – wait event ‘LNS wait on SENDREQ’

Here they are in picture (using Orachrome Lighty):

  • The LGWR is waiting on NSS – wait event is ‘SYNC Remote Write’:

b2ap3_thumbnail_Capture002.JPG

  • NSS is waiting on redo transport:

b2ap3_thumbnail_Capture003.JPG

If you were monitoring for ‘LNS’ wait events, you have to change it.
Here is the new pattern in an AWR report:

Event Waits %Time -outs Total Wait Time (s) Avg wait (ms) Waits /txn % bg time
log buffer space 7 0 896 128023.85 0.00 58.96
SYNC Remote Write 129 0 300 2322.96 0.00 19.72
buffer busy waits 2 0 287 143596.94 0.00 18.89
log file parallel write 116 0 6 48.02 0.00 0.37
Data Guard Broker Wait 5 100 5 1000.26 0.00 0.33
Redo Transport MISC 110 0 2 22.70 0.00 0.16

The documentation is not yet up-to-date, but all the old and new wait events are documented here.

7 Comments

  • Gaurav says:

    Hi Franck,
    Thank you for the article. I have below query:
    How can we reduce the time for background wait events – Redo transport MISC and SYNC remote Write?
    I have 1 Primary Database and 3 Physical Standby Databases in the DR setup. In ADG, The Protection mode is MAX Availability. Oracle 12c – 12.1.0.2
    Recovery is configured with Redo log real apply mode.

    Please suggest.

    • Hi Gaurav,
      So you have 3 standbys in SYNC? This means that each commit has to wait that redo is written in 4 places, two of them having network latency in addition. I don’t know if they are all done in parallel in 12.1.0.2
      Do you really need that? If you have Active Data Guard, you may prefer real-time cascading.
      Regards,
      Franck.

  • ksun says:

    Hi Franck,

    Nice Blog with great examples.

    One question, is each destination(LOG_ARCHIVE_DEST_n) has its own NSSn ?
    i.e. NSSx is named as LOG_ARCHIVE_DEST_y, where x and y are same number ?
    For example, NSS2 is for LOG_ARCHIVE_DEST_2.

    Regards,

  • mateen says:

    Hi,

    What is the difference between LNSn and NSSn. Can anyone make it clear. I am getting different names at different documents. Can someone make it clear.

    Regards

    • Hi,
      The difference is that the name of the processes has changed between versions.
      You have LNSn to send redo in a 11g database, for SYNC or ASYNC destinations.
      In 12c you have NSSn for SYNC destinations and NSAn for ASYNC destinations.
      Regards,
      Franck.

  • Pragya says:

    So lets say I have a FAR Sync standby configured in Fast Sync mode but I do see LFS on primary
    and the LGWR waits on ‘ SYNC Remote Write’ with NSS on ‘Redo Transport MISC ‘ for almost the entire time.
    Now since we are using fast Sync i.e removing time spent on writing to SRL’s out of pic, the assumption is that customer has network bottleneck( bandwidth or latency) between primary and far sync instance right ?

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Franck Pachot
Franck Pachot

Principal Consultant / Database Evangelist