WP reconnection to Oracle ends with 'Access Denied'

Products

CA Automic Workload Automation - Automation Engine

Issue/Introduction

In AE 12.3.9, or AE 21.0.x when a WP database session is killed in Oracle, this WP is unable to open again a new connection to the database with the errors below:

20220722/125922.735 - U00003594 UCUDB Ret: '3590' opcode: 'SLCU' SQL Stmnt: 'SELECT * FROM MQ1WP WHERE MQWP_SchedTime &lt;= CAST(SYS_EXTRACT_UTC(SYSTIMESTAMP(0)) AS DATE) ORDER BY MQWP_Priority, MQWP_SchedTime, MQWP_PK FOR UPDATE SKIP LOCKED'
20220722/125922.735 - U00003590 UCUDB - DB error: 'OCIStmtExecute', 'ERROR  ', '', 'ORA-00028: your session has been killed'
20220722/125922.863 - U00029108 UCUDB: SQL_ERROR  Database handles DB-HENV: 1164ad790 DB-HDBC: 116507ec0
20220722/125922.863 - U00003591 UCUDB - DB error info: OPC: 'OCIStmtExecute' Return code: 'ERROR'
20220722/125922.863 - U00003592 UCUDB - Status: '' Native error: '1012' Msg: 'ORA-01012: not logged on
Process ID: 20513586
Session ID: 1378 Serial number: 17725'
20220722/125922.863 - U00003536 UCUDB: FATAL DATA BASE ERROR: Re-connection will be attempted in 10 seconds.
20220722/125922.864 - U00003537 UCUDB - RECONNECT: DB call 'OCITransRollback': Return code: '-1'.
20220722/125922.864 - U00003590 UCUDB - DB error: 'OCITransRollback', 'ERROR  ', '', 'ORA-01012: not logged on
Process ID: 20513586
Session ID: 1378 Serial number: 17725'
20220722/125922.864 - U00003592 UCUDB - Status: '' Native error: '1012' Msg: 'ORA-01012: not logged on
Process ID: 20513586
Session ID: 1378 Serial number: 17725'
20220722/125923.975 - U00003537 UCUDB - RECONNECT: DB call 'OCILogon': Return code: '-1'.
20220722/125923.975 - U00003590 UCUDB - DB error: 'OCILogon', 'ERROR  ', '', 'ORA-01017: invalid username/password; logon denied'
20220722/125923.975 - U00003592 UCUDB - Status: '' Native error: '1017' Msg: 'ORA-01017: invalid username/password; logon denied'
20220722/125933.975 - U00003537 UCUDB - RECONNECT: DB call 'OCITransRollback': Return code: '-1'.

Eventually, this could even cause the user to be locked at Oracle with an error like:

20220818/150300.035 - 28     U00003590 UCUDB - DB error: '99999', 'ORA-28000: the account is locked

Environment

Release : 12.3.9 and 21.0.x

Component : AUTOMATION ENGINE

Cause

This a bug was introduced in version 12.3.9/21.0.3 of the Automation Engine.

Correction done on the product: A problem has been fixed where WPs were unable to reconnect after losing the connection to Oracle database.

Resolution

Workaround on 12.3.x or 21.0.x:

Use a plaintext password for the DB user in ucsrv.ini

Solution:

Update to a fix version listed below or a newer version if available.

Fix version:
Component(s): Automation Engine

Automation.Engine 12.3.9HF1 - Available

Automation.Engine 12.3.9HF2 - Available

Automation.Engine 21.0.4 - Available

Additional Information

Under special circumstances the problem can be worked around by restarting the process whose session had been killed.

But very often, the Oracle account for the AE system can get locked rapidly after a certain number of re-connection attempts or hence the PWP and the impacted WP can freeze and must be killed to be able to restart the system.

In this case the Oracle user account must be unlocked before the Engine can be restarted.

Solution details:

A problem has been fixed where a WP did not reconnect with the correct credentials to the database when a connection was lost.
This happened on Oracle databases if the password of the DB user is encrypted in ucsrv.ini