Error “Poster: Oracle error: ORA-00942: table or view does not exist” in CDC environment even though the table does exist on target (4302619)

Error “Poster: Oracle error: ORA-00942: table or view does not exist” in CDC environment even though the table does exist on target

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

There is a syntax error in the config file.

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:

target_server_name@o.SID2

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. 

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.