REPORT SNAPSHOT schema accumulating a lot of inactive sessions in backend DB

Products

• CA Identity Suite

Issue/Introduction

In the Production environment, there are a lot of inactive sessions in the backend Oracle DB 12c related to IDM Report snapshot. To avoid hitting the limit, the Database team has been manually monitoring and killing the inactive sessions. What is a fix or workaround for this issue?

 

Environment

CA IDM 14.2 

Oracle 12c

DB Schema: IM14_REPORTSNAPSHOT_OWN     

RHEL: 7.x

Resolution

Tune the MAX_IDLE_TIME database parameter.

This change KILLs off the inactive session after the period of time set to this database parameter and the session is disconnected therefore no longer counted towards the overall database session count.

Using this database parameter called MAX_IDLE_TIME specifies the maximum number of minutes that a session can be idle. After the INACTIVE session hits that timeframe set for this parameter, the session is KILLED and is released from the database, and no longer is counted in the overall session count in the database. 

NOTE: This database parameter applies to all database users, but it will only affect database users whose session is INACTIVE for this period.

Contact your Database Administrator to configure the MAX_IDLE_TIME parameter.

The following values have been implemented successfully but are only suggestions. 

DEV, VAL and INT to 60 MINUTES (1 HR)

PROD to 180 MINUTES (3 HRs)

Contact your DBA team for further tuning advice.