TNS-12535: TNS:operation timed out以及TNS-00505: Operation timed out的處理，tns-12535tns-00505

TNS-12535: TNS:operation timed out以及TNS-00505: Operation timed out的處理，tns-12535tns-00505


序言：查看alert日誌，發現很多報錯資訊：
[oracle@localhost trace]$ more alert_powerdes.log


***********************************************************************


***********************************************************************


Fatal NI connect error 12170.


  VERSION INFORMATION:
TNS for Linux: Version 11.2.0.1.0 - Production
Oracle Bequeath NT Protocol Adapter for Linux: Version 11.2.0.1.0 - Production
TCP/IP NT Protocol Adapter for Linux: Version 11.2.0.1.0 - Production


  VERSION INFORMATION:
TNS for Linux: Version 11.2.0.1.0 - Production
Oracle Bequeath NT Protocol Adapter for Linux: Version 11.2.0.1.0 - Production
TCP/IP NT Protocol Adapter for Linux: Version 11.2.0.1.0 - Production
  Time: 28-JAN-2015 15:14:16
  Time: 28-JAN-2015 15:14:16
  Tracing not turned on.
  Tracing not turned on.
  Tns error struct:
  Tns error struct:
    ns main err code: 12535
    ns main err code: 12535
    
TNS-12535: TNS:operation timed out
TNS-12535: TNS:operation timed out
    ns secondary err code: 12560
    ns secondary err code: 12560
    nt main err code: 505
    nt main err code: 505
    
TNS-00505: Operation timed out
TNS-00505: Operation timed out
    nt secondary err code: 110
    nt secondary err code: 110
    nt OS err code: 0
    nt OS err code: 0
  Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=xxx.xxx.170.220)(PORT=54418))
  Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=xxx.xxx.170.220)(PORT=54420))
Wed Jan 28 15:19:00 2015
LGWR: Standby redo logfile selected to archive thread 1 sequence 29226
LGWR: Standby redo logfile selected for thread 1 sequence 29226 for destination LOG_ARCHIVE_DEST_2
Thread 1 advanced to log sequence 29226 (LGWR switch)
  Current log# 3 seq# 29226 mem# 0: /home/oradata/powerdes/redo03.log
Wed Jan 28 15:19:01 2015
Archived Log entry 57344 added for thread 1 sequence 29225 ID 0xca2ab4eb dest 1:
Wed Jan 28 15:34:28 2015
Wed Jan 28 15:34:28 2015


......


一：分析，參考官方說明關於該警告的說明：
  Note:465043.1

   The "WARING:inbound connection timed out (ORA-3136)" in the alert log indicates that the client was not able to complete it's authentication within the period of time specified by parameter SQLNET.INBOUND_CONNECT_TIMEOUT.

   You may also witness ORA-12170 without timeout error on the database sqlnet.log file.This entry would also have the client address which failed to get authenticated.Some applications or JDBC thin driver applications may not have these details.

 1、網路攻擊，例如：半開串連攻擊

    Server gets a connection request from a malcious client which is not supposed to connect to the database,in which case the error thrown is the correct behavior.You can get the client address for which the error was thrown via sqlnet log file.

這個oracle dba處於區域網路，來自網路攻擊的可能也被排除了。


2、Client在default 60秒內沒有完成認證

    The server receives a valid client connection request but the client tabkes a long time to authenticate more than the default 60 seconds.


    去check是否預設的60秒：
[oracle@localhost ~]$ lsnrctl 


LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 28-JAN-2015 16:26:25

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

Welcome to LSNRCTL, type "help" for information.

LSNRCTL> show inbound_connect_timeout
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost.localdomain)(PORT=1521)))
LISTENER parameter "inbound_connect_timeout" set to 60
The command completed successfully
LSNRCTL> 

看到inbound_connect_timeout是60秒，有可能是由於Client在預設60秒內沒有完成認證這個原因引起的。


3、DB負載太高

    The DB server is heavily loaded due to which it cannot finish the client logon within the timeout specified.

    WANGING:inbound connection timed out (ORA-3136)
[oracle@localhost admin]$ w
18:24:09 up 88 days, 17:36,  6 users,  load average: 0.60, 0.88, 1.21
USER     TTY      FROM              LOGIN@   IDLE   JCPU   PCPU WHAT
root     pts/3    xxx.1xx.120.238  Tue11    1:55m  0.29s  0.04s -bash
root     pts/4    xxx.1xx.120.238  Tue11    0.00s  0.18s  0.00s w
root     pts/7    xxx.1xx.120.238  Tue14    6:51m  0.28s  0.20s rlwrap sqlplus / as sysdba
root     pts/6    xxx.1xx.120.238  15:49    2:34m  0.00s  0.00s -bash
[oracle@localhost admin]$ 
線上db負載很低，w下來不到1，所以排除這種情況。


二：開始設定inbound_connect_timeout的值


1，查看inbound_connect_timeout的值

[oracle@localhost ~]$ lsnrctl 


LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 28-JAN-2015 16:26:25


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


Welcome to LSNRCTL, type "help" for information.


LSNRCTL> show inbound_connect_timeout
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost.localdomain)(PORT=1521)))
LISTENER parameter "inbound_connect_timeout" set to 60
The command completed successfully
LSNRCTL> 


