Number of connection to the database

Products

• DX Unified Infrastructure Management (Nimsoft / UIM)

Issue/Introduction

This weekend we received an alert from the oracle database informing us that there was too much connection to the NIMPROD database. There's a limit of 300 connections. The DBA told us that they grow the limit to a higher number if needed.

There was near 300 connections and most of the connection were old and inactive. The connections were coming from the discovery server, the primary hub and both UMP servers. We restarted the robot of all these servers and the connection number went down.

We would like to know why there was so much inactive connection and why they were not kill automatically. When a connection is not use anymore, does the application kill the connection? There was connection as old as august.

Should we configure oracle to raise the limit of the connection? If yes, what should be the number?

 

Environment

Release : Any UIM

Component : UNIFIED INFRASTRUCTURE MGMT

Resolution

We generally recommend these settings for Oracle:

ALTER SYSTEM SET PROCESSES = 300 SCOPE=SPFILE;
ALTER SYSTEM SET SESSIONS = 335 SCOPE=SPFILE; -- 1.1 * PROCESSES +5
ALTER SYSTEM SET OPEN_CURSORS = 500 SCOPE=BOTH;



Normally, 300 might be ok. Looking at the dates, I see some of the sessions were old.

Are you using Oracle 11g?

For Oracle 11g servers, we recommended that you define a SQLNET.EXPIRE_TIME in $ORACLE_HOME/network/admin/sqlnet.ora (defined in minutes). A common configuration is: SQLNET.EXPIRE_TIME=10. This ensures that connections are not left open indefinitely due to an abnormal client termination. For more information, see Oracle documentation at: SQLNET.EXPIRE_TIME for Oracle 11g.