alter日志里出现error 12170报错

alter日志里出现error 12170报错，但是数据库正常，这个错误出现在alert.log总是不好的，客户会总觉得数据库不正常。报错信息如下

Fatal NI connect error 12170.

VERSION INFORMATION:
TNS for Solaris: Version 11.2.0.1.0 - Production
Oracle Bequeath NT Protocol Adapter for Solaris: Version 11.2.0.1.0 - Production
TCP/IP NT Protocol Adapter for Solaris: Version 11.2.0.1.0 - Production
Time: 22-JAN-2011 21:48:23
Tracing not turned on.
Tns error struct:
ns main err code: 12535

TNS-12535: TNS:operation timed out
ns secondary err code: 12560
nt main err code: 505

TNS-00505: Operation timed out
nt secondary err code: 145
nt OS err code: 0
Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=10.xxx.yy.117)(PORT=1092))

---------
The "nt secondary err code" will be different based on the operating system.

Linux x86 or Linux x86-64: "nt secondary err code: 110"
HP-UX Server: "nt secondary err code: 238"
AIX: "nt secondary err code: 78"

根据MOS-1286376.1，可以看出原因是11g Automatic Diagnostic Repository (ADR)导致的，Client连接超时，ADR会收集数据库的各种信息，以体现在alert.log里，解决方法是在sqlnet.ora里面加DIAG_ADR_ENABLED = OFF同时在你正在使用的listener.ora加DIAG_ADR_ENABLED_<listenername> = OFF 如你的监听名称叫LISTENER 那么就是DIAG_ADR_ENABLED_LISTENER = OFF，监听名可以使用ps -ef |grep tns查看

最后也可以根据情况适当的修改连接超时的时间

SQLNET.INBOUND_CONNECT_TIMEOUT=120 在sqlnet.ora

SQLNET.INBOUND_CONNECT_TIMEOUT parameter in sqlnet.ora on the database server

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.

INBOUND_CONNECT_TIMEOUT_listener_name in listener.ora 默认是60

可以如下方法

lsnrctl

show INBOUND_CONNECT_TIMEOUT

Specify the time, in seconds, for the client to complete its connect request to the listener after the network connection had been established.

If the listener does not receive the client request in the time specified, then it terminates the connection. In addition, the listener logs the IP address of the client and an ORA-12525: TNS:listener has not received client’s request in time allowed error message to the listener.log file