Search Deamon es_ebl occupies all Oracle db sessions

Products

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

Issue/Introduction

Elastic Search consumes all database sessions

After installing Option "xflow_installed" and restarting all servers, we encountered the following:
The Search Deamon (es_ebl) consumes a lot of db connections , obviously without limitation
Our current limitation of concurrent sessions in oracle is set to 500.
this is hit by our servers because of the huge amount of connections per server(> 150 each)

sqlagt:select55         5944 ERROR        orclclass.c           1313 SQL Statement Failed. ORA: 3114  Message: ORA-03114: not connected to ORACLE 

sqlagt:select55         5944 ERROR        orclclass.c           1314 SELECT count(*) FROM crwf WHERE ( ( ( crwf.group_id IN (SELECT grpmem.group_id FROM grpmem WHERE grpmem.member = HEXTORAW('F1810FA24080B645B12E46171A84AB7D')) ) AND crwf.assignee IS NULL AND crwf.status = N'PEND' AND crwf.last_mod_dt > 1615203946 AND crwf.last_mod_by <> HEXTORAW('F1810FA24080B645B12E46171A84AB7D') ) ) AND ( ( crwf.group_id IN (SELECT grpmem.group_id FROM grpmem WHERE grpmem.member = HEXTORAW('F1810FA24080B645B12E46171A84AB7D')) ) OR crwf.assignee = HEXTORAW('F1810FA24080B645B12E46171A84AB7D') OR crwf.task != N'ZSECINFO' )

sqlagt:select55         5944 ERROR        orclclass.c           1220 Execution failed.  Not connected to ORACLE

sqlagt:select55         5944 ERROR        orcl.c                1747 Oracle connection lost detected (rc=3114) and is being handled.

**************

 

sqlagt:Act_Log          3800 ERROR        orclclass.c           1313 SQL Statement Failed. ORA: 3113  Message: ORA-03113: end-of-file on communication channel Process ID: 49016 Session ID: 411 Serial number: 7499 
sqlagt:Act_Log          3800 ERROR        orclclass.c           1314 Clause (INSERT INTO act_log ( action_desc, analyst, call_req_id, description, internal, knowledge_session, knowledge_tool, last_mod_dt, persid, rel_ticket_type, system_time, time_spent, time_stamp, type, z_srl_logIntegracao, z_srl_tipo_solucao, id ) VALUES ( :val1 , :val2 , :val3 , :val4 , :val5 , :val6 , :val7 , :val8 , :val9 , :val10 , :val11 , :val12 , :val13 , :val14 , :val15 , :val16 , :val17 )) Input (<action_desc:string>Status alterado de 'Encaminhado' para 'Atribuído'.|<analyst:uuid>E883BBBB8A549A4CB73EF2F08C25F739|<call_req_id:string>cr:445327|<description:string>Status alterado de 'Encaminhado' para 'Atribuído'.|<internal:int>1|<knowledge_session:string>(NULL)|<knowledge_tool:string>PDM|<last_mod_dt:time>03/08/2021 08:55:04|<persid:string>alg:1920674|<rel_ticket_type:string>(NULL)|<system_time:time>03/08/2021 08:55:04|<time_spent:duration>4|<time_stamp:time>03/08/2021 08:55:04|<type:string>ST|<z_srl_logIntegracao:int>0|<z_srl_tipo_solucao:string>(NULL)|<id:int>1920674)
sqlagt:Act_Log          3800 ERROR        orclclass.c           1220 Execution failed. Reason: end-of-file on communication channel
sqlagt:Act_Log          3800 ERROR        orcl.c                 799 Oracle connection lost detected (rc=3113) and is being handled.
bpvirtdb_srvr           6480 ERROR        vdbagent.c             897 Detected database connection problem during INSERT. Requeuing request for agent Act_Log.

 

Environment

Service Desk 17.3

Resolution

execute at the command line:

pdm_options_mgr -s ES_DB_SUPPORT -v off -a pdm_option.inst
pdm_options_mgr -s ES_DB_SUPPORT -v off -a pdm_option.inst -t

These commands create @NX_ES_DB_SUPPORT=off in NX.env and template.

With the variable set as per above, data is directly sent to elastic search instead of dumping it in the es_events table in the mdb database and then moving this data from DB to elastic search.