SQL failures in AutoSys Application Server causes request discard and denial of service to clients

Products

• Autosys Workload Automation

Issue/Introduction

Every 2 minutes we see the following failed query

 CAUAJM_E_18416 Event Server: <AUTOSYS_PR1>  Failed Query: <select j.joid, j.job_ver, jr.esp_lstatus, jr.esp_status, j.as_applic, j.has_condition, round((j.create_stamp-to_date(:I_S_1,:I_S_2))*:I_I_3+(select int_val from ujo_alamode where type=:I_S_4)), j.description, s.last_end, j.as_group, j.box_joid, j.mach_name, round((j.update_stamp-to_date(:I_S_5,:I_S_6))*:I_I_7+(select int_val from ujo_alamode where type=:I_S_8)), j.job_name, s.next_start, s.ntry, j.owner, s.run_num, s.last_start, s.status, s.run_machine, i.timezone, j.job_type, s.status_time, t.lineage, s.exit_code, j.has_resource, s.next_start_stamp from ujo_job j join ujo_sched_info i on i.joid = j.joid and i.job_ver = j.job_ver and i.over_num = j.over_num join ujo_job_status s on s.joid = j.joid and s.job_ver = j.job_ver join ujo_job_tree t on t.joid = j.joid left join ujo_job_runs jr on jr.run_num = s.run_num and jr.joid = s.joid and jr.ntry = s.ntry and jr.run_machine != :I_S_9 where j.joid>:I_I_10 and j.is_active=:I_I_11 and j.is_currver=:I_I_12 and j.joid in :I_S_9 where j.joid>:I_I_10 and j.is_active=:I_I_11 and j.is_currver=:I_I_12 and j.joid in (:I_I_13, ... :I_I_1012) ...

Subsequently application server  loses DB connection 

[05/12/2024 16:17:00]      CAUAJM_E_18412 The database client has been interrupted while query execution is in progress.
[05/12/2024 16:17:00]      CAUAJM_E_18400 An error has occurred while interfacing with ORACLE.
[05/12/2024 16:17:00]      CAUAJM_W_10900 The database monitoring system has detected a potential problem with the database.

Other symptoms in Oracle Server:

You may see below messages in Oracle Alert.log when the Oracle Server is configured to enable traces.

ORA-12161: TNS:internal error: partial data received
ORA-12599: TNS:cryptographic checksum mismatch

Environment

Autosys  12.0 01.04

as_server -x
12.0.01.04-0101

Cause

 Network activity between the Oracle client and Oracle server is too high due to default data transfer size.  You will notice high values for below variables in AWR reports:

Top 10 Foreground Events by Total Wait Time

Resolution

Added the following in sqlnet.ora in the TNS_ADMIN in the server running Autosys application server

DEFAULT_SDU_SIZE=32767
SEND_BUF_SIZE=65536

Restarted the application server

This resolved the sql query errors.

Additional Information

More information on how to enable SQLNet trace to get the ORA errors in the alert.log in Oracle DB side

 https://support.oracle.com/epmos/faces/DocumentDisplay?parent=DOCUMENT&sourceId=2199844.1&id=395525.1

Setup  the following in the sqlnet.ora on the DB server

 #Net encryption

SQLNET.ENCRYPTION_SERVER            = REQUIRED
SQLNET.ENCRYPTION_TYPES_SERVER      = (AES256, AES192, AES128)
SQLNET.CRYPTO_CHECKSUM_SERVER       = REQUIRED
SQLNET.CRYPTO_CHECKSUM_TYPES_SERVER = (SHA512, SHA384, SHA256, SHA1)