IDLE timeout parameter in Oracle

Question

We are stuck in a situation where one of our processes is taking 3 hours of computing without touching the database. The connection that was taken before calling the process gets closed by the Oracle server and any subsequent query or commit throws connection closed exception.

It appears to us that the problem is related to Oracle closing the connection that is idle for that long for some reason.

We tried changing EXPIRE_TIMEOUT in sqlnet.ora but that didn't help either.

What can we do to resolve this problem?

Scudder

If you are not using the connection for such a long time why not close it and later get a new connection when you need it? Holding onto a resource you don't need for such a long time is wasteful. – Behring

The code that calls the process is a third party code, cant do much about that. What I heard back from them is that we need to increase the connection timeout to atleast until the process returns. – Scudder

Answer 1

What is the error you get when you try to use the connection?

Oracle by default will not close a connection due to inactivity. You can configure a profile with an IDLE_TIME to cause Oracle to close inactive connections, but it doesn't sound like you've done that. You can also configure Oracle to detect dead connections and close the connection if the client doesn't respond-- if the client is buried for three hours, it's possible that it's not responding in a timely fashion. But that seems less likely ad requires additional configuration steps.

The more likely situation in my experience is that your network is dropping the connection. If you are connecting via a firewall, for example, the firewall will frequently close connections that have been idle too long.

The actual Oracle error message you are receiving will indicate which of these alternatives is causing your problem.

Bumble

Answer 2

Irfan,

1. Please make sure you have the resource_limit=TRUE in the init.ora file for the changes to take effect.

2. Also, please check if the user you are trying to set the limit for is assigned to the default profile.

select profile from dba_users where username = 'TEST_USER';
  PROFILE1

select profile, resource_name, limit from dba_profiles where profile='PROFILE1' and
resource_name ='IDLE_TIME'

3 If the user is asigned to a custom profile make sure the parameters for the custom profile are set acordingly. You should also look at the connect_time parameter (in the default or the custom profile whichever applies to you. Once the connection time is exceeded, the connection is terminated . )

And finally, please note that if the current session started before the parameter was set, it will not be taken into effect. The changes kick-in only from the next session after you make the changes.

Useful links.

http://www.adp-gmbh.ch/blog/2005/april/17.html
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:453256655431

Thanks,

Rajesh

Buehler

init.ora do not have resource_limit variable, I assume its set to false by default. So does that mean IDLE_TIME is set to a smaller values where resource_limit is false. OR is it set to UNLIMITED. Beacuase if its unlimited by default I dont need to do anything. thoughts – Scudder

from the documentation , it seems if the resource_limit is set to flase (which is default), it disables the enforcements of resource limits. I am not sure if this gives unlimited time for IDLE_TIME. What do you get when you execute select profile from dba_users where username = <USER_NAME>; ? – Buehler

Specify DEFAULT if you want to omit a limit for this resource in this profile. A user assigned this profile is subject to the limit for this resource specified in the DEFAULT profile. The DEFAULT profile initially defines unlimited resources. You can change those limits with the ALTER PROFILE statement download.oracle.com/docs/cd/B19306_01/server.102/b14200/… – Buehler

When I query for idle_time, result is UNLIMITED. So my current state is resource_limit=false and idle_time=UNLIMITED and sqlnet.expire_time=1440 And what I am trying to achieve is that my connection not times out event if its idle for less than 24 hours , do you think this configuration is good to achieve this. Or do you think there are things that I am missing. – Scudder

Not sure what's missing. Is the connect_time set to unlimited too? select profile, resource_name, limit from dba_profiles where profile='DEFAULT' and resource_name in ('CONNECT_TIME','IDLE_TIME'); Other than this, the only thing I could think of is your application server is timing out idle database connections. Example: for oracle AS SSO, the following parameter can cause idle connections to be terminated. connectionIdleTimeout in the ORACLE_HOME/sso/conf/policy.properties file. – Buehler

Yes you correct, both idle_timeout and connect_timeout are set to UNLIMITED – Scudder

Not sure what the problem could be. Probably an application server which times out idle connections? – Buehler

Answer 3

It seems actual reason for the connection closed exception is same as what @Justin Cave mentioned in his answer:

The more likely situation in my experience is that your network is dropping the connection. If you are connecting via a firewall, for example, the firewall will frequently close connections that have been idle too long.

The actual Oracle error message you are receiving will indicate which of these alternatives is causing your problem.

If still someone want to know the IDLE_TIME and CONNECT_TIME configured for a profile, then one can execute below query:

select * from user_resource_limits user_resource where user_resource.resource_name in ('IDLE_TIME','CONNECT_TIME');

Unsought

Thank you for the query which gets me the properties on the DB side. Both are set to UNLIMITED for me, so now I know the DB is not the issue. – Drummond

Answer 4

No matter what database you're using it's a bad idea to assume your connection is going to be live when you want to use it. One way to handle this is to create a function to return an active connection to the database in question, and to call it every time you need a handle/object/whatever for a given database. The routine maintains a list of databases and their associated connection object. If the connection object is live when the function is called all's well and good and the object is returned after the function does something with it to convince the database to keep the handle/object/whatever open. If there's no live connection object the routine opens a new one and returns that. It's useful to have a second routine that camps out on a timer that expires after 1 minute or so. When the timer expires and the second routine is called it looks through the list of database connections, looking for ones with no activity for a set amount of time (something significantly less that the database's session timeout value). Those that have been inactive for too long get closed and cleaned up.

Sedge

Thanks for the reply, as you can see my comments on the question, its the third party code that we are using and the solution that is recommended by them is that we icrease the idletimeout parameter. – Scudder