Oracle數(shù)據(jù)庫由dataguard備庫引起的log file sync等待問題

導(dǎo)讀：

最近數(shù)據(jù)庫經(jīng)常出現(xiàn)會話阻塞的報警，過一會又會自動消失，昨天晚上恰好發(fā)生了一次，于是趕緊進行了查看，不看不知道，一看嚇一跳，發(fā)現(xiàn)是由dataguard引起的log file sync等待。我們知道，通常log file sync等待都是由頻繁寫日志造成的，這次居然是由DG環(huán)境引起的。

（一）問題描述

數(shù)據(jù)庫：Oracle 11.2.0.4，單機版，有Dataguard環(huán)境

操作系統(tǒng)：centos 7.4

通過zabbix監(jiān)控到的會話阻塞信息如下圖，這里是自定義的監(jiān)控，解釋如下：

用戶usera,其session id為2663，session serial為27727，該會話未在執(zhí)行SQL語句，但是卻一直處于非空閑等待，等待的事件為log file sync，一共等待了548s

（二）分析

查看報警期間的歷史會話信息：

select sample_time, session_id,session_serial#,session_type,user_id,sql_id,sql_plan_operation,event,
  blocking_session,blocking_session_serial#,PROGRAM,MACHINE 
from v$active_session_history a
where a.sample_time > to_date('2020-11-25 20:40:00','yyyy-mm-dd hh24:mi:ss')
and  a.sample_time  to_date('2020-11-25 20:59:00','yyyy-mm-dd hh24:mi:ss')
and  blocking_session is not null
order by a.sample_time;

可以看到，會話1333，2191，2663均被會話1331阻塞了，等待事件是log file sync，它們在等待的會話為1311。

查詢1331會話信息，發(fā)現(xiàn)是日志寫進程LGWR，1311會話不再被其它會話阻塞，可以判定該會話為阻塞源頭，1331會話的等待事件是LGWR-LNS wait on channel。

select sample_time, session_id,session_serial#,session_type,user_id,sql_id,event,
  blocking_session_status,blocking_session,PROGRAM,MACHINE 
from v$active_session_history a
where a.sample_time > to_date('2020-11-25 20:40:00','yyyy-mm-dd hh24:mi:ss')
and  a.sample_time  to_date('2020-11-25 20:59:00','yyyy-mm-dd hh24:mi:ss')
and  a.session_id = 1331
order by a.sample_time;

在本案例中，一共出現(xiàn)了2種類型的非空閑等待事件：

• log file sync
• LGWR-LNS wait on channel（阻塞源頭）

什么是log file sync：當(dāng)用戶提交一個事務(wù)之后就開始等待log file sync，直到LGWR進程完成了對SCN的傳播和對應(yīng)重做日志的寫入操作。所以log file sync的等待時間是由重做日志I/O時間和SCN傳播時間兩部分構(gòu)成的，如果還使用了DataGuard，且日志傳送時使用了同步+確認(rèn)(SYNC+AFFRIM)選項時，那么LGWR還需在用戶提交事務(wù)之后將重做日志信息傳遞到遠(yuǎn)程備庫節(jié)點?？偨Y(jié)一下，log file sync的計算公式如下：

用戶進程log file sync等待時間 = LGWR執(zhí)行重做日志I/O時間 + SCN傳播時間 + LGWR傳送重做日志到備庫的時間。

在數(shù)據(jù)庫實例中，log file sync的等待步驟如下：

步驟①和②時所經(jīng)歷的時間就是log file sync所經(jīng)歷的時間。a1~a4是LGWR傳送重做日志到備庫的過程，b1~b4是LGWR傳播SCN的過程，c1~c2是LGWR將重做日志寫入到重做日志文件的過程。

a1~a4代表LGWR傳送重做日志到DataGuard備庫，過程如下：

a1：LGWR將事務(wù)對應(yīng)的重做信息發(fā)送給本地節(jié)點的LNS(network server)進程

a2：LNS進程通過網(wǎng)絡(luò)將重做信息發(fā)送給備庫的RFS(remote file server)進程

a3：RFS進程將重做日志信息寫入到備庫的備用重做日志文件(Standby redo log)，返回消息給主庫的LNS進程

a4：主庫的LNS進程通知LGWR進程重做信息已經(jīng)寫入到備庫的備用重做日志文件

b1~b4代表LGWR傳播SCN，SCN是數(shù)據(jù)庫內(nèi)部的時鐘，不重復(fù)，單項增長，SCN是針對數(shù)據(jù)庫的，不是針對實例的，也就是說，對于RAC數(shù)據(jù)庫，雖然有多個實例，這些實例會使用相同的SCN，但是每個實例都可以進行各自的任務(wù)，這就意味著實例之間需要傳播SCN。對于分布式數(shù)據(jù)庫（例如，使用了DB Link），也同樣存在著同步SCN的概念。同步SCN的過程如下：

b1：LGWR進程將事務(wù)提交的SCN發(fā)送給本地的一個LMS進程

b2：本地節(jié)點的LMS進程將包含了SCN的消息發(fā)送給所有遠(yuǎn)程節(jié)點的LMS進程

b3：所有遠(yuǎn)程節(jié)點的LMS進程接受到了SCN消息并反饋給本地節(jié)點的LMS進程

b4：本地節(jié)點的LMS進程通知LGWR，所有遠(yuǎn)程節(jié)點都受到了事務(wù)的SCN

