We had a situation where we had to set the sqlnet.expire_time parameter in the sqlnet.ora file to avoid firewall dropping “inactive” sessions. The only problem was that we were not allowed to restart the listener or the database instance. Searching through the Oracle documentation and google just didn’t give a solid answer what needs to be restarted to start using this setting. Trying to figure out what needs to done to get this setting being used, got me to find out the following:
When you reload the listener (re-reading the configuration, not actually restarting the listener process, lsnrctl reload …) the sqlnet.ora file gets read, but only if it existed when the listener was started! If an sqlnet.ora file existed when the listener was started (and you removed the sqlnet.ora file while the listener is running) and you reload the listener, it will find out that the sqlnet.ora file does not exist anymore and will not try to read the sqlnet.ora file the next time you reload the listener. The only option you’ll have is to stop and start the listener again.
So do you need to restart?
However to actually let the oracle server process use the sqlnet.expire_time parameter, you just have to set the parameter in the sqlnet.ora. When you open a new session to your database a new Oracle dedicated server process gets started (if your database is running in dedicated server mode of course) which will read the sqlnet.ora file and use the specified sqlnet.expire_time. So whenever you change this parameter the new value gets used by all Oracle processed created after the change. All running dedicated server processes keep using the value for the parameter as it was at the time they were created.
So do you need to restart anything to start using the sqnlet.expire_time parameter? No you do not for new sessions!
If you want to use the new value for existing sessions you have to make these sessions reconnect.
Allot of thanks to Marcel, here is the link to his orignal blog post http://marcel.vandewaters.nl/oracle/database-oracle/sqlnet-expire_time-to-restart-or-not-to-restart