Hi Rob, I m thankful for your reply and the effort put up by yourself to get this bug fixed.
I have already downloaded and run the standalone version of 5.2 snapshot on windows environment. After leaving it open for one night since 6 May 2008, I m glad to see that its not producing the same problem anymore. For validity purpose I will leave it on again for today. (its been running more than 18 hours now). Again, I appreciate the effort given. Thanks. Regards Hatta rajdavies wrote: > > Could you try the latest SNAPSHOT - I'm hoping this could be fixed by > https://issues.apache.org/activemq/browse/AMQ-1702 > > cheers, > > Rob > > http://open.iona.com/ -Enterprise Open Integration > http://rajdavies.blogspot.com/ > > > On 5 May 2008, at 03:58, Hatta wrote: > >> >> Hi again, >> >> To the respected ActiveMQ Team, appreciate your kind response on >> this matter >> as I have performed the action based on the following advise given by >> Oracle. >> >> The result was the lock did not occurred for some time. But after 5 >> to 6 >> hours, the message is getting displayed again. >> >> The message is the same as the previous posting. >> >> I sincerely appreciate that the ActiveMQ team would look into this >> matter. >> >> Regards >> Hatta >> >> >> >> >> Hatta wrote: >>> >>> Hi again, >>> >>> Below is the response from Oracle Meta-link regarding the issue with >>> maximum open cursors exceeded: >>> >>> WORKAROUNDS FOR ORA-01000 >>> >>> Solution Description: >>> ===================== >>> >>> There are two ways to workaround this ORA-01000 error. You can tune >>> cursor >>> usage at the database level and at the application level. >>> >>> 1. Tuning at the DATABASE LEVEL >>> >>> There is a parameter you can set in the init.ora that determines the >>> number of >>> cursors a user can open in a session: OPEN_CURSORS. >>> >>> OPEN_CURSORS by default is 50 and usually, this is not high enough. >>> The >>> highest >>> value you can set this parameter to is operating system dependant. >>> For >>> more >>> information, please refer to Oracle7 Server Administrator's Guide, >>> Appendix A. >>> >>> To solve the ORA-01000 error, set the OPEN_CURSORS to a higher number >>> (such as >>> 255). You may need to set it to the maximum of the operating system >>> limit. >>> >>> Consequences to changing this parameter: >>> >>> This parameter does not effect performance in any way but Oracle >>> will now >>> need >>> a little more memory to store the cursors. >>> >>> >>> 2. Tuning at the APPLICATION LEVEL >>> >>> There are three parameters that affect handling cursors at the >>> application >>> level: RELEASE_CURSOR, HOLD_CURSOR, MAXOPENCURSORS. You should set >>> these >>> parameters at the precompiler level. >>> >>> HOLD_CURSOR by default is NO. This means that after Oracle executes >>> a SQL >>> statement the links to the cursor cache, memory, and parse locks are >>> released >>> and marked for reuse. For more details refer to Programmer's Guide >>> to >>> Precompilers Version 1.6 p.6-16. >>> >>> RELEASE_CURSOR by default is NO. This means that after Oracle >>> executes a >>> SQL >>> statement, the links to the cursor cache is maintained and not >>> released. >>> For >>> more information, refer to Programmer's Guide to Precompilers >>> Version 1.6 >>> p.6-26. >>> >>> These two parameters must be used in conjunction for them to be >>> effective. >>> Here is a table that shows how settings of the two parameters >>> interact. >>> >>> ---------------------------------------------------- >>> |HOLD_CURSOR | RELEASE_CURSOR | LINKS ARE... | >>> ---------------------------------------------------- >>> | NO | not applicable | marked as reusable | >>> | YES | NO | maintained | >>> | NO | YES | removed immediately| >>> | n/a | YES | removed immediately| >>> ---------------------------------------------------- >>> >>> To resolve the ORA-01000 error, you should set HOLD_CURSOR=NO and >>> RELEASE_CURSOR=YES. This way, after the cursors are used, Oracle >>> will free >>> up >>> the memory for other cursors. >>> >>> Consequences of setting these parameters HOLD_CURSOR=NO and >>> RELEASE_CURSOR=YES: >>> >>> This will cause Oracle to release the links and locks for each cursor >>> after the >>> SQL statement is executed. This means that the next time Oracle >>> needs to >>> issue >>> the same SQL statement, Oracle will have to reparse the statement, >>> and >>> rebuild >>> the execution plan. This will cause some performance overhead. >>> >>> MAXOPENCURSORS by default is 10. This number indicates the concurrent >>> number >>> of open cursors that the precompiler tries to keep cached. It >>> specifies >>> the >>> initial size of the cursor cache. The limit of this parameter is >>> determined by >>> what you set OPEN_CURSORS to. Here is the formula: >>> >>> MAXOPENCURSORS + 6 <= OPEN_CURSORS >>> 6 is the overhead cursors Oracle needs. >>> >>> Here is a calculation of the maximum number of cursors in use: >>> SQL statement cursors >>> PL/SQL parent cursors >>> PL/SQL child cursors >>> +6 cursors for overhead >>> ----------------------- >>> sum of cursors in use. >>> >>> Appreciate a response from the ActiveMQ Team. >>> >>> Regards >>> Hatta >>> >>> >>> >>> >>> >>> >>> Hatta wrote: >>>> >>>> Hi again, >>>> >>>> There are a few items which I would like to add on this topic as >>>> well, >>>> >>>> 1. I have referred to the Oracle Forums and the general statement >>>> given >>>> was to ensure that the application client whom is accessing Oracle >>>> Database to check their open cursor statement. >>>> >>>> If there exist codes where the open cursor statements are not >>>> closed, >>>> then it should be considered to correct that code. >>>> >>>> From the Oracle point of view, is by increasing the open cursor >>>> parameter >>>> in the database to a certain amount. This would definitely hide the >>>> issue. >>>> >>>> But the flaw of this approach is that if the issue occured again, >>>> then >>>> what is the final solution? >>>> >>>> Appreciate the ActiveMQ technical team to respond to this matter. >>>> >>>> Thanks in advance >>>> >>>> Hatta >>>> >>>> >>>> >>>> >>>> >>>> >>>> Hatta wrote: >>>>> >>>>> Hi, >>>>> >>>>> I m using ActiveMQ 5.1 SNAPSHOT version. I have already >>>>> experienced the >>>>> same problem in ActiveMQ 5.0.0 Production release. >>>>> >>>>> ActiveMQ 5.1 has been installed on a Linux OS : kernel version >>>>> 2.6 and >>>>> its currently connecting to an Oracle 10 RAC (Real Application >>>>> Cluster). >>>>> >>>>> After a few hours of observation and no activity between my >>>>> application >>>>> and ActiveMQ 5.1, the following was noticed: >>>>> >>>>> ERROR DefaultDatabaseLocker - Failed to update database >>>>> lock: >>>>> java.sql.SQLException: ORA-01000: maximum open cursors exceeded >>>>> >>>>> >>>>> >>>>> 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:743) >>>>> >>>>> at >>>>> oracle >>>>> .jdbc >>>>> .driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:213) >>>>> >>>>> at >>>>> oracle >>>>> .jdbc >>>>> .driver >>>>> .T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:952) >>>>> >>>>> at >>>>> oracle >>>>> .jdbc >>>>> .driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java: >>>>> 1160) >>>>> >>>>> at >>>>> oracle >>>>> .jdbc >>>>> .driver >>>>> .OraclePreparedStatement >>>>> .executeInternal(OraclePreparedStatement.java:3285) >>>>> >>>>> at >>>>> oracle >>>>> .jdbc >>>>> .driver >>>>> .OraclePreparedStatement >>>>> .executeUpdate(OraclePreparedStatement.java:3368) >>>>> >>>>> at >>>>> org >>>>> .apache >>>>> .commons >>>>> .dbcp >>>>> .DelegatingPreparedStatement >>>>> .executeUpdate(DelegatingPreparedStatement.java:94) >>>>> >>>>> at >>>>> org >>>>> .apache >>>>> .activemq >>>>> .store >>>>> .jdbc.DefaultDatabaseLocker.keepAlive(DefaultDatabaseLocker.java: >>>>> 103) >>>>> >>>>> at >>>>> org >>>>> .apache >>>>> .activemq >>>>> .store >>>>> .jdbc >>>>> .JDBCPersistenceAdapter >>>>> .databaseLockKeepAlive(JDBCPersistenceAdapter.java:458) >>>>> >>>>> at >>>>> org.apache.activemq.store.jdbc.JDBCPersistenceAdapter >>>>> $3.run(JDBCPersistenceAdapter.java:260) >>>>> >>>>> at >>>>> java.util.concurrent.Executors >>>>> $RunnableAdapter.call(Executors.java:417) >>>>> >>>>> at >>>>> java.util.concurrent.FutureTask >>>>> $Sync.innerRunAndReset(FutureTask.java:280) >>>>> >>>>> at >>>>> java.util.concurrent.FutureTask.runAndReset(FutureTask.java:135) >>>>> >>>>> at >>>>> java.util.concurrent.ScheduledThreadPoolExecutor >>>>> $ScheduledFutureTask.access$101(ScheduledThreadPoolExecutor.java: >>>>> 65) >>>>> >>>>> at >>>>> java.util.concurrent.ScheduledThreadPoolExecutor >>>>> $ScheduledFutureTask.runPeriodic(ScheduledThreadPoolExecutor.java: >>>>> 142) >>>>> >>>>> at >>>>> java.util.concurrent.ScheduledThreadPoolExecutor >>>>> $ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:166) >>>>> >>>>> at >>>>> java.util.concurrent.ThreadPoolExecutor >>>>> $Worker.runTask(ThreadPoolExecutor.java:650) >>>>> >>>>> at >>>>> java.util.concurrent.ThreadPoolExecutor >>>>> $Worker.run(ThreadPoolExecutor.java:675) >>>>> >>>>> at java.lang.Thread.run(Thread.java:595) >>>>> >>>>> >>>>> However, My application is still able to send and receive jms >>>>> messages >>>>> from the broker. But this error message is disturbing >>>>> and may give an impression that there something wrong with the >>>>> server >>>>> communication with the DB. >>>>> >>>>> Appreciate a response to this matter. >>>>> >>>>> Regards >>>>> Hatta >>>>> >>>>> >>>>> >>>> >>>> >>> >>> >> >> -- >> View this message in context: >> http://www.nabble.com/maximum-open-cursors-exceeded-tp16834950s2354p17053893.html >> Sent from the ActiveMQ - User mailing list archive at Nabble.com. >> > > > -- View this message in context: http://www.nabble.com/maximum-open-cursors-exceeded-tp16834950s2354p17093701.html Sent from the ActiveMQ - User mailing list archive at Nabble.com.
