ORA-12609: TNS: Receive timeout during COLD start after upgrade

Products

• CA Automic Workload Automation - Automation Engine
• CA Automic One Automation

Issue/Introduction

When performing a version upgrade (e.g., from v21 to v24) and initiating the mandatory COLD start, the Primary Work Process (PWP) fails to start. The WP logs show the following error sequence:

U00029108 UCUDB: SQL_ERROR Database handles DB-HENV: ... DB-HDBC: ...U00003591 UCUDB - DB error info: OPC: 'OCIStmtExecute' Return code: 'ERROR'U00003592 UCUDB - Status: '' Native error: '12609' Msg: 'ORA-12609: TNS: Receive timeout occurred'U00003594 UCUDB Ret: '3590' opcode: 'EXEC' SQL Stmnt: 'delete from bt where bt_bh_idnr not in (select EH_BH_Idnr from eh )'

Note: Other errors may include ORA-00942: table or view does not exist errors for tables like MQ1CP012 immediately preceding this. Those are often normal at startup as the engine checks for the existence of CP tables; the ORA-12609 is the actual blocker.

Environment

This can happen on any version of Automation Engine

Cause

This issue is caused by an aggressive SQLNET.RECV_TIMEOUT setting in the sqlnet.ora file (often set to 10 or 15 seconds).

During a COLD start, the PWP performs a massive system cleanup by issuing bulk DELETE and TRUNCATE statements on heavy staging tables such as BT and BH. If these tables contain millions of rows, the database operations will naturally take longer than the configured timeout. The Oracle client then forcefully severs the connection, leading to the ORA-12609 error.

While these aggressive timeouts are sometimes implemented to facilitate rapid failover in High Availability (HA) environments like Oracle Data Guard, they act as a "double-edged sword" during heavy maintenance tasks.

Resolution

To allow the COLD start to complete, the database must have enough time to finish bulk operations which may take minutes in some cases and is normal.

Workaround (For immediate resolution during upgrade)

1. Locate the sqlnet.ora file (typically in $ORACLE_HOME/network/admin).
2. Temporarily comment out or significantly increase the SQLNET.RECV_TIMEOUT setting.
3. Restart the PWP to complete the COLD start.

Long-term Recommendation

Instead of a hard RECV_TIMEOUT which kills active queries, consider using with Dead Connection Detection (DCD) settings like SQLNET.EXPIRE_TIME on the database or TCP Keepalives with ENABLE=BROKEN in tnsnames.ora.  Consult a Database Administrator for the best solution for the situation.