DG環境中的Redo傳輸主要是在LGWR、LNS、RFS這三類程式之間進行通訊其中主庫上有LGWR程式、LNS在主庫上的服務程式是nsa或者nss開頭的後臺程式 備庫上的RFS服務是由普通的server process來完成，也就是在備庫上不存在名為rfs的後臺程式 NSSn或者NSAn程式的常見等待事件(n與log_archive_dest_n裡的n對應)有：LNS wait on SENDREQ、LNS wait on ATTACH、LNS wait on DETACH LGWR程式的常見等待事件有：LGWR wait on LNS

等待事件說明如下：
"LNS WAIT on SENDSEQ"
Total time spent waiting for redo data to be written to all ASYNC and SYNC redo transport destinations

"LGWR-LNS wait on channel"
This wait event monitors the amount of time spent by the log writer (LGWR) process or the LNS processes waiting to receive messages.

LNS wait on ATTACH"
Total time spent waiting for redo transport sessions to be established to all ASYNC and SYNC redo transport destinations

"LNS wait on DETACH"
Total time spent waiting for redo transport connections to be terminated to all ASYNC and SYNC redo transport destinations

"LGWR wait on LNS"
This wait event monitors the amount of time spent by the log writer (LGWR) process waiting to receive messages from LNS processes.

看一下這些等待事件在哪些場景下會發生

<<<< LNS wait on SENDREQ 等待事件 >>>>
prmy: tstdb1
stdby1: tstdb1_stdby1
mode：MAXIMUM AVAILABILITY

###tstdb1：記錄"LNS wait on SENDREQ"事件的當前統計值
col event format a20
col wait_class format a12
set linesize 170
select * from v$session_event where (event like 'LNS%' or event like 'LGWR%') and sid=(select sid from v$session where program like '%NSS%') and event='LNS wait on SENDREQ';
SID EVENT TOTAL_WAITS TOTAL_TIMEOUTS TIME_WAITED AVERAGE_WAIT MAX_WAIT TIME_WAITED_MICRO EVENT_ID WAIT_CLASS_ID WAIT_CLASS# WAIT_CLASS
---------- -------------------- ----------- -------------- ----------- ------------ ---------- ----------------- ---------- ------------- ----------- ------------
332 LNS wait on SENDREQ 206 0 84 .41 14 840676 1468748253 2000153315 7 Network

###tstdb1_stdby1: 人為中斷與tstdb1間的網路，網路尚未恢復之前"LNS wait on SENDREQ"統計值一直沒有變化
col event format a20
col wait_class format a12
set linesize 170
select * from v$session_event where (event like 'LNS%' or event like 'LGWR%') and sid=(select sid from v$session where program like '%NSS%') and event='LNS wait on SENDREQ';
SYS@tstdb1-SQL> select * from v$session_event where (event like 'LNS%' or event like 'LGWR%') and sid=(select sid from v$session where program like '%NSS%') and event='LNS wait on SENDREQ'

SID EVENT TOTAL_WAITS TOTAL_TIMEOUTS TIME_WAITED AVERAGE_WAIT MAX_WAIT TIME_WAITED_MICRO EVENT_ID WAIT_CLASS_ID WAIT_CLASS# WAIT_CLASS
---------- -------------------- ----------- -------------- ----------- ------------ ---------- ----------------- ---------- ------------- ----------- ------------
332 LNS wait on SENDREQ 244 0 92 .38 14 915474 1468748253 2000153315 7 Network

SYS@tstdb1-SQL> select * from v$session_event where (event like 'LNS%' or event like 'LGWR%') and sid=(select sid from v$session where program like '%NSS%') and event='LNS wait on SENDREQ'

SID EVENT TOTAL_WAITS TOTAL_TIMEOUTS TIME_WAITED AVERAGE_WAIT MAX_WAIT TIME_WAITED_MICRO EVENT_ID WAIT_CLASS_ID WAIT_CLASS# WAIT_CLASS
---------- -------------------- ----------- -------------- ----------- ------------ ---------- ----------------- ---------- ------------- ----------- ------------
332 LNS wait on SENDREQ 244 0 92 .38 14 915474 1468748253 2000153315 7 Netw

SYS@tstdb1-SQL> select * from v$session_event where (event like 'LNS%' or event like 'LGWR%') and sid=(select sid from v$session where program like '%NSS%') and event='LNS wait on SENDREQ'

SID EVENT TOTAL_WAITS TOTAL_TIMEOUTS TIME_WAITED AVERAGE_WAIT MAX_WAIT TIME_WAITED_MICRO EVENT_ID WAIT_CLASS_ID WAIT_CLASS# WAIT_CLASS
---------- -------------------- ----------- -------------- ----------- ------------ ---------- ----------------- ---------- ------------- ----------- ------------
332 LNS wait on SENDREQ 244 0 92 .38 14 915474 1468748253 2000153315 7 Netw

