联系:手机/微信(+86 17813235971) QQ(107644445)
标题:记录一次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