maximum open cursors exceeded from Oracle XA Connection pool
Jeeva
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(DBError.java:168)
at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:208)
at oracle.jdbc.ttc7.Oopen.receive(Oopen.java:118)
at oracle.jdbc.ttc7.TTC7Protocol.open(TTC7Protocol.java:466)
at oracle.jdbc.driver.OracleStatement.<init>(OracleStatement.java:413)
at oracle.jdbc.driver.OracleStatement.<init>(OracleStatement.java:432)
at oracle.jdbc.driver.OraclePreparedStatement.<init>(OraclePreparedStatement.java:182)
at oracle.jdbc.driver.OracleCallableStatement.<init>(OracleCallableStatement.java:102)
at oracle.jdbc.driver.OracleCallableStatement.<init>(OracleCallableStatement.java:86)
at oracle.jdbc.driver.OracleConnection.privatePrepareCall(OracleConnection.java:736)
at oracle.jdbc.driver.OracleConnection.prepareCall(OracleConnection.java:622)
at oracle.jdbc.xa.client.OracleXAResource.start(OracleXAResource.java:163)
at weblogic.jdbc.jta.VendorXAResource.start(VendorXAResource.java:41)
at weblogic.transaction.internal.ServerResourceInfo.start(ServerResourceInfo.java:1032)
at weblogic.transaction.internal.ServerResourceInfo.xaStart(ServerResourceInfo.java:975)
at weblogic.transaction.internal.ServerResourceInfo.enlist(ServerResourceInfo.java:234)
at weblogic.transaction.internal.ServerTransactionImpl.enlistResource(ServerTransactionImpl.java:374)
at weblogic.jdbc.common.internal.ConnectionEnv.test(ConnectionEnv.java:937)
at weblogic.common.internal.ResourceAllocator.reserve(ResourceAllocator.java:465)
at weblogic.common.internal.ResourceAllocator.reserveUnused(ResourceAllocator.java:376)
at weblogic.common.internal.ResourceAllocator.trigger(ResourceAllocator.java:1103)
at weblogic.time.common.internal.ScheduledTrigger.executeLocally(ScheduledTrigger.java:238)
at weblogic.time.common.internal.ScheduledTrigger.execute(ScheduledTrigger.java:229)
at weblogic.time.server.ScheduledTrigger.execute(ScheduledTrigger.java:65)
at weblogic.kernel.ExecuteThread.execute(ExecuteThread.java:139)
at weblogic.kernel.ExecuteThread.run(ExecuteThread.java:120)
Galen Boyer
guess is you have weblogic testing of the pool enabled and that
is buggy in the version you are on.
--
Galen Boyer
Jeeva
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.
sree bodapati
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" <je...@mediaocean.com> wrote in message
news:40ac...@newsgroups.bea.com...
Joe Weinstein
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
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?
Joe Weinstein
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.
>
Jeeva
Thanks Joe.
Coule you tell me Where I could get the latest driver from?
Joe Weinstein
Jeeva wrote:
> Thanks Joe.
> Coule you tell me Where I could get the latest driver from?
>
>
Jeeva
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/java/sqlj_jdbc/htdocs/jdbc817.html
JDK 1.3 drivers are available only with Oracel 9.
Jeeva
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?
Jeeva
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?
Joe Weinstein
Jeeva wrote:
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?
>
>
>
Jeeva
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.
Deyan D. Bektchiev
Just set the user_name to whatever your server is connecting as.
Dejan
Joe Weinstein
SYSTEM...
Joe
Jeeva
Missing the SQL?
I dont see it...
Thanks.
Jeeva
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'
Joe Weinstein
Please show me the pool definition from the config file.
thanks
Joe
Jeeva
WLS 6.1 SP2
<JDBCConnectionPool
Name="TXadminfw" Targets="jeeva_adminServer"
InitialCapacity="2" CapacityIncrement="2" MaxCapacity="25" RefreshMinutes="15"
DriverName="oracle.jdbc.xa.client.OracleXADataSource" Password="xxx" URL="jdbc:oracle:thin:@xxx.xxx.com:1521:dev"
Properties="user=blue2;password=xxx"
TestConnectionsOnReserve="true"
TestTableName="dual"/>
Jeeva
Hi,
May I know what the patched in the atatchement?
Thanks,
jeeva
Joe Weinstein
Jeeva wrote:
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
Jeeva
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?
Joe Weinstein
Jeeva wrote:
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
Jeeva
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?
Jeeva
Hi,
There seems to be patch available for WLS 7.1
Is there a similar patch for 6.1?
Joe Weinstein
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?namespace=askbea&origin=ask_bea.jsp&event=button.search_ask_bea&askbea_display=relevancy&askbea_max_number_returned=50&question=cursor+70sp1testcon_w_rs.jar&all_bea_products=all_bea_products&ES=ES