Fatal NI connect error 12170 TNS-12535 TNS-00505 解决办法
1. alert.log报警日志
Wed Sep 10 08:06:45 2014
***********************************************************************
Fatal NI connect error 12170.
VERSION INFORMATION:
TNS for Linux: Version 11.2.0.3.0 -
Production
Oracle Bequeath NT Protocol Adapter for Linux: Version
11.2.0.3.0 - Production
TCP/IP NT Protocol Adapter for Linux: Version
11.2.0.3.0 - Production
Time: 10-SEP-2014 08:06:45 <=instance drop
session time
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: 110
nt OS err code:
0
Client address:
(ADDRESS=(PROTOCOL=tcp)(HOST=10.124.88.125)(PORT=11901))
--(HOST=10.124.88.125)(PORT=11901)这个session被instance drop时间是10-SEP-2014
08:06:45
2.
listener.log日志
--listener.log位于$ORACLE_BASE/diag/tnslsnr///trace/.log
10-SEP-2014 07:36:15 *
(CONNECT_DATA=(SERVICE_NAME=DOUDOU)(CID=(PROGRAM=c:\app\administrator\product\11.2.0\dbhome_1\bin\ORACLE.EXE)(HOST=FYUJHRF)(USER=SYSTEM)))
* (ADDRESS=(PROTOCOL=tcp)(HOST=10.124.88.125)(PORT=11901)) * establish * DOUDOU
* 0
--10-SEP-2014 07:36:15=> into database time
综上所述:(HOST=10.124.88.125)(PORT=11901)这个session从10-SEP-2014
07:36:15链接数据库上,10-SEP-2014 08:06:45被instance drop掉,在数据库中活动了近30分钟。
总结:
--(HOST=10.124.88.125) =》确定链接的客户端IP地址
--establish
=》已经链接
--SERVICE_NAME=DOUDOU
=》链接的数据库名DOUDOU
##注意:alert.log有些(HOST=10.124.88.125)(PORT=11901),在listener.log中找不到。这很正常,因为只有establish状态的session才会被listener.log记录。##
解决办法:
1.确定网络防火墙设置是否适合现有的业务需求(应用监控、流量监控、系统监控)
2.确定网络防火墙是符合业务需求后,可以尝试在sqlnet.ora中添加SQLNET.EXPIRE_TIME参数
举例:SQLNET.EXPIRE_TIME=n
Where is a non-zero value set in minutes. 此参数会自动解决DCD问题(僵尸进程问题)。