突破网络瓶颈：如何从致命错误中恢复ADG同步

本期投稿人

杜兴春

Oracle ACE ，PG ACE 获得 11g OCM、12c OCM、PGCM、RHCE、KCP、ACP、DCP等多项认证。公众号：老杜随笔

擅长数据库优化与故障处理，主要从事Oracle、PostgreSQL数据库运维管理工作，

服务于政府、医疗、电力、金融等领域. 兴趣广泛，喜欢听歌、读书

正文开始

问题描述

最近在部署生产库到容灾库adg时，用Duplicate target database传输时前30s传输很快，当到了30s后，传输速度每10分钟1M.导致数据库文件无法Duplicate。

分析如下

1、主库与备库的防火墙

[root@PRI_DB ~]# iptables -L
Chain INPUT (policy ACCEPT)
target    prot opt source              destination        
Chain FORWARD (policy ACCEPT)
target    prot opt source              destination3        
Chain OUTPUT (policy ACCEPT)
target    prot opt source              destination        
[root@PRI_DB ~]#  ip6tables -L
Chain INPUT (policy ACCEPT)
target    prot opt source              destination        
Chain FORWARD (policy ACCEPT)
target    prot opt source              destination        
Chain OUTPUT (policy ACCEPT)
target    prot opt source              destination        
[root@PRI_DB ~]# getsebool
getsebool: SELinux is disabled

2、alter 日志如下：

Error 1034 received logging on to the standby 
PING[ARC2]: Heartbeat failed to connect tostandby 'testdg'. Error is 1034. 
Fri Mar 30 10:23:11 2018 
Error 1034 received logging on to the standby 
PING[ARC2]: Heartbeat failed to connect tostandby 'testdg'. Error is 1034. 
Fri Mar 30 10:23:48 2018 
ALTER SYSTEM SETremote_login_passwordfile='EXCLUSIVE' SCOPE=SPFILE; 
Fri Mar 30 10:24:11 2018 
Error 1034 received logging on to the standby 
PING[ARC2]: Heartbeat failed to connect tostandby 'testdg'. Error is 1034. 
Fri Mar 30 10:25:11 2018 
Error 1034 received logging on to the standby 
PING[ARC2]: Heartbeat failed to connect tostandby 'testdg'. Error is 1034. 
PING[ARC2]: Heartbeat failed to connect tostandby 'testdg'. Error is 1034. 
Fri Mar 30 10:28:11 2018 
Error 1034 received logging on to the standby 
ORA-27037: unable to obtain file status 
Linux-x86_64 Error: 2: No such file or directory 
Additional information: 3 
Errors in file /u01/oracle/diag/rdbms/testdg/testdg/trace/testdg_lgwr_7720.trc: 
ORA-00313: open failed for members of loggroup 6 of thread 0 
ORA-00312: online log 6 thread 0:'/data/dbs/standby_redo05.log' 
ORA-27037: unable to obtain file status 
Linux-x86_64 Error: 2: No such file ordirectory 
Additional information: 3 
Errors in file /u01/oracle/diag/rdbms/testdg/testdg/trace/testdg_lgwr_7720.trc: 
ORA-00313: open failed for members of loggroup 7 of thread 0 
ORA-00312: online log 7 thread 0:'/data/dbs/standby_redo06.log' 
ORA-27037: unable to obtain file status 
Linux-x86_64 Error: 2: No such file ordirectory 
Additional information: 3 
Errors in file /u01/oracle/diag/rdbms/testdg/testdg/trace/testdg_lgwr_7720.trc: 
ORA-00313: open failed for members of loggroup 7 of thread 0 
ORA-00312: online log 7 thread 0:'/data/dbs/standby_redo06.log' 
ORA-27037: unable to obtain file status

没有发现任何问题。当时Duplicate时awr的等待事件及rman的日志如下：发现数据库有两个比较长的等待事件：'remote db file write'，'Streams AQ: waiting for messages in thequeue' ，这两个等待事件也就是Rman进行duplicate时所产生，根据目前的状态判断处于Hangs状态。也就是说主库处于等待状态

3、Rman Debug如下：

strace -fit -o /tmp/strace.out rman target<username/password@primary> auxiliary <username/password@standby>debug all trace=/tmp/rmandebug.trc log=/tmp/rmandebug.log 
run{ 
allocate channel d1 type disk trace=3; 
<duplicate target database for standby fromactive database nofilenamecheck;> 
release channel d1; }

4、系统strace 做debug

$sqlplus / as sysdba 
SQL>oradebug setmypid 
SQL>oradebug unlimit 
SQL>oradebug hanganalyze 3 
SQL>oradebug dump systemstate 258 
Wait for 10 seconds 
SQL>oradebug hanganalyze 3 
SQL>oradebug dump systemstate 258 
Wait for 10 seconds 
SQL>oradebug hanganalyze 3 
SQL>oradebug dump systemstate 258 
SQL>oradebug tracefile_name 
SQL>oradebug close_trace

