Max opened cursors limit reached on Oracle

Hi,

We are using Jive-Messenger with Oracle 8.1.7.4 and we have this error after few hours:

2004.06.14 14:18 com.jivesoftware.xmpp.user.spi.DbUserManager.deleteUser(DbUserManager.java:256)

java.sql.SQLException: ORA-01000: Nombre maximum de curseurs ouverts atteint

at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:114)

at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:208)

at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:542)

at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1330)

at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteFetch(TTC7Protocol.java:757)

at oracle.jdbc.driver.OracleStatement.executeNonQuery(OracleStatement.java:1313)

at oracle.jdbc.driver.OracleStatement.doExecuteOther(OracleStatement.java:1232)

at oracle.jdbc.driver.OracleStatement.doExecuteWithBatch(OracleStatement.java:1353 )

at oracle.jdbc.driver.OracleStatement.doExecute(OracleStatement.java:1760)

at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:18 07)

at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatemen t.java:332)

at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java :376)

at com.jivesoftware.xmpp.user.spi.DbUserManager.deleteUser(DbUserManager.java:253)

at com.jivesoftware.xmpp.user.spi.DbUserManager.deleteUser(DbUserManager.java:271)

at com.jivesoftware.xmpp.handler.IQRegisterHandler.handleIQ(IQRegisterHandler.java :111)

at com.jivesoftware.xmpp.spi.IQHandler.handleMessages(IQHandler.java:48)

at com.jivesoftware.xmpp.spi.BasicChannel$ChannelWorker.run(BasicChannel.java:340)

We use Oracle thin java client (classe_12.zip) with Jive-Messenger 1.0.8 and our max opened cursor limit is 400. It seems some ressources not released by the jdbc client.

Any Idea ?

Thanks,

LC

Message was edited by: ipsystem

Thanks for the bug report. We’'ll investigate the issue and post an update as soon as we find the cause of the issue.

Regards,

Matt

Is that all ?

And what can we do during the time you’'re looking for the solution ? Is there any workaround ? I remind you that the web site is currently in production ? Do we really need to restart the database every day ?

Tarkus.

Tarkus,

I’'d like to check whether there is a leaking of cursors in the application or a problem of configuration. My first suggestion is to check the maximum allowed number of cursors per user. You can run this query to obtain this information. I think that Oracle, by default, has a maximum of 50 which is normally quite low for a real application.

/* Identify maximum number of cursors per user (configuration) */
select * from v$parameter where name = ''open_cursors''

If the number is around 500 to 1000 then I’'d recommend identifying cursors opened at some point of time that are still active (i.e. possible leaked cursors). You can execute this query to obtain this information:

/* Identify cursors opened at some point of time */
select user_name, b.status, osuser, machine, a.sql_text from v$session b, v$open_cursor a where a.sid = b.sid

You can take a look at the last returned column (of the rows whose status is ACTIVE) that contains the SQL text of the query that is still consuming a cursor. This information could help us locate possible leaking of cursors.

This last query is useful for identifying the number of cursors that are being currently opened by sessions.

/* Identify how many cursors are being currently opened by sessions */
select a.username, a.sid, b.value
from   v$session a, v$sesstat b, v$statname c
where  b.sid = a.sid
and    c.statistic# = b.statistic#
and    c.name = ''opened cursors current''
order  by 3 desc

In case that Messenger is in fact leaking cursors, I’'d recommend increasing the value of no. of open cursors parameter in init.ora file so that you can keep the application running more time while the problem is being solved.

BTW, are you running any process (like for example creating user groups) before you hit this problem?

Regards,

– Gato

Tarkus,

There was an issue in past builds with not closing jdbc statements and resultsets which has now been fixed. There has also been numerous bug fixes in Messenger base that I’'m sure will be benefical for your company. I will try to get the latest release on our site by Monday June 28th. This should take care of the problems you listed.

Regards,

Derek

Hi,

One month later, we still not have the new release available for download. When will we able to download it ?

Regards,

LC

Message was edited by: ipsystem

Hi all,

The beta release of Messenger 1.1 is out. This handled many of the issues posted on this forum, and I would like to thank everyone for their patience. PLEASE DO NOT try and upgrade from your previous version, but instead install in a seperate directory with a different database, or test with the embedded database. You can find download instructions at

http://www.jivesoftware.com/products/messenger/beta.

Thank You,

Derek