ORACLE LGWR ARCHIVAL 归档时业务卡顿

相关LOG

******************************************************************
LGWR: Setting 'active' archival for destination LOG_ARCHIVE_DEST_3
******************************************************************
LNS: Standby redo logfile selected for thread 3 sequence 61034 for destination LOG_AR
Tue Feb 02 06:20:44 2021
Archived Log entry 275577 added for thread 3 sequence 61033 ID 0x620b7fd7 dest 1:
Tue Feb 02 06:38:56 2021
opiodr aborting process unknown ospid (112094) as a result of ORA-609
Tue Feb 02 06:57:47 2021
Thread 3 advanced to log sequence 61035 (LGWR switch)
  Current log# 15 seq# 61035 mem# 0: +DATA/sqmesdb/onlinelog/group_15.570.1061736811
  Current log# 15 seq# 61035 mem# 1: +FRA/sqmesdb/onlinelog/group_15.635.1061736817
Tue Feb 02 06:57:47 2021
Deleted Oracle managed file +FRA/sqmesdb/archivelog/2021_01_15/thread_3_seq_60118.526
Tue Feb 02 06:57:47 2021

由于有纪录下LOG慢的时间

正好与LGWR时间差不多

进行了怀疑

在ORACLE里面下查询

> show parameter log_archive_dest_3

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_3                   string      service=standby lgwr sync
 

查看standby lgwr的说明

ORACLE官方建议是 ASYNC

LGWR还分为LGWR ASYNC(异步)和LGWR SYNC(同步)两种。

	最大保护	最大可用	最大性能
进程	LGWR	LGWR	LGWR或ARCH
网络传输模式	SYNC	SYNC	LGWR时设置ASYNC
磁盘写操作	AFFIRM	AFFIRM	NOAFFIRM
备用日志	YES	物理备用需要	LGWR和物理备用时需要
备用库类型	物理Standby	物理或逻辑	物理或逻辑

 

最大保护(maximize protection):最高级别的保护模式。primay上的事务在commit前必须确认redo已经传递到至少一个standby上，如果所有standby不可用，则primary会挂起。该模式能保证零数据丢失。对于最大保护和最高可用性模式，Standby数据库必须配置standby redo log，并且oracle推荐所有数据库都使用LGWR ASYNC模式传输。

 

遂直接修改设定

> ALTER SYSTEM SET log_archive_dest_3='service=standby lgwr async' SCOPE=BOTH;

 

 

RAC环境每一台都需要修改

修改后观察12小时

业务在无发生卡顿现象

 

---

LGWR ASYNC: ------------

Asynchronously archiving with the LGWR process in
conjunction with SSH port forwarding showed the following characteristics when
compared to the baseline: - Significant reduction in network traffic -
Slight increase in primary database throughput - Minimal increase in cpu usage
When using LGWR to remotely archive in ASYNC mode, the LGWR process does not
wait for each network I/O to complete before proceeding. This behavior. is made
possible by the use of an intermediate process, known as a LGWR network server
process (LNS), that performs the actual network I/O and waits for each network
I/O to complete. Each LNS has a user configurable buffer that is used to
accept outbound redo data from the LGWR. This is configured by specifying the
size in 512 byte blocks on the ASYNC attribute in the archivelog destination
parameter. For example ASYNC=2048 indicates a 1Mb buffer. As long as the
LNS process is able to empty this buffer faster than the LGWR can fill it, the
LGWR will never stall. If the LNS cannot keep up, then the buffer will become
full and the LGWR will stall until either sufficient buffer space is freed up
by a successful network transmission or a timeout occurs. Reducing network
traffic in a network with high round trip times (RTT) reduces network server
timeouts due to buffer full conditions, thus reducing the impact to the
primary database throughput. ASYNC can improve the primary database throughput
due to the fact that by compressing the redo traffic, the transfer (in 1 MB
chunks) is quicker and thus the ASYNC buffer doesn't reach full capacity as
often, thereby avoiding the wait that can occur when the buffer is full。