[GeoNetwork-devel] geonetwork fresh install on Oracle - too many cursors

Ian_Allen · June 1, 2012, 5:27pm

When doing a fresh installation of geonetwork (using the 2.7/2.8 nightly
builds), I cannot get the initial tables and data to load correctly in the
oracle database using a reasonable open_cursors value.

I always get an error from oracle saying that there are too many cursors
opened. Then because the schema was not created properly, I get a null
pointer error on subsequent attempts. So I generally drop all the tables and
try again...

I tried setting the value to 1000, and then 2000 and that did not work.

I then set it to 10000 and it loaded correctly. (alter system set
open_cursors=10000 scope=memory;)

This value is much too high - this seems to be a bug - anyone else seeing
this issue?

--
View this message in context: http://osgeo-org.1560.n6.nabble.com/geonetwork-fresh-install-on-Oracle-too-many-cursors-tp4978602.html
Sent from the GeoNetwork developer mailing list archive at Nabble.com.

sppigot · June 2, 2012, 5:12am

I haven't run into this on either my local XE or a corporate Oracle db Ian (11.2.0.2, open cursors set to 300 (in XE anyway)) and I reload the db quite often as well (just did it again successfully to check now) - but it sounds to me that it is the initial filling out of the tables that might be the cause of this problem in your particular install/oracle setup? To test this I suppose that after GeoNetwork has successfully created and filled the initial tables, you could restart and run with open cursors set to something more reasonable (eg. 300)?

BTW I'm using tomcat with:

- JNDIPool provider in config.xml
- oracle config in tomcat/conf/context.xml
- ojdbc6.jar and sdoapi.jar in tomcat/lib directory.

Cheers and thanks,
Simon
________________________________________
From: ianwallen [ianwallen@anonymised.com]
Sent: Saturday, 2 June 2012 3:27 AM
To: geonetwork-devel@lists.sourceforge.net
Subject: [GeoNetwork-devel] geonetwork fresh install on Oracle - too many cursors

When doing a fresh installation of geonetwork (using the 2.7/2.8 nightly
builds), I cannot get the initial tables and data to load correctly in the
oracle database using a reasonable open_cursors value.

I always get an error from oracle saying that there are too many cursors
opened. Then because the schema was not created properly, I get a null
pointer error on subsequent attempts. So I generally drop all the tables and
try again...

I tried setting the value to 1000, and then 2000 and that did not work.

I then set it to 10000 and it loaded correctly. (alter system set
open_cursors=10000 scope=memory;)

This value is much too high - this seems to be a bug - anyone else seeing
this issue?

--
View this message in context: http://osgeo-org.1560.n6.nabble.com/geonetwork-fresh-install-on-Oracle-too-many-cursors-tp4978602.html
Sent from the GeoNetwork developer mailing list archive at Nabble.com.

------------------------------------------------------------------------------
Live Security Virtual Conference
Exclusive live event will cover all the ways today's security and
threat landscape has changed and how IT managers can respond. Discussions
will include endpoint security, mobile security and the latest in malware
threats. http://www.accelacomm.com/jaw/sfrnl04242012/114/50122263/
_______________________________________________
GeoNetwork-devel mailing list
GeoNetwork-devel@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/geonetwork-devel
GeoNetwork OpenSource is maintained at http://sourceforge.net/projects/geonetwork

Ian_Allen · June 4, 2012, 12:40pm

Simon,

I am using ApacheDBCPool as this was the default and most common(to my
understanding).
/As an FYI: I just did do a test with "JNDIPool" and I no longer have the
"too many cursors issue" - I do have some issues - but I will post it at a
separate thread. That said - I think the default ApacheDBCPool should also
work - should it not?
/

Here is the ApacheDBCPool config that I am using.

    <resource enabled="true">
      <name>main-db</name>
      <provider>jeeves.resources.dbms.ApacheDBCPool</provider>
      <config>
        <user>geonetwork</user>
        <password>geonetwork</password>
        <driver>oracle.jdbc.driver.OracleDriver</driver>
        <url>jdbc:oracle:thin:@localhost:1521:test</url>
        <poolSize>10</poolSize>
        <validationQuery>SELECT 1 FROM DUAL</validationQuery>
      </config>
    </resource>

