首页 » GoldenGate » GoldenGate ORA-01400: cannot insert NULL into after “update” , like “upsert” or “merge”

GoldenGate ORA-01400: cannot insert NULL into after “update” , like “upsert” or “merge”

有时出于历史原因或之前使用其它同步产品做同步,后期想改为OGG方案时但不想做原数据一致性比对,或可以接受部分数据不一致,比如目标表可能比原表少数据时,只想在replicat端增加INSERTMISSINGUPDATES参数,如果update目标记录不存在转换insert. 想法是好的?前提是要知道ogg的机制。

2020-05-12 08:35:57  WARNING OGG-01003  Repositioning to rba 346238969 in seqno 322.
2020-05-12 08:35:57  WARNING OGG-01004  Aborted grouped transaction on ANBOB.OGG_SYNC_TABLE_HIS, Database error 1400 (OCI Error ORA-01400: cannot insert NULL into ("ANBOB"."OGG_SYNC_TABLE_HIS"."REC_SEQ") (status = 1400), SQL ).
2020-05-12 08:35:57  WARNING OGG-01003  Repositioning to rba 346238969 in seqno 322.
2020-05-12 08:35:57  WARNING OGG-01154  SQL error 1400 mapping TBCS.OGG_SYNC_TABLE_HIS to ANBOB.OGG_SYNC_TABLE_HIS OCI Error ORA-01400: cannot insert NULL into ("ANBOB"."OGG_SYNC_TABLE_HIS"."REC_SEQ") (status = 1400), SQL .
2020-05-12 08:35:57  ERROR   OGG-01296  Error mapping from TBCS.OGG_SYNC_TABLE_HIS to ANBOB.OGG_SYNC_TABLE_HIS.

***********************************************************************
*                   ** Run Time Statistics **                         *
***********************************************************************

Reading /oracle/ogg/ogg12/dirdat/yya/rp000000322, current RBA 346239182, 0 records, m_file_seqno = 322, m_file_rba = 346239182

Report at 2020-05-12 08:35:57 (activity since 2020-05-12 08:35:57)


...skipping 1 line
BATCHSQL statistics:

Last log location read:
     FILE:      /oracle/ogg/ogg12/dirdat/yya/rp000000322
     SEQNO:     322
     RBA:       346239182
     TIMESTAMP: 2020-05-11 18:11:37.004323
     EOF:       NO
     READERR:   0

2020-05-12 08:35:57  ERROR   OGG-01668  PROCESS ABENDING.

SQL error 1400 mapping TBCS.OGG_SYNC_TABLE_HIS to ANBOB.OGG_SYNC_TABLE_HIS 
OCI Error ORA-01400: cannot insert NULL into ("ANBOB"."OGG_SYNC_TABLE_HIS"."REC_SEQ") (status = 1400), SQL .

源表

SQL> @desc tbcs.OGG_SYNC_TABLE_HIS
           Name                            Null?    Type
           ------------------------------- -------- ----------------------------
    1      WORKF_SEQ                       NOT NULL VARCHAR2(32)
    2      REC_SEQ                         NOT NULL VARCHAR2(32)
    3      ORDER_SEQ                       NOT NULL VARCHAR2(32)
    4      REGION                          NOT NULL NUMBER(5)
    5      ORDER_ID                        NOT NULL VARCHAR2(64)
    6      ORDER_PRI                       NOT NULL NUMBER(3)
    7      WORKF_ID                        NOT NULL VARCHAR2(128)
    8      SORT_ORDER                      NOT NULL NUMBER(3)
    9      WORKF_TYPE                               VARCHAR2(32)
   10      OPER_TYPE                       NOT NULL CHAR(1)
   11      PROC_SYSTEM                              VARCHAR2(32)
   12      PLAT_TYPE                       NOT NULL VARCHAR2(32)
   13      NE_ID                           NOT NULL VARCHAR2(32)
   14      TELNUM                          NOT NULL VARCHAR2(32)
   15      IMSI                                     VARCHAR2(20)
   16      CREATE_TIME                     NOT NULL DATE
   17      PROC_TIME                                DATE
....
   43      REAL_CREATETIME                          DATE

目标表

