Search Server could not connect to Oracle database

Products

• CA Service Desk Manager
• CA Service Management - Service Desk Manager

Issue/Introduction

The following error appears in ca_es_cluster.log:

[ERROR][loader.source            ][main] Database connection could not be established: jdbc:oracle:thin:@//<hostname>:<port>/<SID> Listener refused the connection with the following error:
ORA-12516, TNS:listener could not find available handler with matching protocol stack
 
java.sql.SQLException: Listener refused the connection with the following error:
ORA-12516, TNS:listener could not find available handler with matching protocol stack
 
 at com.ca.casm.esearch.Runner.main(Runner.java:75) [search-17.3.0.626.jar:?]
Caused by: oracle.net.ns.NetException: Listener refused the connection with the following error:
ORA-12516, TNS:listener could not find available handler with matching protocol stack
 
 at oracle.net.ns.NSProtocol.connect(NSProtocol.java:399) ~[ojdbc6-11.2.0.4.jar:11.2.0.4.0]

Environment

Release : 17.3

Cause

The issue is caused by the number of sessions and processes allowed by the Oracle database

Resolution

Increase the number of sessions and processes allowed by the Oracle DB. Engage your dba to implement the following steps:

1. Log as sysdba (sqlplus / as sysdba)

2. Verify the current allowed sessions and parameters:

show parameter sessions;
show parameter processes;

3. Increase the number of sessions and processes by running:

alter system set processes=<new value> scope=spfile;
alter system set sessions=<new value> scope=spfile;

Example:

alter system set processes=300 scope=spfile;
alter system set sessions=300 scope=spfile;

4. Restart the database server for the changes to take effect

shutdown immediate 
startup

Additional Information

Changing the Default Value for the Maximum Processes Initialization Parameter

Oracle sessions parameter tips