Mailing-List: contact users-help@activemq.apache.org; run by ezmlm
Precedence: bulk
Reply-To: users@activemq.apache.org
Received-SPF: pass (nike.apache.org: domain of lists@nabble.com designates
 216.139.236.158 as permitted sender)
Message-ID: <17093701.post@talk.nabble.com>
Date: Tue, 6 May 2008 18:29:15 -0700 (PDT)
From: Hatta <rmi_jndi@yahoo.com>
To: users@activemq.apache.org
Subject: Re: maximum open cursors exceeded
In-Reply-To: <692689E5-B3BE-4166-8F2F-FE7D093C96B0@gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
References: <16834950.post@talk.nabble.com> <16883070.post@talk.nabble.com>
 <16884121.post@talk.nabble.com> <17053893.post@talk.nabble.com>
 <692689E5-B3BE-4166-8F2F-FE7D093C96B0@gmail.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.