虽然用户没有报性能错误，但是因为这个问题告警日志文件大小高达几个G，导致每次查看都有些困难，还是查看一下解决办法。
原因
在MOS上查找fatal NI connect error 12170可以找到问题原因。英文原文为
These time out related messages are mostly informational in nature. The messages indicate the specified client connection (identified by the 'Client address:' details) has experienced a time out. The 'nt secondary err code' identifies the underlying network transport, such as (TCP/IP) timeout limits after a client has abnormally terminated the database connection.
The 'nt secondary err code' translates to underlying network transport timeouts for the following Operating Systems:
For the Solaris system: nt secondary err code: 145:
ETIMEDOUT 145 /* Connection timed out */
For the Linux operating system: nt secondary err code: 110
ETIMEDOUT 110 Connection timed out
For the HP-UX system: nt secondary err code: 238:
ETIMEDOUT 238 /* Connection timed out */
For AIX: nt secondary err code: 78:
ETIMEDOUT 78 /* Connection timed out */
For Windows based platforms: nt secondary err code: 60 (which translates to Winsock Error: 10060)
Description: A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond.
The reason the messages are written to the alert log is related to the use of the new 11g Automatic Diagnostic Repository (ADR) feature being enabled by default. See (Doc ID 454927.1).
These time out related messages are mostly informational in nature. The messages indicate the specified client connection (identified by the 'Client address:' details) has experienced a time out. The 'nt secondary err code' identifies the underlying network transport, such as (TCP/IP) timeout limits after a client has abnormally terminated the database connection.
The 'nt secondary err code' translates to underlying network transport timeouts for the following Operating Systems:
For the Solaris system: nt secondary err code: 145:
ETIMEDOUT 145 /* Connection timed out */
For the Linux operating system: nt secondary err code: 110
ETIMEDOUT 110 Connection timed out
For the HP-UX system: nt secondary err code: 238:
ETIMEDOUT 238 /* Connection timed out */
For AIX: nt secondary err code: 78:
ETIMEDOUT 78 /* Connection timed out */
For Windows based platforms: nt secondary err code: 60 (which translates to Winsock Error: 10060)
Description: A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond.
The reason the messages are written to the alert log is related to the use of the new 11g Automatic Diagnostic Repository (ADR) feature being enabled by default. See (Doc ID 454927.1).
————————————————
这段话的大致意思是特定客户端的连接经历了一次超时，这个超时可能由两个原因导致的：
- 一个连接试图连接主机，但是主机没有正确响应
- 被连接的主机没有响应导致一个已经建立的连接失败了
由于11g新的自动诊断仓库特性默认是将这些连接失败信息写入告警日志里的，故导致了告警日志里存在大量的类似错误信息。
解决办法
设置Oracle Net的配置参数使它的诊断追踪信息不再写入到告警日志文件中，需要修改两个地方：
1. 在服务端的sqlnet.ora文件中增加一行
DIAG_ADR_ENABLED=OFF
2. 在服务端的listener.ora中增加一行(其中listenername替换为你自己的监听器名称)
DIAG_ADR_ENABLED_<listenername>=OFF
3. 使用lsnrctl命令使以上配置生效
lsnrctl reload;(业务不会中断，如果业务不是很紧张，最好使用lsnrctl restart确保参数生效）
或者
lsnrclt restart;（业务会中断）
文章评论