###tstdb1：執行update後commit
---session 1:
update scott.t0930_1 set username='EEE' where username='DDD';
commit; <---暫時Hang住

---session 2: 觀察到以下兩種等待
col event format a30
col PROGRAM format a30
col MACHINE format a30
col WAIT_CLASS format a30
set linesize 150
select event,program,machine,wait_class from v$session where event like 'LNS%' or event like 'LGWR%';
EVENT PROGRAM MACHINE WAIT_CLASS
------------------------------ ------------------------------ ------------------------------ ------------------------------
LNS wait on SENDREQ oracle@jq570322b (NSS2) jq570322b Network
LGWR-LNS wait on channel oracle@jq570322b (LGWR) jq570322b Other

###tstdb1：網路恢復後，再次觀察"LNS wait on SENDREQ"事件，發現統計值已經更新了
col event format a20
col wait_class format a12
set linesize 170
select * from v$session_event where (event like 'LNS%' or event like 'LGWR%') and sid=(select sid from v$session where program like '%NSS%') and event='LNS wait on SENDREQ';
SID EVENT TOTAL_WAITS TOTAL_TIMEOUTS TIME_WAITED AVERAGE_WAIT MAX_WAIT TIME_WAITED_MICRO EVENT_ID WAIT_CLASS_ID WAIT_CLASS# WAIT_CLASS
---------- -------------------- ----------- -------------- ----------- ------------ ---------- ----------------- ---------- ------------- ----------- ------------
332 LNS wait on SENDREQ 348 0 119 .34 15 1193196 1468748253 2000153315 7 Network

select * from v$session_event where (event like 'LNS%' or event like 'LGWR%') and sid=(select sid from v$session where program like '%NSS%') and event='LNS wait on SENDREQ';
SID EVENT TOTAL_WAITS TOTAL_TIMEOUTS TIME_WAITED AVERAGE_WAIT MAX_WAIT TIME_WAITED_MICRO EVENT_ID WAIT_CLASS_ID WAIT_CLASS# WAIT_CLASS
---------- -------------------- ----------- -------------- ----------- ------------ ---------- ----------------- ---------- ------------- ----------- ------------
332 LNS wait on SENDREQ 390 0 126 .32 15 1260910 1468748253 2000153315 7 Network

在v$session裡我們同時觀察到了名為"LGWR-LNS wait on channel"的 event，但在v$session_event裡卻未查到
SYS@tstdb1-SQL> select * from v$session_event where event like 'LGWR-LNS wait on%';

no rows selected

注：經過測試在MAXIMUM AVAILABILITY和MAXIMUM AVAILABILITY模式下，如果修改stdby的SRLs許可權為root.system，雖然prmy側的ORLs無法即時寫入到SRLs，但是也不會引起LNS wait on SENDREQ等待值的飆升；測試過程略

<<<< LNS wait on ATTACH/LNS wait on DETACH 等待事件 >>>>
prmy: tstdb1
stdby1: tstdb1_stdby1
mode：MAXIMUM AVAILABILITY

###tstdb1: 記錄NSS程式當前的等待事件數量
col event format a20
col wait_class format a12
set linesize 170
select * from v$session_event where (event like 'LNS%' or event like 'LGWR%') and sid=(select sid from v$session where program like '%NSS%') and event in ('LNS wait on ATTACH','LNS wait on DETACH');
SID EVENT TOTAL_WAITS TOTAL_TIMEOUTS TIME_WAITED AVERAGE_WAIT MAX_WAIT TIME_WAITED_MICRO EVENT_ID WAIT_CLASS_ID WAIT_CLASS# WAIT_CLASS
---------- -------------------- ----------- -------------- ----------- ------------ ---------- ----------------- ---------- ------------- ----------- ------------
332 LNS wait on ATTACH 3 0 33 11.13 11 333887 1454243430 2000153315 7 Network
332 LNS wait on DETACH 2 0 0 .1 0 1987 4185804011 2000153315 7 Network

###tstdb1_stdby1: kill掉所有RFS程式 <---經測試若tstdb1、tstdb1_stdby1之間的網路中斷，不會使"LNS wait on ATTACH"事件的數量累計
select process,pid,sysdate,'kill -9 '||pid from v$managed_standby where process='RFS';

PROCESS PID SYSDATE 'KILL-9'||PID
--------- ---------- ----------------- ------------------------------------------------
RFS 4129274 20151003 10:40:05 kill -9 4129274
RFS 3080542 20151003 10:40:05 kill -9 3080542
RFS 2425848 20151003 10:40:05 kill -9 2425848
RFS 6029994 20151003 10:40:05 kill -9 6029994

SQL> select process,pid,'kill -9 '||pid from v$managed_standby where process='RFS';

no rows selected

