一、问题现象
源端是DB11.2.0.4 OGG 12.1, 目标端DB 11.2.0.4 OGG19.1 复制进程报错 2021-06-08 01:42:44 WARNING OGG-02904 Replication of PARTIAL XML containing NCHAR/NVARCHAR/NCLOB data may cause divergence. 2021-06-08 01:42:57 WARNING OGG-06439 No unique key is defined for table B1. All viable columns will be used to represent the key, but may
not guarantee uniqueness. KEYCOLS may be used to define the key. 2021-06-08 01:42:57 ERROR OGG-00423 Could not find definition for SCOTT.B1. 2021-06-08 01:43:02 INFO OGG-02333 Reading /u01/ogg/base/dirdat/b1/b1000000, current RBA 1,425, 0 records, m_file_seqno = 0,
m_file_rba = 1,589. 很神奇的就是源端OGG 12.2,参数一样的情况下,ogg12.2没毛病，但是12.1报错了！！！
报错提示表的定义找不到！！！
二、报错分析
2.1 OGG SHOWSYNTAX方法
GGSCI (t1) 2> edit param rep_b1 SHOWSYNTAX 把replicat 转换成SQL
检查日志没有输出SQL，也就是说OGG还没有到执行SQL的程度，进程就报错终止了！
2.2 Logdump
GGSCI (t1) 2> info rep_b1 REPLICAT REP_B1 Last Started 2021-06-08 01:51 Status ABENDED INTEGRATED Checkpoint Lag 00:00:00 (updated 09:05:48 ago) Log Read Checkpoint File /u01/ogg/base/dirdat/b1/b1000000 First Record RBA 0 [ogg@t1 base]$ ./logdump Oracle GoldenGate Log File Dump Utility for Oracle Version 19.1.0.0.4 OGGCORE_19.1.0.0.0_PLATFORMS_191017.1054 Copyright (C) 1995, 2019, Oracle and/or its affiliates. All rights reserved. Logdump 1 >open /u01/ogg/base/dirdat/b1/b1000000 Current LogTrail is /u01/ogg/base/dirdat/b1/b1000000 Logdump 2 >n 2021/06/07 23:49:01.199.038 FileHeader Len 1417 RBA 0 Name: *FileHeader* 3000 0327 3000 0008 4747 0d0a 544c 0a0d 3100 0002 | 0..'0...GG..TL..1... 0004 3200 0004 2000 0000 3300 0008 02f2 ea6f 0254 | ..2... ...3......o.T 8abe 3400 001d 001b 7572 693a 7773 6777 323a 3a75 | ..4.....uri:wsgw2::u 3031 3a6f 6767 3132 313a 4450 5f42 3135 0000 2235 | 01:ogg121:DP_B15.."5 0000 1e00 1c75 7269 3a77 7367 7732 3a3a 7530 313a | .....uri:wsgw2::u01: 6f67 6731 3231 3a45 5854 5f42 3136 0000 2200 202f | ogg121:EXT_B16..". / 7530 312f 6f67 672f 6261 7365 2f64 6972 6461 742f | u01/ogg/base/dirdat/ Logdump 3 >n 2021/06/07 16:48:57.726.771 Delete Len 44 RBA 1425 Name: SCOTT.B1 Before Image: Partition x04 G s 0000 000a 0000 0000 0000 0000 0014 0001 000c 0000 | .................... 0008 5245 5345 4152 4348 0002 000a 0000 0006 4441 | ..RESEARCH........DA 4c4c 4153 | LLAS 对比一下,上面的是源端OGG 12.1的trail文件，可以发现没有Table的元数据,直接从FileHeader跳到了DML语句修改！
如下是源端OGG12.2的Trail文件,对比一下正常多了,FileHeader到DML之间存在MetaData元数据！ Logdump 1 >open /u01/ogg/base/dirdat/a1/a1000000002 Current LogTrail is /u01/ogg/base/dirdat/a1/a1000000002 Logdump 2 >n 2021/06/07 22:38:17.603.104 FileHeader Len 1418 RBA 0 Name: *FileHeader* 3000 032a 3000 0008 4747 0d0a 544c 0a0d 3100 0002 | 0..*0...GG..TL..1... 0005 3200 0004 2000 0000 3300 0008 02f2 ea6e 0564 | ..2... ...3......n.d 6820 3400 001d 001b 7572 693a 7773 6777 323a 3a75 | h 4.....uri:wsgw2::u 3031 3a6f 6767 3132 323a 4450 5f41 3135 0000 2235 | 01:ogg122:DP_A15.."5 0000 1e00 1c75 7269 3a77 7367 7732 3a3a 7530 313a | .....uri:wsgw2::u01: 6f67 6731 3232 3a45 5854 5f41 3136 0000 2500 232f | ogg122:EXT_A16..%.#/ 7530 312f 6f67 672f 6261 7365 2f64 6972 6461 742f | u01/ogg/base/dirdat/ Logdump 3 >n 2021/06/07 15:38:06.903.447 RestartAbend Len 0 RBA 1426 Name: After Image: Partition x00 G s Logdump 4 >n 2021/06/07 22:40:32.721.130 Metadata Len 79 RBA 1487 Database Name: 3000 004b 0100 0002 0001 0200 0037 0100 0004 0000 | 0..K.........7...... 0000 0200 0004 0000 0001 0300 0002 0000 0400 0005 | .................... 0003 474d 5405 0000 1400 0000 1014 1414 1414 1414 | ..GMT............... 1414 1414 1411 1414 1403 0000 0600 0457 5347 57 | ...............WSGW Logdump 5 >n 2021/06/07 22:40:32.721.131 Metadata Len 619 RBA 1617 Table Name: SCOTT.A1 3040 0000 0265 0100 0002 0002 0200 001e 0100 0006 | 0@...e.............. 0100 0002 0008 0200 0004 0000 0000 0300 0002 0000 | .................... 0400 0002 0000 0340 0000 0237 0008 0045 0033 0005 | .......@...7...E.3.. 454d 504e 4f00 8600 0000 0800 0000 0800 0000 0800 | EMPNO............... 0000 0800 0000 0000 0000 00ff ffff ff00 0200 0300 | .................... 0000 0000 0000 0000 2300 0e00 0000 0000 0000 0000 | ........#........... 0000 0000 0000 4500 3300 0545 4e41 4d45 0040 0000 | ......E.3..ENAME.@.. Logdump 6 >n 2021/06/07 15:40:30.729.194 Delete Len 124 RBA 2295 Name: SCOTT.A1 (TDR Index: 1) Before Image: Partition x0c G s 0000 000a 0000 0000 0000 0000 1d4b 0001 0009 0000 | .............K...... 0005 414c 4c45 4e00 0200 0c00 0000 0853 414c 4553 | ..ALLEN........SALES 4d41 4e00 0300 0a00 0000 0000 0000 001e 1200 0400 | MAN................. 1500 0031 3938 312d 3032 2d32 303a 3030 3a30 303a | ...1981-02-20:00:00: 3030 0005 000a 0000 0000 0000 0002 7100 0006 000a | 00............q..... 0000 0000 0000 0000 7530 0007 000a 0000 0000 0000 | ........u0.......... 0000 001e | ....
2.3 参数assumetargetdefs
复制进程添加参数
assumetargetdefs
查询官方文档的含义！
https://docs.oracle.com/en/middleware/goldengate/core/19.1/reference/assumetargetdefs.html#GUID-A64DC16B-B554-4FD9-A2E0-B5C3A1345C13
Valid For
Replicat for trail file formats prior to 12c (12.2.0.1)
在12.2.0.1之前的Trail文件格式
Description
说明,使用这个参数则源端与目标端表结构一致,对于源表结构与目标端表结构不一致的环境请使用SOURCEDEFS,两个参数不要同时使用。
Use the ASSUMETARGETDEFS parameter when the source and target objects specified in a MAP statement have identical column structure, such as when synchronizing a hot site. It directs Oracle GoldenGate to assume that the data definitions of the source and target objects are identical, and to refer to the target definitions when metadata is needed for the source data.
When source and target tables have dissimilar structures, do not use ASSUMETARGETDEFS. Create a data-definitions file for the source object, and specify the definitions file with the SOURCEDEFS parameter. See SOURCEDEFS for more information. Do not use ASSUMETARGETDEFS and SOURCEDEFS in the same parameter file.
ASSUMETARGETDEFS [OVERRIDE]
OVERRIDE
By default, the table definitions from the metadata records override the definitions from any ASSUMETARGETDEFS file.
Specify OVERRIDE to request Replicat to use the definitions from the target database as the definitions for the trail records.
配置这个参数后，复制进程启动正常，问题解决。
三、报错相关的文档信息
3.1 OGG12.2 Mysql->oracle OGG 11.2
No Data Is Replicating Or ERROR OGG-00423 Could Not Find Definition For table Name (Doc ID 2290299.1) Oracle GoldenGate - Version 11.2.1.0.22 to 11.2.1.0.21 [Release 11.2] Using OGG version 12.2, for MySQL 5.6, replicat to an Oracle 11.2 database running GG 11.2, replicat does the following (1) When using a SOURCEDEF parm you get this error ERROR OGG-00423 Could not find definition for (2) When you remove the SOURCEDEF parm The replicat works but nothing get replicated. CAUSE defgen file not generated with format 11.2 parm map and target not in upper case (1) Change map and target with UPPER case as below. Example : MAP table, TARGET schema.target; (2) Run DEFGEN again with FORMAT 11.2 and use in the target side
符合上述版本条件的，mysql ->oracle,使用Sourcedef报错OGG-00423, 删除Sourcedef则复制进程没有数据
参数使用大写，参照上述举例； 另外一个主要的问题是源端OGG DEFGEN版本高,需要FORMAT 11.2降低版本重新创建一个DEF文件！！！
3.2 OGG Upgrade 12.1->12.3
Replictat abends with error " ERROR OGG-00423 Could not find definition " (Doc ID 2742349.1) APPLIES TO: Oracle GoldenGate - Version 12.3.0.1.4 to 12.3.0.1.4 [Release 12.3] Information in this document applies to any platform. SYMPTOMS After upgrade ggs for Mysql from 12.1 to 12.3. And tan, I upgraded ggs for Oracle to 12.3 too. And tan, I started replicate on Oracle side.
I saw this error:" ERROR OGG-00423 Could not find definition for ABC.EMP" CAUSE The issue with reading metadata from the trail file SOLUTION The issue fixed when added trail format 12.3 at the extract parameter file.
参数文件中添加trail format 12.3
3.3 本次处理匹配的MOS文档,破案了
OGG Replicat abends with Error OGG-00423 Could Not Find Definition For {Table} (Doc ID 1309972.1) Oracle GoldenGate - Version 9.5.0.0 and later SYMPTOMS OGG replicat process abends with the following error message: Error OGG-00423 Could Not Find Definition For <Table> CAUSE The error is caused by replicat not being able to determine the target table structure OGG replicat requires the source and target metadata to deliver the DB operations SOLUTION To resolve this error add either of these parameters to the Replicat parameter file prior to the MAP parameter statement: Use the SOURCEDEFS parameter to specify the name of a definitions file on the target system that contains the definition of the source tables.
The SOURCEDEFS parameter must precede the MAP statements in the Replicat parameter file.Verify that the table in question is included in the
Definitions file. Use the ASSUMETARGETDEFS parameter when the source and target tables specified with a MAP statement have identical column structure.
It directs GoldenGate not to look up source structures from a source-definitions file. For structures to be identical,
they must contain identical column names (including case, if applicable) and data types, and they must appear in the same order in each table.
This parameter must preceded the MAP statements in the Replicat parameter file
ASSUMETARGETDEFS参数。
它指示GoldenGate不要从源定义文件中查找源结构。对于相同的结构，
它们必须包含相同的列名（包括大小写，如果适用）和数据类型，并且在每个表中必须以相同的顺序出现。
此参数必须在Replicat参数文件中的MAP语句之前
3.4 序列报错OGG-00423
我们使用defgen提取对象定义，并在目标端使用生成的文件。如果序列已映射但不是定义文件的一部分，则会出现错误：OGG-00423
Sequence is mapped but was not part of the definitions file, we get an error: OGG-00423 (Doc ID 2045265.1) APPLIES TO: Oracle GoldenGate - Version 12.1.2.1.2 and later GOAL We use defgen to extract objects definitions and use the generated file on the target side. If sequence is mapped but was not part
of the definitions file, we get an error: OGG-00423 SOLUTION
对于序列，不必使用defgen，请遵循/参考以下注释以实现序列 One doesn't have to use defgen for sequences, please follow/refer below notes for sequence implementation: How To Replicat SEQUENCE In Oracle GoldenGate? (Doc ID 1532804.1) Implementing replication of cyclic sequences in GoldenGate (Doc ID 1321558.1) How to use FLUSH SEQUENCE ? (Doc ID 1477974.1) Also keep all the objects which doesn't need translation under "ASSUMETARGETDEFS" and all other under "SOURCEDEFS ./dirdef/XXXX1a.def " ASSUMETARGETDEFS
重点：可以把序列的复制放到前面,在序列的后面是有SOURECEDEFS参数,最后是表的同步！！！ --SEQUENCES MAP SOURCE.XXX_SEQ,TARGET TARGET.XXX_SEQ; MAP SOURCE.YYY_SEQ,TARGET TARGET.YYY_SEQ; MAP SOURCE.ZZZ_SEQ,TARGET TARGET.ZZZ_SEQ; SOURCEDEFS ./dirdef/qhis1a.def MAP SOURCE.XXX_AUDIT_LOG,TARGET TARGET.XXX_AUDIT_LOG; MAP SOURCE.XXX_COLUMNS,TARGET TARGET.XXX_COLUMNS; MAP SOURCE.XXX_CONSTANT_MAP,TARGET TARGET.XXX_CONSTANT_MAP; MAP SOURCE.XXX_DATABASES,TARGET TARGET.XXX_DATABASES; MAP SOURCE.XXX_DST_RULE,TARGET TARGET.XXX_DST_RULE; MAP SOURCE.XXX_EDITS,TARGET TARGET.XXX_EDITS; REFERENCES NOTE:1532804.1 - How To Replicat SEQUENCE In Oracle GoldenGate? NOTE:1477974.1 - How to use FLUSH SEQUENCE ? NOTE:1321558.1 - Implementing replication of cyclic sequences in GoldenGate
3.5 DB2有兴趣的看下~
Replicat Abend - ERROR OGG-00423 Could Not Find Definition For $VOL.SUBVOL.FILE (Nsk To Opensys) (Doc ID 2516986.1) Doing replication from Nsk to Db2 and the replicat in Db2 abends with the following error when deali CAUSE The output of the DEFGEN has a hardcoded HP NSK filename. In the case of the BASE24 TLF/PTLF/RTLF files, the filenames changes every day and so defgen does not match the filename and so the abend SOLUTION Use RECORDNAMEPROMPTING to trigger DEFGEN to prompt for the name of an existing record definition instead of a file. Use this parameter to enter the record name when the same definition is to be used for multiple tables with identical definitions made
up of the same columns, column order, and data types. Must precede the EXPANDDDL argument. Now you also need to have the DEF name to the map in the target For ex: run defgen RECORDNAMEPROMPTING EXPANDDDL RESOLVEDUPGROUP OMITREDEFS Now in the target replicat specify the new SOURCEDEFS and use DEF in the MAP