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.
Update to a fix version listed below or a newer version if available.
Automation.Engine 12.2.8 - Planned release in February 2021
Automation.Engine 12.3.5 - Planned release in March 2021
A problem has been fixed where the log file of CPs contained the error message ORA-01455 (converting column overflows integer datatype).
This problem happened when the table MQ1OWP or MQ2OWP had a lot of messages during an ILM action.
The code that counts the number of messages used the wrong data type (16bit Integer) to store the result of the count. The check is repeated every ten seconds. The error messages in the log disappear when the number of entries in MQxOWP is low enough to be stored in a 16bit integer.