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.
微信扫一扫，打赏作者吧～
对不起，这篇文章暂时关闭评论。