I simply wipe out the tables and start the web application. Here is the
error received when starting up. It seems you are correct in that it fails
when populating the database? (org.fao.geonet.lib.DbLib.insertData)

2012-06-04 11:17:51,742 ERROR [jeeves.engine] - Stack :
java.sql.SQLException: ORA-01000: maximum open cursors exceeded

        at
oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
        at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)
        at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288)
        at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:745)
        at
oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:216)
        at
oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:966)
        at
oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1170)
        at
oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3339)
        at
oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:3423)
        at
org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:105)
        at
org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:105)
        at
org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:105)
        at jeeves.resources.dbms.Dbms.execute(Dbms.java:265)
        at jeeves.resources.dbms.Dbms.execute(Dbms.java:239)
        at org.fao.geonet.lib.DbLib.runSQL(DbLib.java:233)
        at org.fao.geonet.lib.DbLib.runSQL(DbLib.java:248)
        at org.fao.geonet.lib.DbLib.insertData(DbLib.java:193)
        at org.fao.geonet.Geonetwork.initDatabase(Geonetwork.java:647)
        at org.fao.geonet.Geonetwork.start(Geonetwork.java:194)
        at jeeves.server.JeevesEngine.initAppHandler(JeevesEngine.java:541)
        at jeeves.server.JeevesEngine.init(JeevesEngine.java:170)
        at
jeeves.server.sources.http.JeevesServlet.init(JeevesServlet.java:85)
        at javax.servlet.GenericServlet.init(GenericServlet.java:212)
        at
org.apache.catalina.core.StandardWrapper.loadServlet(StandardWrapper.java:1173)
        at
org.apache.catalina.core.StandardWrapper.load(StandardWrapper.java:993)
        at
org.apache.catalina.core.StandardContext.loadOnStartup(StandardContext.java:4187)
        at
org.apache.catalina.core.StandardContext.start(StandardContext.java:4496)
        at
org.apache.catalina.core.ContainerBase.addChildInternal(ContainerBase.java:791)
        at
org.apache.catalina.core.ContainerBase.addChild(ContainerBase.java:771)
        at
org.apache.catalina.core.StandardHost.addChild(StandardHost.java:526)
        at
org.apache.catalina.startup.HostConfig.deployWAR(HostConfig.java:905)
        at
org.apache.catalina.startup.HostConfig.deployWARs(HostConfig.java:740)
        at
org.apache.catalina.startup.HostConfig.deployApps(HostConfig.java:500)
        at
org.apache.catalina.startup.HostConfig.start(HostConfig.java:1277)
        at
org.apache.catalina.startup.HostConfig.lifecycleEvent(HostConfig.java:321)
        at
org.apache.catalina.util.LifecycleSupport.fireLifecycleEvent(LifecycleSupport.java:119)
        at
org.apache.catalina.core.ContainerBase.start(ContainerBase.java:1053)
        at
org.apache.catalina.core.StandardHost.start(StandardHost.java:722)
        at
org.apache.catalina.core.ContainerBase.start(ContainerBase.java:1045)
        at
org.apache.catalina.core.StandardEngine.start(StandardEngine.java:443)
        at
org.apache.catalina.core.StandardService.start(StandardService.java:516)
        at
org.apache.catalina.core.StandardServer.start(StandardServer.java:710)
        at org.apache.catalina.startup.Catalina.start(Catalina.java:593)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at
sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
        at
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:616)
        at org.apache.catalina.startup.Bootstrap.start(Bootstrap.java:289)
        at org.apache.catalina.startup.Bootstrap.main(Bootstrap.java:414)

If I then restart tomcat, I get the following error.
which seems to be caused due to "SELECT * FROM Settings" not containing any
data when called in the settingManager. Then the following leave "port" as
null
String port = settingMan.getValue(Geonet.Settings.SERVER_PORT);
and fails in the following
return protocol + "://" + host + (port.equals("80") ? "" : ":" + port)
+ locServ;

2012-06-04 11:31:24,963 ERROR [jeeves.engine] - Stack :
java.lang.NullPointerException
        at
org.fao.geonet.kernel.DataManager.getSiteURL(DataManager.java:1325)
        at
org.fao.geonet.kernel.ThesaurusManager.loadRepositories(ThesaurusManager.java:208)
        at
