• 博客访问: 1588264
  • 博文数量: 117
  • 用 户 组: 普通用户
  • 注册时间: 2014-04-06 15:21
  • 认证徽章:
个人简介

不仅仅专注Oracle database技术, member of SHOUG

文章分类

全部博文(117)

文章存档

2017年(3)

2016年(20)

2015年(58)

2014年(36)

分类: Oracle

2015-10-19 11:36:09


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       


阅读(10799) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~
评论热议
请登录后评论。

登录 注册