c1~c2代表LGWR執(zhí)行重做日志寫I/O。過程如下：

c1：LGWR進程將redo buffer cache中的日志寫入到online redo log

c2：寫完之后LGWR會收到通知已完成

在分析完log file sync等待事件的過程之后，基本上可以知道其形成原因了。然而，新的問題又來了，log file sync等待由3部分原因構(gòu)成，在我的環(huán)境中，到底是LGWR執(zhí)行重做日志比較慢，還是SCN傳播時間存在異常等待，還是LGWR傳送重做日志到備庫存在性能瓶頸，這個時候我們就需要確認(rèn)log file sync的并發(fā)現(xiàn)象了，我們繼續(xù)分析。

（1）由LGWR執(zhí)行重做日志I/O引起的log file sync

如果是由于LGWR將日志寫入到online redo log引起的I/O問題，往往會伴隨著log file parallel write等待事件出現(xiàn)，也就是說，如果log file sync和log file parallel write一起出現(xiàn)，那么往往是存放在線日志文件的磁盤I/O出問題了，有可能是磁盤吞吐量較差，也有可能是頻繁的小I/O操作，磁盤I/O問題的主要解決方案如下：

• 優(yōu)化了redo日志的I/O性能，盡量使用快速磁盤，不要把redo log file存放在raid 5的磁盤上；
• 加大日志緩沖區(qū)(log buffer)；
• 使用批量提交，減少提交的次數(shù)；

（2）由SCN傳播引起的log file sync

由SCN傳播引起的log file sync等待事件幾乎沒有見過，個人覺得SCN傳播引起log file sync的概率較小，可以忽略

SQL> SELECT NAME FROM v$event_name a WHERE a.name LIKE '%SCN%' OR a.name LIKE '%LMS%';

NAME
----------------------------------------------------------------
retry contact SCN lock master
ges master to get established for SCN op

（3）由LGWR傳送重做日志到備庫引起的log file sync

需要特別注意的是，只有在LOG_ARCHIVE_DEST_n參數(shù)中使用了"SYNC，AFFIRM"屬性時，log file sync等待事件才會與LGWR傳送日志有關(guān)，如果使用了其它屬性，不用考慮。

LNS進程DataGuard環(huán)境中主庫用來傳送日志到備庫的進程，查看所有與之相關(guān)的等待事件。

SQL> SELECT NAME FROM v$event_name a WHERE a.name LIKE '%LNS%';

NAME
----------------------------------------------------------------
LNS wait on ATTACH
LNS wait on SENDREQ
LNS wait on DETACH
LNS wait on LGWR
LGWR wait on LNS
LNS ASYNC archive log
LNS ASYNC dest activation
LNS ASYNC end of log
LNS simulation latency wait
LGWR-LNS wait on channel

回過頭，再次查看我們的生產(chǎn)環(huán)境的問題，是log file sync伴隨著LGWR-LNS wait on channel出現(xiàn)，再次確認(rèn)數(shù)據(jù)庫的參數(shù)信息，發(fā)現(xiàn)數(shù)據(jù)庫運行在最大可用模式，備庫采用了同步(sync)方式傳送數(shù)據(jù)。

SQL> select name,open_mode,database_role,protection_mode,protection_level from v$database;

NAME  OPEN_MODE   DATABASE_ROLE PROTECTION_MODE  PROTECTION_LEVEL 
--------- -------------------- ---------------- -------------------- --------------------
ORCL2  READ WRITE   PRIMARY   MAXIMUM AVAILABILITY MAXIMUM AVAILABILITY


SQL> show parameter log
NAME       TYPE VALUE                        
----------------------------- ------- ---------------------------------------------------------------------------------------------------- 
log_archive_dest_2   string SERVICE=adg_orcl LGWR SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)        
          DB_UNIQUE_NAME=adg_orcl

再進一步分析"LGWR-LNS wait on channel"等待事件：

什么是LGWR-LNS wait on channel：這個等待事件監(jiān)視LGWR或LNS進程等待在KSR通道上接收消息所花費的時間（This wait event monitors the amount of time spent by the log writer (LGWR) process or the network server processes waiting to receive messages on KSR channels. Data Guard Wait Events (Doc ID 233491.1) ）。

KSR通道的解釋:https://docs.oracle.com/en/database/oracle/oracle-database/12.2/refrn/DBA_HIST_CHANNEL_WAITS.html#GUID-682C58F4-5787-4C8E-844C-9DFE04612BDD。

可以斷定，數(shù)據(jù)庫的異常等待是由于主庫的LNS進程同步傳送在線日志信息給DG環(huán)境引起的，且引起的瓶頸在備庫端。想到我們的主庫是高配的物理服務(wù)器，備庫是低配的云主機(虛擬機)，出現(xiàn)這種問題也就不足為奇了。

（三）解決方案

使用異步方式傳送日志信息，修改日志傳送方式為異步(async)傳送

SQL> alter system set log_archive_dest_2= SERVICE="adg_orcl" LGWR ASYNC VALID_FOR=(all_logfiles, primary_role) DB_UNIQUE_NAME="adg_orcl" scope=both;

-- 重新啟用通道
SQL> alter system set log_archive_dest_state_2= defer;
SQL> alter system set log_archive_dest_state_2= enable;

到此這篇關(guān)于Oracle數(shù)據(jù)庫由dataguard備庫引起的log file sync等待的文章就介紹到這了,更多相關(guān)Oracle dataguard備庫引起的log file sync等待內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！