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 ; I am on WLS 6.1 sp2. When i leave the server up overnight unaccessed and come back and see in the morning, I see the following stacktrace on the server console. I dont think its causing any harm, but is ...

+ Reply to Thread
Page 1 of 2 1 2 LastLast
Results 1 to 20 of 27

Thread: maximum open cursors exceeded from Oracle XA Connection pool

  1. maximum open cursors exceeded from Oracle XA Connection pool


    I am on WLS 6.1 sp2.
    When i leave the server up overnight unaccessed and come back and see in the morning,
    I see the following stacktrace on the server console.
    I dont think its causing any harm, but is there anyway to prevent this from occuring?

    java.sql.SQLException: ORA-01000: maximum open cursors exceeded

    at oracle.jdbc.dbaccess.DBError.throwSqlException(DBE rror.java:168)
    at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:2 08)
    at oracle.jdbc.ttc7.Oopen.receive(Oopen.java:118)
    at oracle.jdbc.ttc7.TTC7Protocol.open(TTC7Protocol.ja va:466)
    at oracle.jdbc.driver.OracleStatement.(OracleStatement.java:413)
    at oracle.jdbc.driver.OracleStatement.(OracleStatement.java:432)
    at oracle.jdbc.driver.OraclePreparedStatement.(OraclePreparedStatement.java:182)
    at oracle.jdbc.driver.OracleCallableStatement.(OracleCallableStatement.java:102)
    at oracle.jdbc.driver.OracleCallableStatement.(OracleCallableStatement.java:86)
    at oracle.jdbc.driver.OracleConnection.privatePrepare Call(OracleConnection.java:736)
    at oracle.jdbc.driver.OracleConnection.prepareCall(Or acleConnection.java:622)
    at oracle.jdbc.xa.client.OracleXAResource.start(Oracl eXAResource.java:163)
    at weblogic.jdbc.jta.VendorXAResource.start(VendorXAR esource.java:41)
    at weblogic.transaction.internal.ServerResourceInfo.s tart(ServerResourceInfo.java:1032)
    at weblogic.transaction.internal.ServerResourceInfo.x aStart(ServerResourceInfo.java:975)
    at weblogic.transaction.internal.ServerResourceInfo.e nlist(ServerResourceInfo.java:234)
    at weblogic.transaction.internal.ServerTransactionImp l.enlistResource(ServerTransactionImpl.java:374)
    at weblogic.jdbc.common.internal.ConnectionEnv.test(C onnectionEnv.java:937)
    at weblogic.common.internal.ResourceAllocator.reserve (ResourceAllocator.java:465)
    at weblogic.common.internal.ResourceAllocator.reserve Unused(ResourceAllocator.java:376)
    at weblogic.common.internal.ResourceAllocator.trigger (ResourceAllocator.java:1103)
    at weblogic.time.common.internal.ScheduledTrigger.exe cuteLocally(ScheduledTrigger.java:238)
    at weblogic.time.common.internal.ScheduledTrigger.exe cute(ScheduledTrigger.java:229)
    at weblogic.time.server.ScheduledTrigger.execute(Sche duledTrigger.java:65)
    at weblogic.kernel.ExecuteThread.execute(ExecuteThrea d.java:139)
    at weblogic.kernel.ExecuteThread.run(ExecuteThread.ja va:120)


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

    Nobody accesses your app overnight and you get this error? My
    guess is you have weblogic testing of the pool enabled and that
    is buggy in the version you are on.
    --
    Galen Boyer

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


    Thats correct, after a long period of no access, this happens from the Oracle XA
    connectio pool thats trying test the connections.

    Has anybody faced this situation before?

    I am on WLS 6.1 sp2. This happens on Windows as well as Linux.

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

    Do you have RefreshMinutes set on the connection pool it by default set to 5
    mins I think. if yes turn that off by setting it to 99999 and see if you
    still see this exception. testConnectionsOnreserve should be sufficent to
    ensure good connection.



    "Jeeva" wrote in message
    news:40ac0ca1@newsgroups.bea.com...
    >
    > Thats correct, after a long period of no access, this happens from the

    Oracle XA
    > connectio pool thats trying test the connections.
    >
    > Has anybody faced this situation before?
    >
    > I am on WLS 6.1 sp2. This happens on Windows as well as Linux.




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

    Hi. I think this is an oracle driver problem, in all likelihood.
    We test a connection with:

    Statement stmt = c.createStatement();
    stmt.execute(sql);

    Where the string sql is "select count(*) from DUAL" if you named your test table as 'DUAL'.

    We obtain no result set, so the driver *shouldn't* be accruing cursors...
    Can you upgrade to the latest oracle driver?
    Can you upgrade to the latest version of 6.1?

    Jeeva wrote:

    > I am on WLS 6.1 sp2.
    > When i leave the server up overnight unaccessed and come back and see in the morning,
    > I see the following stacktrace on the server console.
    > I dont think its causing any harm, but is there anyway to prevent this from occuring?
    >
    > java.sql.SQLException: ORA-01000: maximum open cursors exceeded
    >
    > at oracle.jdbc.dbaccess.DBError.throwSqlException(DBE rror.java:168)
    > at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:2 08)
    > at oracle.jdbc.ttc7.Oopen.receive(Oopen.java:118)
    > at oracle.jdbc.ttc7.TTC7Protocol.open(TTC7Protocol.ja va:466)
    > at oracle.jdbc.driver.OracleStatement.(OracleStatement.java:413)
    > at oracle.jdbc.driver.OracleStatement.(OracleStatement.java:432)
    > at oracle.jdbc.driver.OraclePreparedStatement.(OraclePreparedStatement.java:182)
    > at oracle.jdbc.driver.OracleCallableStatement.(OracleCallableStatement.java:102)
    > at oracle.jdbc.driver.OracleCallableStatement.(OracleCallableStatement.java:86)
    > at oracle.jdbc.driver.OracleConnection.privatePrepare Call(OracleConnection.java:736)
    > at oracle.jdbc.driver.OracleConnection.prepareCall(Or acleConnection.java:622)
    > at oracle.jdbc.xa.client.OracleXAResource.start(Oracl eXAResource.java:163)
    > at weblogic.jdbc.jta.VendorXAResource.start(VendorXAR esource.java:41)
    > at weblogic.transaction.internal.ServerResourceInfo.s tart(ServerResourceInfo.java:1032)
    > at weblogic.transaction.internal.ServerResourceInfo.x aStart(ServerResourceInfo.java:975)
    > at weblogic.transaction.internal.ServerResourceInfo.e nlist(ServerResourceInfo.java:234)
    > at weblogic.transaction.internal.ServerTransactionImp l.enlistResource(ServerTransactionImpl.java:374)
    > at weblogic.jdbc.common.internal.ConnectionEnv.test(C onnectionEnv.java:937)
    > at weblogic.common.internal.ResourceAllocator.reserve (ResourceAllocator.java:465)
    > at weblogic.common.internal.ResourceAllocator.reserve Unused(ResourceAllocator.java:376)
    > at weblogic.common.internal.ResourceAllocator.trigger (ResourceAllocator.java:1103)
    > at weblogic.time.common.internal.ScheduledTrigger.exe cuteLocally(ScheduledTrigger.java:238)
    > at weblogic.time.common.internal.ScheduledTrigger.exe cute(ScheduledTrigger.java:229)
    > at weblogic.time.server.ScheduledTrigger.execute(Sche duledTrigger.java:65)
    > at weblogic.kernel.ExecuteThread.execute(ExecuteThrea d.java:139)
    > at weblogic.kernel.ExecuteThread.run(ExecuteThread.ja va:120)
    >



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


    HI,

    I could try upgrading but I have few questions:

    Why would select count(*) from DUAL not fetch a resultset?

    How would this work most of the times but fails only when not accessed?

    Is there way to tell not to test connections? may be set testConnectionsOnReserver
    to false?
    Is there any negative to that?


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



    Jeeva wrote:

    > HI,
    >
    > I could try upgrading



    Good. IF you get the latest 6.1 I could also make a patch to maybe help this. First try the
    latest oracle driver. Get support to make sure the server uses the latest driver instead of
    the one that we ship with 61sp2.

    but I have few questions:
    >
    > Why would select count(*) from DUAL not fetch a resultset?


    We are talking about a jdbc call execute() vs. executeQuery().
    Until we obtain the result set, we shouldn't have to be responsible
    for it.

    > How would this work most of the times but fails only when not accessed?


    Dunno. It may be an oracle driver issue.

    > Is there way to tell not to test connections? may be set testConnectionsOnReserver
    > to false?


    yes.

    > Is there any negative to that?


    Yes. If the DBMS dies we won't know it. You'd have to reset the pool manually.

    >



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


    Thanks Joe.
    Coule you tell me Where I could get the latest driver from?



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



    Jeeva wrote:

    > Thanks Joe.
    > Coule you tell me Where I could get the latest driver from?
    >
    >


    www.oracle.com


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


    We are on 8.1.7
    and the drivers they have are only for JDK 1.2, but we are on JDK 1.3

    http://otn.oracle.com/software/tech/...s/jdbc817.html

    JDK 1.3 drivers are available only with Oracel 9.

    Joe Weinstein wrote:
    >
    >
    >Jeeva wrote:
    >
    >> Thanks Joe.
    >> Coule you tell me Where I could get the latest driver from?
    >>
    >>

    >
    >www.oracle.com
    >



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


    Happened to come across this @

    http://edocs.bea.com/wls/docs61/jta/thirdpartytx.html

    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."

    and the comments as:
    Weblogic Server provides an internal workaround for this issue.

    Anyone knows if this is fixed in WLS 6.1 sp2 or if a patch is available?



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


    Observing the the number of open cursor logs, we found that the number of open
    cursors keep increasing every 5 minutes by 1.

    None of our params is set to 5 min.

    The referesh minutes is 1 min.

    Anyidea whats causing this?




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



    Jeeva wrote:

    > Observing the the number of open cursor logs, we found that the number of open
    > cursors keep increasing every 5 minutes by 1.
    >
    > None of our params is set to 5 min.
    >
    > The referesh minutes is 1 min.
    >
    > Anyidea whats causing this?


    Hi. Can you query the DBMS tro see what query the cursor is about?
    Do a select * from v$open_cursor.
    thanks,
    Joe
    PS: what version of weblogic?


    >
    >
    >



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


    USER_PROCESS Recursive Calls Opened Cursors Current Cursors
    BLUE2(144,2599) 20 5 3
    BLUE2(149,6019) 171 11 3
    BLUE2(26,4388) 13 17 0
    BLUE2(80,2476) 4310 4564 0
    BLUE2(82,7122) 27 20 2
    BLUE2(91,2963) 4098 4353 0

    Where BLUE2 is the Schema name

    For most of the user processes, the current cursors get reset to zero, but for
    ONE/TWO Processes, for which the count keeps increasing by 1 every 5 minutes.

    Unfortunately I dont see way to find which SQL statement causes a open cursor.



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

    select count(*), sql_text from v$open_cursor where user_name='SCOTT' group by sql_text;

    Just set the user_name to whatever your server is connecting as.

    Dejan



    Jeeva wrote:

    >USER_PROCESS Recursive Calls Opened Cursors Current Cursors
    > BLUE2(144,2599) 20 5 3
    > BLUE2(149,6019) 171 11 3
    > BLUE2(26,4388) 13 17 0
    > BLUE2(80,2476) 4310 4564 0
    > BLUE2(82,7122) 27 20 2
    > BLUE2(91,2963) 4098 4353 0
    >
    >Where BLUE2 is the Schema name
    >
    >For most of the user processes, the current cursors get reset to zero, but for
    >ONE/TWO Processes, for which the count keeps increasing by 1 every 5 minutes.
    >
    >Unfortunately I dont see way to find which SQL statement causes a open cursor.
    >
    >
    >
    >


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

    Hi. The query I sent will show the cursor SQL. It probably has to be run as
    SYSTEM...
    Joe

    Jeeva wrote:

    > USER_PROCESS Recursive Calls Opened Cursors Current Cursors
    > BLUE2(144,2599) 20 5 3
    > BLUE2(149,6019) 171 11 3
    > BLUE2(26,4388) 13 17 0
    > BLUE2(80,2476) 4310 4564 0
    > BLUE2(82,7122) 27 20 2
    > BLUE2(91,2963) 4098 4353 0
    >
    > Where BLUE2 is the Schema name
    >
    > For most of the user processes, the current cursors get reset to zero, but for
    > ONE/TWO Processes, for which the count keeps increasing by 1 every 5 minutes.
    >
    > Unfortunately I dont see way to find which SQL statement causes a open cursor.
    >
    >



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


    Missing the SQL?
    I dont see it...
    Thanks.

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




    SQLs from v$OPEN_CURSOR:

    ALTER SESSION SET NLS_LANGUAGE = 'AMERICAN'
    26,4388
    BLUE2 select count(*)
    from dual
    26,4388
    BLUE2 SELECT VALUE
    FROM NLS_INSTANCE_PARAMETERS WHERE PARAMETER =:
    26,4388
    BLUE2 ALTER SESSION
    SET NLS_TERRITORY = 'AMERICA'

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


    WLS 6.1 SP2

    Name="TXadminfw" Targets="jeeva_adminServer"
    InitialCapacity="2" CapacityIncrement="2" MaxCapacity="25" RefreshMinutes="15"
    DriverName="oracle.jdbc.xa.client.OracleXADataSource" Password="xxx" URL="jdbcracle:thin:@xxx.xxx.com:1521:dev"
    Properties="user=blue2;password=xxx"
    TestConnectionsOnReserve="true"
    TestTableName="dual"/>

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

    What version of weblogic are you using?
    Please show me the pool definition from the config file.
    thanks
    Joe

    Jeeva wrote:
    > SQLs from v$OPEN_CURSOR:
    >
    > ALTER SESSION SET NLS_LANGUAGE = 'AMERICAN'
    > 26,4388
    > BLUE2 select count(*)
    > from dual
    > 26,4388
    > BLUE2 SELECT VALUE
    > FROM NLS_INSTANCE_PARAMETERS WHERE PARAMETER =:
    > 26,4388
    > BLUE2 ALTER SESSION
    > SET NLS_TERRITORY = 'AMERICA'



+ Reply to Thread
Page 1 of 2 1 2 LastLast