Oracle ADG TroubleShooting -- 主库大事务

原创有问题吗？ 2022-09-29

1257

Oracle ADG TroubleShooting – 主库大事务

背景：

钉钉报警(报警代码详见<<Oracle ADG(二) Python 监控源码>>)，ADG 传输和应用归档日志均发生较大延迟，造成生产数据和备库数据不同步，影响ADG备库上的实时性业务较高的业务稳定运行。

(

问题排查

通过报警可以知主要的问题在于MRP0 进程状态处于异常状态： WAIT_FOR_GAP, 前几天刚排查出FAL_SERVER缺乏配置导致的传输失败,且日志无法通过FAL进程重新拉取，导致长时间的WAIT_FOR_GAP（详见：<<Oracle ADG TroubleShooting – FAL 配置>>）。所以再次出现WAIT_FOR_GAP比较可疑。

备库排查

alter.log 日志如下：

Thu Sep 29 12:00:04 2022
Archived Log entry 1960627 added for thread 2 sequence 559159 ID 0x61907735 dest 1:
Thu Sep 29 12:00:23 2022
RFS[234]: Selected log 121 for thread 1 sequence 1086959 dbid 1265333748 branch 896830165
Thu Sep 29 12:00:27 2022
Archived Log entry 1960628 added for thread 1 sequence 1086958 ID 0x61907735 dest 1:
Thu Sep 29 12:01:49 2022
RFS[268]: Selected log 131 for thread 2 sequence 559162 dbid 1265333748 branch 896830165
Thu Sep 29 12:01:53 2022
Media Recovery Waiting for thread 2 sequence 559161
Fetching gap sequence in thread 2, gap sequence 559161-559161
Thu Sep 29 12:01:57 2022
Archived Log entry 1960629 added for thread 2 sequence 559160 ID 0x61907735 dest 1:
Thu Sep 29 12:02:36 2022
Fetching gap sequence in thread 2, gap sequence 559161-559161
Thu Sep 29 12:02:59 2022
RFS[234]: Selected log 122 for thread 1 sequence 1086960 dbid 1265333748 branch 896830165
Thu Sep 29 12:03:00 2022
Archived Log entry 1960630 added for thread 1 sequence 1086959 ID 0x61907735 dest 1:
Thu Sep 29 12:03:18 2022
RFS[279]: Selected log 130 for thread 2 sequence 559164 dbid 1265333748 branch 896830165
Thu Sep 29 12:03:21 2022
RFS[268]: Selected log 132 for thread 2 sequence 559165 dbid 1265333748 branch 896830165
Thu Sep 29 12:03:25 2022
Archived Log entry 1960631 added for thread 2 sequence 559162 ID 0x61907735 dest 1:
Thu Sep 29 12:03:53 2022
RFS[234]: Selected log 121 for thread 1 sequence 1086961 dbid 1265333748 branch 896830165
Thu Sep 29 12:03:53 2022
Archived Log entry 1960632 added for thread 1 sequence 1086960 ID 0x61907735 dest 1:
Thu Sep 29 12:04:09 2022
RFS[279]: Selected log 131 for thread 2 sequence 559166 dbid 1265333748 branch 896830165
Thu Sep 29 12:04:14 2022
Archived Log entry 1960633 added for thread 2 sequence 559164 ID 0x61907735 dest 1:
Thu Sep 29 12:04:20 2022
FAL[client]: Failed to request gap sequence
 GAP - thread 2 sequence 559161-559161
 DBID 1265333748 branch 896830165
FAL[client]: All defined FAL servers have been attempted.
------------------------------------------------------------
Check that the CONTROL_FILE_RECORD_KEEP_TIME initialization
parameter is defined to a value that's sufficiently large
enough to maintain adequate log switch information to resolve
archivelog gaps.
------------------------------------------------------------
Thu Sep 29 12:04:47 2022
RFS[234]: Selected log 122 for thread 1 sequence 1086962 dbid 1265333748 branch 896830165
Thu Sep 29 12:04:47 2022
Archived Log entry 1960634 added for thread 1 sequence 1086961 ID 0x61907735 dest 1:
Thu Sep 29 12:05:41 2022
RFS[234]: Selected log 121 for thread 1 sequence 1086963 dbid 1265333748 branch 896830165
Thu Sep 29 12:05:41 2022
Archived Log entry 1960635 added for thread 1 sequence 1086962 ID 0x61907735 dest 1:

复制

我们从日志中可以看出12:02分的时候 FAL 进程发现日志传输 thread 2 sequence 559161 有问题后，就主动去重新拉去缺失的thread 2 sequence 559161日志，但在12:04分的时候FAL进程再次报错，没有拉取到thread 2 sequence 559161-559161的日志，很可疑，而且从日志中发现thread 2 sequence 559162 和后续的日志已传输成功，继续排查主库上的thread 2 sequence 559161-559161的归档日志。此时备库MRP0 进程也处于WAIT_FOR_GAP 559161的状态

Thu Sep 29 12:02:36 2022
Fetching gap sequence in thread 2, gap sequence 559161-559161
复制

Thu Sep 29 12:04:20 2022
FAL[client]: Failed to request gap sequence
 GAP - thread 2 sequence 559161-559161
 DBID 1265333748 branch 896830165
FAL[client]: All defined FAL servers have been attempted.
------------------------------------------------------------
Check that the CONTROL_FILE_RECORD_KEEP_TIME initialization
parameter is defined to a value that's sufficiently large
enough to maintain adequate log switch information to resolve
archivelog gaps.
------------------------------------------------------------
复制

主库排查：

alter.log

Thu Sep 29 11:59:48 2022
LNS: Standby redo logfile selected for thread 2 sequence 559160 for destination LOG_ARCHIVE_DEST_2
Thu Sep 29 12:00:09 2022
Thread 2 advanced to log sequence 559161 (LGWR switch)
  Current log# 113 seq# 559161 mem# 0: +ARCHIVE/adg/onlinelog/group_113.310.1114862983
Thu Sep 29 12:00:10 2022
Archived Log entry 329189 added for thread 2 sequence 559160 ID 0x61907735 dest 1:
Thu Sep 29 12:01:06 2022
Thread 2 advanced to log sequence 559162 (LGWR switch)
  Current log# 114 seq# 559162 mem# 0: +ARCHIVE/adg/onlinelog/group_114.311.1114862985
Thu Sep 29 12:01:07 2022
Archived Log entry 329192 added for thread 2 sequence 559161 ID 0x61907735 dest 1:
Thu Sep 29 12:01:39 2022
LNS: Standby redo logfile selected for thread 2 sequence 559162 for destination LOG_ARCHIVE_DEST_2
Thu Sep 29 12:02:00 2022
Thread 2 advanced to log sequence 559163 (LGWR switch)
  Current log# 115 seq# 559163 mem# 0: +ARCHIVE/adg/onlinelog/group_115.312.1114862987
Thu Sep 29 12:02:01 2022
Archived Log entry 329194 added for thread 2 sequence 559162 ID 0x61907735 dest 1:
Thu Sep 29 12:02:48 2022
Thread 2 advanced to log sequence 559164 (LGWR switch)
  Current log# 116 seq# 559164 mem# 0: +ARCHIVE/adg/onlinelog/group_116.313.1114862989
Thu Sep 29 12:02:49 2022
Archived Log entry 329197 added for thread 2 sequence 559163 ID 0x61907735 dest 1:
Thu Sep 29 12:03:06 2022
Thread 2 advanced to log sequence 559165 (LGWR switch)
  Current log# 109 seq# 559165 mem# 0: +ARCHIVE/adg/onlinelog/group_109.306.1114862973
Thu Sep 29 12:03:07 2022
Archived Log entry 329198 added for thread 2 sequence 559164 ID 0x61907735 dest 1:
Thu Sep 29 12:03:07 2022
ARC1: Standby redo logfile selected for thread 2 sequence 559164 for destination LOG_ARCHIVE_DEST_2
Thu Sep 29 12:03:10 2022
LNS: Standby redo logfile selected for thread 2 sequence 559165 for destination LOG_ARCHIVE_DEST_2
Thu Sep 29 12:03:24 2022
Thread 2 advanced to log sequence 559166 (LGWR switch)
  Current log# 110 seq# 559166 mem# 0: +ARCHIVE/adg/onlinelog/group_110.307.1114862975
Thu Sep 29 12:03:25 2022
Archived Log entry 329200 added for thread 2 sequence 559165 ID 0x61907735 dest 1:
Thu Sep 29 12:03:42 2022
Thread 2 advanced to log sequence 559167 (LGWR switch)
  Current log# 111 seq# 559167 mem# 0: +ARCHIVE/adg/onlinelog/group_111.308.1114862977
Thu Sep 29 12:03:43 2022
Archived Log entry 329203 added for thread 2 sequence 559166 ID 0x61907735 dest 1:
Thu Sep 29 12:03:59 2022
ARC1: Standby redo logfile selected for thread 2 sequence 559166 for destination LOG_ARCHIVE_DEST_2
Thu Sep 29 12:04:00 2022
Thread 2 advanced to log sequence 559168 (LGWR switch)
  Current log# 112 seq# 559168 mem# 0: +ARCHIVE/adg/onlinelog/group_112.309.1114862981
Thu Sep 29 12:04:01 2022
Archived Log entry 329205 added for thread 2 sequence 559167 ID 0x61907735 dest 1:
Thu Sep 29 12:04:18 2022
Thread 2 advanced to log sequence 559169 (LGWR switch)
  Current log# 113 seq# 559169 mem# 0: +ARCHIVE/adg/onlinelog/group_113.310.1114862983
Thu Sep 29 12:04:19 2022
Archived Log entry 329206 added for thread 2 sequence 559168 ID 0x61907735 dest 1:
Thu Sep 29 12:04:36 2022
Thread 2 advanced to log sequence 559170 (LGWR switch)
  Current log# 114 seq# 559170 mem# 0: +ARCHIVE/adg/onlinelog/group_114.311.1114862985
Thu Sep 29 12:04:37 2022
Archived Log entry 329209 added for thread 2 sequence 559169 ID 0x61907735 dest 1:
Thu Sep 29 12:04:54 2022
Thread 2 advanced to log sequence 559171 (LGWR switch)
  Current log# 115 seq# 559171 mem# 0: +ARCHIVE/adg/onlinelog/group_115.312.1114862987
Thu Sep 29 12:04:55 2022
Archived Log entry 329210 added for thread 2 sequence 559170 ID 0x61907735 dest 1:
Thu Sep 29 12:05:12 2022
Thread 2 advanced to log sequence 559172 (LGWR switch)
  Current log# 116 seq# 559172 mem# 0: +ARCHIVE/adg/onlinelog/group_116.313.1114862989
Thu Sep 29 12:05:13 2022
Archived Log entry 329211 added for thread 2 sequence 559171 ID 0x61907735 dest 1:
Thu Sep 29 12:05:30 2022
Thread 2 advanced to log sequence 559173 (LGWR switch)
  Current log# 109 seq# 559173 mem# 0: +ARCHIVE/adg/onlinelog/group_109.306.1114862973
Thu Sep 29 12:05:31 2022
Archived Log entry 329214 added for thread 2 sequence 559172 ID 0x61907735 dest 1:
Thu Sep 29 12:05:48 2022
Thread 2 advanced to log sequence 559174 (LGWR switch)
  Current log# 110 seq# 559174 mem# 0: +ARCHIVE/adg/onlinelog/group_110.307.1114862975
Thu Sep 29 12:05:49 2022
Archived Log entry 329215 added for thread 2 sequence 559173 ID 0x61907735 dest 1:
Thu Sep 29 12:06:06 2022
Thread 2 advanced to log sequence 559175 (LGWR switch)
  Current log# 111 seq# 559175 mem# 0: +ARCHIVE/adg/onlinelog/group_111.308.1114862977
Thu Sep 29 12:06:07 2022
Archived Log entry 329216 added for thread 2 sequence 559174 ID 0x61907735 dest 1:
Thu Sep 29 12:06:12 2022
LNS: Standby redo logfile selected for thread 2 sequence 559175 for destination LOG_ARCHIVE_DEST_2
Thu Sep 29 12:06:24 2022
Thread 2 advanced to log sequence 559176 (LGWR switch)
  Current log# 112 seq# 559176 mem# 0: +ARCHIVE/adg/onlinelog/group_112.309.1114862981
Thu Sep 29 12:06:25 2022
Archived Log entry 329220 added for thread 2 sequence 559175 ID 0x61907735 dest 1:
Thu Sep 29 12:07:15 2022
Thread 2 advanced to log sequence 559177 (LGWR switch)
  Current log# 113 seq# 559177 mem# 0: +ARCHIVE/adg/onlinelog/group_113.310.1114862983
Thu Sep 29 12:07:16 2022
Archived Log entry 329221 added for thread 2 sequence 559176 ID 0x61907735 dest 1:
Thu Sep 29 12:07:31 2022
ARC1: Standby redo logfile selected for thread 2 sequence 559167 for destination LOG_ARCHIVE_DEST_2
Thu Sep 29 12:07:51 2022
LNS: Standby redo logfile selected for thread 2 sequence 559177 for destination LOG_ARCHIVE_DEST_2
Thu Sep 29 12:07:57 2022
Thread 2 cannot allocate new log, sequence 559178
Checkpoint not complete
  Current log# 113 seq# 559177 mem# 0: +ARCHIVE/adg/onlinelog/group_113.310.1114862983
Thread 2 advanced to log sequence 559178 (LGWR switch)
  Current log# 114 seq# 559178 mem# 0: +ARCHIVE/adg/onlinelog/group_114.311.1114862985
Thu Sep 29 12:08:00 2022
Archived Log entry 329224 added for thread 2 sequence 559177 ID 0x61907735 dest 1:
Thu Sep 29 12:08:27 2022
Thread 2 advanced to log sequence 559179 (LGWR switch)
  Current log# 115 seq# 559179 mem# 0: +ARCHIVE/adg/onlinelog/group_115.312.1114862987
Thu Sep 29 12:08:28 2022
ARC1: Standby redo logfile selected for thread 2 sequence 559168 for destination LOG_ARCHIVE_DEST_2
Thu Sep 29 12:08:28 2022
Archived Log entry 329228 added for thread 2 sequence 559178 ID 0x61907735 dest 1:
Thu Sep 29 12:08:54 2022
Thread 2 advanced to log sequence 559180 (LGWR switch)
  Current log# 116 seq# 559180 mem# 0: +ARCHIVE/adg/onlinelog/group_116.313.1114862989
Thu Sep 29 12:08:55 2022
Archived Log entry 329229 added for thread 2 sequence 559179 ID 0x61907735 dest 1:
Thu Sep 29 12:09:21 2022
Thread 2 advanced to log sequence 559181 (LGWR switch)
  Current log# 109 seq# 559181 mem# 0: +ARCHIVE/adg/onlinelog/group_109.306.1114862973
Thu Sep 29 12:09:22 2022
Archived Log entry 329230 added for thread 2 sequence 559180 ID 0x61907735 dest 1:
Thu Sep 29 12:09:24 2022
LNS: Standby redo logfile selected for thread 2 sequence 559181 for destination LOG_ARCHIVE_DEST_2
Thu Sep 29 12:09:48 2022
Thread 2 advanced to log sequence 559182 (LGWR switch)
  Current log# 110 seq# 559182 mem# 0: +ARCHIVE/adg/onlinelog/group_110.307.1114862975
Thu Sep 29 12:09:49 2022
LNS: Standby redo logfile selected for thread 2 sequence 559182 for destination LOG_ARCHIVE_DEST_2
Thu Sep 29 12:09:49 2022
Archived Log entry 329234 added for thread 2 sequence 559181 ID 0x61907735 dest 1:
Thu Sep 29 12:09:51 2022
ARC1: Standby redo logfile selected for thread 2 sequence 559169 for destination LOG_ARCHIVE_DEST_2
Thu Sep 29 12:10:15 2022
Thread 2 advanced to log sequence 559183 (LGWR switch)
  Current log# 111 seq# 559183 mem# 0: +ARCHIVE/adg/onlinelog/group_111.308.1114862977
Thu Sep 29 12:10:16 2022
Archived Log entry 329237 added for thread 2 sequence 559182 ID 0x61907735 dest 1:
Thu Sep 29 12:10:42 2022
Thread 2 advanced to log sequence 559184 (LGWR switch)
  Current log# 112 seq# 559184 mem# 0: +ARCHIVE/adg/onlinelog/group_112.309.1114862981
Thu Sep 29 12:10:43 2022
Archived Log entry 329238 added for thread 2 sequence 559183 ID 0x61907735 dest 1:
Thu Sep 29 12:11:09 2022
Thread 2 cannot allocate new log, sequence 559185
Checkpoint not complete
  Current log# 112 seq# 559184 mem# 0: +ARCHIVE/adg/onlinelog/group_112.309.1114862981
Thread 2 advanced to log sequence 559185 (LGWR switch)
  Current log# 113 seq# 559185 mem# 0: +ARCHIVE/adg/onlinelog/group_113.310.1114862983
Thu Sep 29 12:11:12 2022
Archived Log entry 329239 added for thread 2 sequence 559184 ID 0x61907735 dest 1:
Thu Sep 29 12:11:36 2022
Thread 2 cannot allocate new log, sequence 559186
Checkpoint not complete
  Current log# 113 seq# 559185 mem# 0: +ARCHIVE/adg/onlinelog/group_113.310.1114862983
Thread 2 advanced to log sequence 559186 (LGWR switch)
  Current log# 114 seq# 559186 mem# 0: +ARCHIVE/adg/onlinelog/group_114.311.1114862985
Thu Sep 29 12:11:40 2022
Archived Log entry 329242 added for thread 2 sequence 559185 ID 0x61907735 dest 1:
Thu Sep 29 12:11:47 2022
ARC1: Standby redo logfile selected for thread 2 sequence 559170 for destination LOG_ARCHIVE_DEST_2
Thu Sep 29 12:12:06 2022
Thread 2 advanced to log sequence 559187 (LGWR switch)
  Current log# 115 seq# 559187 mem# 0: +ARCHIVE/adg/onlinelog/group_115.312.1114862987
Thu Sep 29 12:12:07 2022
Archived Log entry 329244 added for thread 2 sequence 559186 ID 0x61907735 dest 1:
Thu Sep 29 12:12:33 2022
Thread 2 advanced to log sequence 559188 (LGWR switch)
  Current log# 116 seq# 559188 mem# 0: +ARCHIVE/adg/onlinelog/group_116.313.1114862989
Thu Sep 29 12:12:34 2022
Archived Log entry 329245 added for thread 2 sequence 559187 ID 0x61907735 dest 1:
Thu Sep 29 12:12:37 2022
ARC1: Standby redo logfile selected for thread 2 sequence 559171 for destination LOG_ARCHIVE_DEST_2
Thu Sep 29 12:13:00 2022
Thread 2 advanced to log sequence 559189 (LGWR switch)
  Current log# 109 seq# 559189 mem# 0: +ARCHIVE/adg/onlinelog/group_109.306.1114862973
Thu Sep 29 12:13:01 2022
Archived Log entry 329249 added for thread 2 sequence 559188 ID 0x61907735 dest 1:
Thu Sep 29 12:13:28 2022
Thread 2 advanced to log sequence 559190 (LGWR switch)
  Current log# 110 seq# 559190 mem# 0: +ARCHIVE/adg/onlinelog/group_110.307.1114862975
Thu Sep 29 12:13:29 2022
Archived Log entry 329250 added for thread 2 sequence 559189 ID 0x61907735 dest 1:
Thu Sep 29 12:13:48 2022
ARC1: Standby redo logfile selected for thread 2 sequence 559172 for destination LOG_ARCHIVE_DEST_2
Thu Sep 29 12:13:55 2022
Thread 2 advanced to log sequence 559191 (LGWR switch)
  Current log# 111 seq# 559191 mem# 0: +ARCHIVE/adg/onlinelog/group_111.308.1114862977
Thu Sep 29 12:13:56 2022
Archived Log entry 329252 added for thread 2 sequence 559190 ID 0x61907735 dest 1:
Thu Sep 29 12:14:22 2022
Thread 2 advanced to log sequence 559192 (LGWR switch)
  Current log# 112 seq# 559192 mem# 0: +ARCHIVE/adg/onlinelog/group_112.309.1114862981
Thu Sep 29 12:14:22 2022
Archived Log entry 329255 added for thread 2 sequence 559191 ID 0x61907735 dest 1:
Thu Sep 29 12:14:49 2022
Thread 2 cannot allocate new log, sequence 559193
Checkpoint not complete
  Current log# 112 seq# 559192 mem# 0: +ARCHIVE/adg/onlinelog/group_112.309.1114862981
Thread 2 advanced to log sequence 559193 (LGWR switch)
  Current log# 113 seq# 559193 mem# 0: +ARCHIVE/adg/onlinelog/group_113.310.1114862983
Thu Sep 29 12:14:51 2022
Archived Log entry 329256 added for thread 2 sequence 559192 ID 0x61907735 dest 1:
Thu Sep 29 12:15:05 2022
LNS: Standby redo logfile selected for thread 2 sequence 559193 for destination LOG_ARCHIVE_DEST_2
Thu Sep 29 12:15:16 2022
Thread 2 cannot allocate new log, sequence 559194
Checkpoint not complete
  Current log# 113 seq# 559193 mem# 0: +ARCHIVE/adg/onlinelog/group_113.310.1114862983
Thread 2 advanced to log sequence 559194 (LGWR switch)
  Current log# 114 seq# 559194 mem# 0: +ARCHIVE/adg/onlinelog/group_114.311.1114862985
Thu Sep 29 12:15:20 2022
Archived Log entry 329258 added for thread 2 sequence 559193 ID 0x61907735 dest 1:
Thu Sep 29 12:15:46 2022
Thread 2 advanced to log sequence 559195 (LGWR switch)
  Current log# 115 seq# 559195 mem# 0: +ARCHIVE/adg/onlinelog/group_115.312.1114862987
Thu Sep 29 12:15:47 2022
Archived Log entry 329261 added for thread 2 sequence 559194 ID 0x61907735 dest 1:
Thu Sep 29 12:16:10 2022
Thread 2 advanced to log sequence 559196 (LGWR switch)
  Current log# 116 seq# 559196 mem# 0: +ARCHIVE/adg/onlinelog/group_116.313.1114862989
Thu Sep 29 12:16:10 2022
Archived Log entry 329262 added for thread 2 sequence 559195 ID 0x61907735 dest 1:
Thu Sep 29 12:16:37 2022
Thread 2 advanced to log sequence 559197 (LGWR switch)
  Current log# 109 seq# 559197 mem# 0: +ARCHIVE/adg/onlinelog/group_109.306.1114862973
Thu Sep 29 12:16:38 2022
Archived Log entry 329263 added for thread 2 sequence 559196 ID 0x61907735 dest 1:
Thu Sep 29 12:16:42 2022


***********************************************************************

Fatal NI connect error 12170.

  VERSION INFORMATION:
        TNS for Linux: Version 11.2.0.4.0 - Production
        TCP/IP NT Protocol Adapter for Linux: Version 11.2.0.4.0 - Production
  Time: 29-SEP-2022 12:16:42
  Tracing not turned on.
  Tns error struct:
    ns main err code: 12535

TNS-12535: TNS:operation timed out
    ns secondary err code: 12560
    nt main err code: 505

TNS-00505: Operation timed out
    nt secondary err code: 110
    nt OS err code: 0
  Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=10.0.xxx.11)(PORT=1521))
