Do you only have one data store or several? In production intend to prefer managing oracle a shared connection pool in tomcat, and access it in each workspace via JNDI.
The version of geoserver in production is quite old; I encourage you to read the outstanding vulnerabilities reports and apply some of the motivations listed :) You may wish to check the oracle logs for a clue on why connections are being not returned? In older GeoServer we were not always as strict about cleaning up a connection when a failure occurred. Perhaps failures started 2 weeks ago and running out of connections is the consequence. -- Jody Garnett On Fri, Apr 12, 2024 at 5:45 PM Jean-Christophe Bastin <jcbas...@thelis.be> wrote: > Hello community, > > > > I'm asking your help because I'm really lost and I don't know what to do, > or I don't understand what is wrong. > > Let me explain first the different environments, they are 3 : > *Development*, *Test* and *Production*. > > We are working on all of them with a web application with Angular and > Openlayers, Web API controllers on Windows Servers, with GeoServer as > layers data sources and an Oracle 12 database. > > The issue appears in Production, must come from GeoServer and/or Oracle, > but I guess it's only GeoServer. > > > > For each environment : > > - Development : > - GeoServer 2.10.0, installed as stand-alone Windows service > - Test : > - GeoServer 2.23.0, installed as stand-alone Windows service > - Production : > - GeoServer 2.16.1, BUT HERE, it is installed with Apache Tomcat > 9.0, not as a stand-alone Windows service (don't ask me why, I can't > answer > ^^) > > > > On each environment, an "Oracle NG" data source is configured to share > spatial data from the database to the web application. > > All was working fine since some years, but last weeks we noticed an issue > that occurs sometime in the Production environnement. I can't reproduce it > for now on Development and Test. > > GeoServer sent an error message "*Rendering process failed, Unable to > obtain connection : Cannot get a connection, pool error Timeout waiting for > idle object*". > > So I updated (Development/Test/Production) the "max connections" parameter > of "Oracle NG" data source from 10 to 50, restarted the service to be sure, > but the error continues to come after sometime. > > I daily checked the Oracle sessions for the user (call it map_ora_user) > associated with the "Oracle NG" GeoServer data source, it keeps most of the > time between 6-10 sessions, sometimes less but never more. > > I tried to connect many times manually to Oracle with the map_ora_user, > more than 10, plus the already opened sessions from GeoServer, it doesn't > look like the limitation comes from Oracle database. > > So I made researches, and most of the time the solution talks about an > "Oracle JNDI" data source, that is also configured on Apache/Tomcat side, > in a context as I see here : > https://docs.geoserver.org/stable/en/user/tutorials/tomcat-jndi/tomcat-jndi.html > > > > But many questions come to me reading this : > > - Why the "max connections" parameter doesn't seem to affect the > connection pool with the "Oracle NG" data source ? How is it clearly > managed ? It looks like it is better explained in the JNDI version than the > NG one. > - Is the issue could come from some configuration in Apache/Tomcat ? > Because this is the only place where GeoServer is installed in another way > than in Development or Test. > - Is the "Oracle NG" need more configuration (Apache/Tomcat context > for example) to manage the connection pool like the JNDI version ? I don't > see a parameter that could come from outside GeoServer, like a > jndiReferenceName for example. > - If I have to migrate my "Oracle NG" connection to an "Oracle JNDI", > is there a simple way to adapt all the layers defined from the "Oracle NG" > data source ? We have many layers defined, it will take many time to reset > them one by one manually. > - Do I have to review the installation of GeoServer in Windows service > in Production environment ? > > > > Hope someone already had this kind of issue, and help me to clarify the > situation. > > > > Many thanks in advance :-) > > > > JC > _______________________________________________ > Geoserver-users mailing list > > Please make sure you read the following two resources before posting to > this list: > - Earning your support instead of buying it, but Ian Turton: > http://www.ianturton.com/talks/foss4g.html#/ > - The GeoServer user list posting guidelines: > http://geoserver.org/comm/userlist-guidelines.html > > If you want to request a feature or an improvement, also see this: > https://github.com/geoserver/geoserver/wiki/Successfully-requesting-and-integrating-new-features-and-improvements-in-GeoServer > > > Geoserver-users@lists.sourceforge.net > https://lists.sourceforge.net/lists/listinfo/geoserver-users >
_______________________________________________ Geoserver-users mailing list Please make sure you read the following two resources before posting to this list: - Earning your support instead of buying it, but Ian Turton: http://www.ianturton.com/talks/foss4g.html#/ - The GeoServer user list posting guidelines: http://geoserver.org/comm/userlist-guidelines.html If you want to request a feature or an improvement, also see this: https://github.com/geoserver/geoserver/wiki/Successfully-requesting-and-integrating-new-features-and-improvements-in-GeoServer Geoserver-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/geoserver-users
