Forum Discussion
brian_11825
Nimbostratus
NimbostratusIntermittent connection errors
We have a Big-Ip VIP fronting our two-node Oracle RAC. All too frequently we get application connection errors when connecting through the VIP. The nodes are fine and connecting directly to them works. The connection only fails through the VIP. The Big-Ip logs are clean. Here is the output from sqlplus and tnsping.
C:\Documents and Settings\a042740>sqlplus fidst_user/****@gfirdbp
SQL*Plus: Release 10.2.0.1.0 - Production on Fri Aug 13 10:06:44 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
ERROR:
ORA-12160: TNS:internal error: Bad error number
C:\Documents and Settings\a042740>tnsping gfirdbp
TNS Ping Utility for 32-bit Windows: Version 10.2.0.1.0 - Production on 13-AUG-2010 10:08:35
Copyright (c) 1997, 2005, Oracle. All rights reserved.
Used parameter files:
C:\oracle\product\10.2.0\client_1\network\admin\sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = GFIRDB)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = fcmfixp) (SRVR = DEDICATED)))
TNS-12537: TNS:connection closed
8 Replies
- 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. 
bletardph_10497Hi!
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 ?- bletardph_10497Hi 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. 
ryanc_76025Altocumulus
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.- bletardph_10497Hi 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 - bletardph_10497
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
