网络带宽不足引起arc进程争用导致两个standby失败

一.背景概述

2014/08/17，按照客户的要求对异地dataguard进行实施工作。实施完成后拓扑结构及相关配置信息如下:

实施异地前同城standby可以正常应用日志且稳定运行时间超过半年，完成异地灾备搭建工作后异地灾备中心的gap越来越大且alert日志经常出现kill hang process的动作。当addrprod业务量增加后addrprod_std出现不再应用日志且主库也不主动往addrprod_std归档，分析后判定为往异地网络带宽不够引起arc进程资源被ADDRCD耗尽。故考虑将异地灾备中心级联到ADDRPROD_STD之后防止出现ADDRCD耗尽进程资源引起ADDRPROD_STD无法正常工作，调整后同城灾备运行正常但是异地灾备中心仍不能正常工作。最终通过调整ADDRPROD_STD到ADDRCD的归档进程数量，减少抢占网络资源的进程数，调整后ADDRPROD再一定时间内成功自动处理了40GB的gap。

以下通过调整拓扑结构，增量备份处理gap及优化ARCn进程数几个步骤对本次配置优化和故障处理的过程进行描述。

二.调整拓扑结构

调整拓扑结构前，客户反应ADDRCD(异地)的GAP越来越大且不能自动修复。进行检查分析工作时发现同城ADDRPROD_STD灾备环境和主库的数据版本差距也越来越大且主库不再主动归档日志到ADDRPROD_STD,同时ADDRPROD_STD从ADDRPROD去FETCH ARCHIVE LOG一直不能成功。各自alert日志如下(已设置log_archive_trace=128):

源端

alert日志：

Reclaiming FAL entry from dead process [pid 29229110]
FAL[server, ARC2]: Begin FAL archive (dbid 0 branch 845289128 thread 1 sequence 11600 dest ADDRCD)
Thu Aug 28 09:29:31 2014
Killing 1 processes with pids 24182958 (Process by index) in order to remove hung processes. Requested by OS process 16056504 on instance 1
ARC5: Detected ARCH process failure
ARC5: STARTING ARCH PROCESSES
Thu Aug 28 09:29:33 2014
ARC0 started with pid=36, OS id=19988636
ARC0: Archival started
ARC5: STARTING ARCH PROCESSES COMPLETE
Reclaiming FAL entry from dead process [pid 24182958]
FAL[server, ARC0]: Begin FAL archive (dbid 0 branch 845289128 thread 1 sequence 11603 dest ADDRCD)
Thu Aug 28 09:34:04 2014
Thread 1 advanced to log sequence 11657 (LGWR switch)
  Current log# 5 seq# 11657 mem# 0: +DATADG/addrprod/onlinelog/group_5.325.845289135
Thu Aug 28 09:34:10 2014
Archived Log entry 162811 added for thread 1 sequence 11656 ID 0xffffffff8f4c47cc dest 1:
Thu Aug 28 09:55:34 2014
Killing 1 processes with pids 29556818 (Process by index) in order to remove hung processes. Requested by OS process 16056504 on instance 1
ARC5: Detected ARCH process failure
ARC5: STARTING ARCH PROCESSES
Thu Aug 28 09:55:36 2014
ARC4 started with pid=44, OS id=29556826
ARC4: Archival started
ARC5: STARTING ARCH PROCESSES COMPLETE
Reclaiming FAL entry from dead process [pid 29556818]
FAL[server, ARC4]: Begin FAL archive (dbid 0 branch 845289128 thread 1 sequence 11594 dest ADDRCD)
Thu Aug 28 09:57:04 2014
FAL[server, ARC2]: FAL archive failed, see trace file.
ARCH: FAL archive failed. Archiver continuing
ORACLE Instance addr11g1 - Archival Error. Archiver continuing.
FAL[server, ARC2]: Begin FAL archive (dbid 0 branch 845289128 thread 1 sequence 11605 dest ADDRPROD_STD)
ARC2: Archive log rejected (thread 1 sequence 11605) at host 'ADDRPROD_STD'
FAL[server, ARC2]: FAL archive failed, see trace file.
ARCH: FAL archive failed. Archiver continuing
ORACLE Instance addr11g1 - Archival Error. Archiver continuing.
FAL[server, ARC2]: Begin FAL archive (dbid 0 branch 845289128 thread 1 sequence 11498 dest addrcd)
FAL[server, ARC2]: FAL archive failed, see trace file.
FAL[server, ARC2]: Begin FAL archive (dbid 0 branch 845289128 thread 1 sequence 11605 dest ADDRCD)
Thu Aug 28 09:59:37 2014
Killing 1 processes with pids 14680250 (Process by index) in order to remove hung processes. Requested by OS process 16056504 on instance 1
ARC5: Detected ARCH process failure
ARC5: STARTING ARCH PROCESSES
Thu Aug 28 09:59:39 2014
ARC6 started with pid=41, OS id=28901584
ARC6: Archival started
ARC5: STARTING ARCH PROCESSES COMPLETE
Reclaiming FAL entry from dead process [pid 14680250]
FAL[server, ARC6]: Begin FAL archive (dbid 0 branch 845289128 thread 1 sequence 11604 dest ADDRCD)

