Mailing-List: contact users-help@activemq.apache.org; run by ezmlm
Precedence: bulk
Reply-To: users@activemq.apache.org
Received-SPF: pass (athena.apache.org: domain of rajdavies@gmail.com
 designates 209.85.198.240 as permitted sender)
DomainKey-Signature: a=rsa-sha1; c=nofws;
        d=gmail.com; s=gamma;
        h=message-id:from:to:in-reply-to:content-type:content-transfer-encoding:mime-version:subject:date:references:x-mailer;
        b=AesBbrkWAPqG1M6XKjiqSC+jrDmSZ3RUo6DOv088s0rJ6WzLd+7zwBZTh6x9UKpDIegLqOujo4/C9nXXI45aNXsxHTJK6DuZVp353cNSzfhb7gNtOyaZzQl1Ol0uRenxELopBanEN4vyEW/5BBS7gPOKBPXKjbG41bHnoX722/c=
Message-Id: <692689E5-B3BE-4166-8F2F-FE7D093C96B0@gmail.com>
From: Rob Davies <rajdavies@gmail.com>
To: users@activemq.apache.org
In-Reply-To: <17053893.post@talk.nabble.com>
Content-Type: text/plain; charset=US-ASCII; format=flowed; delsp=yes
Content-Transfer-Encoding: 7bit
Mime-Version: 1.0 (Apple Message framework v919.2)
Subject: Re: maximum open cursors exceeded
Date: Mon, 5 May 2008 18:35:16 +0100
References: <16834950.post@talk.nabble.com> <16883070.post@talk.nabble.com>
 <16884121.post@talk.nabble.com> <17053893.post@talk.nabble.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.
>