RAC 11g: ORA-01000: maximum open cursors exceeded
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 18

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

  1. #1
    Join Date
    Mar 2001
    Location
    Ireland/Dublin
    Posts
    684

    Angry 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
    Best wishes!
    Dmitri

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    have you thought about upping open_cursors?

  3. #3
    Join Date
    Mar 2001
    Location
    Ireland/Dublin
    Posts
    684
    open_cursors = 600 - which is enough
    With 10g database we use open_cursors = 300 and have never had such problem.
    Best wishes!
    Dmitri

  4. #4
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,027
    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.
    this space intentionally left blank

  5. #5
    Join Date
    Mar 2001
    Location
    Ireland/Dublin
    Posts
    684
    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.
    Best wishes!
    Dmitri

  6. #6
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    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

  7. #7
    Join Date
    Mar 2001
    Location
    Ireland/Dublin
    Posts
    684
    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.
    Best wishes!
    Dmitri

  8. #8
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,027
    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.
    this space intentionally left blank

  9. #9
    Join Date
    Mar 2001
    Location
    Ireland/Dublin
    Posts
    684
    I am sorry, but you don't understand the problem
    Best wishes!
    Dmitri

  10. #10
    Join Date
    Jan 2001
    Posts
    2,828
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width