Post stops as below in a CDC environment:
Error 2016-01-25 11:09:52.345564 13340 310298368 s:3 Poster: que/seq = 3/ 83872 seqno/offset = 6703/124989060 Unexpected Oracle error: ORA-01400: cannot insert NULL into ("OWNER"."TABLE_NAME"."COLUMN_NAME"). (object name:"OWNER"."TABLE_NAME") (posting from SID1, queue queue_name, to SID2) [module opo]
The column named COLUMN_NAME has NOT NULL constraint defined on both source and target database.
The config file is using user defined key to include all columns in the table, something like:
OWNER.SOURCE_TABLE !key (COLUMN1, COLUMN2, …..) !cdc:OWNER.TARGET_TABLE firstname.lastname@example.orgSID
The above is done to bring over all columns in the WHERE clause to the target.
The supplemental log group has not been created for the key columns.
When supplemental log group does not exist for all columns listed in the user defined key (which happens to be all the columns in the table in our scenario), then the information is not in redo log thus capture may send NULL for such key columns. This will result in the ORA-1400 as above. To get around the problem, one will need to enable supplemental log group and also get rid of the problem messages so that new messages do not have NULL for these key columns and Post does not stop due to error. Here are the steps:
1. Quiet the source table and let all messages accumulate in the Post queue.
2. In the source database add Supplemental Log Group for that problem table by:
SQL> ALTER TABLE SOURCE_TABLE ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;
3. On source sp_ctrl, set the following Capture parameter and bounce Capture:
sp_ctrl>set param SP_OCT_REDUCED_KEY 0
4. Shutdown Shareplex on target, delete the Post queue by invoking qview utility from /proddir/bin directory as:
$ qview –i (where $ denotes OS prompt)
qview>deleteq p (answer ‘y’ to the correct queue to be deleted)
5. Login to target database as Shareplex user and truncate the table SHAREPLEX_TRANS
SQL>TRUNCATE TABLE SHAREPLEX_TRANS;
6. Restart Shareplex.
7. Allow user activity on source table.
The other option is to disable NOT NULL constraint on affected columns.