RAC 11g: ORA-01000: maximum open cursors exceeded

[kgb]

Hi All,

We have Oracle RAC on Oracle Enterpise Linux.
And very often we get error: ORA-01000: maximum open cursors exceeded
We were gitting it more often when we had cursor_sharing=exact
Currently it is set to simmilar
It does not matter what application we use, the example bellow is very demonstrative as this is a new session and only collect statistics were running.

Did anyone meet the same behaviour?

SQL*Plus: Release 10.2.0.3.0 - Production on Fri Sep 5 12:20:23 2008

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
With the Partitioning, Real Application Clusters, Oracle Label Security, OLAP,
Data Mining, Oracle Database Vault and Real Application Testing options


Session altered.

12:20:23 > connect user/user@DUBRAC1
Connected.

Session altered.

Elapsed: 00:00:00.00
12:20:35 > EXEC DBMS_STATS.gather_schema_stats (ownname => 'USER', cascade =>true,estimate_percent => dbms_stats.auto_sample_size
);
BEGIN DBMS_STATS.gather_schema_stats (ownname => 'USER', cascade =>true,estimate_percent => dbms_stats.auto_sample_size); END;

*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01000: maximum open cursors exceeded
ORA-00604: error occurred at recursive SQL level 1
ORA-01000: maximum open cursors exceeded
ORA-01000: maximum open cursors exceeded


Elapsed: 00:24:34.30
12:45:41 > analyze table ACCOUNT_POSTING comute statistics cascade constraint;
analyze table ACCOUNT_POSTING comute statistics cascade constraint
                              *
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01000: maximum open cursors exceeded
ORA-00604: error occurred at recursive SQL level 1
ORA-01000: maximum open cursors exceeded
ORA-01490: invalid ANALYZE command


Elapsed: 00:00:00.01

[davey23uk]

have you thought about upping open_cursors?

[kgb]

open_cursors = 600 - which is enough
With 10g database we use open_cursors = 300 and have never had such problem.

[gandolf989]

open_cursors = 600 - which is enough
With 10g database we use open_cursors = 300 and have never had such problem.

Perhaps this article will help, http://www.orafaq.com/node/758.
You also need to make sure that you code is not leaving cursors open.

[kgb]

Thank you for the link.

The problem is that our application works fine with 10g,
with RAC 11i - even dbms_stats does not work (see provided code).
We have a standaolne version of Oracle 11i and application works fine, but does not work with RAC of the sam eversion. I assume there is something wrong related to RAC.

[davey23uk]

open_cursors = 600 - which is enough
With 10g database we use open_cursors = 300 and have never had such problem.

different version = different requirements, so 600 isnt enough is it

[kgb]

different version = different requirements, so 600 isnt enough is it

Thank you, but our application does not start to open more cursors, more over Standalone version of Oracle 11i with 300 sursors works fine.
In my first post you can see that even dbms_stats fails to run.

[gandolf989]

Thank you, but our application does not start to open more cursors, more over Standalone version of Oracle 11i with 300 sursors works fine.
In my first post you can see that even dbms_stats fails to run.

But if it is interfering with users on a production database then set it to 2000 if that is what needed, and then see how many you are using and whether you can rewrite your code to be more efficient in closing cursors. You could wonder why 600 isn't enough, or you could just deal with it and try to improve the situation once you have it stabilized.

[kgb]

I am sorry, but you don't understand the problem

[hrishy]

Hi

very curious about this problem.
Have you considered opening a TAR with Oracle support on this and see what they have to say.

regards
Hrishy