Oracle Connection Pooling through the LTM

Hi Ken,

 

 

The problem sounds like one of priorities - do you want the app always accessible to (valid) users with the risk of an attacker abusing the connection?

 

 

I'm not certain what we can do for you - we can help you adjust the timeout value or point you at the docs that show how to, but that leaves the security risk. I guess if you have a lot of users hitting the box, it won't necessarily time out - connection pools will live as long as they're being used - which means the risk will already be there for at least some of your connections... So I don't see the level of risk you seem to. Am I missing something?

 

 

You could pin the connections to only between the two boxes, but since the Oracle Client on the webserver is essentially a DB proxy, I don't think that would alleviate your issues, so I'm trying to understand the nature of your security concern.

 

 

Regards,

 

Don.

Thanks for the response

 

 

Currently I have the have the F5 configured so that connections on TCP/1521 will not timeout at all. These application have low usage so the connections do timeout if the timeout is not set to infinite.

 

 

What I am looking for is if some has experience with a configuration of SQLNET so I do not have to have the connections set to infinite.

 

 

F5 is causing the issue but I am thinking the resolution is in the configuration of SQLNET.ORA.

 

 

We are going to test the following configuration but i am not sure if it will do the trick.

 

 

sqlnet.expire_time = 45

 

 

 

Possible values: 0-any valid positive integer! (in minutes)

 

Default: 0 minutes

 

Recommended value: 10 minutes

 

 

Purpose: Indicates the time interval to send a probe to verify the

 

client session is alive (this is used to reclaim wasteful

 

resources on a dead client)

 

 

 

We will set the F5 to timeout at 60mins and see what happens.

 

 

Ken

Hi Ken,

 

 

It's been awhile but what was the outcome of your testing?

Ken,

 

 

Also, how do you configure the LTM so that connections on TCP\1521 do not timeout? Are you using a TCP profile?

What I have seen in the past is a mix of this issue mixed with firewalls also having timeouts of 30 minutes.

 

30 minutes is a long time!

 

 

So you end up in a situation where you have to set the timeout in a custom profile.

 

However the application server that has the connection pool should be configured so that you are validation the connections, using table validation and specifying a valid table is required for this as many other validation routines get cached in the client so defeat the purpose.

 

 

This is more of an application server centric issue really, you can expect to see errors like "Broken Pipe Exception" thrown when you have timeouts occur.

 

 

What we found was that some devices on timout dont FIN the connection they just RST and some app servers really hate that. Sun App server is especially bad at handling this situation and you quickly exhaust your available connections and need to restart the app server.

The way Oracle's sqlnet.expire_time works is to send a DCD (Dead Client Detection) packet at the specified time interval which tests the connection. If the test fails, the application server "should" establish a new session to the database server. In effect, however, the DCD packet will reset idle timeout counters on the F5's and most network firewalls.

 

 

Generally, a good rule of thumb is to use the formula, 3n+1, for the number of seconds to set idle timeouts to on network equipment. This allows for the possibility of a couple of dropped test packets without requiring reestablishment of the session. With a 300 second TCP timeout on the F5's default tcp profile, we can work backward to establish what n should be to set the sqlnet.expire_time on the application servers without creating a new F5 profile. 3n+1=300sec means that n in this case should be 99 seconds. Due to the fact that sqlnet.expire_time represents whole integer minutes only, the Oracle application server sqlnet.expire_time should be set to 1 minute.

 

 

Since it seems that all Oracle documentation makes reference to setting sqlnet.expire_time to 10 minutes, perhaps a better solution would be to create a F5 tcp profile using 3n+1 seconds as the idle timeout where n would be 600 seconds in this case. Were we to do this, then the F5 idle timeout should be set to 1801 seconds.

 

 

In practice, setting sqlnet.expire_time to 45 and the F5 timeout to 1 hour wouldn't be a very good solution since it could take up to 45 minutes for the application server to database connection to be down before the system realized it and tried to recover. Depending on the application, somewhere between 1 and 10 minutes feels like it would make more sense.