记录一次ARC1: Destination LOG_ARCHIVE_DEST_2 network reconnect abandoned解决

联系:手机/微信(+86 17813235971) QQ(107644445)QQ咨询惜分飞

标题:记录一次ARC1: Destination LOG_ARCHIVE_DEST_2 network reconnect abandoned解决

作者:惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

Data Guard主库出现如下错误
导致归档日志不同通过LOG_ARCHIVE_DEST_2传输到备库

Thu Apr 19 19:58:40 2012
Errors in file /u01/app/oracle/admin/orcl/udump/orcl_ora_6756.trc:
Thu Apr 19 19:58:40 2012
Errors in file /u01/app/oracle/admin/orcl/udump/orcl_ora_6756.trc:
Thu Apr 19 19:58:40 2012
Errors in file /u01/app/oracle/admin/orcl/udump/orcl_ora_6756.trc:
Thu Apr 19 20:00:26 2012
ARC1: Attempting destination LOG_ARCHIVE_DEST_2 network reconnect (3113)
ARC1: Destination LOG_ARCHIVE_DEST_2 network reconnect abandoned
PING[ARC1]: Error 3113 when pinging standby standby.
Thu Apr 19 20:18:18 2012
Errors in file /u01/app/oracle/admin/orcl/udump/orcl_ora_6756.trc:
Thu Apr 19 20:18:18 2012
Errors in file /u01/app/oracle/admin/orcl/udump/orcl_ora_6756.trc:
Thu Apr 19 20:18:18 2012
Errors in file /u01/app/oracle/admin/orcl/udump/orcl_ora_6756.trc:
Thu Apr 19 20:33:27 2012
ARC1: Attempting destination LOG_ARCHIVE_DEST_2 network reconnect (3113)
ARC1: Destination LOG_ARCHIVE_DEST_2 network reconnect abandoned 

[oracle@localhost ~]$ oerr ora 3113
03113, 00000, "end-of-file on communication channel"
// *Cause: The connection between Client and Server process was broken.
// *Action: There was a communication error that requires further investigation.
//          First, check for network problems and review the SQL*Net setup. 
//          Also, look in the alert.log file for any errors. Finally, test to 
//          see whether the server process is dead and whether a trace file
//          was generated at failure time.
提示连接错误

orcl_ora_6756.trc文件内容
这里没有得任何重要的有效信息

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1
System name:    Linux
Node name:      fcdb1
Release:        2.6.18-194.el5
Version:        #1 SMP Fri Apr 2 14:58:14 EDT 2010
Machine:        x86_64
Instance name: orcl
Redo thread mounted by this instance: 1
Oracle process number: 21
Unix process pid: 6756, image: oracle@fcdb1 (TNS V1-V3)

*** 2012-04-19 19:51:32.033
*** ACTION NAME:(0000045 STARTED16) 2012-04-19 19:51:32.026
*** MODULE NAME:(backup incr datafile) 2012-04-19 19:51:32.026
*** SERVICE NAME:(SYS$USERS) 2012-04-19 19:51:32.026
*** SESSION ID:(1518.294) 2012-04-19 19:51:32.026
*** ACTION NAME:(0000062 STARTED68) 2012-04-19 19:58:40.083
*** MODULE NAME:(backup full datafile) 2012-04-19 19:58:40.083
*** 2012-04-19 19:58:40.083
*** ACTION NAME:(0000068 STARTED16) 2012-04-19 19:58:40.156
*** 2012-04-19 20:18:18.436
*** ACTION NAME:(0000118 STARTED16) 2012-04-19 20:18:18.436
*** MODULE NAME:(backup incr datafile) 2012-04-19 20:18:18.436

查看相关参数

SQL> show parameter archive;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_config                   string      DG_CONFIG=(primary,standby)
log_archive_dest_1                   string      LOCATION=/u01/app/oracle/arch
                                                 VALID_FOR=(ALL_LOGFILES,ALL_RO
                                                 LES) DB_UNIQUE_NAME=primary
log_archive_dest_2                   string      SERVICE=standby LGWR ASYNC VAL
                                                 ID_FOR=(ONLINE_LOGFILES,PRIMAR
                                                 Y_ROLE) DB_UNIQUE_NAME=standby 
log_archive_dest_state_1             string      ENABLE
log_archive_dest_state_2             string      ENABLE

测试TNS

[oracle@fcdb1 bdump]$ tnsping standby

TNS Ping Utility for Linux: Version 10.2.0.1.0 - Production on 19-APR-2012 20:47:51

Copyright (c) 1997, 2005, Oracle.  All rights reserved.

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.203)(PORT = 1521))) 
(CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl)))
OK (0 msec)

[oracle@fcdb1 bdump]$ sqlplus sys/oracle@standby as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Thu Apr 19 20:49:05 2012

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

问题原因分析
从log_archive_dest_2 参数上可以看出默认是配置lgwr进程传输日志,但是因为备库没有配置standby redo log,所以使得启动arch传输日志,然后出现该问题,因为在传输过程中出现异常,导致arch不能继续和备库建立连接。

解决方法
查看了很多资料,给出的解决方法都是重启主库或者备库解决,我想既然是ARCH建立连接的失败,那么重启log_archive_dest_state_2参数,让arch进程重启。

SQL> ALTER SYSTEM SET log_archive_dest_state_2='DEFER' SCOPE=BOTH;

System altered.

SQL> ALTER SYSTEM SET log_archive_dest_state_2='ENABLE' SCOPE=BOTH;

System altered.

SQL> alter system switch logfile;

System altered.


--alert日志
Thu Apr 19 20:51:12 2012
ALTER SYSTEM SET log_archive_dest_state_2='DEFER' SCOPE=BOTH;
Thu Apr 19 20:51:32 2012
ALTER SYSTEM SET log_archive_dest_state_2='ENABLE' SCOPE=BOTH;
LNS1 started with pid=35, OS id=7012
Thu Apr 19 20:51:47 2012
Thread 1 advanced to log sequence 2025
  Current log# 2 seq# 2025 mem# 0: /u01/app/oracle/oradata/orcl/redo02.log
Thu Apr 19 20:51:48 2012
******************************************************************
LGWR: Setting 'active' archival for destination LOG_ARCHIVE_DEST_2
******************************************************************
Thu Apr 19 20:52:02 2012
Thread 1 advanced to log sequence 2026
  Current log# 3 seq# 2026 mem# 0: /u01/app/oracle/oradata/orcl/redo03.log
Thread 1 cannot allocate new log, sequence 2027

这个时候,查看备库日志也已经传输过去,通过修改log_archive_dest_state_2解决

ARC1: Attempting destination LOG_ARCHIVE_DEST_2 network reconnect (3113)
ARC1: Destination LOG_ARCHIVE_DEST_2 network reconnect abandoned 
此条目发表在 Oracle 分类目录。将固定链接加入收藏夹。

评论功能已关闭。