You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@activemq.apache.org by Hatta <rm...@yahoo.com> on 2008/04/24 03:40:02 UTC
maximum open cursors exceeded
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-tp16834950s2354p16834950.html
Sent from the ActiveMQ - User mailing list archive at Nabble.com.
Re: maximum open cursors exceeded
Posted by Hatta <rm...@yahoo.com>.
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.
Re: maximum open cursors exceeded
Posted by Rob Davies <ra...@gmail.com>.
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.
>
Re: maximum open cursors exceeded
Posted by Hatta <rm...@yahoo.com>.
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.
Re: maximum open cursors exceeded
Posted by Hatta <rm...@yahoo.com>.
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-tp16834950s2354p16884121.html
Sent from the ActiveMQ - User mailing list archive at Nabble.com.
Re: maximum open cursors exceeded
Posted by Hatta <rm...@yahoo.com>.
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-tp16834950s2354p16883070.html
Sent from the ActiveMQ - User mailing list archive at Nabble.com.