合 OGG-01296、OGG-06439、OGG-01169 表有主键或惟一键，但是仍然使用全部列来解析行
Tags： 故障处理OGG微服务OGG错误OGG-01296OGG-06439
👉 本文共约722个字，系统预计阅读时间或需3分钟。
现象
OGG的replicate进程报错，查看报告文件：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 | 2021-12-13 17:01:59 INFO OGG-06506 Wildcard MAP resolved (entry lhr.*): MAP "LHR"."ORDERS", TARGET "PDB2".lhr."ORDERS". 2021-12-13 17:02:04 WARNING OGG-06439 No unique key is defined for table ORDERS. All viable columns will be used to represent the key, but may not guarantee uniqueness. KEYCOLS may be used to define the key. 2021-12-13 17:02:04 INFO OGG-02756 The definition for table LHR.ORDERS is obtained from the trail file. 2021-12-13 17:02:04 INFO OGG-06511 Using following columns in default map by name: ORDER_ID, ORDER_DATE, ORDER_MODE, CUSTOMER_ID, ORDER_STATUS, ORDER_TOTAL, SALES_REP_ID, PROMOTION_ID, WAREHOUSE_ID, DELIVERY_TYPE, COST_OF_DELIVERY, WAIT_TILL_ALL_AVAILABLE, DELIVERY_ADDRESS_ID, CUSTOMER_CLASS, CARD_ID, INVOICE_ADDRESS_ID. 2021-12-13 17:02:04 INFO OGG-06510 Using the following key columns for target table PDB2.LHR.ORDERS: ORDER_ID, ORDER_DATE, ORDER_MODE, CUSTOMER_ID, ORDER_STATUS, ORDER_TOTAL, SALES_REP_ID, PROMOTION_ID, WAREHOUSE_ID, DELIVERY_TYPE, COST_OF_DELIVERY, WAIT_TILL_ALL_AVAILABLE, DELIVERY_ADDRESS_ID, CUSTOMER_CLASS, CARD_ID, INVOICE_ADDRESS_ID. 2021-12-13 17:02:04 INFO OGG-06074 Unique constraint columns used in dependency calculation for table PDB2.LHR.ORDERS: ORDER_ID. 2021-12-13 17:02:04 INFO OGG-06075 Referential constraint columns used in dependency calculation for table PDB2.LHR.ORDERS: CUSTOMER_ID. 2021-12-13 17:02:04 WARNING OGG-01431 Canceled grouped transaction on PDB2.LHR.ORDERS, Mapping error. 2021-12-13 17:02:04 WARNING OGG-01003 Repositioning to rba 2944 in seqno 7. 2021-12-13 17:02:04 WARNING OGG-01151 Error mapping from LHR.ORDERS to PDB2.LHR.ORDERS. Source Context : SourceModule : [er.replicat.errors] SourceID : [er/replicat/reperrors.cpp] SourceMethod : [repError] SourceLine : [1832] ThreadBacktrace : [15] elements : [/ogg213c/ogg_ma/bin/../lib/libgglog.so(CMessageContext::AddThreadContext())] : [/ogg213c/ogg_ma/bin/../lib/libgglog.so(CMessageFactory::CreateMessage(CSourceContext*, unsigned int, ...))] : [/ogg213c/ogg_ma/bin/../lib/libgglog.so(_MSG_QualTableName_QualTableName(CSourceContext*, int, ggs::gglib::ggapp::CQualDBObjName<(DBObjType)1> const&, ggs::gglib::ggapp::CQualDBObjName<(DBObjType)1> const&, CMessageFactory::MessageDisposition))] : [/ogg213c/ogg_ma/bin/replicat(ggs::er::ReplicatContext::repError(short, int, char const*, extr_ptr_def*, ggs::gglib::gglcr::CommonLCR const*, std_rec_hdr_def*, char*, ObjectMetadata*, bool))] : [/ogg213c/ogg_ma/bin/replicat()] : [/ogg213c/ogg_ma/bin/replicat(ggs::er::ReplicatContext::processRecord(ggs::gglib::gglcr::CommonLCR const*, ggs::gglib::gglcr::CommonLCR*, extr_ptr_def*&, extr_ptr_def*&, bool&, int&, bool, RepCsn&))] : [/ogg213c/ogg_ma/bin/replicat(ggs::er::ReplicatContext::processReplicatLoop())] : [/ogg213c/ogg_ma/bin/replicat(ggs::er::ReplicatContext::run())] : [/ogg213c/ogg_ma/bin/replicat()] : [/ogg213c/ogg_ma/bin/replicat(ggs::gglib::MultiThreading::MainThread::ExecMain())] : [/ogg213c/ogg_ma/bin/replicat(ggs::gglib::MultiThreading::Thread::RunThread(ggs::gglib::MultiThreading::Thread::ThreadArgs*))] : [/ogg213c/ogg_ma/bin/replicat(ggs::gglib::MultiThreading::MainThread::Run(int, char**))] : [/ogg213c/ogg_ma/bin/replicat(main)] : [/lib64/libc.so.6(__libc_start_main)] : [/ogg213c/ogg_ma/bin/replicat()] 2021-12-13 17:02:04 ERROR OGG-01296 Error mapping from LHR.ORDERS to PDB2.LHR.ORDERS. |
问题分析
日志中以下问题比较可疑：
1 2 3 4 5 6 7 8 | 2021-12-13 17:02:04 WARNING OGG-06439 No unique key is defined for table ORDERS. All viable columns will be used to represent the key, but may not guarantee uniqueness. KEYCOLS may be used to define the key. 2021-12-13 17:02:04 INFO OGG-02756 The definition for table LHR.ORDERS is obtained from the trail file. 2021-12-13 17:02:04 INFO OGG-06511 Using following columns in default map by name: ORDER_ID, ORDER_DATE, ORDER_MODE, CUSTOMER_ID, ORDER_STATUS, ORDER_TOTAL, SALES_REP_ID, PROMOTION_ID, WAREHOUSE_ID, DELIVERY_TYPE, COST_OF_DELIVERY, WAIT_TILL_ALL_AVAILABLE, DELIVERY_ADDRESS_ID, CUSTOMER_CLASS, CARD_ID, INVOICE_ADDRESS_ID. 2021-12-13 17:02:04 INFO OGG-06510 Using the following key columns for target table PDB2.LHR.ORDERS: ORDER_ID, ORDER_DATE, ORDER_MODE, CUSTOMER_ID, ORDER_STATUS, ORDER_TOTAL, SALES_REP_ID, PROMOTION_ID, WAREHOUSE_ID, DELIVERY_TYPE, COST_OF_DELIVERY, WAIT_TILL_ALL_AVAILABLE, DELIVERY_ADDRESS_ID, CUSTOMER_CLASS, CARD_ID, INVOICE_ADDRESS_ID. |
表LHR.ORDERS是有主键的，但是，OGG却不认！！
查询表的约束状态validated：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 | LHR@lhrsdb> select owner, constraint_name, constraint_type, status, validated from dba_constraints where owner='LHR' and VALIDATED='NOT VALIDATED'; OWNER CONSTRAINT_NAME CO STATUS VALIDATED ---------- ------------------------------------------------------------ -- ---------------- -------------------------- LHR ADD_CUST_FK R ENABLED NOT VALIDATED LHR ORDERS_CUSTOMER_ID_FK R ENABLED NOT VALIDATED LHR INVENTORIES_WAREHOUSES_FK R ENABLED NOT VALIDATED LHR INVENTORIES_PRODUCT_ID_FK R ENABLED NOT VALIDATED LHR ORDER_ITEMS_ORDER_ID_FK R ENABLED NOT VALIDATED LHR ORDER_ITEMS_PRODUCT_ID_FK R ENABLED NOT VALIDATED LHR CUSTOMER_CREDIT_LIMIT_MAX C ENABLED NOT VALIDATED LHR CUSTOMER_ID_MIN C ENABLED NOT VALIDATED LHR ORDER_MODE_LOV C ENABLED NOT VALIDATED LHR ORDER_TOTAL_MIN C ENABLED NOT VALIDATED LHR CUSTOMERS_PK P ENABLED NOT VALIDATED LHR ADDRESS_PK P ENABLED NOT VALIDATED LHR CARD_DETAILS_PK P ENABLED NOT VALIDATED LHR WAREHOUSES_PK P ENABLED NOT VALIDATED LHR ORDER_ITEMS_PK P ENABLED NOT VALIDATED LHR ORDER_PK P ENABLED NOT VALIDATED LHR PRODUCT_DESCRIPTIONS_PK P ENABLED NOT VALIDATED LHR INVENTORY_PK P ENABLED NOT VALIDATED 18 rows selected. |
发现有很多都是NOT VALIDATED，包括ORDER_PK。
问题解决
根据MOS No Unique Key Is Defined For Table - OGG-01296 Error mapping (Doc ID 2017835.1)可知，问题在于表的主键或惟一键处于“NOT VALIDATED”状态。
Verified that the table had primary and unique key but goldengate was still using all the columns to resolve the row.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 | select 'alter table '||TABLE_NAME||' enable validate constraint '||CONSTRAINT_NAME||';' from dba_constraints where owner='LHR1' and VALIDATED='NOT VALIDATED'; 'ALTERTABLE'||TABLE_NAME||'ENABLEVALIDATECONSTRAINT'||CONSTRAINT_NAME||';' ---------------------------------------------------------------------------------------- alter table INVENTORIES enable validate constraint INVENTORIES_WAREHOUSES_FK; alter table ORDER_ITEMS enable validate constraint ORDER_ITEMS_ORDER_ID_FK; alter table ADDRESSES enable validate constraint ADD_CUST_FK; alter table ORDERS enable validate constraint ORDERS_CUSTOMER_ID_FK; alter table INVENTORIES enable validate constraint INVENTORIES_PRODUCT_ID_FK; alter table ORDER_ITEMS enable validate constraint ORDER_ITEMS_PRODUCT_ID_FK; alter table CUSTOMERS enable validate constraint CUSTOMER_CREDIT_LIMIT_MAX; alter table CUSTOMERS enable validate constraint CUSTOMER_ID_MIN; alter table ORDERS enable validate constraint ORDER_MODE_LOV; alter table ORDERS enable validate constraint ORDER_TOTAL_MIN; alter table CUSTOMERS enable validate constraint CUSTOMERS_PK; alter table ADDRESSES enable validate constraint ADDRESS_PK; alter table CARD_DETAILS enable validate constraint CARD_DETAILS_PK; alter table WAREHOUSES enable validate constraint WAREHOUSES_PK; alter table ORDER_ITEMS enable validate constraint ORDER_ITEMS_PK; alter table ORDERS enable validate constraint ORDER_PK; alter table PRODUCT_DESCRIPTIONS enable validate constraint PRODUCT_DESCRIPTIONS_PK; alter table INVENTORIES enable validate constraint INVENTORY_PK; |
执行这些SQL语句，再重新启动replicate进程，问题得到解决！
本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信dbaup66，谢谢！
