Add SQLNET.EXPIRE_TIME to the sqlnet.ora file

Good morning, Oracle fans!

I made an entry to the sqlnet.ora file in an attempt to get rid of some irritating alert log messages…

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.

Thanks for reading!

Jason