Hi every one,

Please can You help me with this. I have a client who use oracle database, and this morning I get mail from their database admin that we have on Maximo database this error:

2024-03-18T07:30:22.005170+01:00Maximum Open Cursors Exceeded in Process 275487 .Maximum Open Cursors Exceeded in Process 275487 .Maximum Open Cursors Exceeded in Process 275487 .Maximum Open Cursors Exceeded in Process 275487 .Maximum Open Cursors Exceeded in Process 275487 .Maximum Open Cursors Exceeded in Process 275487 .Maximum Open Cursors Exceeded in Process 275487 .Maximum Open Cursors Exceeded in Process 275487 .Maximum Open Cursors Exceeded in Process 275487 .Maximum Open Cursors Exceeded in Process 275487 .Maximum Open Cursors Exceeded in Process 275487 .Maximum Open Cursors Exceeded in Process 275487 .Maximum Open Cursors Exceeded in Process 275487 .Maximum Open Cursors Exceeded in Process 275487 .Maximum Open Cursors Exceeded in Process 275487 .Maximum Open Cursors Exceeded in Process 275487 .Maximum Open Cursors Exceeded in Process 275487 .Maximum Open Cursors Exceeded in Process 275487 .Maximum Open Cursors Exceeded in Process 275487 .Maximum Open Cursors Exceeded in Process 275487

Did someone have something like this, where can I found what can cause this error?

As I can see on internet, it can be because of some report, code in sql queries or something like this....Where can I found what report or query is problem?

P.S.

Thank you.

------------------------------
Dario Stjepanović
------------------------------

Hi Dario,

So basically there is some code that is not closing its connections properly / at all.

You don't mention which version of Maximo / MAS you are using so that makes it harder to make specific recommendations.

I'll assume that you haven't got any 3rd party pieces of software misbehaving e.g. interfaces that are directly logging into the database.

On that basis there are three main areas that can cause these problems

• BIRT reports - prior to 7.6 there were about 25 out of the box BIRT reports that leaked connections because they failed to close secondary datasets - Any BIRT reports built on 7.5 reports should be checked to confirm that secondary datasets (e.g. in fetch methods) are closing the dataset
  • The 7.6 reports were fixed as part of the rollout process so they should be fine now
  • Cohesive offer a service where we can automatically check BIRT reports and warn of problems - if you think you/your client would be interested in this then reach out to me
• OOB Java classes - For the past few years I, and others, have been raising PMRs and getting fixes for DB connection leaks in OOB Java code - so ensure that the system is patched to the latest interim fix pack to receive a substantial number of fixes
• Custom code - Look for code that opens MBOSets from the MXServer but doesn't close them - I did a series of articles about DB connection leaks - see this link and go to the section labelled ""
  • Cohesive offer a service where we can automatically check automation scripts and warn of problems - if you think you/your client would be interested in this then reach out to me

How do you get more details about the leaks and the code that is submitting the queries?

You can query the DB to get the SQL for open cursors but that often doesn't help because it doesn't tell you anything about the Java/script code that submitted the SQL.

Go to the Logging application and reconfigure the dbconnection logger.

My article here explains how to do this and what to look for:

https://www.linkedin.com/pulse/db-connections-mbosets-understanding-which-piece-code-mark-robbins

I have published details of some of the key ways that code / scripts leak cursors here:

https://www.linkedin.com/pulse/maximo-support-advice-from-non-ibm-engineer-article-mark-robbins/ in the section labelled "Managing MboSets – MBOSet overview - (#mbosetMgtPractices)"

that should give you enough information to get started.

the sooner you change the dbconnection logger to INFO the sooner you will get information about the code that leaked the cursors ... 

If you aren't familiar with the SystemOut.log files then we offer an automated log analysis service which can process the logs and generate a nice report that lists the database connection leaks along with details of fixes for known issues

------------------------------
Mark Robbins
Support Lead/Technical Design Authority / IBM Champion 2017 - 2023
Cohesive (previously Vetasi Limited)
https://www.linkedin.com/pulse/maximo-support-advice-from-non-ibm-engineer-article-mark-robbins/
------------------------------

https://www.ibm.com/support/pages/troubleshooting-ora-01000-maximum-open-cursors-exceeded

------------------------------
Hubert Duda
IBM
Warsaw
------------------------------

Hi Dario,

SELECT  b.cursors_count, b.sid, b.sql_Id, c.sql_text FROM V$SQLTEXT a,(SELECT sid, sql_id, count (*) cursors_count FROM V$OPEN_CURSOR WHERE sid in (SELECT sid From V$OPEN_CURSOR  GROUP BY sid HAVING count (*) > 650)  GROUP BY sql_id, sid) b , V$SQL c WHERE a.sql_id = b.sql_id and a.sql_id = c.sql_id ORDER BY b.SID, b.cursors_count DESC, b.sql_id, a.piece;

You can use the above query and change the underlined value to 650 to your open cursors limit and identify the processes/queries that are taking a lot of open cursors for tuning/fixing them.

------------------------------
Surendar Balasundaram
------------------------------

Hi Dario,

One usual place where we can find out the reason for exceeded cursors are automation scripts.

The major culprit being the Mbosets opened using MXserver and not closing or cleaning it up properly or immediately.

If there are are integration autoscripts or UI based initialization scripts which are being called very frequently, those can pile up the cursors very fast.

If you are facing the same issue in your lower environments as well, it will be easier to debug the issue.

Try turning in the admin mode and then clear the seesion from background. Then see if the cursors are getting added up.

Then check if there are continuous api calls. You can try that by utilising a system property : mxe.int.trackrestrequests. This property is used to enable message tracking for REST API.

 We then can try out using another system property which will forcefully close the mbosets opened using MXServer and not properly closed. You can set mxe.script.autocleanup to 1 to handle those mbosets. You should use this property only in development environments as this can cause certain functionalities to function abnormally.

One another reason this could happen due to could be when Maximo leaves a connection open by using isEmpty() and more than 1 record is returned from the Mboset. When we call an isEmpty against an mboset, it just goes and fetch the first mbo in the mboset. Unless we traverse through the set or explicitly closes it, this can create a connection leak. This can be eliminated by using notExists() which runs an sql query instead of fetching the whole set to the memory.

------------------------------
Salah Hassan
Consultant
Infosys
9746885191
------------------------------