goldengate OCI Error ORA-01400: cannot insert NULL

     goldengate目标端出现 OCI Error ORA-01400: cannot insert NULL

     我分析可能遇到的一种情况是，目标端开启了HANDLECOLLISIONS ，当源端update一条记录时，目标端没有这条记录，如果更新的是主键，就会转换为insert语句，但是默认只是插入主键，如果更新的非主键列会被抛弃并且不会记录错误到discard

 

   如果更新的是主键列，但是这个表的其他的列要是有非空的限制，就会报错

 

 

源端：

        

SQL>  create table t1 (id int primary key,name varchar2(20) not null);

Table created.
SQL> insert into t1 values(1,'nag');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from t1;

        ID NAME
---------- --------------------
         1 nag

 

目标端：

SQL> select * from t1;

        ID NAME
---------- --------------------
         1 nag

SQL> delete from t1;

1 row deleted.

SQL> commit;

Commit complete.

SQL> select * from t1;

no rows selected

 

源端：

update t1 set name='namu' where id=1;

SQL> update t1 set name='namu' where id=1;

1 row updated.

SQL> commit;

Commit complete.

SQL> select * from t1;

        ID NAME
---------- --------------------
         1 namu

 

目标端：

stats replt1 发现有一个update被记录，但是目标端没有执行，日志也没有报错

SQL> select * from t1;

no rows selected

 

源端：

update t1 set id=3 where id=1;

 

目标端：

2013-03-20 15:00:43  INFO    OGG-00996  Oracle GoldenGate Delivery for Oracle, replt1.prm:  REPLICAT REPLT1 started.
2013-03-20 15:00:43  WARNING OGG-01396  Oracle GoldenGate Delivery for Oracle, replt1.prm:  A complete after image is not available in SCOTT.T1 at rba 3335 in file /u01/app/goldengate/dirdat/tt000004, while inserting a row into TEST.T1 due to missing target row for a key update operation. NOCOMPRESSUPDATES or FETCHOPTIONS FETCHPKUPDATECOLS may be specified in the EXTRACT parameter file to include a complete image for key update operations.
2013-03-20 15:00:43  WARNING OGG-00869  Oracle GoldenGate Delivery for Oracle, replt1.prm:  OCI Error ORA-01400: cannot insert NULL into ("TEST"."T1"."NAME") (status = 1400). INSERT /*+ RESTRICT_ALL_REF_CONS */ INTO "TEST"."T1" ("ID") VALUES (:a0).
2013-03-20 15:00:43  WARNING OGG-01004  Oracle GoldenGate Delivery for Oracle, replt1.prm:  Aborted grouped transaction on 'TEST.T1', Database error 1400 (OCIError ORA-01400: cannot insert NULL into ("TEST"."T1"."NAME") (status = 1400). INSERT /*+RESTRICT_ALL_REF_CONS */ INTO "TEST"."T1" ("ID") VALUES (:a0)).
2013-03-20 15:00:43  WARNING OGG-01003  Oracle GoldenGate Delivery for Oracle, replt1.prm:  Repositioning to rba 3335 in seqno 4.
2013-03-20 15:00:43  WARNING OGG-01154  Oracle GoldenGate Delivery for Oracle, replt1.prm:  SQL error 1400 mapping SCOTT.T1 to TEST.T1 OCI Error ORA-01400: cannot insert NULL into ("TEST"."T1"."NAME") (status = 1400). INSERT /*+ RESTRICT_ALL_REF_CONS */ INTO "TEST"."T1" ("ID") VALUES (:a0).
2013-03-20 15:00:43  WARNING OGG-01003  Oracle GoldenGate Delivery for Oracle, replt1.prm:  Repositioning to rba 3335 in seqno 4.
2013-03-20 15:00:43  ERROR   OGG-01296  Oracle GoldenGate Delivery for Oracle, replt1.prm:  Error mapping from SCOTT.T1 to TEST.T1.
2013-03-20 15:00:43  ERROR   OGG-01668  Oracle GoldenGate Delivery for Oracle, replt1.prm:  PROCESS ABENDING.

 

 

  

     可以在源端开启FETCHOPTIONS FETCHPKUPDATECOLS将捕获完整的redo image镜像到trail中 这样保证插入完整的记录

   但是建议关闭目标端的HANDLECOLLISIONS，因为update非主键列会有数据丢失

  源端：

SQL> insert into t1 values(1,'nag');

1 row created.

SQL> commit;

 

目标：

SQL> select * from t1;

        ID NAME
---------- --------------------
         1 nag

 

SQL> delete from t1;

1 row deleted.

SQL> commit;

Commit complete.

 

 

源端：

 

SQL> update t1 set id=2 where id=1;

1 row updated.

SQL> commit;

 

 

目标：

SQL> select * from t1;

        ID NAME
---------- --------------------
        2     nag

 

 

 

参考文档：

http://www.askmaclean.com/archives/%e4%ba%86%e8%a7%a3goldengate-replicat%e7%9a%84handlecollisions%e5%8f%82%e6%95%b0.html