On COLD start, WP logs show ORA-00942 error
Article ID: 191075
Updated On:
Products
Issue/Introduction
U00029108 UCUDB: SQL_ERROR Database handles DB-HENV: 2000730 DB-HDBC: 207ca30
U00003591 UCUDB - DB error info: OPC: 'OCIStmtExecute' Return code: 'ERROR'
U00003592 UCUDB - Status: '' Native error: '942' Msg: 'ORA-00942: table or view does not exist
ORA-06512: at "UC4.UC_TRUNCATE_TABLE", line 1'
U00003594 UCUDB Ret: '3590' opcode: 'EXEC' SQL Stmnt: 'call UC_Truncate_Table('MQ1CP009')'
U00003590 UCUDB - DB error: 'OCIStmtExecute', 'ERROR ', '', 'ORA-00942: table or view does not exist
ORA-06512: at "UC4.UC_TRUNCATE_TABLE", line 1'
The table in the error message 'MQ1CP009' can be any CP table, starting with 'MQ#CP###' where the # are numbers.
Environment
AWA 11.x, 12.x, 21.x
Cause
It will:
Start by truncating the message queue (MQ) table for CP1 (CP001)
Loop, adding one to the CP number
Until it hits a CP table that does not exist
Throw the warning message above
In the example above, this means that 8 tables were truncated and when it went to truncate the 9th, it was not found.
Resolution
This error message is normal and is not indicative of any problems as long as the two following conditions are true:
The error is seen during a cold start
The error points to a MQ table for CPs
Additional Information
If you are seeing the message for many days after the restart and you would like it to no longer show, here is some information on
a) why you still see the message even months after the coldstart
b) how you can remove it without having to do a full normal restart of Automic.
When reviewing the WP logs even 3 days after the coldstart, the message still shows up as if it is constantly happening.
If you take closer look at the WP logs, you will see the first part of the log file shows two time stamps, like:
20231207/034046.098 - 20231202/065354.899
The left timestamp is the current time, what is happening right now
The right timestamp is best described as a snapshot timestamp, what was happening on that date and time.
As long as there are two timestamps shown like this, and you'll continue to see the message like:
20231207/034046.096 - 20231202/065118.978 - U00003590 UCUDB - DB error: 'OCIStmtExecute', 'ERROR ', '', 'ORA-00942: table or view does not exist
That message only happened, in this case, on Dec 02, 2023 at 06:51am. It is not currently happening.
This can be ignored, as stated above.
To remove these messages without having to cause and outage of your system, a rolling restart of the WP processes can be done as follows:
In AWI from client 0, stop the first WP (NOT the PWP), then start it again.
Once this WP has been started, right-click on it and select Server Mode > Change Server mode to PWP:
Once this has taken over as the PWP, you can then stop each remaining WP one at a time then restart them.
Feedback
