点击打开链接
-
ORA-3136错误解决1.参数含义:SQLNET.INBOUND_CONNECT_TIMEOUT is set to a value in seconds and determines how long a client has toprovide the necessary authentication information to a database.INBOUND_CONNECT_TIMEOUT_listenername is set to a value in seconds and determines how long a client hasto complete its connect request to the listener after the network connection has been established.2.参数意义:在oracle 10.2以后的版本当中将SQLNET.INBOUND_CONNECT_TIMEOUT 和 INBOUND_CONNECT_TIMEOUT_listenername参数设置缺省为60s,之前的版本当中默认为0(即关闭此功能)。这两个参数用于防止恶意client通过网络攻击dbserverNote: This timeout restriction was introduced to combat Denial of Service (DoS) attack wherebymalicious clients attempt to flood database servers with connect requests that consumes resources.3.报出此warning的原因如下:a.server收到一个从恶意client发来的连接请求,在这种情况下抛出的错误将是预期的和可取的。(客户端反复输错username/password)b.服务器接收到一个有效的客户端连接请求,但客户需要很长时间来验证超过默认60秒。(客户端网络延迟)c.DB服务器负载很高导致它无法在指定时间内完成客户端登录请求。( 数据库服务器高负载)4.解决办法:a.验证db server端,本地连接是否成功(listener是否可用)b.验证本地连接是否延迟(本地网络是否高效可用)c.验证数据库性能(db server是否高负载)d.告警日志是否在之前报过ORA-600或ORA-7445e.检查是否有client反复尝试登录密码(这是目前最有可能的原因)f.关闭此功能(将2参数设置为0,设置方法参照步骤5)也可将2参数设置为适当值。5.修改这两个参数的方法如下:Edit the server side sqlnet.ora file and add this parameter:SQLNET.INBOUND_CONNECT_TIMEOUT=<n> Where <n> is the value in seconds.E.g.:SQLNET.INBOUND_CONNECT_TIMEOUT = 120Edit the listener.ora file and add this parameter:INBOUND_CONNECT_TIMEOUT_<listenername> = <n> Again, where <n> is the timeout value in seconds.For example if the listener name is LISTENER then use:INBOUND_CONNECT_TIMEOUT_LISTENER = 1106.总结:此warning提示,极有可能是有客户端在不知道密码的情况下,反复尝试连接数据库或登录延迟导致,个人认为可以忽略,重启监听并不会解决该问题,只是暂时清空了监听的告警信息,也可观察一段时间,或抓取性能分析报告,确认是否为负载过高导致性能下降从而引起的告警。:ORA-3136 错误解决:ORA-3136 错误解决1、首先通过后台alter日志发现报如下错误:Mon May 20 12:44:19 2013WARNING: inbound connection timed out (ORA-3136)Mon May 20 12:44:19 2013WARNING: inbound connection timed out (ORA-3136)Mon May 20 12:44:19 2013WARNING: inbound connection timed out (ORA-3136)2、观察sqlnet.log中记录如下错误:Fatal NI connect error 12514, connecting to:(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=XXXXX)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=XXXXXXXX)(INSTANCE_NAME=XXXX)(CID=(PROGRAM=XXXXXX)(HOST=XXXXXX)(USER=oracle))))VERSION INFORMATION:TNS for Linux: Version 10.2.0.1.0 - ProductionTCP/IP NT Protocol Adapter for Linux: Version 10.2.0.1.0 - ProductionTime: 28-11月-2008 14:10:42Tracing not turned on.Tns error struct:ns main err code: 12564TNS-12564: TNS:connection refusedns secondary err code: 0nt main err code: 0nt secondary err code: 0nt OS err code: 03、重新reload一下Listner:[oracle@XXX ~]$ lsnrctlLSNRCTL for Linux: Version 10.2.0.4.0 - Production on 20-5月 -2013 12:54:56Copyright (c) 1991, 2007, Oracle. All rights reserved.Welcome to LSNRCTL, type "help" for information.LSNRCTL> reloadConnecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))The command completed successfullyLSNRCTL> servicesConnecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))Services Summary...Service "+ASM" has 1 instance(s).Instance "+ASM2", status BLOCKED, has 1 handler(s) for this service...Handler(s):"DEDICATED" established:0 refused:0 state:readyLOCAL SERVERService "+ASM_XPT" has 1 instance(s).Instance "+ASM2", status BLOCKED, has 1 handler(s) for this service...Handler(s):"DEDICATED" established:0 refused:0 state:readyLOCAL SERVERService "PLSExtProc" has 1 instance(s).Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...Handler(s):"DEDICATED" established:0 refused:0LOCAL SERVERService "XXXXXXXX" has 2 instance(s).Instance "XXXXXX", status READY, has 1 handler(s) for this service...Handler(s):"DEDICATED" established:16 refused:0 state:readyREMOTE SERVER(ADDRESS=(PROTOCOL=tcp)(HOST=XXXXXXXX)(PORT=1521))Instance "XXXXXX", status READY, has 2 handler(s) for this service...Handler(s):"DEDICATED" established:0 refused:0 state:readyREMOTE SERVER(ADDRESS=(PROTOCOL=tcp)(HOST=XXXXXX)(PORT=1521))"DEDICATED" established:31 refused:0 state:readyLOCAL SERVERService "XXXXXXXXX" has 2 instance(s).Instance "XXXXXX", status READY, has 1 handler(s) for this service...Handler(s):"D000" established:0 refused:0 current:0 max:972 state:readyDISPATCHER <machine: XXXXXXX, pid: 28579>(ADDRESS=(PROTOCOL=tcp)(HOST=XXXXXX)(PORT=6661))Instance "XXXXXX", status READY, has 1 handler(s) for this service...Handler(s):"D000" established:0 refused:0 current:0 max:1022 state:readyDISPATCHER <machine: XXXX, pid: 28800>(ADDRESS=(PROTOCOL=tcp)(HOST=XXXXX)(PORT=51924))Service "XXXXXXXX" has 2 instance(s).Instance "XXXXXXX", status READY, has 1 handler(s) for this service...Handler(s):"DEDICATED" established:16 refused:0 state:readyREMOTE SERVER(ADDRESS=(PROTOCOL=tcp)(HOST=XXXXX)(PORT=1521))Instance "XXXXXXX", status READY, has 2 handler(s) for this service...Handler(s):"DEDICATED" established:0 refused:0 state:readyREMOTE SERVER(ADDRESS=(PROTOCOL=tcp)(HOST=XXXXXXX)(PORT=1521))"DEDICATED" established:31 refused:0 state:readyLOCAL SERVERThe command completed successfullyLSNRCTL> show inbound_connect_timeoutConnecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))LISTENER parameter "inbound_connect_timeout" set to 60The command completed successfully4、修改之后观察一段时间 系统恢复正常。
-
ORA-3136错误解决1.参数含义:SQLNET.INBOUND_CONNECT_TIMEOUT is set to a value in seconds and determines how long a client has toprovide the necessary authentication information to a database.INBOUND_CONNECT_TIMEOUT_listenername is set to a value in seconds and determines how long a client hasto complete its connect request to the listener after the network connection has been established.2.参数意义:在oracle 10.2以后的版本当中将SQLNET.INBOUND_CONNECT_TIMEOUT 和 INBOUND_CONNECT_TIMEOUT_listenername参数设置缺省为60s,之前的版本当中默认为0(即关闭此功能)。这两个参数用于防止恶意client通过网络攻击dbserverNote: This timeout restriction was introduced to combat Denial of Service (DoS) attack wherebymalicious clients attempt to flood database servers with connect requests that consumes resources.3.报出此warning的原因如下:a.server收到一个从恶意client发来的连接请求,在这种情况下抛出的错误将是预期的和可取的。(客户端反复输错username/password)b.服务器接收到一个有效的客户端连接请求,但客户需要很长时间来验证超过默认60秒。(客户端网络延迟)c.DB服务器负载很高导致它无法在指定时间内完成客户端登录请求。( 数据库服务器高负载)4.解决办法:a.验证db server端,本地连接是否成功(listener是否可用)b.验证本地连接是否延迟(本地网络是否高效可用)c.验证数据库性能(db server是否高负载)d.告警日志是否在之前报过ORA-600或ORA-7445e.检查是否有client反复尝试登录密码(这是目前最有可能的原因)f.关闭此功能(将2参数设置为0,设置方法参照步骤5)也可将2参数设置为适当值。5.修改这两个参数的方法如下:Edit the server side sqlnet.ora file and add this parameter:SQLNET.INBOUND_CONNECT_TIMEOUT=<n> Where <n> is the value in seconds.E.g.:SQLNET.INBOUND_CONNECT_TIMEOUT = 120Edit the listener.ora file and add this parameter:INBOUND_CONNECT_TIMEOUT_<listenername> = <n> Again, where <n> is the timeout value in seconds.For example if the listener name is LISTENER then use:INBOUND_CONNECT_TIMEOUT_LISTENER = 1106.总结:此warning提示,极有可能是有客户端在不知道密码的情况下,反复尝试连接数据库或登录延迟导致,个人认为可以忽略,重启监听并不会解决该问题,只是暂时清空了监听的告警信息,也可观察一段时间,或抓取性能分析报告,确认是否为负载过高导致性能下降从而引起的告警。:ORA-3136 错误解决:ORA-3136 错误解决1、首先通过后台alter日志发现报如下错误:Mon May 20 12:44:19 2013WARNING: inbound connection timed out (ORA-3136)Mon May 20 12:44:19 2013WARNING: inbound connection timed out (ORA-3136)Mon May 20 12:44:19 2013WARNING: inbound connection timed out (ORA-3136)2、观察sqlnet.log中记录如下错误:Fatal NI connect error 12514, connecting to:(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=XXXXX)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=XXXXXXXX)(INSTANCE_NAME=XXXX)(CID=(PROGRAM=XXXXXX)(HOST=XXXXXX)(USER=oracle))))VERSION INFORMATION:TNS for Linux: Version 10.2.0.1.0 - ProductionTCP/IP NT Protocol Adapter for Linux: Version 10.2.0.1.0 - ProductionTime: 28-11月-2008 14:10:42Tracing not turned on.Tns error struct:ns main err code: 12564TNS-12564: TNS:connection refusedns secondary err code: 0nt main err code: 0nt secondary err code: 0nt OS err code: 03、重新reload一下Listner:[oracle@XXX ~]$ lsnrctlLSNRCTL for Linux: Version 10.2.0.4.0 - Production on 20-5月 -2013 12:54:56Copyright (c) 1991, 2007, Oracle. All rights reserved.Welcome to LSNRCTL, type "help" for information.LSNRCTL> reloadConnecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))The command completed successfullyLSNRCTL> servicesConnecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))Services Summary...Service "+ASM" has 1 instance(s).Instance "+ASM2", status BLOCKED, has 1 handler(s) for this service...Handler(s):"DEDICATED" established:0 refused:0 state:readyLOCAL SERVERService "+ASM_XPT" has 1 instance(s).Instance "+ASM2", status BLOCKED, has 1 handler(s) for this service...Handler(s):"DEDICATED" established:0 refused:0 state:readyLOCAL SERVERService "PLSExtProc" has 1 instance(s).Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...Handler(s):"DEDICATED" established:0 refused:0LOCAL SERVERService "XXXXXXXX" has 2 instance(s).Instance "XXXXXX", status READY, has 1 handler(s) for this service...Handler(s):"DEDICATED" established:16 refused:0 state:readyREMOTE SERVER(ADDRESS=(PROTOCOL=tcp)(HOST=XXXXXXXX)(PORT=1521))Instance "XXXXXX", status READY, has 2 handler(s) for this service...Handler(s):"DEDICATED" established:0 refused:0 state:readyREMOTE SERVER(ADDRESS=(PROTOCOL=tcp)(HOST=XXXXXX)(PORT=1521))"DEDICATED" established:31 refused:0 state:readyLOCAL SERVERService "XXXXXXXXX" has 2 instance(s).Instance "XXXXXX", status READY, has 1 handler(s) for this service...Handler(s):"D000" established:0 refused:0 current:0 max:972 state:readyDISPATCHER <machine: XXXXXXX, pid: 28579>(ADDRESS=(PROTOCOL=tcp)(HOST=XXXXXX)(PORT=6661))Instance "XXXXXX", status READY, has 1 handler(s) for this service...Handler(s):"D000" established:0 refused:0 current:0 max:1022 state:readyDISPATCHER <machine: XXXX, pid: 28800>(ADDRESS=(PROTOCOL=tcp)(HOST=XXXXX)(PORT=51924))Service "XXXXXXXX" has 2 instance(s).Instance "XXXXXXX", status READY, has 1 handler(s) for this service...Handler(s):"DEDICATED" established:16 refused:0 state:readyREMOTE SERVER(ADDRESS=(PROTOCOL=tcp)(HOST=XXXXX)(PORT=1521))Instance "XXXXXXX", status READY, has 2 handler(s) for this service...Handler(s):"DEDICATED" established:0 refused:0 state:readyREMOTE SERVER(ADDRESS=(PROTOCOL=tcp)(HOST=XXXXXXX)(PORT=1521))"DEDICATED" established:31 refused:0 state:readyLOCAL SERVERThe command completed successfullyLSNRCTL> show inbound_connect_timeoutConnecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))LISTENER parameter "inbound_connect_timeout" set to 60The command completed successfully4、修改之后观察一段时间 系统恢复正常。