Intermittent connection errors

Hi Brian, just a quick follow-up for you, we have just published a deployment guide for BIG-IP for oracle database and RAC. You can find it on f5.com/oracle in the database section. Let us know if this helps with your issue.

 

 

-Chris.

 

Hi!

 

 

I have also the same issue even after following the deployment guide "Oracle Database and RAC (BIG-IP LTM)" (document is 'oracle-rac-database-dg.pdf)

 

I'm able to connect with SQL Developer but after a while (more or less 5 min, 300 sec which is the default TCP timeout if I'm right) I'm getting a reset and I get the following error message in SQL Developer :

 

"Your database connection has been reset. Any pending transactions or session state has been lost."

 

 

Then if I retry, it's ok, I'm getting re-connected.

 

 

I'm missing something in one of (or the two ?) TCP profiles but what ?

 

Hi Chris!

 

 

Thanks for your reply...

 

I've made some very quick tests today...

 

 

I've checked in the sqlnet.ora file and the timeout is set to

 

TCP.CONNECT_TIMEOUT=10

 

 

On the BigIP, the value for Idle Timeout is set to 3600 seconds.

 

I've also uncheck 'Reset on Timeout' in the profile but still... My SQLDeveloper gets diconnected after a while...

 

Will have to block some time to sniff...

 

 

Phil

My apologies if I was not very clear. The actual sqlnet.ora setting is called the SQLNET.EXPIRE_TIME, I was referring to it generically as Sqlnet.Timeout. The TCP.CONNECT_TIMEOUT is for new connection timeouts, not existing connections. Here is the paragraph from the deployment guide that covers this that I referred to before:

 

 

8.In the Idle Timeout box, type the value of the SQLNET.EXPIRE_TIME in your Oracle Net settings.

 

If you have not changed the value of SQLNET.EXPIRE_TIME in your Oracle configuration from the default, you do not need to change the value of Idle Timeout in your BIG-IP configuration.

 

However, if you set your SQLNET.EXPIRE_TIME value to the Oracle recommended value of 10 minutes, change the value of Idle Timeout to a value that is slightly larger, such as 660 (11 minutes).

 

For more information on configuring your Oracle Net Expire Timeout, see the following link: http://download.oracle.com/docs/cd/B28359\_01/network.111/b28317/sqlnet.htmBIIEEGJH

 

Note: Setting 'Idle Timeout' to a value that is slightly larger than the Oracle SQLNET.EXPIRE_TIME setting will ensure that the BIG-IP does not prematurely close an idle connection, but will help to cleanup stale connections.

 

 

Thanx - Chris.

 

 

My apologies if I was not very clear. The actual sqlnet.ora setting is called the SQLNET.EXPIRE_TIME, I was referring to it generically as Sqlnet.Timeout. The TCP.CONNECT_TIMEOUT is for new connection timeouts, not existing connections. Here is the paragraph from the deployment guide that covers this that I referred to before:

 

 

8.In the Idle Timeout box, type the value of the SQLNET.EXPIRE_TIME in your Oracle Net settings.

 

If you have not changed the value of SQLNET.EXPIRE_TIME in your Oracle configuration from the default, you do not need to change the value of Idle Timeout in your BIG-IP configuration.

 

However, if you set your SQLNET.EXPIRE_TIME value to the Oracle recommended value of 10 minutes, change the value of Idle Timeout to a value that is slightly larger, such as 660 (11 minutes).

 

For more information on configuring your Oracle Net Expire Timeout, see the following link: http://download.oracle.com/docs/cd/B28359\_01/network.111/b28317/sqlnet.htmBIIEEGJH

 

Note: Setting 'Idle Timeout' to a value that is slightly larger than the Oracle SQLNET.EXPIRE_TIME setting will ensure that the BIG-IP does not prematurely close an idle connection, but will help to cleanup stale connections.

 

 

Thanx - Chris.

 

 

What are the timeouts set to in your client-side TCP profile in your Virtual Server? It sounds like SQL Developer expects a much longer-lived than either the BIG-IP or the Oracle database server is configured to allow.

 

Hi Chris, Rcorder,

 

 

First of all thanks for your help !

 

 

I will try to start sniffing the traffic today...

 

 

These are the parameters in the sqlnet.ora file:

 

NAMES.DIRECTORY_PATH= (TNSNAMES)

 

TCP.CONNECT_TIMEOUT=10

 

SQLNET.EXPIRE_TIME=10

 

 

In the several tests I've made, I've also tried setting SQLNET.EXPIRE_TIME=0 (to try with the connections left open indefinitely) but still the same...

 

I've also added a screenshot of the client tcp profile... I had unchecked the 'Reset On Timeout' to avoid the RST but I will check it again...

 

 

BR,

 

 

Phil

 

Hi,

 

 

Just another test I've made...

 

I've installed the Oracle Client on my PC, configuring to reach a DB directly and through the BigIP...

 

 

DIRECT connection is working:

 

 

SQL*Plus: Release 11.2.0.1.0 Production on Thu Aug 4 15:41:10 2011

 

 

Copyright (c) 1982, 2010, Oracle. All rights reserved.

 

 

 

Connected to:

 

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

 

With the Partitioning, Real Application Clusters, Automatic Storage Management,

 

OLAP, Data Mining and Real Application Testing options

 

 

But through the BigIP:

 

 

SQL*Plus: Release 11.2.0.1.0 Production on Thu Aug 4 15:41:18 2011

 

 

Copyright (c) 1982, 2010, Oracle. All rights reserved.

 

 

ERROR:

 

ORA-12547: TNS:lost contact

 

 

I've also created a small Weblogic domain with a datasource going through the BigIP and for this one, the test on the datasource is still OK...

 

 

 

BR,

 

 

Phil