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.
