maximum open cursors exceeded from Oracle XA Connection pool - Weblogic

This is a discussion on maximum open cursors exceeded from Oracle XA Connection pool - Weblogic ; Jeeva wrote: > Hi, > May I know what the patched in the atatchement? > > Thanks, > jeeva Sure. It makes only one change to the pool where it tests connections. Before, we just did a stmt.execute("select count(*) from ...

+ Reply to Thread
Page 2 of 2 FirstFirst 1 2
Results 21 to 27 of 27

Thread: maximum open cursors exceeded from Oracle XA Connection pool

  1. Re: maximum open cursors exceeded from Oracle XA Connection pool



    Jeeva wrote:

    > Hi,
    > May I know what the patched in the atatchement?
    >
    > Thanks,
    > jeeva


    Sure. It makes only one change to the pool where it tests connections.
    Before, we just did a stmt.execute("select count(*) from dual");
    We did not obtain any result set, we just close the statement, which
    by spec, should do everything. However, it may be that the oracle
    driver does not clear the cursor. The change is to add:
    ResultSet r = stmt.getResultSet();
    if (r != null) try { r.close(); } catch (Exception ignore){}

    That should clear up the cursors associated with that test SQL...
    Joe


  2. Re: maximum open cursors exceeded from Oracle XA Connection pool


    Hi,
    May I know what the patched in the atatchement?

    Thanks,
    jeeva

  3. Re: maximum open cursors exceeded from Oracle XA Connection pool


    Hi Joe:

    I am still seeing a constant increase the number of cursors;

    (I do have Your patch on ..Closing the result set explicitely)
    and set the refresh minutes to 99999999...

    Still its increasing every 5 minutes.

    Selected the SQL_TEXT from the open cursors table and I have more information
    for you:

    SELECT HIGH_KEY FROM KEY_GENERATOR WHERE TABLE_NAME = UPP 7A5E9414 325199581
    ------------------------------------------------
    begin :1 := JAVA_XA.xa_start_new(:2,:3,:4,:5,:6); end; 7A3D9EBC 1469188185
    begin :1 := JAVA_XA.xa_commit_new (:2,:3,:4,:5); end; 7A3BB978 358719002
    ----------------------------------------------
    UPDATE ORGANIZATION SET NAME=:"SYS_B_0",ADDRESS1=null,CITY=n 77AEC8D8 812017163
    begin :1 := JAVA_XA.xa_end_new(:2,:3,:4,:5); end; 7A3BD9D8 633871228
    SELECT SYSDATE FROM DUAL 7A6F11B8 3614774371
    SELECT SYSDATE FROM DUAL 7A6F11B8 3614774371
    SELECT ID, NAME, ADDRESS1, CITY, STATE, ZIPCODE, ZIPCODE4, O 7A338634 3714295547
    SELECT ID, NAME, ADDRESS1, CITY, STATE, ZIPCODE, ZIPCODE4, O 7A338634 3714295547
    UPDATE KEY_GENERATOR SET HIGH_KEY=HIGH_KEY + :b1 ,TIMESTAMP= 7A5E902C 1694445837
    SELECT er.ENTITY_ID1, er.ENTITY_ID2, er.DEFINER_ENTITY_ID, e 7A391490 300182699
    SELECT ID, NAME, ORG_TYPE, IS_ACTIVE, ENABLE, ENTITY_ID FROM 7A3AF7E0 3271025726
    select longname from javasnm$ where short = :1 7A927EA0 2044415509
    insert into javasnm$(short, longname, longdbcs) values(:1,:2 7A9430CC 3686129482

    -------------------------------------------------------------

    Comparing these SQL_TXT with the SQL_TEXT for the other processes for which Open
    cursors are not increasing, I find the follwoing text is present with the user
    process for which the open cursors keeps increasing:

    begin :1 := JAVA_XA.xa_start_new(:2,:3,:4,:5,:6); end; 7A3D9EBC 1469188185
    begin :1 := JAVA_XA.xa_commit_new (:2,:3,:4,:5); end; 7A3BB978 358719002

    That sounds like XA Start Stored procedure is not closing the cursors?

  4. Re: maximum open cursors exceeded from Oracle XA Connection pool



    Jeeva wrote:

    > Hi Joe:
    >
    > I am still seeing a constant increase the number of cursors;
    >
    > (I do have Your patch on ..Closing the result set explicitely)
    > and set the refresh minutes to 99999999...
    >
    > Still its increasing every 5 minutes.
    >
    > Selected the SQL_TEXT from the open cursors table and I have more information
    > for you:
    >
    > SELECT HIGH_KEY FROM KEY_GENERATOR WHERE TABLE_NAME = UPP 7A5E9414 325199581
    > ------------------------------------------------
    > begin :1 := JAVA_XA.xa_start_new(:2,:3,:4,:5,:6); end; 7A3D9EBC 1469188185
    > begin :1 := JAVA_XA.xa_commit_new (:2,:3,:4,:5); end; 7A3BB978 358719002
    > ----------------------------------------------
    > UPDATE ORGANIZATION SET NAME=:"SYS_B_0",ADDRESS1=null,CITY=n 77AEC8D8 812017163
    > begin :1 := JAVA_XA.xa_end_new(:2,:3,:4,:5); end; 7A3BD9D8 633871228
    > SELECT SYSDATE FROM DUAL 7A6F11B8 3614774371
    > SELECT SYSDATE FROM DUAL 7A6F11B8 3614774371
    > SELECT ID, NAME, ADDRESS1, CITY, STATE, ZIPCODE, ZIPCODE4, O 7A338634 3714295547
    > SELECT ID, NAME, ADDRESS1, CITY, STATE, ZIPCODE, ZIPCODE4, O 7A338634 3714295547
    > UPDATE KEY_GENERATOR SET HIGH_KEY=HIGH_KEY + :b1 ,TIMESTAMP= 7A5E902C 1694445837
    > SELECT er.ENTITY_ID1, er.ENTITY_ID2, er.DEFINER_ENTITY_ID, e 7A391490 300182699
    > SELECT ID, NAME, ORG_TYPE, IS_ACTIVE, ENABLE, ENTITY_ID FROM 7A3AF7E0 3271025726
    > select longname from javasnm$ where short = :1 7A927EA0 2044415509
    > insert into javasnm$(short, longname, longdbcs) values(:1,:2 7A9430CC 3686129482
    >
    > -------------------------------------------------------------
    >
    > Comparing these SQL_TXT with the SQL_TEXT for the other processes for which Open
    > cursors are not increasing, I find the follwoing text is present with the user
    > process for which the open cursors keeps increasing:
    >
    > begin :1 := JAVA_XA.xa_start_new(:2,:3,:4,:5,:6); end; 7A3D9EBC 1469188185
    > begin :1 := JAVA_XA.xa_commit_new (:2,:3,:4,:5); end; 7A3BB978 358719002
    >
    > That sounds like XA Start Stored procedure is not closing the cursors?


    Yes. This sounds like a question you should ask Oracle support about.
    You should ask them why their driver/DBMS would be showing this. You
    might also download Oracle's very latest thin driver, and get it listed
    ahead of that patch jar. Oracle changes their driver often. You may be
    seeing a bug that they've fixed.
    Please do post what they tell you.
    thanks,
    Joe


  5. Re: maximum open cursors exceeded from Oracle XA Connection pool


    Hi:

    WHat does this mean:?
    Please contact bea and get the workaround for this.

    XAResource.recover repeatedly returns the same set of in-doubt Xids irrespective
    of the input flag. According to the XA spec, the Transaction Manager should initially
    call XAResource.recover with TMSTARTRSCAN and then call XAResource.recover with
    TMNOFLAGS repeatedly until no Xids are returned. This Oracle bug could lead to
    infinite recursion and subsequent running out of Oracle cursors with error "ORA-01000:
    maximum open cursors exceeded."

    Weblogic Server provides an internal workaround for this issue.

    What workaround its talking about?

  6. Re: maximum open cursors exceeded from Oracle XA Connection pool


    Hi,

    There seems to be patch available for WLS 7.1
    Is there a similar patch for 6.1?

    http://support.bea.com/application?n...products&ES=ES

  7. Re: maximum open cursors exceeded from Oracle XA Connection pool



    Jeeva wrote:

    > Hi,
    >
    > There seems to be patch available for WLS 7.1
    > Is there a similar patch for 6.1?


    At this point, if you make an official support request you'll get your answer the fastest.
    Joe
    >
    > http://support.bea.com/application?n...products&ES=ES



+ Reply to Thread
Page 2 of 2 FirstFirst 1 2