oracle log network server,记录一次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