5、网络数据打包如下：

 tcpdump dsthost  10.1.2.3> /tmp/tcpdump.log

6、以上数据如下：

+++testdg_ora_5308.trc+++ 发现数据库有两个比较长的等待事件：'remote db file write'，'Streams AQ: waiting for messages in the queue' ，这两个等待事件也就是Rman进行duplicate时所产生，根据目前的状态判断处于Hangs状态。也就是说主库处于等待状态。其中'remote db file write'等待事件的short stack包含两个函数如下: ksrpccqwrt - Queue a sequential write to a file on the remote instance ksrpc_ttcsndcbk - ksrpc Receive callback function registered with ttc

Chains most likely to have caused the hang: 
[a] Chain 1 Signature: 'remote db file write'------------------------------------>Here 
Chain 1 Signature Hash: 0x8253fd15 
[b] Chain 2 Signature: 'Streams AQ: waitingfor messages in the queue'------------------------------------>Here 
Chain 2 Signature Hash: 0xa00e2e87 
Sessions in an involuntary wait or not in await: 
------------------------------------------------------------------------------- 
Chain 1: 
------------------------------------------------------------------------------- 
Oracle session identified by: 
{ 
instance: 1 (prod.prod) 
os id: 5219 
process id: 44, oracle@TEST_DB 
session id: 195 
session serial #: 29459 
} 
is waiting for 'remote db file write' withwait info: 
{ 
p1: 'clientid'=0x1 
p2: 'count'=0x100000 
p3: 'intr'=0x0 
time in wait: 0.013906 sec 
timeout after: never 
wait id: 6767 
blocking: 0 sessions 
current sql: <none> 
short stack:ksedsts()+465<-ksdxfstk()+32<-ksdxcb()+1927<-sspuser()+112<-__sighandler()<-writev()+27<-nsvntsn()+5384<-nsvdosn()+1905<-nsvsend()+5159<-niovsn()+990<-ttciovconv()+3803<-ksrpc_ttcsndcbk()+361<-ttcdrv()+1058<-nioqwa()+61<-upirtrc()+1378<-kpurcsc()+98<-ksrpccqwrt()+1987<-ksfqwr()+1088<-krbb0qwr()+488<-krbbpcint()+5999<-krbbpc()+967<-krbibpc()+1574<-pevm_icd_call_common()+897<-pfrinstr_ICAL()+169<-pfrrun_no_tool()+63<-pfrrun()+627<-plsql_run()+649<-pricar()+1048<-pricbr()+572<-prient2()+1259<-prient()+2309<-kkxrpc()+ 
wait history: 
* time between current wait and wait #1:0.000466 sec 
1. event: 'RMAN backup & recovery I/O' 
time waited: 0.000004 sec 
wait id: 6766 p1: 'count'=0x1 
Frame Control Field: 0x205b 
.101 1101 0010 1110 = Duration: 23854microseconds 
Receiver address: 2c:20:73:65:71:20(2c:20:73:65:71:20) 
Destination address: 2c:20:73:65:71:20(2c:20:73:65:71:20) 
Transmitter address: 32:31:33:34:39:33 (32:31:33:34:39:33) 
Source address: 32:31:33:34:39:33(32:31:33:34:39:33) 
BSS Id: 33:3a:32:31:33:36 (33:3a:32:31:33:36) 
.... .... .... 0011 = Fragment number: 3 
0011 0000 0011 .... = Sequence number: 771 
Frame check sequence: 0x202c5d36 incorrect, shouldbe 0xa38d2dd2------------------------------------>Here 
[FCS Status: Bad] 
TKIP/CCMP parameters 
Data (12641 bytes)

7、通过以上数据分析如下：

tcpdump.log文件，发现如下问题

Frame check sequence: 0x202c5d36 incorrect,should be 0xa38d2dd2

解决方法（参考官方文档Doc ID 1212204.1）

ASA设置如下：

ASA(config)#class-map sqlnet-port
ASA(config-cmap)#match port tcp eq <PORT_NUMBER>
ASA(config-cmap)#exit
ASA(config)#policy-map sqlnet_policy
ASA(config-pmap)#class sqlnet-port
ASA(config-pmap-c)#no inspect sqlnet
ASA(config-pmap-c)#exit
ASA(config)#service-policy sqlnet_policy interface outside

至此adg同步正常。

总结

11G在配置adg时会出现各种各样的问题。如：Heartbeat failed to connect to standby error is 439、error is 12154 这样的报错，可以在官方文档上搜索 ora-439 去解决问题。参考文档 Intermittent "Broken Pipe" Errors Through Cisco Firewall (Doc ID 1212204.1)