1 源端创建测试表
源端
OGGTEST@orcl>create table testnull (id number(8),id2 number(8));
Table created.
2 目标端创建测试表，并新增一列ID3，用于测试ORA-01400错误
目标端
OGGTEST@orcl>create table testnull (id number(8),id2 number(8));
Table created.
alter table testnull add id3 number(12) not null;
3 测试数据是否同步
OGG配置的参数如下：
MAP oggtest.testnull, TARGET oggtest.testnull, COLMAP (USEDEFAULTS, id3=@STRCAT("612",ID2));
源端：
OGGTEST@orcl>insert into testnull values(1,0);
1 row created.
OGGTEST@orcl>commit;
Commit complete.
源端数据：
OGGTEST@orcl>select * from testnull;
ID ID2
---------- ----------
1 0
目标端：
ID ID2 ID3
---------- ---------- ----------
1 0 6120
4 测试负值，OGG是否报错，发现报错，如下
测试负值：
OGGTEST@orcl>insert into testnull values(1,-1);
1 row created.
OGGTEST@orcl>commit;
目标端：
GGSCI (oracle10g1) 31> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT ABENDED REPTB1 00:00:07 00:02:17
报错信息：
2021-09-15 10:07:49 WARNING OGG-01431 Aborted grouped transaction on 'OGGTEST.TESTNULL', Mapping error.
2021-09-15 10:07:49 WARNING OGG-01003 Repositioning to rba 1490 in seqno 16.
2021-09-15 10:07:49 WARNING OGG-01151 Error mapping from OGGTEST.TESTNULL to OGGTEST.TESTNULL.
2021-09-15 10:07:49 WARNING OGG-01003 Repositioning to rba 1490 in seqno 16.
Source Context :
SourceModule : [er.errors]
SourceID : [/scratch/aime1/adestore/views/aime1_adc4150267/oggcore/OpenSys/src/app/er/errors.cpp]
SourceFunction : [take_rep_err_action]
SourceLine : [632]
ThreadBacktrace : [12] elements
: [/app/oracle/goldengate/libgglog.so(CMessageContext::AddThreadContext()+0x1e) [0x2aec4eb4e61e]]
: [/app/oracle/goldengate/libgglog.so(CMessageFactory::CreateMessage(CSourceContext*, unsigned int, ...)+0x2ec) [0x2aec4eb4715c]]
: [/app/oracle/goldengate/libgglog.so(_MSG_ERR_MAP_TO_TANDEM_FAILED(CSourceContext*, ggs::gglib::ggapp::CQualDBObjName<(DBObjType)1> const
&, ggs::gglib::ggapp::CQualDBObjName<(DBObjType)1> const&, CMessageFactory::MessageDisposition)+0x53) [0x2aec4eb4312b]]
: [/app/oracle/goldengate/replicat(take_rep_err_action(short, int, char const*, extr_ptr_def*, __std_rec_hdr*, char*, file_def*, bool)+0xa
a7) [0x524c31]]
: [/app/oracle/goldengate/replicat(process_extract_loop()+0x274c) [0x53e78c]]
: [/app/oracle/goldengate/replicat(replicat_main(int, char**)+0x741) [0x551101]]
: [/app/oracle/goldengate/replicat(ggs::gglib::MultiThreading::MainThread::ExecMain()+0x4f) [0x5ef7ff]]
: [/app/oracle/goldengate/replicat(ggs::gglib::MultiThreading::Thread::RunThread(ggs::gglib::MultiThreading::Thread::ThreadArgs*)+0x104) [
0x5efa54]]
: [/app/oracle/goldengate/replicat(ggs::gglib::MultiThreading::MainThread::Run(int, char**)+0x8b) [0x5efb5b]]
: [/app/oracle/goldengate/replicat(main+0x2c) [0x5502ac]]
: [/lib64/libc.so.6(__libc_start_main+0xf4) [0x3c9561d994]]
: [/app/oracle/goldengate/replicat(__gxx_personality_v0+0x31a) [0x4c266a]]
2021-09-15 10:07:49 ERROR OGG-01296 Error mapping from OGGTEST.TESTNULL to OGGTEST.TESTNULL.
目标端，发现插入负值，OGG报错，通过此事件可以说明，插入不合规的数据，OGG又可能会报错，且不知道如何处理：
ID ID2 ID3
---------- ---------- ----------
1 0 6120
5 插入空值，测试ORA-1400错误，按照Oracle的理解：空值+任何值=空置，故会触发ORA-1400错误
源端数据插入，未报错，正常。
OGGTEST@orcl>insert into testnull values(2,'');
1 row created.
OGGTEST@orcl>commit;
Commit complete.
OGGTEST@orcl>select * from testnull;
ID ID2
---------- ----------
1 0
1 -1
2
目标端：
GGSCI (oracle10g1) 60> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT ABENDED REPTB1 00:00:06 00:00:47
报错信息：
2021-09-15 10:42:27 WARNING OGG-00869 OCI Error ORA-01400: cannot insert NULL
into ("OGGTEST"."TESTNULL"."ID3") (status = 1400). INSERT /*+ RESTRICT_ALL_REF_CONS
*/ INTO "OGGTEST"."TESTNULL" ("ID","ID2","ID3") VALUES (:a0,:a1,:a2).
2021-09-15 10:42:27 WARNING OGG-01004 Aborted grouped transaction on 'OGGTEST.TESTNULL',
Database error 1400 (OCI Error ORA-01400: cannot insert NULL into ("OGGTE
ST"."TESTNULL"."ID3") (status = 1400). INSERT /*+ RESTRICT_ALL_REF_CONS */ INTO "OGGTEST"."TESTNULL"
("ID","ID2","ID3") VALUES (:a0,:a1,:a2)).
2021-09-15 10:42:27 WARNING OGG-01003 Repositioning to rba 1782 in seqno 16.
2021-09-15 10:42:27 WARNING OGG-01154 SQL error 1400 mapping OGGTEST.TESTNULL to
OGGTEST.TESTNULL OCI Error ORA-01400: cannot insert NULL into ("OGGTEST"."TESTNUL
L"."ID3") (status = 1400). INSERT /*+ RESTRICT_ALL_REF_CONS */ INTO "OGGTEST"."TESTNULL" ("ID","ID2","ID3")
VALUES (:a0,:a1,:a2).
2021-09-15 10:42:27 WARNING OGG-01003 Repositioning to rba 1782 in seqno 16.
Source Context :
SourceModule : [er.errors]
SourceID : [/scratch/aime1/adestore/views/aime1_adc4150267/oggcore/OpenSys/src/app/er/errors.cpp]
SourceFunction : [take_rep_err_action]
SourceLine : [632]
ThreadBacktrace : [12] elements
: [/app/oracle/goldengate/libgglog.so(CMessageContext::AddThreadContext()+0x1e) [0x2af68912b61e]]
: [/app/oracle/goldengate/libgglog.so(CMessageFactory::CreateMessage(CSourceContext*, unsigned int, ...)+0x2ec) [0x2af68912415c]]
: [/app/oracle/goldengate/libgglog.so(_MSG_ERR_MAP_TO_TANDEM_FAILED(CSourceContext*, ggs::gglib::ggapp::CQualDBObjName<(DBObjType)1> const
&, ggs::gglib::ggapp::CQualDBObjName<(DBObjType)1> const&, CMessageFactory::MessageDisposition)+0x53) [0x2af68912012b]]
: [/app/oracle/goldengate/replicat(take_rep_err_action(short, int, char const*, extr_ptr_def*, __std_rec_hdr*, char*, file_def*, bool)+0xa
a7) [0x524c31]]
: [/app/oracle/goldengate/replicat(process_extract_loop()+0x274c) [0x53e78c]]
: [/app/oracle/goldengate/replicat(replicat_main(int, char**)+0x741) [0x551101]]
: [/app/oracle/goldengate/replicat(ggs::gglib::MultiThreading::MainThread::ExecMain()+0x4f)
[0x5ef7ff]]
: [/app/oracle/goldengate/replicat(ggs::gglib::MultiThreading::Thread::RunThread(ggs::gglib::
MultiThreading::Thread::ThreadArgs*)+0x104) [
0x5efa54]]
: [/app/oracle/goldengate/replicat(ggs::gglib::MultiThreading::MainThread::Run(int, char**)+0x8b) [0x5efb5b]]
: [/app/oracle/goldengate/replicat(main+0x2c) [0x5502ac]]
: [/lib64/libc.so.6(__libc_start_main+0xf4) [0x3c9561d994]]
: [/app/oracle/goldengate/replicat(__gxx_personality_v0+0x31a) [0x4c266a]]
2021-09-15 10:42:27 ERROR OGG-01296 Error mapping from OGGTEST.TESTNULL to OGGTEST.TESTNULL.
处理方法，修改OGG参数，使其可以进行判断，避免ID3列为空。
MAP oggtest.testnull, TARGET oggtest.testnull,
COLMAP (USEDEFAULTS, id3 = @IF ( @COLTEST (ID 2,NULL),"612", @STRCAT("612",ID2)));
启动OGG进程，验证数据是否同步：
GGSCI (oracle10g1) 70> start reptb1
Sending START request to MANAGER ...
REPLICAT REPTB1 starting
GGSCI (oracle10g1) 71> info reptb1
REPLICAT REPTB1 Last Started 2021-09-15 10:52 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:00:02 ago)
Log Read Checkpoint File ./dirdat/pt000016
2021-09-15 10:42:21.000006 RBA 1929
OGGTEST@orcl>/
ID ID2 ID3
---------- ---------- ----------
1 0 6120
2 612 －－发现OGG已经恢复正常。
原因分析：空值＋任何值＝空值，故在源端当某个列为空时，会导致连接字符串变为空值，最终触发ORA-1400错误