大概有3種辦法來操作：
  1)、設定sqlnet.ora檔案：SQLNET.INBOUND_CONNECT_TIMEOUT=0；
  2)、設定listener.ora檔案：INBOUND_CONNECT_TIMEOUT_listenername=0；
  3)、然後reload或者重啟監聽。


2，線上臨時重新設定值
LSNRCTL> show inbound_connect_timeout 
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
LISTENER parameter "inbound_connect_timeout" set to 60
The command completed successfully
LSNRCTL> 
LSNRCTL> 
LSNRCTL> set inbound_connect_timeout 0
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
LISTENER parameter "inbound_connect_timeout" set to 0
The command completed successfully
LSNRCTL> 


3，永久性在listener.ora設定
[oracle@powerlong4 admin]$ vim listener.ora 
INBOUND_CONNECT_TIMEOUT_listener=0
[oracle@powerlong4 admin]$ 
[oracle@powerlong4 admin]$ 
[oracle@powerlong4 admin]$ lsnrctl stop


LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 28-JAN-2015 16:40:33


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


Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
The command completed successfully
[oracle@powerlong4 admin]$ lsnrctl start


LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 28-JAN-2015 16:40:37


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


Starting /oracle/app/oracle/product/11.2.0/dbhome_1/bin/tnslsnr: please wait...


TNSLSNR for Linux: Version 11.2.0.1.0 - Production
System parameter file is /oracle/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Log messages written to /oracle/app/oracle/diag/tnslsnr/powerlong4/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=powerlong4)(PORT=1521)))


Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date                28-JAN-2015 16:40:37
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /oracle/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File         /oracle/app/oracle/diag/tnslsnr/powerlong4/listener/alert/log.xml
Listening Endpoints Summary...
 (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=powerlong4)(PORT=1521)))
The listener supports no services
The command completed successfully
[oracle@powerlong4 admin]$ lsnrctl 


LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 28-JAN-2015 16:40:41


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


Welcome to LSNRCTL, type "help" for information.


LSNRCTL> show inbound_connect_timeout
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
LISTENER parameter "inbound_connect_timeout" set to 0
The command completed successfully
LSNRCTL> exit
[oracle@powerlong4 admin]$ vim listener.ora 
[oracle@powerlong4 admin]$ vim listener.ora 
[oracle@powerlong4 admin]$ 
[oracle@powerlong4 admin]$ lsnrctl stop


LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 28-JAN-2015 16:41:38


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


Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
^[[AThe command completed successfully
[oracle@powerlong4 admin]$ lsnrctl start


LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 28-JAN-2015 16:41:46


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


Starting /oracle/app/oracle/product/11.2.0/dbhome_1/bin/tnslsnr: please wait...


TNSLSNR for Linux: Version 11.2.0.1.0 - Production
System parameter file is /oracle/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Log messages written to /oracle/app/oracle/diag/tnslsnr/powerlong4/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=powerlong4)(PORT=1521)))


Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date                28-JAN-2015 16:41:46
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /oracle/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File         /oracle/app/oracle/diag/tnslsnr/powerlong4/listener/alert/log.xml
Listening Endpoints Summary...
 (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=powerlong4)(PORT=1521)))
The listener supports no services
The command completed successfully
[oracle@powerlong4 admin]$ lsnrctl 


LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 28-JAN-2015 16:41:49


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


Welcome to LSNRCTL, type "help" for information.


LSNRCTL> show inbound_connect_timeout
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
LISTENER parameter "inbound_connect_timeout" set to 0
The command completed successfully
LSNRCTL> 
LSNRCTL> exit
[oracle@powerlong4 admin]$ 

PS：這裡使用lsnrctl stop|start會斷掉當前oracle裡的所有用戶端串連，也可以使用lsnrctl reload來載入

       後續：設定為0之後再沒有出現過類似的警示資訊，不過設定inbound_connect_timeout為0，是有隱患的。因為這個參數從9i開始引入，指定了用戶端串連伺服器並且提供認證資訊的逾時時間，如果超過這個時間用戶端沒有提供正確的認證資訊，伺服器會自動中止串連請求，同時會記錄試圖串連的IP地址和ORA-12170：TNS:Connect timeout occurred錯誤。

    這個參數的引入，主要是防止DoS攻擊，惡意攻擊者可以通過不停的開啟大量串連請求，佔用伺服器的串連資源，使得伺服器無法提供有效服務。在10.2.0.1起，該參數預設設定為60秒。但是，這個參數的引入也導致了一些相關的Bug。比如：


    Bug 5594769 - REMOTE SESSION DROPPED WHEN LOCAL SESSION SHARED AND INBOUND_CONNECT_TIMEOUT SET
    Bug 5249163 - CONNECTS REFUSED BY TNSLSNR EVERY 49 DAYS FOR INBOUND_CONNEC_TIMEOUT SECONDS
    所以設定為0也是存在被攻擊的隱患，設定為60秒太長了，所以最後權衡了下，我將inbound_connect_timeout設定成了8秒。


    參考文章地址：http://www.cnblogs.com/future2012lg/p/3739752.html

 ----------------------------------------------------------------------------------------------------------------
<著作權，文章允許轉載，但必須以連結方式註明源地址，否則追究法律責任!>
原部落格地址：      http://blog.itpub.net/26230597/viewspace-1418586/
原作者：黃杉 (mchdba)
----------------------------------------------------------------------------------------------------------------