As our production environment workload is steadily increasing we've been noticing that our content store connections seem to grow over time until we get this error and have to recycle. I think in the past they were growing and our monthly server patching simply forced us to restart often enough but lately we are finding that we need to restart at least once a week to clear out the connections. I've been monitoring the connections and they seem to grow in blocks at non-peak periods like 9pm or 8am. They seem to coincide with an idle period followed by a number of subscriptions being run but not any one particular one. I do have a case open with IBM but as of yet have not been able to get to the bottom of this issue. We are on 11.1.7 FP2. We've considered setting the advanced setting to control the connection pool but haven't yet. Anyone else have a similar experience with Oracle 19c as the content store?

------------------------------
Robert Hofstetter
------------------------------

#CognosAnalyticswithWatson

Hi Robert,

There are a number of advanced settings in Cognos Analytics for the Content Store service, the link to them is below. A number of them set the maximum number of connections and more importantly the timeout. You need to ensure that if they are unused for a period of time that they are recycled to preserve resources.

Content Manager service advanced settings - IBM Documentation

I have seen this kind of behaviour before when using Oracle as a Content Store. You need to make sure you have enough connections specified in Oracle. I am not sure how to do that as I had a DBA to do it for me but I set the limit in Cognos to the same value as specified in Oracle and I tightened up the timeouts etc.

Hope that helps.

Andy

------------------------------
Andrew Copeland
------------------------------

Original Message:
Sent: Mon June 14, 2021 03:30 PM
From: Robert Hofstetter
Subject: ORA-00020 maximum number of processes (400) exceeded

As our production environment workload is steadily increasing we've been noticing that our content store connections seem to grow over time until we get this error and have to recycle. I think in the past they were growing and our monthly server patching simply forced us to restart often enough but lately we are finding that we need to restart at least once a week to clear out the connections. I've been monitoring the connections and they seem to grow in blocks at non-peak periods like 9pm or 8am. They seem to coincide with an idle period followed by a number of subscriptions being run but not any one particular one. I do have a case open with IBM but as of yet have not been able to get to the bottom of this issue. We are on 11.1.7 FP2. We've considered setting the advanced setting to control the connection pool but haven't yet. Anyone else have a similar experience with Oracle 19c as the content store?

------------------------------
Robert Hofstetter
------------------------------

#CognosAnalyticswithWatson

Hi Again Robert,

Further to my earlier email, it also sounds like the Oracle instance is under some period strain and you might want your DBA to look at increasing the resources. When I lookup that specific ORA error it seems to relate to high load on the system and some configuration work / killing dead processes is needed.

ORA-00020: maximum number of processes exceeded | Mohsin's DBA Blog (wordpress.com)

It might also be worth seeing if you can log into Oracle when you get this message via SQLPlus. I suspect not. Details of how to bulldoze your way in are below.

Unable to connect with sys, ORA-00020: maximum number of processes (n) exceeded | deveshdba (wordpress.com)

Andy

------------------------------
Andrew Copeland
------------------------------

Original Message:
Sent: Mon June 14, 2021 03:30 PM
From: Robert Hofstetter
Subject: ORA-00020 maximum number of processes (400) exceeded

As our production environment workload is steadily increasing we've been noticing that our content store connections seem to grow over time until we get this error and have to recycle. I think in the past they were growing and our monthly server patching simply forced us to restart often enough but lately we are finding that we need to restart at least once a week to clear out the connections. I've been monitoring the connections and they seem to grow in blocks at non-peak periods like 9pm or 8am. They seem to coincide with an idle period followed by a number of subscriptions being run but not any one particular one. I do have a case open with IBM but as of yet have not been able to get to the bottom of this issue. We are on 11.1.7 FP2. We've considered setting the advanced setting to control the connection pool but haven't yet. Anyone else have a similar experience with Oracle 19c as the content store?

------------------------------
Robert Hofstetter
------------------------------

#CognosAnalyticswithWatson

Hi Robert,

We have had an issue with idle connections which were not removed in version 11.1.2. After the upgrade to version 11.2.0 the idle connections are removed.
There were both CQM and DQM idle connections. So we created a file configuration/CQEConfig.xml for the CQM connections to be removed. We also changed the configuration/xqe.providers.xml. We added connectionExpirationThreshold="300000" for the JDBC instance name. That that did not have any effect. But in version 11.2.0 it worked.
It is a different issue, but I hope it helps you to solve your problem.

------------------------------
Thomas van der Meer
------------------------------

Original Message:
Sent: Mon June 14, 2021 03:30 PM
From: Robert Hofstetter
Subject: ORA-00020 maximum number of processes (400) exceeded

As our production environment workload is steadily increasing we've been noticing that our content store connections seem to grow over time until we get this error and have to recycle. I think in the past they were growing and our monthly server patching simply forced us to restart often enough but lately we are finding that we need to restart at least once a week to clear out the connections. I've been monitoring the connections and they seem to grow in blocks at non-peak periods like 9pm or 8am. They seem to coincide with an idle period followed by a number of subscriptions being run but not any one particular one. I do have a case open with IBM but as of yet have not been able to get to the bottom of this issue. We are on 11.1.7 FP2. We've considered setting the advanced setting to control the connection pool but haven't yet. Anyone else have a similar experience with Oracle 19c as the content store?

------------------------------
Robert Hofstetter
------------------------------

#CognosAnalyticswithWatson