Oracle10g ora12170,ORA-3136、TNS-12535 12170 12606

问题描述：

ALERT文件中报以下错误：**********************************************************************

Tue Nov 10 20:33:22 2009

WARNING: inbound connection timed out (ORA-3136)

Tue Nov 10 20:33:26 2009

WARNING: inbound connection timed out (ORA-3136)

Tue Nov 10 20:39:09 2009

WARNING: inbound connection timed out (ORA-3136)

Tue Nov 10 20:46:02 2009

WARNING: inbound connection timed out (ORA-3136)

Tue Nov 10 21:44:52 2009

WARNING: inbound connection timed out (ORA-3136)

Tue Nov 10 21:45:21 2009

WARNING: inbound connection timed out (ORA-3136)

Tue Nov 10 21:45:21 2009

WARNING: inbound connection timed out (ORA-3136)

Tue Nov 10 21:45:22 2009

WARNING: inbound connection timed out (ORA-3136)

sqlnet文件中报以下错误：***********************************************************************

Fatal NI connect error 12170.

VERSION INFORMATION:

TNS for IBM/AIX RISC System/6000: Version 10.2.0.3.0 - Production

TCP/IP NT Protocol Adapter for IBM/AIX RISC System/6000: Version 10.2.0.3.0 - Production

Oracle Bequeath NT Protocol Adapter for IBM/AIX RISC System/6000: Version 10.2.0.3.0 - Production

Time: 10-NOV-2009 13:47:37

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=172.17.130.52)(PORT=52424))

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

METALINK：

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 Proto Adapter for IBM/AIX RISC System/6000: Version

10.2.0.1.0 - Production

Oracle Bequeath NT Proto Adapter for IBM/AIX RISC System/6000:

Version 10.2.0.1.0 - Production

Time: 20-APR-2006 18:15:22

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=(PROTO =tcp)(HOST=)(PORT=47622))

alert.log:

WARNING: inbound connection timed out (ORA-3136)

Thu Apr 20 18:15:22 200

Solution

1.set INBOUND_CONNECT_TIMEOUT_=0 in listener.ora

2. set SQLNET.INBOUND_CONNECT_TIMEOUT = 0 in sqlnet.ora of server.

3. stop and start both listener and database.

Connections that Used to Work in Oracle 10.1 Now Intermittently Fail with ORA-3113,ORA-3106 or ORA-3136 in 10.2 [ID 345197.1]

修改时间 24-SEP-2008     类型 PROBLEM     状态 PUBLISHED

In this Document

Applies to:

Oracle Net Services - Version: 10.2.0.1.0

This problem can occur on any platform.

Checked for relevance on 01-MAY-2008.

Symptoms

The Oracle Net 10g parameters SQLNET.INBOUND_CONNECT_TIMEOUT and INBOUND_CONNECT_TIMEOUT_listenername default to 0 (indefinite) in 10.1.  To address Denial of Service (DOS) issues,  the parameters were set to have a default of 60 (seconds) in Oracle 10.2.

If applications are longer than 60 secs to authenticate with the Oracle database, the errors occur.

The following may be seen in the alert log: WARNING: inbound connection timed out (ORA-3136)

SQLNET.INBOUND_CONNECT_TIMEOUT is set to a value in seconds and determines how long a client has to provide the necessary authentication information to a database.

INBOUND_CONNECT_TIMEOUT_listenername is set to a value in seconds and determines how long a client has to complete its connect request to the listener after the network connection has been established.

To protect both the listener and the database server, Oracle Corporation recommends setting INBOUND_CONNECT_TIMEOUT_listenername in combination with the SQLNET.INBOUND_CONNECT_TIMEOUT parameter.

Cause

Whenever default timeouts are assigned to a parameter, there may be cases where this default does not work well with a particular application. However, some type of timeout on the connection establishment is necessary to combat Denial of Service attacks on the database.  In this case, SQLNET.INBOUND_CONNECT__TIMEOUT and INBOUND_CONNECT_TIMEOUT_listenername were given default values of 60 seconds in Oracle 10.2.  It is these timeout values that can cause the errors described in this note.

Also note that it is possilbe the reason the database is slow to authenticate, may be due to an overloaded Oracle database or node.

Solution

Set the parameters SQLNET.INBOUND_CONNECT_TIMEOUT and INBOUND_CONNECT_TIMEOUT_listenername to 0 (indefinite) or to an approprate value for the application yet still combat DOS attacks (120 for example).

These parameters are set on the SERVER side:

listener.ora: INBOUND_CONNECT_TIMEOUT_listenername

sqlnet.ora:   SQLNET.INBOUND_CONNECT_TIMEOUT

Further tuning of these parameters may be needed is the problem persists.