org.fao.geonet.kernel.ThesaurusManager.initThesauriTable(ThesaurusManager.java:141)
        at
org.fao.geonet.kernel.ThesaurusManager.<init>(ThesaurusManager.java:102)
        at
org.fao.geonet.kernel.ThesaurusManager.getInstance(ThesaurusManager.java:58)
        at org.fao.geonet.Geonetwork.start(Geonetwork.java:363)
        at jeeves.server.JeevesEngine.initAppHandler(JeevesEngine.java:541)
        at jeeves.server.JeevesEngine.init(JeevesEngine.java:170)
        at
jeeves.server.sources.http.JeevesServlet.init(JeevesServlet.java:85)
        at javax.servlet.GenericServlet.init(GenericServlet.java:212)
        at
org.apache.catalina.core.StandardWrapper.loadServlet(StandardWrapper.java:1173)
        at
org.apache.catalina.core.StandardWrapper.load(StandardWrapper.java:993)
        at
org.apache.catalina.core.StandardContext.loadOnStartup(StandardContext.java:4187)
        at
org.apache.catalina.core.StandardContext.start(StandardContext.java:4496)
        at
org.apache.catalina.core.ContainerBase.addChildInternal(ContainerBase.java:791)
        at
org.apache.catalina.core.ContainerBase.addChild(ContainerBase.java:771)
        at
org.apache.catalina.core.StandardHost.addChild(StandardHost.java:526)
        at
org.apache.catalina.startup.HostConfig.deployWAR(HostConfig.java:905)
        at
org.apache.catalina.startup.HostConfig.deployWARs(HostConfig.java:740)
        at
org.apache.catalina.startup.HostConfig.deployApps(HostConfig.java:500)
        at
org.apache.catalina.startup.HostConfig.start(HostConfig.java:1277)
        at
org.apache.catalina.startup.HostConfig.lifecycleEvent(HostConfig.java:321)
        at
org.apache.catalina.util.LifecycleSupport.fireLifecycleEvent(LifecycleSupport.java:119)
        at
org.apache.catalina.core.ContainerBase.start(ContainerBase.java:1053)
        at
org.apache.catalina.core.StandardHost.start(StandardHost.java:722)
        at
org.apache.catalina.core.ContainerBase.start(ContainerBase.java:1045)
        at
org.apache.catalina.core.StandardEngine.start(StandardEngine.java:443)
        at
org.apache.catalina.core.StandardService.start(StandardService.java:516)
        at
org.apache.catalina.core.StandardServer.start(StandardServer.java:710)
        at org.apache.catalina.startup.Catalina.start(Catalina.java:593)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at
sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
        at
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:616)
        at org.apache.catalina.startup.Bootstrap.start(Bootstrap.java:289)
        at org.apache.catalina.startup.Bootstrap.main(Bootstrap.java:414)

--
View this message in context: http://osgeo-org.1560.n6.nabble.com/geonetwork-fresh-install-on-Oracle-too-many-cursors-tp4978602p4978962.html
Sent from the GeoNetwork developer mailing list archive at Nabble.com.

sppigot · June 4, 2012, 4:28pm

Ian,

Indeed it should - turns out that the ApacheDBCPool switches on the prepared statement pool feature in DBCP which (in the case of oracle at least) keeps cursors open and causes the error you've noticed when the db is built. The JNDIPool does not do this (you have to do it manually in the container context.xml config) so it doesn't get this error. I have a fix prepared for this which I'll commit shortly.

The broader issue here is that the statements used in the database create scripts should be structured so that they don't fill the prepared statement cache up.

Cheers,
Simon
________________________________________
From: ianwallen [ianwallen@anonymised.com]
Sent: Monday, 4 June 2012 10:40 PM
To: geonetwork-devel@lists.sourceforge.net
Subject: Re: [GeoNetwork-devel] geonetwork fresh install on Oracle - too many cursors

Simon,

I am using ApacheDBCPool as this was the default and most common(to my
understanding).
/As an FYI: I just did do a test with "JNDIPool" and I no longer have the
"too many cursors issue" - I do have some issues - but I will post it at a
separate thread. That said - I think the default ApacheDBCPool should also
work - should it not?
/