SQL> @desc tbcs.OGG_SYNC_TABLE_HIS
           Name                            Null?    Type
           ------------------------------- -------- ----------------------------
    1      WORKF_SEQ                       NOT NULL VARCHAR2(32)
    2      REC_SEQ                         NOT NULL VARCHAR2(32)
    3      ORDER_SEQ                       NOT NULL VARCHAR2(32)
    4      REGION                          NOT NULL NUMBER(5)
    5      ORDER_ID                        NOT NULL VARCHAR2(64)
    6      ORDER_PRI                       NOT NULL NUMBER(3)
    7      WORKF_ID                        NOT NULL VARCHAR2(128)
    8      SORT_ORDER                      NOT NULL NUMBER(3)
    9      WORKF_TYPE                               VARCHAR2(32)
   10      OPER_TYPE                       NOT NULL CHAR(1)
   11      PROC_SYSTEM                              VARCHAR2(32)
   12      PLAT_TYPE                       NOT NULL VARCHAR2(32)
   13      NE_ID                           NOT NULL VARCHAR2(32)
   14      TELNUM                          NOT NULL VARCHAR2(32)
   15      IMSI                                     VARCHAR2(20)
   16      CREATE_TIME                     NOT NULL DATE
   17      PROC_TIME                                DATE
....
   43      REAL_CREATETIME                          DATE

分析trail 日志

Logdump 1 >open /oracle/ogg/ogg12/dirdat/yya/rp000000322
Current LogTrail is /oracle/ogg/ogg12/dirdat/yya/rp000000322

Logdump 4 >position 346239182
Reading forward from RBA 346239182
Logdump 5 >detail on
Logdump 6 >ghdr on
Logdump 7 >ggstoken detail
Logdump 8 >n
___________________________________________________________________
Hdr-Ind : E (x45) Partition : . (x0c)
UndoFlag : . (x00) BeforeAfter: A (x41)
RecLength : 91 (x005b) IO Time : 2020/05/11 18:11:37.004.323
IOType : 15 (x0f) OrigNode : 255 (xff)
TransInd : . (x02) FormatType : R (x52)
SyskeyLen : 0 (x00) Incomplete : . (x00)
AuditRBA : 338336 AuditPos : 1127900176
Continued : N (x00) RecCount : 1 (x01)

2020/05/11 18:11:37.004.323 FieldComp Len 91 RBA 346239182 
Name: ANBOB.OGG_SYNC_TABLE_HIS (TDR Index: 2)
After Image: Partition 12 G e
0000 0019 0000 0015 4344 3230 3139 3132 3039 3431 | ........CD2019120941
3039 3539 3333 3339 3600 0300 0a00 0000 0000 0000 | 095933396...........
0001 3a00 0e00 1300 0000 0f34 3630 3030 3435 3332 | ..:........460004532
3833 3235 3836 000f 0015 0000 3230 3139 2d31 322d | 832586......2019-12-
3039 3a31 333a 3239 3a33 38 | 09:13:29:38
Column 0 (x0000), Len 25 (x0019)
Column 3 (x0003), Len 10 (x000a)
Column 14 (x000e), Len 19 (x0013)
Column 15 (x000f), Len 21 (x0015)

GGS tokens:
TokenID x52 'R' ORAROWID Info x00 Length 20
4141 6944 6b33 4142 5841 4146 384a 4d41 4157 0001 | AAiDk3ABXAAF8JMAAW..

FieldComp identifies records where a Compressed Update operation was written to the source database transaction log.A row in a SQL table was updated. In this format, only the changed bytes are present. Before images of unchanged columns are not logged by the database.

从源表查记录很多not null的列也都是有数据的,但是trail中只有4列, 这就是问题所在。

我们看一下真正的insert

Hdr-Ind    :     E  (x45)     Partition  :     .  (x0c)  
UndoFlag   :     .  (x00)     BeforeAfter:     A  (x41)  
RecLength  :   676  (x02a4)   IO Time    : 2020/05/11 21:39:46.004.393   
IOType     :     5  (x05)     OrigNode   :   255  (xff) 
TransInd   :     .  (x03)     FormatType :     R  (x52) 
SyskeyLen  :     0  (x00)     Incomplete :     .  (x00) 
AuditRBA   :     193974       AuditPos   : 2888195088 
Continued  :     N  (x00)     RecCount   :     1  (x01) 

