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

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.

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.

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)
>

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?

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.

>

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

Jeeva wrote:

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

www.oracle.com

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
>

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?

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?

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?


>
>
>

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.

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

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

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

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'

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

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'