No more connection can be made to the oracle GTREP repository database

Products

• CA Test Data Manager (Data Finder / Grid Tools)

Issue/Introduction

We had some issues with tools trying to connect to our Oracle GTREP repository in DEV environment.
We realized the cause was too much sessions opened on that repository server. The only way to release those sessions was to restart the TDM Portal service on one of our application server. Once done, the repository database was accepting connections again.
This is what we use as a query to pull of opened sessions on the database.

(see screenshot)

Although, the whole query I like to use is this one:

select username,service_name,program,module,machine,count(sid) 
from v$session  
where type='USER'
group by  username,service_name,program,module,machine
order by machine;

Why are there so much sessions opened espacially for user SCRAMBLE?

It seems also that these sessions don't get released for 3 days and that is not normal I think.

We talked to a DBA about this and he told us that the maximum processes(sessions) that are set for our database is 2000. And sometimes processes(sessions) go over 1500 and this is where we start having connection issues.

The query for process is :  select count(*) from v$process

We don't have this issue in PROD environment because the are not that much people using the tools and we have doubled the process limit for our repository database.

Environment

Release : 4.9.1

Component : Repository (GTREP)

Resolution

My understanding is that you started getting these higher numbers when you started testing with your K8s environment. 

I am afraid the numbers will keep growing as you add more resources that interact with the scramble database.

 

You will have to either dedicate another oracle instance for the scramble database or make sure that the current instance has more sessions

You should also consider reducing the number of idle connections by configuring the portal's application.properties. However this could only be a temporary solution as adding more resources will bring you back to the same upper session limit.

 

Note that you should not confuse idle connection to inactive database sessions. 

There is a one to many relationship between a connection and sessions. The oracle database can decide to create another session when a command is sent down the connection pipe.