2020/05/11 21:39:46.004.393 Insert               Len   676 RBA 499977859 
Name: ANBOB.OGG_SYNC_TABLE_HIS  (TDR Index: 2) 
After  Image:                                             Partition 12   G  s   
 0000 0019 0000 0015 4c46 3230 3230 3035 3131 3431 | ........LF2020051141  
 3730 3636 3430 3234 3400 0100 1600 0000 1233 3136 | 706640244........316  
 3230 3035 3131 3338 3233 3935 3531 3200 0200 1900 | 200511382395512.....  
 0000 154c 4632 3032 3030 3531 3132 3631 3338 3132 | ...LF202005112613812  
 3033 3138 0003 000a 0000 0000 0000 0000 013c 0004 | 0318.............<..  
 0010 0000 000c 3447 4c4c 5f4c 5445 5f44 454c 0005 | ......4GLL_LTE_DEL..  
 000a 0000 0000 0000 0000 001e 0006 000e 0000 000a | ....................  
Column     0 (x0000), Len    25 (x0019)  
Column     1 (x0001), Len    22 (x0016)  
Column     2 (x0002), Len    25 (x0019)  
Column     3 (x0003), Len    10 (x000a)  
Column     4 (x0004), Len    16 (x0010)  
Column     5 (x0005), Len    10 (x000a)  
Column     6 (x0006), Len    14 (x000e)  
Column     7 (x0007), Len    10 (x000a)  
Column     8 (x0008), Len    13 (x000d)  
Column     9 (x0009), Len     3 (x0003)  
Column    10 (x000a), Len    15 (x000f)  
Column    11 (x000b), Len     8 (x0008)  
Column    12 (x000c), Len     9 (x0009)  
Column    13 (x000d), Len    15 (x000f)  
Column    14 (x000e), Len    19 (x0013)  
Column    15 (x000f), Len    21 (x0015)  
Column    16 (x0010), Len    21 (x0015)  
Column    17 (x0011), Len    10 (x000a)  
Column    18 (x0012), Len    11 (x000b)  
Column    19 (x0013), Len    10 (x000a)  
Column    20 (x0014), Len    10 (x000a)  
Column    21 (x0015), Len    10 (x000a)  
Column    22 (x0016), Len     6 (x0006)  
Column    23 (x0017), Len     4 (x0004)  
Column    24 (x0018), Len     5 (x0005)  
Column    25 (x0019), Len    32 (x0020)  
Column    26 (x001a), Len    10 (x000a)  
Column    27 (x001b), Len    16 (x0010)  
Column    28 (x001c), Len     4 (x0004)  
Column    29 (x001d), Len     9 (x0009)  
Column    30 (x001e), Len     4 (x0004)  
Column    31 (x001f), Len     4 (x0004)  
Column    32 (x0020), Len     4 (x0004)  
Column    33 (x0021), Len    16 (x0010)  
Column    34 (x0022), Len     4 (x0004)  
Column    35 (x0023), Len     4 (x0004)  
Column    36 (x0024), Len     4 (x0004)  
Column    37 (x0025), Len    10 (x000a)  
Column    38 (x0026), Len    10 (x000a)  
Column    39 (x0027), Len    10 (x000a)  
Column    40 (x0028), Len    10 (x000a)  
Column    41 (x0029), Len    10 (x000a)  
Column    42 (x002a), Len    21 (x0015)  
  
GGS tokens: 
TokenID x52 'R' ORAROWID         Info x00  Length   20 
 4141 6a79 3439 4143 6f41 4146 6c66 7241 4155 0001 | AAjy49ACoAAFlfrAAU..  
TokenID x4c 'L' LOGCSN           Info x00  Length   14 
 3136 3731 3036 3331 3531 3634 3631                | 16710631516461  
TokenID x36 '6' TRANID           Info x00  Length   15 
 3438 3137 2e33 302e 3237 3934 3533 32             | 4817.30.2794532  
TokenID x69 'i' ORATHREADID      Info x01  Length    2 
 0002     

关键字在trail中查找

Logdump 66 >filter all reset;
Unknown filter keyword (ALL) 
Logdump 67 >filter inc reset;
Logdump 68 >pos 0
Reading forward from RBA 0 
Logdump 69 >filter inc string 'CD2019120941095933396'
Logdump 70 >n

2020/05/11 18:11:37.004.323 FieldComp            Len    91 RBA 346238969 
Name: ANBOB.OGG_SYNC_TABLE_HIS  (TDR Index: 2) 
Before Image:                                             Partition 12   G  b   
 0000 0019 0000 0015 4344 3230 3139 3132 3039 3431 | ........CD2019120941  
 3039 3539 3333 3339 3600 0300 0a00 0000 0000 0000 | 095933396...........  
 0001 3a00 0e00 1300 0000 0f34 3630 3030 3435 3332 | ..:........460004532  
 3839 3737 3536 000f 0015 0000 3230 3139 2d31 322d | 897756......2019-12-  
 3039 3a31 333a 3239 3a33 38                       | 09:13:29:38  
