GoldenGate OGG-01004 ORA-01400 Cannot insert null into ...

OGG出現故障， ORA-01400, cannot insert null into “TEST“.“TEST“
目標端複製程式使用了配置HANDLECOLLISIONS， 此配置在目標端更新時，如果更新不到資料，會將UPDATE轉換成insert
但是因為源端並沒有配置所有列的附加日誌，所以，沒有被更新的列並不會包含在insert中，從而導致錯誤

做了以下步驟， 這裡是臨時解決方案：
1. 將列修改成null: alter table test modify col1 null;
2. 啟動複製程式
3. 檢查資料：select count(*) from test where col1 is null (發現有八條）
4. 處理並手工同步這八條資料
5. 將欄位改成not null : alter table test modify col1 not null;

最終解決: 需要加入表的所有列的附加日誌: alter table test add supplemental log data (ALL) columns;
DDL操作在生產環境較為危險， 暫緩動作

MOS文件ID： Replicat Abends With Errror ORA-01400: Cannot Insert NULL Into Table for Not Null Columns When Specifying INSERTALLRECORDS (文件 ID 1994869.1)

                      REPLICAT Reports ORA-01400: Cannot Insert NULL When Using HANDLECOLLISIONS (文件 ID 1576900.1)

REPLICAT tried to UPDATE an existing row, but none was found, so due to parameter HANDLECOLLISIONS, the failed UPDATE was converted into an INSERT.
That INSERT then failed because the Trail file did not have all the columns, because the record was for an UPDATE for 5 out of 15 columns, where the table on the Oracle database had the NOT NULL constraint set for all columns.

The cause of the problem has been determined from the following points;

• The REPLICAT report shows the message "... inserting a row into USER1.TABLE1 due to missing target row for a key update operation. " 
  where the key words are " missing target row for a key update ". 
  This means there was no row to UPDATE, for the change record found in the Trail file.
• The REPLICAT had the HANDLECOLLISIONS parameter set.
• The GoldenGate Reference Guide shows that when HANDLECOLLISIONS is used, failed UPDATE's will be converted into INSERT's 
  "If the row with the old key is not found in the target, the change record in the trail is converted to an insert".
• The REPLICAT report finally shows that after the failed UPDATE was reported, the INSERT also failed.  
  The INSERT failed because the Trail file record only had 5 out of 15 columns, where the table is defined with NOT NULL on all the columns.  
  This then caused the Oracle database error: ORA-01400: cannot insert NULL...

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/8520577/viewspace-2652792/，如需轉載，請註明出處，否則將追究法律責任。