IDM Servers losing connection to oracle db

Products

• CA Identity Manager

Issue/Introduction

We are experiencing an issue where the sessions that were created to lookup an Oracle table via JDBC connection are not released.

Oracle table is set up as a custom datasource in the standalone-full-ha.xml file.  In IDM, on the task to create or modify users, there is a lookup for a particular number and returns a name.  After this lookup occurs, the IDM application does not release the sessions.

The Oracle database itself gets maxed out at 2000 sessions.  The maximum setting is being overrun and causes the production database to restart.

How can we get the sessions to release appropriately?

 

Resolution

Here are some suggestions to help manage the DB connection.

1. Set the max connections for the datasource pool lower than 2000. Work with your DBA to determine how much activity occurs and how many connections are required. You may find it can be set much lower than 2000.

2. Periodically restart the listener on the database to clear stale connections. This will prevent the need for the DB to be restarted. 

3. If the above items do not resolve the problem try adding these validation elements to the oristore datasource. to clear stale connections.

The validation elements should be like this:

 

 <check-valid-connection-sql>select 1 from dual</check-valid-connection-sql>
 <validate-on-match>true</validate-on-match>
<background-validation>false</background-validation>

 

When the <validate-on-match> option is set to true, the database connection is validated every time it is checked out from the connection pool . You will not need background-validation when validate-on-match is true so please set that to false.

For vAPP, you can run the following via the JBoss/Wildfly CLI:

/subsystem=datasources/data-source=iam_im-oristore-ds/:write-attribute(name=background-validation,value=false)

/subsystem=datasources/data-source=iam_im-oristore-ds/:write-attribute(name=validate-on-match,value=true)