trace日志：

*** 2014-08-29 10:00:23.516 4329 krsh.c
FAL[server, ARC3]: Begin FAL archive (dbid 0 branch 845289128 thread 1 sequence 11634 dest ADDRPROD_STD)
FAL[server]: krsf_fal_driver: state:1
FAL[server]: krsf_fal_driver: state:2
krsd_match_lad: matching dest 1 found.
Resized overflow buffer to 2235K (for 2235K LWN)
FAL[server]: krsf_fal_driver: state:3
krsd_match_lad: matching dest 1 found.
Error 16401 creating standby archive log file at host 'ADDRPROD_STD'
FAL[server]: krsf_fal_driver: state:6
FAL[server]: FAL archive is INCOMPLETE
FAL[server]: krsf_fal_driver: state:8
krsf_fal_complete: Not adding AL for thread 1 sequence 11634.
FAL[server]: FAL archive failed
FAL[server]: krsf_fal_driver: state:10
*** 2014-08-29 10:00:24.058 4994 krsf.c
FAL[server]: FAL archive failed
(dbid 0 branch 845289128 thread 1 sequence 11634 destination ADDRPROD_STD)
*** 2014-08-29 10:00:24.058 5017 krsf.c
FAL[server]: Removing frb entry from queue
*** 2014-08-29 10:00:24.058 3911 krsf.c
krsf_falq_remove_frb: removing frb 0x7000107e22354c0 from FAL queue
   (thread 1 sequence 11634 dest ADDRPROD_STD)
krsf_falq_free_frb: FAL request block memory is freed. (dbid 0 branch 845289128 thread 1 sequence 11634 dest ADDRPROD_STD)
kcrrwkx: unknown error:16401
--报错代码含义:
oracle@p740a:/orastg/app/oracle/diag/rdbms/addrprod/addr11g1/trace[addr11g1]$oerr ora 16401
16401, 00000, "archive log rejected by Remote File Server (RFS)"
// *Cause:  An attempt was made to re-archive an existing archive log.
// *Action: See alert log and trace file for more details.
//          No action is necessary. This is an informational statement provided
//          to record the event for diagnostic purposes.

判定:

通过日志及trace文件可以看出，ADDRPROD-->ADDRCD的进程经常被hang住然后kill,然后系统自动在FAL时启动。多个进程抢占不到网络资源时hang住再被kill，如此进入一个不可逆循环。此时主库到ADDRPROD_STD的带宽虽然足够但没有办法申请到进程资源(log_archive_max_processes=8,则有7个归档进程可以归档到standby)。主要灵感来自手工清理进程后的日志:

ADDRPROD_STD

alert日志:

RFS[665]: Identified database type as 'physical standby': Client is ARCH pid 28639432
Thu Aug 28 07:27:17 2014
FAL[client]: Trying FAL server: ADDR11G2
.

.

.
FAL[client]: Trying FAL server: ADDR11G2
Thu Aug 28 07:30:08 2014
FAL[client]: Trying FAL server: ADDR11G1
Thu Aug 28 07:30:18 2014
FAL[client]: Trying FAL server: ADDR11G2
Thu Aug 28 07:30:28 2014
FAL[client]: Trying FAL server: ADDR11G1
Thu Aug 28 07:30:38 2014
FAL[client]: Trying FAL server: ADDR11G2
Thu Aug 28 07:30:48 2014
FAL[client]: Failed to request gap sequence
GAP - thread 1 sequence 11607-11607
DBID 2138367678 branch 845289128
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 Aug 28 07:32:46 2014
RFS[661]: Possible network disconnect with primary database
Thu Aug 28 07:34:34 2014
RFS[664]: Opened log for thread 1 sequence 11607 dbid 2138367678 branch 845289128
Archived Log entry 23174 added for thread 1 sequence 11607 rlc 845289128 ID 0x8f4c47cc dest 2:
RFS[664]: Opened log for thread 2 sequence 11270 dbid 2138367678 branch 845289128
Thu Aug 28 07:34:39 2014
Media Recovery Log /orastg/app/oracle/arch/addrprod_std/845289128_1_11607.arc
Archived Log entry 23175 added for thread 2 sequence 11270 rlc 845289128 ID 0x8f4c47cc dest 2:
Media Recovery Log /orastg/app/oracle/arch/addrprod_std/845289128_2_11259.arc

结合ADDRCD日志：