Here is the ApacheDBCPool config that I am using.

                <resource enabled="true">
                        <name>main-db</name>
                        <provider>jeeves.resources.dbms.ApacheDBCPool</provider>
                        <config>
                                <user>geonetwork</user>
                                <password>geonetwork</password>
                                <driver>oracle.jdbc.driver.OracleDriver</driver>
                                <url>jdbc:oracle:thin:@localhost:1521:test</url>
                                <poolSize>10</poolSize>
                                <validationQuery>SELECT 1 FROM DUAL</validationQuery>
                        </config>
                </resource>

I simply wipe out the tables and start the web application. Here is the
error received when starting up. It seems you are correct in that it fails
when populating the database? (org.fao.geonet.lib.DbLib.insertData)

2012-06-04 11:17:51,742 ERROR [jeeves.engine] - Stack :
java.sql.SQLException: ORA-01000: maximum open cursors exceeded

        at
oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
        at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)
        at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288)
        at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:745)
        at
oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:216)
        at
oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:966)
        at
oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1170)
        at
oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3339)
        at
oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:3423)
        at
org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:105)
        at
org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:105)
        at
org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:105)
        at jeeves.resources.dbms.Dbms.execute(Dbms.java:265)
        at jeeves.resources.dbms.Dbms.execute(Dbms.java:239)
        at org.fao.geonet.lib.DbLib.runSQL(DbLib.java:233)
        at org.fao.geonet.lib.DbLib.runSQL(DbLib.java:248)
        at org.fao.geonet.lib.DbLib.insertData(DbLib.java:193)
        at org.fao.geonet.Geonetwork.initDatabase(Geonetwork.java:647)
        at org.fao.geonet.Geonetwork.start(Geonetwork.java:194)
        at jeeves.server.JeevesEngine.initAppHandler(JeevesEngine.java:541)
        at jeeves.server.JeevesEngine.init(JeevesEngine.java:170)
        at
jeeves.server.sources.http.JeevesServlet.init(JeevesServlet.java:85)
        at javax.servlet.GenericServlet.init(GenericServlet.java:212)
        at
org.apache.catalina.core.StandardWrapper.loadServlet(StandardWrapper.java:1173)
        at
org.apache.catalina.core.StandardWrapper.load(StandardWrapper.java:993)
        at
org.apache.catalina.core.StandardContext.loadOnStartup(StandardContext.java:4187)
        at
org.apache.catalina.core.StandardContext.start(StandardContext.java:4496)
        at
org.apache.catalina.core.ContainerBase.addChildInternal(ContainerBase.java:791)
        at
org.apache.catalina.core.ContainerBase.addChild(ContainerBase.java:771)
        at
org.apache.catalina.core.StandardHost.addChild(StandardHost.java:526)
        at
org.apache.catalina.startup.HostConfig.deployWAR(HostConfig.java:905)
        at
org.apache.catalina.startup.HostConfig.deployWARs(HostConfig.java:740)
        at
org.apache.catalina.startup.HostConfig.deployApps(HostConfig.java:500)
        at
org.apache.catalina.startup.HostConfig.start(HostConfig.java:1277)
        at
org.apache.catalina.startup.HostConfig.lifecycleEvent(HostConfig.java:321)
        at
org.apache.catalina.util.LifecycleSupport.fireLifecycleEvent(LifecycleSupport.java:119)
        at
org.apache.catalina.core.ContainerBase.start(ContainerBase.java:1053)
        at
org.apache.catalina.core.StandardHost.start(StandardHost.java:722)
        at
org.apache.catalina.core.ContainerBase.start(ContainerBase.java:1045)
        at
org.apache.catalina.core.StandardEngine.start(StandardEngine.java:443)
        at
org.apache.catalina.core.StandardService.start(StandardService.java:516)
        at
org.apache.catalina.core.StandardServer.start(StandardServer.java:710)
        at org.apache.catalina.startup.Catalina.start(Catalina.java:593)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at
sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
        at
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:616)
        at org.apache.catalina.startup.Bootstrap.start(Bootstrap.java:289)
        at org.apache.catalina.startup.Bootstrap.main(Bootstrap.java:414)

