OGG-01154 SQL error 1400 cannot insert NULL into错误解析

2012-11-22 14:46:37 WARNING OGG-03504 NLS_LANG character set UTF8 on the target is different from the source database character se
t AL32UTF8. Replication may not be valid if the source data has an incompatible character for the target NLS_LANG character set.

2012-11-22 14:46:37 WARNING OGG-00869 Aborting BATCHSQL transaction. Detected inconsistent result: executed 1 operations in batch,
resulting in 0 affected rows.

2012-11-22 14:46:37 WARNING OGG-01137 BATCHSQL suspended, continuing in normal mode.

2012-11-22 14:46:37 WARNING OGG-01003 Repositioning to rba 2834 in seqno 0.

2012-11-22 14:46:37 WARNING OGG-00869 OCI Error ORA-01400: 无法将 NULL 插入 ("EP"."T_SYS_TASK"."ID") (status = 1400). INSERT INTO 
"EP"."T_SYS_TASK" ("ID","TASK_TYPE","UNIT_ID","START_TIME","STOP_TIME","STATUS","INFO_ID","TITLE","CONTENT","EXEC_START_TIME","EXEC_
STOP_TIME","ADDR_LIST_FILE","NOTICE_MAIL_ADDR","TASK_NAME","CREATOR_ID","CREATOR_TIME","AUDITOR_ID","AUDITOR_TIME","ADVICE") VALUES 
(:a0,:a1,:a2,:a3,:a4,:a5,:a6,:a7,:a8,:a9,:a10,:a11,:a12,:a13,:a14,:a15,:a16,:a17,:a18).

2012-11-22 14:46:37 WARNING OGG-01004 Aborted grouped transaction on 'EP.T_SYS_TASK', Database error 1400 (OCI Error ORA-01400: 无
法将 NULL 插入 ("EP"."T_SYS_TASK"."ID") (status = 1400). INSERT INTO "EP"."T_SYS_TASK" ("ID","TASK_TYPE","UNIT_ID","START_TIME","STO
P_TIME","STATUS","INFO_ID","TITLE","CONTENT","EXEC_START_TIME","EXEC_STOP_TIME","ADDR_LIST_FILE","NOTICE_MAIL_ADDR","TASK_NAME","CRE
ATOR_ID","CREATOR_TIME","AUDITOR_ID","AUDITOR_TIME","ADVICE") VALUES (:a0,:a1,:a2,:a3,:a4,:a5,:a6,:a7,:a8,:a9,:a10,:a11,:a12,:a13,:a
14,:a15,:a16,:a17,:a18)).

2012-11-22 14:46:37 WARNING OGG-01003 Repositioning to rba 2834 in seqno 0.

2012-11-22 14:46:37 WARNING OGG-01154 SQL error 1400 mapping EP.T_SYS_TASK to EP.T_SYS_TASK 

OCI Error ORA-01400: 无法将 NULL 插入 
("EP"."T_SYS_TASK"."ID") (status = 1400). INSERT INTO "EP"."T_SYS_TASK" ("ID","TASK_TYPE","UNIT_ID","START_TIME","STOP_TIME","STATUS
","INFO_ID","TITLE","CONTENT","EXEC_START_TIME","EXEC_STOP_TIME","ADDR_LIST_FILE","NOTICE_MAIL_ADDR","TASK_NAME","CREATOR_ID","CREAT
OR_TIME","AUDITOR_ID","AUDITOR_TIME","ADVICE") VALUES (:a0,:a1,:a2,:a3,:a4,:a5,:a6,:a7,:a8,:a9,:a10,:a11,:a12,:a13,:a14,:a15,:a16,:a
17,:a18).

2012-11-22 14:46:37 WARNING OGG-01003 Repositioning to rba 2834 in seqno 0.
WARNING OGG-01396 A complete after image is not available in <schema.table> at rba 123456 in file ./dirdat/yyy, 
while inserting a row into <schema.table> 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.

WARNING OGG-00869 OCI Error ORA-01400: cannot insert NULL into ("<schema>"."<table>"."<PK>") 
(status = 1400), SQL <INSERT INTO "<schema>"."<table>" ("<PK>",...) VALUES (:a0,:...)>.

2011-03-28 10:39:05 WARNING OGG-01004 Aborted grouped transaction on '<schema.table>', 
Database error 1400 (ORA-01400: cannot insert NULL into ("<schema>"."<table>"."<PK>")).

2011-03-28 10:39:05 WARNING OGG-01003 Repositioning to rba 123455 in seqno 678.

2011-03-28 10:39:05 WARNING OGG-01154 SQL error 1400 mapping <schema.table> to <schema.table> 

OCI Error ORA-01400: cannot insert NULL into ("<schema>"."<table>"."<PK>") (status = 1400), 
SQL <INSERT INTO "<schema>"."<table>" ("<PK>",...) VALUES (:a0,...)>.

2011-03-28 10:39:05 WARNING OGG-01003 Repositioning to rba 123455 in seqno 678.

Source Context :
SourceModule : [er.main]
SourceID : [/home/ecloud/workspace/Build_FBO_OpenSys_r11.1.1.0.0_078_[34093]/perforce/src/app/er/rep.c]
SourceFunction : [take_rep_err_action]
SourceLine : [15780]
ThreadBacktrace : [8] elements
: [/oradata/ogg/replicat(CMessageContext::AddThreadContext()+0x26) [0x5d9516]]
: [/oradata/ogg/replicat(CMessageFactory::CreateMessage(CSourceContext*, unsigned int, ...)+0x7b2) [0x5cffb2]]
: [/oradata/ogg/replicat(_MSG_ERR_MAP_TO_TANDEM_FAILED(CSourceContext*, DBString<777> const&, 
DBString<777> const&, CMessageFactory::MessageDisposition)+0x9b) [0x57bd7b]]
: [/oradata/ogg/replicat [0x7df1a3]]
: [/oradata/ogg/replicat [0x8ac301]]
: [/oradata/ogg/replicat(main+0x1d30) [0x4f4b90]]
: [/lib64/libc.so.6(__libc_start_main+0xf4) [0x3fca01d994]]
: [/oradata/ogg/replicat(__gxx_personality_v0+0x1e2) [0x4d86ba]]

2011-03-28 10:39:05 ERROR OGG-01296 Error mapping from <schema.table> to <schema.table>.

 

 

OGG-01154 SQL error 1400 该错误常由replicat端使用了HANDLECOLLISIONS时(启用HANDLECOLLISIONS时 target丢失update记录(missing update)更新的键值是主键=》 update转换成INSERT ,默认情况下插入记录不完整,详见《了解GoldenGate Replicat的HANDLECOLLISIONS参数》)对于丢失的PK UPDATE转换为INSERT但是由于UPDATE记录未包含更新后的所有列的镜像而引起(if the PK is not available at target side, then this error is expected, because HANDLECOLLISIONS turns the PK update into an insert as result of no target record to update.The problem is that the source PK update record doesn’t contain all the after image columns. That is also expected because the update record is intended to only update the affected columns. )

有同学认为OGG-01154 SQL error 1400 是因为数据不一致引起的,这样说也有道理,因为如果说不丢失该PK记录则UPDATE不会转变为INSERT。

对于上述问题常见的Workaround方法是在 capture/extract端加入FETCHOPTIONS FETCHPKUPDATECOLS ,以便extract获取完整的记录更新后镜像,使得HANDLECOLLISIONS 正常工作将PK UPDATE转换为INSERT。

FETCHOPTIONS is an Extract parameter that controls certain aspects of the way that GoldenGate fetches data.

FETCHOPTIONS FETCHPKUPDATECOLS

needs to be added to extract parameter file and extract needs to be restarted. This will cause the extract to capture the full image for primary key update.