分类目录归档:Oracle 监听

Alert Log Errors: 12170 TNS-12535/TNS-00505: Operation Timed Out

客户反馈系统经常报会话超时,导致应用测试无法正常进行,经检查alert日志发现

Fatal NI connect error 12170.

  VERSION INFORMATION:
        TNS for HPUX: Version 11.2.0.4.0 - Production
        Oracle Bequeath NT Protocol Adapter for HPUX: Version 11.2.0.4.0 - Production
        TCP/IP NT Protocol Adapter for HPUX: Version 11.2.0.4.0 - Production
  Time: 29-SEP-2014 20:42:56
  Tracing not turned on.
  Tns error struct:
    ns main err code: 12535

TNS-12535: TNS:operation timed out
    ns secondary err code: 12560
    nt main err code: 505

TNS-00505: Operation timed out
    nt secondary err code: 238
    nt OS err code: 0
  Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=10.78.199.47)(PORT=55447))
Mon Sep 29 20:42:56 2014

虽然大部分网站或者mos上描述,Fatal NI connect error 12170部分情况考虑使用配置如下配置

##调整listener.ora 
vi $ORACLE_HOME/network/admin/listener.ora
增加:
DIAG_ADR_ENABLED_LISTENER=OFF
INBOUND_CONNECT_TIMEOUT_LISTENER=180

##调整sqlnet.ora  
vi $ORACLE_HOME/network/admin/sqlnet.ora
增加:
DIAG_ADR_ENABLED=OFF
SQLNET.INBOUND_CONNECT_TIMEOUT=180

这些已经配置,但是现在报12170 TNS-12535 TNS-00505错误,通过结合mos发现,出现该问题,可能是由于应用服务器和数据库服务器之间的防火墙策略设置不适合业务查询需求,出现应用服务器和数据库服务器防火墙超时(比如应用服务器发起一个大查询,在数据库服务器中执行,尚未返回结果,可是网络已经超时,终止会话)
补充知识点

The 'nt secondary err code' identifies the underlying network transport, such as (TCP/IP) timeout limit. 
In the current case 60 identifies Windows underlying transport layer.

The "nt secondary err code" will be different based on the operating system:

Linux x86 or Linux x86-64: "nt secondary err code: 110"
HP-UX : "nt secondary err code: 238"
AIX: "nt secondary err code: 78"
Solaris: "nt secondary err code: 145"


The alert.log message indicates that a connection was terminated AFTER it was established to the instance.  
In this case, it was terminated 2 hours and 3 minutes after the listener handed the connection to the database. 

 This would indicate an issue with a firewall where a maximum idle time setting is in place. 

The connection would not necessarily be "idle".  This issue can arise during a long running query
or when using JDBC Thin connection pooling. If there is no data 'on the wire' for lengthy

periods of time for any reason, the firewall might terminate the connection.

解决方案

The non-Oracle solution would be to remove or increase the firewall setting for maximum idle time.  
In cases where this is not feasible, Oracle offers the following suggestion:

The following parameter, set at the **RDBMS_HOME/network/admin/sqlnet.ora, can resolve this kind of problem.  
DCD or SQLNET.EXPIRE_TIME can mimic data transmission between the server and the client during long periods of idle time.

SQLNET.EXPIRE_TIME=n  Where <n> is a non-zero value set in minutes.  

See the following : Note 257650.1 Resolving Problems with Connection Idle Timeout With Firewall

当然除下面数据库中解决外,还可以在网络防火墙层面解决,比如增加网络空闲终止时间等

具体参考:Alert Log Errors: 12170 TNS-12535/TNS-00505: Operation Timed Out (Doc ID 1628949.1)
Fatal NI Connect Error 12170, ‘TNS-12535: TNS:operation timed out’ Reported in 11g Alert Log (Doc ID 1286376.1)

发表在 Oracle 监听 | 标签为 , , | 评论关闭

WARNING: Subscription for node down event still pending

监听日志中出现很多”WARNING: Subscription for node down event still pending”警告

--监听日志中内容
……
26-NOV-2012 09:35:48 * ping * 0
WARNING: Subscription for node down event still pending
……
26-NOV-2012 09:45:49 * ping * 0
WARNING: Subscription for node down event still pending
……

--监听日志大小
$ ls -l /oracle/product/10g/network/log/listener.log
-rw-r-----    1 oracle   dba       229526148 Nov 26 14:20 /oracle/product/10g/network/log/listener.log

--总计条数
$ grep "Subscription for node down event still pending" \
> /oracle/product/10g/network/log/listener.log|wc -l
   77306

数据库版本和平台

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
PL/SQL Release 10.2.0.5.0 - Production
CORE    10.2.0.5.0      Production
TNS for IBM/AIX RISC System/6000: Version 10.2.0.5.0 - Productio
NLSRTL Version 10.2.0.5.0 - Production

监听日志配置

$ more /oracle/product/10g/network/admin/listener.ora
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /oracle/product/10g)
      (PROGRAM = extproc)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = xifenfei)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
    )
  )

解决方法

--监听文件中增加
SUBSCRIBE_FOR_NODE_DOWN_EVENT_<listener_name>=OFF

--reload监听
lsnrctl reload

补充说明

SUBSCRIBE_FOR_NODE_DOWN_EVENT_<listener_name>=OFF will prevent the messages 
from being written to the log file and may also prevent the TNS Listener from hanging periodically. 

Setting SUBSCRIBE_FOR_NODE_DOWN_<listener_name> to OFF disables 
a necessary RAC functionality(Oracle Notification Service[ONS],fast application notification[FAN]). 
The above workaround is recommended only for non-RAC environments. 
The issue may be present in all 10g and newer installations.

