oracle 12c tns,Oracle 11g TNS-12547错误

在DB11g的alert日志中，有时间会遇到以下关于 Fatal NI connect error 12547  和 ORA-609错误，说到这里，有必要联想到与12547类似的TNS错误提示，如下：

1、TNS-12537

TNS-12537: TNS: Connection closed

ORA-609 Opiodr Aborting Process Unknown Ospid

2、TNS-12535

Fatal NI connect error 12170.

TNS-12535: TNS:operation timed out

ns secondary err code: 12560

nt main err code: 505

下面截取一段这个博客记录的日志输出，关键部分用粗体标识。

Fatal NI connect error

12547, connecting to:

(LOCAL=NO)

VERSION INFORMATION:

TNS for IBM/AIX RISC System/6000:

Version 11.2.0.1.0 - Production

TCP/IP NT Protocol Adapter for IBM/AIX

RISC System/6000: Version 11.2.0.1.0 - Production

Oracle Bequeath NT Protocol Adapter for

IBM/AIX RISC System/6000: Version 11.2.0.1.0 - Production

Time: 07-FEB-2017 18:31:56

Tracing not turned on.

Tns error struct:

ns main err code: 12547

TNS-12547: TNS:lost

contact

ns secondary err code: 12560

nt main err code: 0

nt secondary err code: 0

nt OS err code: 0

opiodr aborting process

unknown ospid (35389884) as a result of ORA-609

Tue Feb 07 19:18:22 2017

产生问题的原因就是在数据库的INBOUND_CONNECT_TIMEOUT参数设置的默认时间60秒太短，导致的业务访问数据时间超过了默认时间，连接进程被kill掉，也就是opiodr aborting。

这种ORA-609错误是会被记录到alert文件中的，但是不会导致服务异常。

ORA 609的解释是

00609, 00000, "could not attach to incoming connection"

// *Cause:  Oracle process could not answer incoming connection

// *Action: If the situation described in the next error on the stack

//         can be corrected, do so; otherwise contact Oracle Support.

解决这类的问题初步的方式，需要主要的这是初步的方式就是增加参数INBOUND_CONNECT_TIMEOUT值，或是该参数值设置为0 ，0表示永不超时，但是设置为0会导致系统资源被独占从而带来系统负载压力。

在以下两个文件中，增加以下值Sqlnet.ora: SQLNET.INBOUND_CONNECT_TIMEOUT=180

Listener.ora: INBOUND_CONNECT_TIMEOUT_listener_name=120

修改完成后，可以在lsnrctl中reload一下就可以，没必要重启监听

LSNRCTL> reload

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.2.101)(PORT=1521)))

The command completed successfully

LSNRCTL> show inbound_connect_timeout

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.2.101)(PORT=1521)))

LISTENER parameter "inbound_connect_timeout" set to 180

The command completed successfully

参数说明：

SQLNET.INBOUND_CONNECT_TIMEOUT

Purpose

Use the SQLNET.INBOUND_CONNECT_TIMEOUT parameter to specify the time, in seconds, for a client to connect with the database server and provide the necessary authentication information.

If the client fails to establish a connection and complete authentication in the time specified, then the database server terminates the connection.

In addition, the database server logs the IP address of the client and an ORA-12170: TNS:Connect timeout occurred error message to the sqlnet.log file.

The client receives either an ORA-12547: TNS:lost contact or an ORA-12637: Packet receive failed error message.

Without this parameter, a client connection to the database server can stay open indefinitely without authentication.

Connections without authentication can introduce possible denial-of-service attacks, whereby malicious clients attempt to flood database servers with connect requests that consume resources.

To protect both the database server and the listener, Oracle Corporation recommends setting this parameter in combination

with the INBOUND_CONNECT_TIMEOUT_listener_name parameter in the listener.ora file. When specifying values for these parameters, consider the following recommendations:

Set both parameters to an initial low value.

Set the value of the INBOUND_CONNECT_TIMEOUT_listener_name parameter to a lower value than the SQLNET.INBOUND_CONNECT_TIMEOUT parameter.

For example, you can set INBOUND_CONNECT_TIMEOUT_listener_name to 2 seconds and INBOUND_CONNECT_TIMEOUT parameter to 3 seconds.

If clients are unable to complete connections within the specified time due to system or network delays that are normal for the particular environment, then increment the time as needed.

参考文档：ORA-609 TNS-12537 and TNS-12547 in 11g Alert.log (Doc ID 1116960.1)