###tstdb1: tstdb1_stdby1上的RFS程式未自動重啟之前，觀察到LNS wait on DETACH事件統計值上升，因為kill掉相當於detach一次
select * from v$session_event where (event like 'LNS%' or event like 'LGWR%') and sid=(select sid from v$session where program like '%NSS%') and event in ('LNS wait on ATTACH','LNS wait on DETACH');
SID EVENT TOTAL_WAITS TOTAL_TIMEOUTS TIME_WAITED AVERAGE_WAIT MAX_WAIT TIME_WAITED_MICRO EVENT_ID WAIT_CLASS_ID WAIT_CLASS# WAIT_CLASS
---------- -------------------- ----------- -------------- ----------- ------------ ---------- ----------------- ---------- ------------- ----------- ------------
332 LNS wait on ATTACH 3 0 33 11.13 11 333887 1454243430 2000153315 7 Network
332 LNS wait on DETACH 3 0 0 .07 0 2022 4185804011 2000153315 7 Network

###tstdb1: 等tstdb1_stdby1上的RFS程式自動重啟後，tstdb1上"LNS wait on ATTACH"事件的等待資訊發生變化，LNS和RFS重新建立連線
select * from v$session_event where (event like 'LNS%' or event like 'LGWR%') and sid=(select sid from v$session where program like '%NSS%') and event in ('LNS wait on ATTACH','LNS wait on DETACH');
SID EVENT TOTAL_WAITS TOTAL_TIMEOUTS TIME_WAITED AVERAGE_WAIT MAX_WAIT TIME_WAITED_MICRO EVENT_ID WAIT_CLASS_ID WAIT_CLASS# WAIT_CLASS
---------- -------------------- ----------- -------------- ----------- ------------ ---------- ----------------- ---------- ------------- ----------- ------------
332 LNS wait on ATTACH 4 0 45 11.16 11 446527 1454243430 2000153315 7 Network
332 LNS wait on DETACH 3 0 0 .07 0 2022 4185804011 2000153315 7 Network

<<<< LGWR wait on LNS 等待事件 >>>>
###tstdb1:記錄當前LGWR wait on LNS等待事件的初始值
select * from v$session_event where (event like 'LNS%' or event like 'LGWR%') and sid=(select sid from v$session where program like '%LGWR%') and event in ('LGWR wait on LNS');
SID EVENT TOTAL_WAITS TOTAL_TIMEOUTS TIME_WAITED AVERAGE_WAIT MAX_WAIT TIME_WAITED_MICRO EVENT_ID WAIT_CLASS_ID WAIT_CLASS# WAIT_CLASS
---------- -------------------- ----------- -------------- ----------- ------------ ---------- ----------------- ---------- ------------- ----------- ------------
793 LGWR wait on LNS 57614 0 66 0 4 663746 2949988816 2000153315 7 Network

###tstdb1: kill掉NSSn程式後，LGWR wait on LNS等待事件的統計值停止更新
SYS@tstdb1-SQL> select * from v$session_event where (event like 'LNS%' or event like 'LGWR%') and sid=(select sid from v$session where program like '%LGWR%') and event in ('LGWR wait on LNS')

SID EVENT TOTAL_WAITS TOTAL_TIMEOUTS TIME_WAITED AVERAGE_WAIT MAX_WAIT TIME_WAITED_MICRO EVENT_ID WAIT_CLASS_ID WAIT_CLASS# WAIT_CLASS
---------- -------------------- ----------- -------------- ----------- ------------ ---------- ----------------- ---------- ------------- ----------- ------------
793 LGWR wait on LNS 57663 0 67 0 4 667684 2949988816 2000153315 7 Network

SYS@tstdb1-SQL> select * from v$session_event where (event like 'LNS%' or event like 'LGWR%') and sid=(select sid from v$session where program like '%LGWR%') and event in ('LGWR wait on LNS')

SID EVENT TOTAL_WAITS TOTAL_TIMEOUTS TIME_WAITED AVERAGE_WAIT MAX_WAIT TIME_WAITED_MICRO EVENT_ID WAIT_CLASS_ID WAIT_CLASS# WAIT_CLASS
---------- -------------------- ----------- -------------- ----------- ------------ ---------- ----------------- ---------- ------------- ----------- ------------
793 LGWR wait on LNS 57663 0 67 0 4 667684 2949988816 2000153315 7 Network

###tstdb1: NSSn程式自動重啟後，LGWR wait on LNS等待事件的統計值又開始更新

SYS@tstdb1-SQL> select * from v$session_event where (event like 'LNS%' or event like 'LGWR%') and sid=(select sid from v$session where program like '%LGWR%') and event in ('LGWR wait on LNS')

SID EVENT TOTAL_WAITS TOTAL_TIMEOUTS TIME_WAITED AVERAGE_WAIT MAX_WAIT TIME_WAITED_MICRO EVENT_ID WAIT_CLASS_ID WAIT_CLASS# WAIT_CLASS
---------- -------------------- ----------- -------------- ----------- ------------ ---------- ----------------- ---------- ------------- ----------- ------------
793 LGWR wait on LNS 57698 0 67 0 4 668095 2949988816 2000153315 7 Network

Dataguard主庫上與redo transport service相關的等待事件

相關文章