具体参考:372959.1和340091.1

发表在 Oracle 监听 | 3 条评论

因IPC导致多个监听不能正常启动

在一台机器上装了9.2.0.4和10.1.0.4数据库,启动监听时候发现有一个启动不了
9i监听配置

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST = xifenfei.com)(PORT = 1522))
      )
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
      )
    )
  )

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = xifenfei)
      (ORACLE_HOME = /u01/oracle/9.2.0/db_1)
      (SID_NAME = xff)
    )
  )

10g监听配置

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /u02/app/oracle/db10g)
      (PROGRAM = extproc)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
      )
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST = xifenfei.com)(PORT = 1521))
      )
    )
  )

分别启动监听
启动10g监听

[oraem@xifenfei ~]$ lsnrctl start

LSNRCTL for Linux: Version 10.1.0.4.0 - Production on 12-JUN-2012 15:28:51

Copyright (c) 1991, 2004, Oracle.  All rights reserved.

Starting /u02/app/oracle/db10g/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 10.1.0.4.0 - Production
System parameter file is /u02/app/oracle/db10g/network/admin/listener.ora
Log messages written to /u02/app/oracle/db10g/network/log/listener.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=xifenfei.com)(PORT=1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 10.1.0.4.0 - Production
Start Date                12-JUN-2012 15:28:51
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u02/app/oracle/db10g/network/admin/listener.ora
Listener Log File         /u02/app/oracle/db10g/network/log/listener.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=xifenfei.com)(PORT=1521)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

启动9i监听

[oracle@xifenfei ~]$ lsnrctl start

LSNRCTL for Linux: Version 9.2.0.4.0 - Production on 12-JUN-2012 15:01:44

Copyright (c) 1991, 2002, Oracle Corporation.  All rights reserved.

Starting /u01/oracle/9.2.0/db_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 9.2.0.4.0 - Production
System parameter file is /u01/oracle/9.2.0/db_1/network/admin/listener.ora
Log messages written to /u01/oracle/9.2.0/db_1/network/log/listener.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=xifenfei.com)(PORT=1522)))
Error listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC)))
TNS-12542: TNS:address already in use
 TNS-12560: TNS:protocol adapter error
  TNS-00512: Address already in use
   Linux Error: 98: Address already in use

Listener failed to start. See the error message(s) above...

这里很奇怪两个监听使用不同的端口,为什么不能提示Address被占用呢,难道9i的已经启动了

查看9i的监听状态

[oracle@xifenfei ~]$ lsnrctl status

LSNRCTL for Linux: Version 9.2.0.4.0 - Production on 12-JUN-2012 15:02:23

Copyright (c) 1991, 2002, Oracle Corporation.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=xifenfei)(PORT=1522)))
TNS-12541: TNS:no listener
 TNS-12560: TNS:protocol adapter error
  TNS-00511: No listener
   Linux Error: 111: Connection refused
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC)))
TNS-12618: TNS:versions are incompatible

9i监听不能启动原因
这里有重大发现TNS:versions are incompatible.我这台服务器只有一个9i和一个10g的数据库出现版本不兼容,那就是说这个命令可能访问了10g的库中的每个监听信息.继续往上看,发现(DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC))).
When a process is on the same machine as the server, use the IPC protocol for connectivity instead of TCP. Inner Process Communication on the same machine does not have the overhead of packet building and deciphering that TCP has.
通过这段话可以看出IPC(Inner Process Communication)是使用于本机的内部通讯,不用包的封装,可以很大程度上提高程序执行效率.看到上面的IPC提示,我们可以确定该错误是因为10g中已经启用了IPC,然后9i的监听也要来启动这个,从而导致该错误.

解决办法
因为在同一个机器上,只能其中的一个数据库启用IPC.修改9i的监听配置

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1522))
      )
    #  (ADDRESS_LIST =
    #    (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
    #  )
    )
  )

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /u01/oracle/9.2.0/db_1)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = xifenfei)
      (ORACLE_HOME = /u01/oracle/9.2.0/db_1)
      (SID_NAME = xff)
    )
  )

启动9i监听

[oracle@xifenfei ~]$ lsnrctl

LSNRCTL for Linux: Version 9.2.0.4.0 - Production on 12-JUN-2012 15:12:55

Copyright (c) 1991, 2002, Oracle Corporation.  All rights reserved.

Welcome to LSNRCTL, type "help" for information.

LSNRCTL> start
Starting /u01/oracle/9.2.0/db_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 9.2.0.4.0 - Production
System parameter file is /u01/oracle/9.2.0/db_1/network/admin/listener.ora
Log messages written to /u01/oracle/9.2.0/db_1/network/log/listener.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=xifenfei.com)(PORT=1522)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1522)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 9.2.0.4.0 - Production
Start Date                12-JUN-2012 15:12:57
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  OFF
SNMP                      OFF
Listener Parameter File   /u01/oracle/9.2.0/db_1/network/admin/listener.ora
Listener Log File         /u01/oracle/9.2.0/db_1/network/log/listener.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=xifenfei.com)(PORT=1522)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "xifenfei" has 1 instance(s).
  Instance "xff", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

查看两个监听进程

[oracle@xifenfei ~]$ ps -ef|grep tns|grep -v grep
oraem    18099     1  0 13:27 ?        00:00:00 /u02/app/oracle/db10g/bin/tnslsnr LISTENER -inherit
oracle   24312     1  0 15:12 pts/0    00:00:00 /u01/oracle/9.2.0/db_1/bin/tnslsnr LISTENER -inherit
发表在 Oracle 监听 | 一条评论