WARNING: inbound connection timed out (ORA-3136)連線超時問題

*
*時間：2009-03-010
*環境：AIX5.3   Oracle10g
*WARNING: inbound connection timed out (ORA-3136)連線超時問題
*/
1、alter_SID.log日誌：aaa
Mon Mar  9 02:18:40 2009
ksvcreate: Process(q002) creation failed
Mon Mar  9 02:32:29 2009
WARNING: inbound connection timed out (ORA-3136)
Mon Mar  9 02:33:02 2009
WARNING: inbound connection timed out (ORA-3136)
Mon Mar  9 02:33:19 2009
WARNING: inbound connection timed out (ORA-3136)

2、sqlnet.log日誌

Fatal NI connect error 12170.

  VERSION INFORMATION:
 TNS for IBM/AIX RISC System/6000: Version 10.2.0.1.0 - Production
 TCP/IP NT Protocol Adapter for IBM/AIX RISC System/6000: Version 10.2.0.1.0 - Production
 Oracle Bequeath NT Protocol Adapter for IBM/AIX RISC System/6000: Version 10.2.0.1.0 - Production
  Time: 09-MAR-2009 02:32:29
  Tracing not turned on.
  Tns error struct:
    ns main err code: 12535
    TNS-12535: TNS:operation timed out
    ns secondary err code: 12606
    nt main err code: 0
    nt secondary err code: 0
    nt OS err code: 0
  Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=10.171.4.201)(PORT=3663))

 

3、參考官方說明關於該警告的說明：
Note:465043.1

The "WARNING: 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 clinet 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 malicious 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.

2.Client在default 60秒內沒有完成認證
The server receives a valid client connection request but the client takes a long time to authenticate more than the default 60 seconds.

3.DB負載太高
The DB server is heavily loaded due to which it cannot finish the client logon within the timeout specified.

 

WARNING: inbound connection timed out (ORA-3136)
這個錯誤跟 監聽的一個引數有關：SQLNET.INBOUND_CONNECT_TIMEOUT
這個引數從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_CONNECT_TIMEOUT SECONDS
該引數可以透過設定為0來禁用，在服務媏
設定sqlnet.ora檔案：SQLNET.INBOUND_CONNECT_TIMEOUT＝0
設定listener.ora檔案: INBOUND_CONNECT_TIMEOUT_listenername＝0
然後reload或者重啟監聽

 

這是由於連線超時所產生的問題，在10.2.0.1.0版本中sqlnet.inbound_connect_timeout引數預設為60秒，即如果連線時間超過60秒則提示超時，而在其他版本中這兩個引數預設為0，即無限制。

如何操作：
一、檢視中listener.ora中的inbound_connect_timeout引數值
1、進入lsnrctl，
LHXXDBS01:oraoms> lsnrctl
2、檢視inbound_connect_timeout引數：
LSNRCTL> show inbound_connect_time
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=LHXXDBS)(PORT=1568)))
LISTENER parameter "inbound_connect_timeout" set to 0
The command completed successfully

如果inbound_connect_timeout引數值不為0，則可以修改為0
修改：
LSNRCTL> set inbound_connect_time 0
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=LHXXDBS)(PORT=1568)))
LISTENER parameter "inbound_connect_timeout" set to 0
The command completed successfully

 

二、修改/oracle/oms/102_64/network/admin/sqlnet.ora
Vi sqlnet.ora
SQLNET.INBOUND_CONNECT_TIMEOUT = 0
保持退出  wq!

三、重新載入listener
LSNRCTL> reload
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=LHXXDBS)(PORT=1568)))
The command completed successfully

第二天觀察沒有出現WARNING: inbound connection timed out (ORA-3136)連線超時的現象了。