Oracle agent produces high CPU usage on the monitored database server and hundreds of sessions and connections are open (4295218)

There are a lot of open Oracle sessions and connections created by Foglight which uses a lot of CPU on the monitored host. 

Cause 1

Whenever the Oracle agent receives a socket timeout, the connection is disconnected automatically by the driver. It seems that the sessions remain open on the host instance.

Cause 2

If a firewall kills/interrupts a database session, the database session itself does not realize that the session has gone and the database agent receives a socket timeout. The database keeps the session open. Foglight will create a new session.

As a result the database runs out of session/processes.

Cause 3

Undetermined

Cause 4

Unidentified

Resolution 1

Change the new ASP setting "Kill unexpected disconnected sessions".

1. Navigate to Administration | Agents | Agent Status
2. Select the Oracle database agent
3. Edit the properties for the Oracle agent
4. Click the link to modify the private properties for the agent
5. Change the Agent Status Property value for Kill Unexpected Disconnected Sessions to True
6. Save the changes
7. Deactivate and activate the Oracle agent

If the following message appears, the Foglight user does not have permissions to kill its sessions

Failed to kill session with sid [3222] and serial number [9358]. error: [ORA-01031: insufficient privileges

Resolution 2

Please set the following OS parameters to avoid the situation:

1. Set the Oracle parameter SQLNET.EXPIRE_TIME to 5. That means, the database will send a data package every 5 minutes, to check, if the session is still alive. The firewall realize the activity and will not kill the session. If the session has been killed, the database realize it and will kill the database session too.
2. Set the TCPIP parameter KeepAliveTime to 300000. The operating system will discover killed session after 5 minutes and will clean them up. 

Resolution 3

1. Create a new user on on the host Operating System (it is suggested change to a different user from 'oracle' on ALL monitored hosts)
2. Add the max process number new OS user (ulimit -u 60)
3. Change "KillUserProcesses=yes" only for the new user.
4. In Foglight, delete the 'oracle' credential on the FMS (Administration | credentials | Manage Credentials | choose credentials | Delete).
5. Test the connection for the database agents (Databases Dashboard | choose an agent | settings | Administration | Connection Details | Set credentials | Test connection | Save changes)

Resolution 4

In one circumstance, a root cause for this issue was not identified by Development