ARC3: Attempting destination LOG_ARCHIVE_DEST_2 network reconnect (3113)
ARC3: RFS network connection re-established at host 'orclold'
ARC3: Standby redo logfile selected for thread 2 sequence 559161 for destination LOG_ARCHIVE_DEST_2
ARC3: RFS destination opened for reconnect at host 'orclold'

复制

从日志中排查出来12:01就已经分配了559161日志，但是LNS进程一直都没有把 thread 2 sequence 559161传输到备库上，而是一直等到12:16分才传输thread 2 sequence 559161给备库，而且传输的时候报TNS超时，也就是说12:01 到12:16之间这个时间段归档还没有完成。

Thu Sep 29 12:01:07 2022
Archived Log entry 329192 added for thread 2 sequence 559161 ID 0x61907735 dest 1:
复制

Thu Sep 29 12:16:42 2022


***********************************************************************

Fatal NI connect error 12170.

  VERSION INFORMATION:
        TNS for Linux: Version 11.2.0.4.0 - Production
        TCP/IP NT Protocol Adapter for Linux: Version 11.2.0.4.0 - Production
  Time: 29-SEP-2022 12:16:42
  Tracing not turned on.
  Tns error struct:
    ns main err code: 12535

TNS-12535: TNS:operation timed out
    ns secondary err code: 12560
    nt main err code: 505

