-
Title
Error “Poster: Oracle error: ORA-00942: table or view does not exist” in CDC environment even though the table does exist on target -
Description
The Post is stopped due to error and the following messages appear in the event_log in a CDC (Change Data Capture or Change History) environment:
Info 2017-08-28 19:47:45.275078 15859 1911934688 Poster launched, pid = 15859 (posting from SID1, queue shareplex3, to SID2)
Warning 2017-08-28 19:48:57.810570 15859 1873299776 s:2 Poster: 15022 - Error posting ODR_INSERT1 to table "!CDC:TEST_DATA"."T_MYAPP_ORDERS_HIST". (posting from SID1, queue shareplex3, to SID2) [module opo]
Notice 2017-08-28 19:48:57.810939 15859 1873299776 s:2 Poster: Oracle error: ORA-00942: table or view does not exist. rowid:AAASwDAAGAAAABtAAA (posting from SID1, queue shareplex3, to SID2) [module opo]
Error 2017-08-28 19:48:57.810996 15859 1873299776 s:2 Poster stopped: Internal error encountered; cannot continue (posting from SID1, queue shareplex3, to SID2)
Notice 2017-08-28 19:48:57.811488 15859 1849716112 Poster: Last committed SCN 0. (posting from SID1, queue shareplex3, to SID2) [module opo]
Info 2017-08-28 19:48:57.813053 15857 1911934688 Poster exited with code=1, pid = 15859 (posting from SID1, queue shareplex3, to SID2).
The target table does exist.
Here is the config file
datasource:o.SID1
#source tables target tables routing map
TEST_DATA.T_MYAPP_ORDERS !cdc:TEST_DATA.T_MYAPP_ORDERS_HIST target_server_name@o.SID2
-
Cause
There is a syntax error in the config file. -
Resolution
The config file had an o.SID notation for target SID though it should be c.SID which is a syntax error. Here it is:
This is causing SharePlex to parse the target table name as "!CDC:TEST_DATA"."T_MYAPP_ORDERS_HIST" and not TEST_DATA.T_MYAPP_ORDERS_HIST.
Carry out the following steps:
1. Run ora_cleansp or abort the currently active config. Either way the current messages in the queue will be purged and this would certainly require a resync later on even if the activation is restored following the steps detailed in this article.
2. Edit the config file in question and replace the o.SID for target SID with c.SID. In our example:
Replace:
o.SID2
With
c.SID2
So our config file entry should look like:
TEST_DATA.T_MYAPP_ORDERS !cdc:TEST_DATA.T_MYAPP_ORDERS_HIST target_server_name@c.SID2
3. Then activate the config. This should resolve the Post error.
-
Additional Information
The error has also known to occur at times when there are constraints enabled on the target table. In a CDC environment, one of the requirement is that constraints should be disabled on the target table. If the error ORA-00942 occurs with constraints enabled on the target table and if there is no syntax error in the config file, then please call Support so that the debug can be set and the issue investigated.