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

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?

Code:

---

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

---

have you thought about upping open_cursors?

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

Quote:

---

Originally Posted by kgb

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.

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.

Quote:

---

Originally Posted by kgb

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

Quote:

---

Originally Posted by davey23uk

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.

Quote:

---

Originally Posted by kgb

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.

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

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

Have you monitored what is in v$open_cursor when you see this error?

Quote:

---

Originally Posted by pando

Have you monitored what is in v$open_cursor when you see this error?

---

Yes, I have only one session as this user (checked 2 nodes) and the result is bellow:

Code:

---

SQL> select sid,serial#,machine,program from v$session where username='USER';

      SID    SERIAL# MACHINE                                                          PROGRAM
---------- ---------- ---------------------------------------------------------------- ------------------------------------------------
      274      43685 MY MACHINE                                              sqlplus.exe

SQL> select count(*) from v$open_cursor where user_name='USER';

  COUNT(*)
----------
      617

---

Quote:

---

Originally Posted by 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

---

Thank you, this will be my next step.

I dont mean count(*)

Look what SQL are being executed in v$open_cursor

Quote:

---

Originally Posted by pando

I dont mean count(*)

Look what SQL are being executed in v$open_cursor

---

Yes, I did look at it, only system queries, I can't say too much about it

Hello KGB

Did you find solution to your problem after moving to RAC , as we are having similar issues

Thanks

-Jas

Quote:

---

Originally Posted by jasvinders

Hello KGB

Did you find solution to your problem after moving to RAC , as we are having similar issues

Thanks

-Jas

---

Not yet, today I have contacted Oracle - will see what they say ...

I found that tehy released 11.0.1.7, and they fixed somethign related to that error, will update.
5377060 ORA-604 ORA-1000 when selecting from DBA_JAVA_CLASSES
377060 ORA-604 ORA-1000 when selecting from DBA_JAVA_CLASSES
6711477 ORA-1000 from repeated use of a REF CURSOR which is already open