RFS[7]: Assigned to RFS process 385708
RFS[1]: Opened log for thread 1 sequence 11553 dbid 2138367678 branch 845289128
RFS[7]: Identified database type as 'physical standby': Client is ARCH pid 22020304
RFS[2]: Opened log for thread 1 sequence 11552 dbid 2138367678 branch 845289128
RFS[3]: Opened log for thread 1 sequence 11554 dbid 2138367678 branch 845289128
RFS[4]: Opened log for thread 1 sequence 11556 dbid 2138367678 branch 845289128
RFS[5]: Opened log for thread 1 sequence 11555 dbid 2138367678 branch 845289128
RFS[6]: Opened log for thread 1 sequence 11557 dbid 2138367678 branch 845289128
RFS[7]: Opened log for thread 1 sequence 11558 dbid 2138367678 branch 845289128
Sat Aug 30 11:44:58 2014
FAL[client]: Trying FAL server: ADDRPROD_STD
Sat Aug 30 11:45:14 2014
FAL[client]: Trying FAL server: ADDRPROD_STD
Sat Aug 30 11:45:29 2014
FAL[client]: Trying FAL server: ADDRPROD_STD
Sat Aug 30 11:45:45 2014
FAL[client]: Trying FAL server: ADDRPROD_STD
Sat Aug 30 11:46:02 2014
FAL[client]: Trying FAL server: ADDRPROD_STD
Sat Aug 30 11:46:18 2014
FAL[client]: Trying FAL server: ADDRPROD_STD
Sat Aug 30 11:46:35 2014
FAL[client]: Trying FAL server: ADDRPROD_STD
Sat Aug 30 11:46:50 2014
FAL[client]: Trying FAL server: ADDRPROD_STD

判定:

通过分析ADDRPROD_STD日志发现:在无人为干扰的情况下偶尔FAL可以成功，通过第一段alert日志可以得出。为了确保一个standby可用，停掉ADDRCD后kill源端arc进程并重启addrprod_std出现第二段日志。因gap过大，故可知ADDRPROD_STD会启动多个RFS进程去源端FAL，而此时源端的ARC进程只有7个则出现了后面申请不到资源的情况。也正是在进行此动作时确认是因为ADDRCD耗尽ADDRPROD的进程资源引起ADDRPROD_STD无法正常FAL。

ADDRCD：

alert日志：

WARN: ARCH: Terminating pid 118948 hung on an I/O operation
Killing 1 processes with pids 118948 (Process by index) in order to remove hung processes. Requested by OS process 479414
Thu Aug 28 07:19:00 2014
WARN: ARC2: Terminating pid 118948 hung on an I/O operation
Thu Aug 28 07:19:05 2014
FAL[client]: Trying FAL server: ADDR11G1
Thu Aug 28 07:19:07 2014
RFS[581]: Assigned to RFS process 487632
RFS[581]: Identified database type as 'physical standby': Client is ARCH pid 28639432
RFS[581]: Opened log for thread 1 sequence 11497 dbid 2138367678 branch 845289128
Thu Aug 28 07:19:27 2014
WARN: ARCH: Terminating pid 147540 hung on an I/O operation
Killing 1 processes with pids 147540 (Process by index) in order to remove hung processes. Requested by OS process 479414
Thu Aug 28 07:19:32 2014
RFS[582]: Assigned to RFS process 176232
RFS[582]: Identified database type as 'physical standby': Client is ARCH pid 24182958
RFS[582]: Opened log for thread 1 sequence 11498 dbid 2138367678 branch 845289128
Thu Aug 28 07:23:06 2014
WARN: ARCH: Terminating pid 454664 hung on an I/O operation
Killing 1 processes with pids 454664 (Process by index) in order to remove hung processes. Requested by OS process 479414

trace日志：

Redo Shipping Client Established Network Login

*** 2014-08-17 11:21:58.820
Redo Shipping Client Established Network Login

*** 2014-08-17 11:22:11.848
FAL[server]: ksfdrcres for file /addrcd/app/oracle/arch/addrcd/845289128_2_10478.arc.
Redo Shipping Client Established Network Login

*** 2014-08-17 11:22:44.901
Queuing write for bd 0x1108c3988 bufp 0x110a9c000 bufl 1048576

*** 2014-08-17 11:24:02.819
Queuing write for bd 0x1108c39e0 bufp 0x110bac000 bufl 1048576

*** 2014-08-17 11:24:55.279
Queuing write for bd 0x1108c3a38 bufp 0x110cbc000 bufl 1048576

*** 2014-08-17 11:25:39.780
Queuing write for bd 0x1108c3a90 bufp 0x110ebc000 bufl 1048576

*** 2014-08-17 11:27:43.101
*** 2014-08-17 11:27:43.101 2470 krss.c
WARN: I/O exceeded 120 seconds (124 secs) [krsb.c:2099]