TNS-00505: Operation timed out
    nt secondary err code: 110
    nt OS err code: 0
  Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=10.0.xxx.11)(PORT=1521))
ARC3: Attempting destination LOG_ARCHIVE_DEST_2 network reconnect (3113)
ARC3: RFS network connection re-established at host 'orclold'
ARC3: Standby redo logfile selected for thread 2 sequence 559161 for destination LOG_ARCHIVE_DEST_2
ARC3: RFS destination opened for reconnect at host 'orclold'

复制

继续看这个v$managed_standby 进程查看归档进程的状态，发现arc 进程还在WRITING thread 2 sequence 559161 这个归档，所以此归档还没有写完。

select process,clent_process,thread#,sequence#,status from v$managed_standby;
1	ARCH	ARCH	2	559161	WRITING
3	ARCH	ARCH	2	559163	WRITING
5   LNS      LNS     2   559195   WRITING
复制

综上： WAIT_FOR_GAP 的原因：是ARCH进程还没有写完thread 2 sequence 559161 这个归档日志，所以FAL进程再次拉取的时候依然没有拉到thread 2 sequence 559161 这个归档日志。继续排查这个归档没有写完的原因，发现此时数据库上有一个大的delete事务。

总结：

当生产库时有一个产生大量归档的大事务，事务没提交前，会导致归档日志一直处于WRITING状态，导致ADG备库无法按照归档顺序继续应用归档，导致ADG备库延迟。此大事务commit 完成后，ADG应用整个延迟消失用了20min。

最近遇到朋友的灵魂拷问，扩展下大事务没有提交导致的归档日志一直处于WRITING状态下，在ADG的三种模式下可能会导致什么问题：

最大性能模式(默认)：上面的结果就是最大性能模式下的结果

生产环境：节点1(正常，可以提交)，节点2(正常，可以提交)，备库: 异常，等待节点2的归档完成
最大数据保护模式：

生产环境：节点1(后续的COMMIT不允许提交)，节点2(后续的COMMIT不允许提交)，生产库只允许559161COMMIT后生产库才能后续提交，备库:异常，等待节点2的归档完成。
最大可用模式(略):