Fatal NI connect error 12170.
13-AUG-14 04.20.22.836 PM -07:00
Tns error struct:
13-AUG-14 04.20.22.851 PM -07:00
TNS-12535: TNS:operation timed out
These messages were appearing in my inbox for both of my databases on this particular server every hour, on the hour. After some research, I suspected that these errors were being caused by dead connections. I then looked at my sqlnet.ora file.
cat $ORACLE_HOME/network/admin/sqlnet.ora
What I found is there was no entry for SQLNET.EXPIRE_TIME. No entry for this parameter defaults to zero. My research into the Oracle documentation led me to believe this means a dead connection can just sit there forever, or until the database is reset. My next move was to set this parameter to the recommended setting of 10. A setting of 10 means, every ten minutes, a probe is sent out to find and terminate dead connections. So I went out and did it. I used vi sqlnet.ora and added this line to the file
vi sqlnet.ora
SQLNET.EXPIRE_TIME=10
The next step is bounce or reload the listener. I decided to wait until off hours and bounce the listener instead of using reload. I am not sure reload would work in this case.
$ lsnrctl stop
$ lsnrctl start
$ lsnrctl status
This morning, I looked at my email inbox and the alert log entries were gone.
SUCCESS!!
I Hope this helps. If it causes more problems then it solves, it is very easy to revert. Simply open the file, remove the line and bounce the listener. I am curious what your experience has been on this parameter setting.
===============================================================================================================
If a client suddenly “disappears”, e.g it’s powered off, or loses network, the connection still remains open on the database server, with the corresponding session as well.
When the above parameter is set, the database probes the connections to check if they are still alive. The above parameter controls the frequency of this check (in minutes).