*** 2014-08-17 11:27:43.102
Queuing write for bd 0x1108c3ae8 bufp 0x1110bc000 bufl 1048576

*** 2014-08-17 11:28:17.137
Queuing write for bd 0x1108c3b40 bufp 0x1111cc000 bufl 1048576

*** 2014-08-17 11:28:40.120
Queuing write for bd 0x1108c3b98 bufp 0x1112dc000 bufl 1048576

*** 2014-08-17 11:29:31.787
Queuing write for bd 0x1108c3bf0 bufp 0x1114bc000 bufl 1048576

*** 2014-08-17 11:31:34.570
*** 2014-08-17 11:31:34.570 2470 krss.c
WARN: I/O exceeded 120 seconds (123 secs) [krsb.c:2099]

初步结论:

通过以上日志分析可知，因网络带宽不足而多个进程同时占用引起的进程挂起被kill然后再次发起资源申请引起的ADDRPROD进程资源耗尽导致同城standby失败。

分析结果：

通过停用ADDRCD+重启ADDRPROD_STD时发现的源端归档进程耗尽会导致目标端FAL失败，外加ADDRCD因网络问题持续占用ADDRPROD的ARC进程。结合以上分析则确认ADDRPROD_STD失败是因为ADDRCD网络带宽不足引起，如果还将ADDRCD直接和ADDRPROD_STD都挂载ADDRPROD上则无法避免进程争用，故最终采用将ADDRCD级联到ADDRPROD_STD后面确保ADDRPROD_STD可用。

降低ADDRPROD_STD的ARC数分析:

ADDRPROD_STD正常应用日志后通过手工方式(详见下一步)追平gap，但是在业务量一般时也无法正常跟进数据版本。因考虑到多个进程同时抢占网络资源引起进程被KILL的现象，决定降低ADDRPROD_STD的归档进程数希望能减少进程因网络hang住被Kill的情况。将arc进程调整到3后系统可以自动修复gap且不会出现Kill hang process的情况。

调整后配置：

调整后ADDRPROD-->ADDRPROD_STD之间的日志应用可以正常进行。相关步骤如下:

1).禁用并清理ADDRPROD-->ADDRCD的log_archive_dest参数；

2).调整ADDRCD的FAL相关参数

3).kill掉ADDRPROD的arc进程并等待系统自动重启他们;

4).在ADDRPROD_STD中增加归档到ADODRCD的参数

5).启用连个standby的MRP

三.使用增量备份等办法手工处理gap

四.优化ARCn进程数:

因调整拓扑后ADDRCD的gap并没有因为手工修复而正常，业务增加后仍然不能正常工作。故考虑调整ADDRPROD_STD-->ADDRCD的ARC数量过多导致多个进程抢占有限的网络资源，进程长期挂起后被kill。调整完成后再无hang住进程出现。步骤如下:

4.1.异地灾备准备工作：

alter database recover managed standby database cancel;

shutdown immediate;

4.2.同城standby准备工作

SQL> alter system set log_archive_dest_state_3='defer' scope=both sid='*';

ps -ef|grep arc (杀掉所有的归档进程，数据库会自动重启这些进程);

oracle@p740t2:/orastg/app/oracle/diag/rdbms/addrprod_std/addr11g_stdb/trace[addr11g_stdb]$ps -ef|grep arc|grep -v grep

  oracle  2293970        1  70 16:23:11      -  0:20 ora_arc6_addr11g_stdb

  oracle  9896180        1  76 16:08:10      -  0:39 ora_arc1_addr11g_stdb

  oracle 13893814        1   0 16:33:14      -  0:00 ora_arc5_addr11g_stdb

  oracle 18415866        1  77 14:05:50      -  0:17 ora_arc7_addr11g_stdb

  oracle 23199994        1   0   Aug 30      -  0:10 ora_arc0_addr11g_stdb

  oracle 30474404        1  69 16:10:11      -  0:21 ora_arc3_addr11g_stdb

  oracle 34275342        1   0 16:33:14      -  0:00 ora_arc4_addr11g_stdb

SQL> ALTER SYSTEM SET LOG_ARCHIVE_MAX_PROCESSES=3 scope=both sid='*';

4.3.正式配置：
启动异地备库:

startup nomount;

alter database mount standby database;

在同城备库启用LAD3:

alter system set log_archive_dest_state_3='enable' scope=both sid='*';

启动异地恢复进程:

alter database recover managed standby database disconnect from session;

4.4.检查当前还有多少日志需要应用到目标端：

select sum(blocks*block_size)/1024/1024/1024 as "Size(GB)" from v$archived_log where dest_id=1 and first_change#>(select first_change# from v$archived_Log where thread#=1 and sequence#=12061 and dest_id=1);

select thread#,sequence#,applied from v$archived_log where applied='NO' and dest_id=1;