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".
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.
Database: Oracle using ILM
Version : 12.2.x/ 12.3.x
Component: Automation Engine
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.
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.
This error 'ORA-01455: converting column overflows integer datatype' should be fixed on an ulterior version of Automation Engine, yet to be scheduled