Scheduler or Application Server is stuck in Oracle database re connection

Document ID : KB000110166
Last Modified Date : 09/08/2018
Show Technical Document Details
Issue:
Something happened in the network, then the scheduler failed to reconnect to the Oracle database and is no longer processing events

Then event_demon log file shows:
07/21/2018 15:31:30] CAUAJM_E_18412 The database client has been interrupted while query execution is in progress. 
[07/21/2018 15:31:30] CAUAJM_E_18400 An error has occurred while interfacing with ORACLE. 
[07/21/2018 15:31:30] CAUAJM_W_10900 The database monitoring system has detected a potential problem with the database. 
[07/21/2018 15:31:30] CAUAJM_E_18401 Function <doExecute> invoked from <nextRow> failed <951> 
[07/21/2018 15:31:30] CAUAJM_I_10901 The database monitoring system is beginning validation of database connections. 
[07/21/2018 15:32:00] ---------------------------------------- 
[07/21/2018 15:33:00] ---------------------------------------- 
[07/21/2018 15:33:00] CAUAJM_E_18416 Event Server: <XE> Failed Query: <update ujo_alamode set int_val = (select int_val from ujo_alamode where type='DB') where type = 'DB'> 
[07/21/2018 15:33:00] CAUAJM_E_18412 The database client has been interrupted while query execution is in progress. 
[07/21/2018 15:33:00] CAUAJM_E_18400 An error has occurred while interfacing with ORACLE. 
[07/21/2018 15:33:00] CAUAJM_E_18401 Function <doExecute> invoked from <execute> failed <864> 
[07/21/2018 15:33:00] CAUAJM_W_10631 Error with database <XE>. Checking connection. 
[07/21/2018 15:33:00] CAUAJM_W_10632 Attempting to reconnect to database <XE>. Attempt number [1]. 
[07/21/2018 15:34:00] ---------------------------------------- 
[07/21/2018 15:35:00] ---------------------------------------- 
[07/21/2018 15:36:00] ---------------------------------------- 
[07/21/2018 15:37:00] ---------------------------------------- 
[07/21/2018 15:38:00] ---------------------------------------- 

A normal shutdown with "unisrvcntr stop waae_sched.<instance>" is unable to kill the process with the default signal 15

Only way to recover is to kill the event_demon process with signal 9 and restart the scheduler

Same behavior can happen with the Application Server
 
Environment:
CA WAAE 11.3.x with Oracle database (Any Release)  on Unix/Linux

It might happen with the scheduler and application server 

 
Cause:

The root cause appears to be a hang occurring within the Oracle library client.
The database monitoring system was in the process of executing Oracle client library calls to disconnect and reconnect to the database server.
The Oracle client library is known to steal control from the calling process and hang for a long time. 

Oracle has a known issue in this area: Bug 19591551 Unable to timeout or break OCI calls if client disconnects 
 
Resolution:

There is a workaround to the known Oracle bug that might help: 
Please source the $AUTOUSER/autosys.sh.<hostname> script at a UNIX shell prompt.
Then locate the $ORACLE_HOME/network/admin/sqlnet.ora file on all machines that have a scheduler and application server installation and add the following variables: 

SQLNET.RECV_TIMEOUT=120
SQLNET.OUTBOUND_CONNECT_TIMEOUT=120
SQLNET.INBOUND_CONNECT_TIMEOUT=120 

If a faster recovery is required, you can decrease these values from 120 seconds to 10 seconds

Then restart the schedulers and application servers so that the Oracle client picks up the variables.
The correct value for ORACLE_HOME that is used by the schedulers and application servers should be set on the console after sourcing the $AUTOUSER/autosys.sh.<hostname> script.