You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@tomcat.apache.org by Wouter D'Haeseleer <wd...@vangenechten.com> on 2009/05/09 11:46:49 UTC
Tomcat and Oracle connections
Hi Guys,
We have an Oracel RAC at work and using some applications running on a tomcat which needs oracle.
Nearly every week we have a problem with these connections.
We are getting:
ORA-12520: TNS:listener could not find available handler for requested type of server
This means, no connections available anymore.
And when I look at the connections itself in oracle I see that the following connection is using more then 200 sessions !
<Resource auth="Container"
description="RAC connection for the SHARK DB"
name="sharkdb"
type="oracle.jdbc.pool.OracleDataSource"
factory="oracle.jdbc.pool.OracleDataSourceFactory"
driverClassName="oracle.jdbc.OracleDriver"
url="jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP) (HOST=ourhost1)(PORT=1521))(ADDRESS=(PROTOCOL=TCP) (HOST=ourhost2)(PORT=1521))(LOAD_BALANCE=yes)(CONNECT_DATA= (SERVER=DEDICATED)(SERVICE_NAME=WFLOW)))"
user="username"
password="password"
maxIdle="8"
minIdle="2"
maxWait="5000"
maxActive="10"
connectionCachingEnabled="true"
fastConnectionFailoverEnabled="true"
connectionCacheName="myOracleDatasourceCache0"
onsConfigStr="nodes=ourhost1:6200,ourhost2:6200"
connectionProperties="oracle.jdbc.ReadTimeout=30000" />
For me this is really strange since this connections says maxActive=10
Could someone try to help me with this issue?
I'm using ojdbc-10.2.0.3.jar and ons.jar
Thanks
Met vriendelijke groeten,
Best Regards,
Wouter D'Haeseleer
2e line support
IMAS NV
Van Genechten Packaging
Raadsherenstraat 2 B-2300 Turnhout
Tel: 0032 14 37 30 Fax: 0032 14 40 37 15
Privileged Confidential Information may be contained in this message.
If you are not the addressee indicated in this message (or responsible for delivery of the message to such person), you may not copy or deliver this message to anyone.
In such case, you should destroy this message and kindly notify the sender by reply email. Please advise immediately if you or your employer does not consent to Internet email for messages of this kind. Opinions, conclusions and other information in this message that do not relate to the official business of my firm shall be understood as neither given nor endorsed by it.
Re: Tomcat and Oracle connections
Posted by George Sexton <ge...@mhsoftware.com>.
Your application is not closing all result sets, statements, or connections.
You might get a tool like FindBugs
http://findbugs.sourceforge.net/
and look at your application if it's compiled java.
Wouter D'Haeseleer wrote:
> Hi Guys,
>
> We have an Oracel RAC at work and using some applications running on a
> tomcat which needs oracle.
> Nearly every week we have a problem with these connections.
>
> We are getting:
> ORA-12520: TNS:listener could not find available handler for requested
> type of server
>
> This means, no connections available anymore.
> And when I look at the connections itself in oracle I see that the
> following connection is using more then 200 sessions !
>
>
> <Resource auth="Container"
> description="RAC connection for the SHARK DB"
> name="sharkdb"
> type="oracle.jdbc.pool.OracleDataSource"
> factory="oracle.jdbc.pool.OracleDataSourceFactory"
> driverClassName="oracle.jdbc.OracleDriver"
>
> url="jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)
> (HOST=ourhost1)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)
> (HOST=ourhost2)(PORT=1521))(LOAD_BALANCE=yes)(CONNECT_DATA=
> (SERVER=DEDICATED)(SERVICE_NAME=WFLOW)))"
> user="username"
> password="password"
> maxIdle="8"
> minIdle="2"
> maxWait="5000"
> maxActive="10"
> connectionCachingEnabled="true"
> fastConnectionFailoverEnabled="true"
> connectionCacheName="myOracleDatasourceCache0"
> onsConfigStr="nodes=ourhost1:6200,ourhost2:6200"
>
> connectionProperties="oracle.jdbc.ReadTimeout=30000" />
>
>
> For me this is really strange since this connections says maxActive=10
>
> Could someone try to help me with this issue?
>
> I'm using ojdbc-10.2.0.3.jar and ons.jar
>
>
> Thanks
>
>
> Met vriendelijke groeten,
> Best Regards,
>
> Wouter D'Haeseleer
> 2e line support
>
> IMAS NV
> Van Genechten Packaging
> Raadsherenstraat 2 B-2300 Turnhout
>
> Tel: 0032 14 37 30 Fax: 0032 14 40 37 15
>
> Privileged Confidential Information may be contained in this message.
>
> If you are not the addressee indicated in this message (or responsible
> for delivery of the message to such person), you may not copy or deliver
> this message to anyone.
>
> In such case, you should destroy this message and kindly notify the
> sender by reply email. Please advise immediately if you or your employer
> does not consent to Internet email for messages of this kind. Opinions,
> conclusions and other information in this message that do not relate to
> the official business of my firm shall be understood as neither given
> nor endorsed by it.
>
>
> ------------------------------------------------------------------------
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
> For additional commands, e-mail: users-help@tomcat.apache.org
--
George Sexton
MH Software, Inc.
Voice: +1 303 438 9585
URL: http://www.mhsoftware.com/
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
For additional commands, e-mail: users-help@tomcat.apache.org
RE: Tomcat and Oracle connections
Posted by Martin Gainty <mg...@hotmail.com>.
Here is configuration which would work for your TC environment
http://forum.springsource.org/showthread.php?t=63392
One more link which will illustrate usage of OracleConnectionCacheImpl
http://www.stanford.edu/group/coursework/stanfordoki/oracle/oracle/jdbc/pool/OracleConnectionCacheImpl.html
feel free to ping me offline for any details on configuring the DataSource or how to determine if your DB supports FCF
Martin Gainty
______________________________________________
Disclaimer and Confidentiality/Verzicht und Vertraulichkeitanmerkung/Note de déni et de confidentialité
This message is confidential. If you should not be the intended receiver, then we ask politely to report. Each unauthorized forwarding or manufacturing of a copy is inadmissible. This message serves only for the exchange of information and has no legal binding effect. Due to the easy manipulation of emails we cannot take responsibility over the the contents.
Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene Empfaenger sein, so bitten wir hoeflich um eine Mitteilung. Jede unbefugte Weiterleitung oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht dient lediglich dem Austausch von Informationen und entfaltet keine rechtliche Bindungswirkung. Aufgrund der leichten Manipulierbarkeit von E-Mails koennen wir keine Haftung fuer den Inhalt uebernehmen.
Ce message est confidentiel et peut être privilégié. Si vous n'êtes pas le destinataire prévu, nous te demandons avec bonté que pour satisfaire informez l'expéditeur. N'importe quelle diffusion non autorisée ou la copie de ceci est interdite. Ce message sert à l'information seulement et n'aura pas n'importe quel effet légalement obligatoire. Étant donné que les email peuvent facilement être sujets à la manipulation, nous ne pouvons accepter aucune responsabilité pour le contenu fourni.
> Date: Tue, 12 May 2009 12:23:19 -0400
> From: chris@christopherschultz.net
> To: users@tomcat.apache.org
> Subject: Re: Tomcat and Oracle connections
>
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> Chuck,
>
> On 5/11/2009 8:47 PM, Caldarale, Charles R wrote:
> >> From: Christopher Schultz [mailto:chris@christopherschultz.net]
> >> Subject: Re: Tomcat and Oracle connections
> >>
> >> I would highly recommend adding these two attributes to your
> >> <Resource>:
> >>
> >> logAbaodoned="true"
> >> removeAbandoned="true"
> >> removeAbandonedTimeout="30"
> >>
> >> While you're at it, maybe you want to set:
> >>
> >> validationQuery="SELECT 1 FROM DUAL"
> >
> > Note that the OP is using type="oracle.jdbc.pool.OracleDataSource"
> > rather than Tomcat's pooling mechanism; consequently the above
> > (slightly misspelled) attributes may not be applicable.
>
> Whoops, I missed that; thanks for catching it.
>
> Wouter, can you check to see if OracleDataSource has similar
> configuration parameters? If not, consider using Tomcat's pool while you
> inspect your application... you can always set it back to using Oracle's
> DataSource if you need to.
>
> - -chris
> -----BEGIN PGP SIGNATURE-----
> Version: GnuPG v1.4.9 (MingW32)
> Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/
>
> iEYEARECAAYFAkoJoncACgkQ9CaO5/Lv0PDx9ACfUvrw45atUZm65T9LI87GvrCB
> RlYAnRAasHu2ay/EVgEtthCdy0kiNOTh
> =agwY
> -----END PGP SIGNATURE-----
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
> For additional commands, e-mail: users-help@tomcat.apache.org
>
_________________________________________________________________
Insert movie times and more without leaving Hotmail®.
http://windowslive.com/Tutorial/Hotmail/QuickAdd?ocid=TXT_TAGLM_WL_HM_Tutorial_QuickAdd1_052009
Re: Tomcat and Oracle connections
Posted by Christopher Schultz <ch...@christopherschultz.net>.
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Chuck,
On 5/11/2009 8:47 PM, Caldarale, Charles R wrote:
>> From: Christopher Schultz [mailto:chris@christopherschultz.net]
>> Subject: Re: Tomcat and Oracle connections
>>
>> I would highly recommend adding these two attributes to your
>> <Resource>:
>>
>> logAbaodoned="true"
>> removeAbandoned="true"
>> removeAbandonedTimeout="30"
>>
>> While you're at it, maybe you want to set:
>>
>> validationQuery="SELECT 1 FROM DUAL"
>
> Note that the OP is using type="oracle.jdbc.pool.OracleDataSource"
> rather than Tomcat's pooling mechanism; consequently the above
> (slightly misspelled) attributes may not be applicable.
Whoops, I missed that; thanks for catching it.
Wouter, can you check to see if OracleDataSource has similar
configuration parameters? If not, consider using Tomcat's pool while you
inspect your application... you can always set it back to using Oracle's
DataSource if you need to.
- -chris
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.9 (MingW32)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/
iEYEARECAAYFAkoJoncACgkQ9CaO5/Lv0PDx9ACfUvrw45atUZm65T9LI87GvrCB
RlYAnRAasHu2ay/EVgEtthCdy0kiNOTh
=agwY
-----END PGP SIGNATURE-----
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
For additional commands, e-mail: users-help@tomcat.apache.org
RE: Tomcat and Oracle connections
Posted by "Caldarale, Charles R" <Ch...@unisys.com>.
> From: Christopher Schultz [mailto:chris@christopherschultz.net]
> Subject: Re: Tomcat and Oracle connections
>
> I would highly recommend adding these two attributes to your
> <Resource>:
>
> logAbaodoned="true"
> removeAbandoned="true"
> removeAbandonedTimeout="30"
>
> While you're at it, maybe you want to set:
>
> validationQuery="SELECT 1 FROM DUAL"
Note that the OP is using type="oracle.jdbc.pool.OracleDataSource" rather than Tomcat's pooling mechanism; consequently the above (slightly misspelled) attributes may not be applicable.
- Chuck
THIS COMMUNICATION MAY CONTAIN CONFIDENTIAL AND/OR OTHERWISE PROPRIETARY MATERIAL and is thus for use only by the intended recipient. If you received this in error, please contact the sender and delete the e-mail and its attachments from all computers.
Re: Tomcat and Oracle connections
Posted by Christopher Schultz <ch...@christopherschultz.net>.
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Wouter,
On 5/9/2009 5:46 AM, Wouter D'Haeseleer wrote:
> This means, no connections available anymore.
>
> And when I look at the connections itself in oracle I see that the
> following connection is using more then 200 sessions !
I think Chuck is right: you're leaking connections.
> <Resource auth="Container"
> ...
I would highly recommend adding these two attributes to your <Resource>:
logAbaodoned="true"
removeAbandoned="true"
removeAbandonedTimeout="30" (# of seconds to wait before considering
a connection "abandoned". Adjust to
fit your needs)
While you're at it, maybe you want to set:
validationQuery="SELECT 1 FROM DUAL"
(or something else appropriate to test your connections)
> For me this is really strange since this connections says maxActive=10
I always recommend setting maxActive="1" in development. You'll find
connection leaks REALLY quick!
I'll take this opportunity to plug my own blog entry regarding proper
JDBC resource management:
http://blog.christopherschultz.net/?p=68
- -chris
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.9 (MingW32)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/
iEYEARECAAYFAkoIm4YACgkQ9CaO5/Lv0PDFcgCeMcq1tf6zJM2LL50BvFfclJ/j
/eQAoISCn7x1AoIxcLz32NY1XT1ZkrlT
=4iKb
-----END PGP SIGNATURE-----
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
For additional commands, e-mail: users-help@tomcat.apache.org
RE: Tomcat and Oracle connections
Posted by "Caldarale, Charles R" <Ch...@unisys.com>.
> From: Wouter D'Haeseleer [mailto:wdhaeseleer@vangenechten.com]
> Subject: Tomcat and Oracle connections
Don't suppose you could be bothered to tell us the Tomcat version, JVM level, and OS you're running on?
> And when I look at the connections itself in oracle I see that the
> following connection is using more then 200 sessions !
Classic symptoms of bad webapp code failing to close connections in all circumstances. You *must* insure that result sets, statements, and connections are always closed (returned to the pool) by using finally clauses in the appropriate places.
> type="oracle.jdbc.pool.OracleDataSource"
> For me this is really strange since this connections says maxActive=10
Since you've specified a non-standard type, you're doing your own pooling rather than letting Tomcat do it. No telling what the proper attribute names are in that case.
Read the Tomcat JDBC doc for whatever level you're using.
- Chuck
THIS COMMUNICATION MAY CONTAIN CONFIDENTIAL AND/OR OTHERWISE PROPRIETARY MATERIAL and is thus for use only by the intended recipient. If you received this in error, please contact the sender and delete the e-mail and its attachments from all computers.
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
For additional commands, e-mail: users-help@tomcat.apache.org