If I then restart tomcat, I get the following error.
which seems to be caused due to "SELECT * FROM Settings" not containing any
data when called in the settingManager. Then the following leave "port" as
null
String port = settingMan.getValue(Geonet.Settings.SERVER_PORT);
and fails in the following
return protocol + "://" + host + (port.equals("80") ? "" : ":" + port)
+ locServ;

2012-06-04 11:31:24,963 ERROR [jeeves.engine] - Stack :
java.lang.NullPointerException
        at
org.fao.geonet.kernel.DataManager.getSiteURL(DataManager.java:1325)
        at
org.fao.geonet.kernel.ThesaurusManager.loadRepositories(ThesaurusManager.java:208)
        at
org.fao.geonet.kernel.ThesaurusManager.initThesauriTable(ThesaurusManager.java:141)
        at
org.fao.geonet.kernel.ThesaurusManager.<init>(ThesaurusManager.java:102)
        at
org.fao.geonet.kernel.ThesaurusManager.getInstance(ThesaurusManager.java:58)
        at org.fao.geonet.Geonetwork.start(Geonetwork.java:363)
        at jeeves.server.JeevesEngine.initAppHandler(JeevesEngine.java:541)
        at jeeves.server.JeevesEngine.init(JeevesEngine.java:170)
        at
jeeves.server.sources.http.JeevesServlet.init(JeevesServlet.java:85)
        at javax.servlet.GenericServlet.init(GenericServlet.java:212)
        at
org.apache.catalina.core.StandardWrapper.loadServlet(StandardWrapper.java:1173)
        at
org.apache.catalina.core.StandardWrapper.load(StandardWrapper.java:993)
        at
org.apache.catalina.core.StandardContext.loadOnStartup(StandardContext.java:4187)
        at
org.apache.catalina.core.StandardContext.start(StandardContext.java:4496)
        at
org.apache.catalina.core.ContainerBase.addChildInternal(ContainerBase.java:791)
        at
org.apache.catalina.core.ContainerBase.addChild(ContainerBase.java:771)
        at
org.apache.catalina.core.StandardHost.addChild(StandardHost.java:526)
        at
org.apache.catalina.startup.HostConfig.deployWAR(HostConfig.java:905)
        at
org.apache.catalina.startup.HostConfig.deployWARs(HostConfig.java:740)
        at
org.apache.catalina.startup.HostConfig.deployApps(HostConfig.java:500)
        at
org.apache.catalina.startup.HostConfig.start(HostConfig.java:1277)
        at
org.apache.catalina.startup.HostConfig.lifecycleEvent(HostConfig.java:321)
        at
org.apache.catalina.util.LifecycleSupport.fireLifecycleEvent(LifecycleSupport.java:119)
        at
org.apache.catalina.core.ContainerBase.start(ContainerBase.java:1053)
        at
org.apache.catalina.core.StandardHost.start(StandardHost.java:722)
        at
org.apache.catalina.core.ContainerBase.start(ContainerBase.java:1045)
        at
org.apache.catalina.core.StandardEngine.start(StandardEngine.java:443)
        at
org.apache.catalina.core.StandardService.start(StandardService.java:516)
        at
org.apache.catalina.core.StandardServer.start(StandardServer.java:710)
        at org.apache.catalina.startup.Catalina.start(Catalina.java:593)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at
sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
        at
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:616)
        at org.apache.catalina.startup.Bootstrap.start(Bootstrap.java:289)
        at org.apache.catalina.startup.Bootstrap.main(Bootstrap.java:414)

--
View this message in context: http://osgeo-org.1560.n6.nabble.com/geonetwork-fresh-install-on-Oracle-too-many-cursors-tp4978602p4978962.html
Sent from the GeoNetwork developer mailing list archive at Nabble.com.

------------------------------------------------------------------------------
Live Security Virtual Conference
Exclusive live event will cover all the ways today's security and
threat landscape has changed and how IT managers can respond. Discussions
will include endpoint security, mobile security and the latest in malware
threats. http://www.accelacomm.com/jaw/sfrnl04242012/114/50122263/
_______________________________________________
GeoNetwork-devel mailing list
GeoNetwork-devel@lists.sourceforge.net
geonetwork-devel List Signup and Options
GeoNetwork OpenSource is maintained at GeoNetwork - Geographic Metadata Catalog download | SourceForge.net