ITPub博客

首页 > 数据库 > Oracle > ORA-01400: cannot insert NULL into ("OGGTEST"."TESTNULL"."ID3")

ORA-01400: cannot insert NULL into ("OGGTEST"."TESTNULL"."ID3")

原创 Oracle 作者:xueshancheng 时间:2021-09-15 11:20:05 0 删除 编辑

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错误





来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/69996316/viewspace-2792147/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
本人目前就职于北京海天起点技术服务有限股份公司,从事Oracle数据库有十几年了,对Oracle及goldengate比较精通。

注册时间:2021-03-11

  • 博文量
    43
  • 访问量
    11607