ORA-01000: maximum open cursors exceeded

, ,
1

Dear All,

We are having Oracle errors on the maximum number of cursors reached on our production environment.

Our setup is the following :

webMethods Integration Server 7.1.2 working in cluster (2 nodes)
DB : Oracle 11g R2

JDBC Adapter settings :
euTSS.connections:stagingDB Details
Connection Type JDBC Adapter Connection
Package Name euTSS
Connection Properties
Transaction Type NO_TRANSACTION
DataSource Class oracle.jdbc.pool.OracleDataSource
serverName
user TSS
password ******
databaseName
portNumber
networkProtocol
Other Properties url=jdbc:oracle:oci:@DCGTSS
Connection Management Properties
Enable Connection Pooling true
Minimum Pool Size 0
Maximum Pool Size 50
Pool Increment Size 1
Block Timeout (msec) 1000000
Expire Timeout (msec) 1000
Startup Retry Count 3
Startup Backoff Timeout (sec) 10

The strange thing is that on our Test environment which has somewhat less volume, this does not happen (and same settings)

I have double checked the code (java) that all statements, resultsets are closed properly and all is according to Oracle Documentation…

I have searched this forum and found some other threads on this but for older versions of WM…

Once this happens, the only way to get rid of the situation is to disable and re enable the JDBC Adapter in WM IS Administration.

Could anyone help? Any idea on where could be the issue?

Thanks and regards,

Jeremy

2

If you receive messages that you are running out of cursors… means You tried to open too many cursors at once…

so you can try to Explicitly Close your statements or
request ur DBA to Increase Open Cursors Parameter in their config File or initialization file ??
but i guess that requires a Database downtime

3

Thanks for the reply.

Unfortunately not really what I was expecting.

The maximum opened cursors have already been increased and the code is correctly explicitly closing the statements (it’s written in my post BTW)…

There is definitely either a bug in Oracle 11G R2 or a bug in webMethods !

We are on this issue already 2 months without solution…

Thanks anyway.

Jeremy

4

Why are you using Java to do these? If you use adapter services it will manage these things for you.

Although you mention that the Java code closes things I suspect there is a scenario that is leaking cursors and connections.

5

Thanks Reamon this is very valid point.
I have already infact proposed this to the developer but the code is quite complex and would take ages to redesign. But at the end if no solution will be found we will have to consider this option.

But I am still suspecting not a leak in the code but some Oracle or JDBC WM issue.

How about the fact that on TEST environment the situation never happens and same data is processed… Only difference is the load…

Thanks again for your valuable replies…

Jeremy

6

Are you using the JDBC adapter connection pools?

Other than that, there would be nothing wM IS is doing–the Java code will be interacting with the JDBC driver which in turn interacts with the DB client libraries.

7

Maybe this is other case, but we have ran into same problem when using auditing. When auditing is turned off, we have no problem processing tons of scenarios, but once turned on, we get this error quite often. After tracing it on DB level, we have found, that it is caused by following insert:

INSERT INTO WMSERVICECUSTOMFLDS (ROOTCONTEXTID, PARENTCONTEXTID, CONTEXTID, MSGID, SERVERID, AUDITTIMESTAMP, INSERTTIMESTAMP, FIELDNAME, FIELDALIAS, FIELDINDEX, STRINGVALUE, NUMBERVALUE, DATEVALUE, FIELDTYPE, INPUTFIELD) VALUES ( :v0 , :v1 , :v2 , :v3 , :v4 , :v5 , SYS_EXTRACT_UTC(CURRENT_TIMESTAMP), :v6 , :v7 , :v8 , :v9 , :v10 , :v11 , :v12 , :v13 )

it is most likely due to number of custom fields we are auditing + some bug in the component which logs entried into DB which causes that “old” open cursosr don’t release…


kekert