Column     0 (x0000), Len    25 (x0019)  
Column     3 (x0003), Len    10 (x000a)  
Column    14 (x000e), Len    19 (x0013)  
Column    15 (x000f), Len    21 (x0015)  
  
GGS tokens: 
TokenID x52 'R' ORAROWID         Info x00  Length   20 
 4141 6944 6b33 4142 5841 4146 384a 4d41 4157 0001 | AAiDk3ABXAAF8JMAAW..  
TokenID x4c 'L' LOGCSN           Info x00  Length   14 
 3136 3731 3035 3330 3439 3332 3134                | 16710530493214  
TokenID x36 '6' TRANID           Info x00  Length   14 
 3331 3137 2e38 2e33 3438 3439 3533                | 3117.8.3484953  
TokenID x69 'i' ORATHREADID      Info x01  Length    2 
 0001                                              | ..  
 

查看表上的trandata

 GGSCI (qdyya2 as ggadmin@ANBOBa2) 5> info trandata ANBOB.OGG_SYNC_TABLE_HIS
Logging of supplemental redo log data is enabled for table ANBOB.OGG_SYNC_TABLE_HIS.
Columns supplementally logged for table ANBOB.OGG_SYNC_TABLE_HIS: CREATE_TIME, REGION, WORKF_SEQ.
Prepared CSN for table ANBOB.OGG_SYNC_TABLE_HIS: 16633737628994

OGG Replicat Encounters OGG-01396 OGG-00869 ORA-01400 on Primary Key Column (Doc ID 1308824.1)

First check the affected trail file yyy at RBA 123456 with logdump to verify, if the PK update does not have the complete key information as described.
If that is the case and the target table does not have the corresponding PK entry, this issue is hit. Otherwise it is something different.

As a workaround use
FETCHOPTIONS FETCHPKUPDATECOLS on the capture/extract side to get all the after images of
the record so that when a HANDLECOLLISIONS logic kicks in, it will be
able to successfully convert the original PK update into insert with all the after image present.

Note: With OGG v12.2 and above following parameters should be used in the Extract instead of FETCHOPTIONS FETCHPKUPDATECOLS !!!
LOGALLSUPCOLS
UPDATERECORDFORMAT FULL

检查 OGG 配置文件
extract

GGSCI (qdyya2 as ggadmin@ANBOBa2) 7> view param EXT2

EXTRACT ext2
...
DISCARDFILE ./dirrpt/e.dsc, APPEND, MEGABYTES 200
TRANLOGOPTIONS LOGRETENTION DISABLED
TRANLOGOPTIONS _RawDeviceOffset 0
TRANLOGOPTIONS dblogreader
-- DBOPTIONS  ALLOWUNUSEDCOLUMN
FETCHOPTIONS NOUSESNAPSHOT
BR BRINTERVAL 30M
REPORTCOUNT EVERY 30 MINUTES, RATE
exttrail /ogg/ogg12/dirdat/eb, FORMAT RELEASE 12.2
FETCHOPTIONS FETCHPKUPDATECOLS   
dynamicresolution
GETUPDATEBEFORES
NOCOMPRESSUPDATES
NOCOMPRESSDELETES
--gettruncates

replicat

replicat R_YYA
...
DiscardFile ./dirrpt/r_yya.dsc, append, Megabytes 200
MaxDiscardRecs 10
REPERROR DEFAULT ABEND
DBOPTIONS NOSUPPRESSTRIGGERS
AllowNoopUpdates
ASSUMETARGETDEFS
CHECKSEQUENCEVALUE
Insertmissingupdates  
HandleCollisions    
GETTRUNCATES
BatchSQL

If ALLOWNOOPUPDATES is specified when the HANDLECOLLISIONS or INSERTMISSINGUPDATES parameters are being used, and if Oracle GoldenGate has all of the target key values, then Oracle GoldenGate will not ignore the update, but instead will apply it using all key columns in the SET clause and the WHERE clause (invoking APPLYNOOPUPDATES behavior). This is necessary so Oracle GoldenGate can detect if the row being updated is missing. If it is, then Oracle GoldenGate turns the update into an insert.

打赏

, , ,

目前这篇文章还没有评论(Rss)

我要评论