转载

Dataguard主库上与redo transport service相关的等待事件

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

正文到此结束