Job processing stops: U0003592 'ORA-01455: converting column overflows integer datatype

book

Article ID: 84540

calendar_today

Updated On:

Products

CA Automic Workload Automation - Automation Engine

Issue/Introduction

After ILM partition switch, the following error message is displayed in the CP log:
U0003592 UCUDB - Status: '' Native error: '1455' Msg: 'ORA-01455: converting column overflows integer datatype'

Additionally, the MQ?OWP count increases a lot and OWP process utilization remains at 100% causing Job processing to stop or slow down with jobs until the MQ?OWP table is empty.
Jobs are not executed, causing delays in its original scheduled time, appearing as "Waiting for parallel task".

 

Cause

This error "'ORA-01455: converting column overflows integer datatype'"  was due to the following query (MQ1OWP or MQ2OWP) that is executed by the CP to check if there are still records to be processed in the MQ?OWP table:

SELECT COUNT(*) AS DIVDB_INT1 FROM MQ2OWP

 

The error occurs when the count number of the MQ*OWP table 'crm_check_empty_mqowp()' is called -> the count is using 'DIVDB_INT1' which is wrong as this is small int (max. 32767),  so if the table has more records this will lead to this error messages during ILM switch action.

Environment

Database: Oracle using ILM

Version : 12.2.x/ 12.3.x

Component: Automation Engine

Resolution

Workaround 1:

The huge count increase on the MQ?OWP table is caused by Non-Connected Agents that had huge Logs and would send all its logs to the CP process as soon as they are able to establish securely the connection to the AE again.

To prevent this from occurring, stop those agents so that their log is emptied, restart them and "Renew Transfer Key".

Additionally, you can also set temporarily LOG_TO_DATABASE to N in UC_HOSTCHAR_DEFAULT and restart those Agents to prevent them from writing their logs into the RT table.

Once the MQ?OWP table stops growing, Job Executions will resume.

 

Workaround 2 ( in case you can't wait any longer):

Perform a COLD restart of the Automation Engine if 'ORA-01455' is seen in the logs and jobs are not currently running

BEWARE:  Performing a cold start can have adverse effects on the Automation Engine installation. The general impact of the COLD start is covered in the following Community post: The COLD start 'HOT' topic.

 

Solution:

This error 'ORA-01455: converting column overflows integer datatype' should be fixed on  an ulterior version of Automation Engine, yet to be scheduled 

 

Additional Information

Workaround :
N/A