使用goldengate的discard参数文件快速定位ORA-0001报错问题

1 发现OGG进程状态异常 GGSCI (host01) 3> info REPTEST REPLICAT   REPTEST  Last Started 2021-05-27 14:26   Status ABENDED Checkpoint Lag       00:00:05 (updated 02:43:37 ago) Log Read Checkpoint  File ./dirdat/yx/ya091301                      2021-05-27 11:42:42.001393  RBA 87058729 2  查看OGG的报错信息,发现违反唯一性约束 2021-05-27 12:46:00  WARNING OGG-01154  SQL error 1 mapping SOURCE.SA_TEST_INST to TEST01.SA_TEST_INST OCI Error ORA-00001: unique constraint ( TEST01.PK_TEST) violated (status = 1). INSERT INTO "TEST01"."SA_TEST_INST" ("MEMO3","MEMO2","MEMO1","BUSINESS_PROCESS_ID","BUSINESS_TYPE"," ORG_NO","INIT_TIME","BUSINESS_OBJ_ID","GROUP_ID","TOKEN_ID","SOURCE_CHANGE_TIME","TARGET_WRITE_TIME") VALUES (:a0,:a1,:a2,:a3,:a4,:a5,:a6,:a7,:a8,:a9,:a10,:a11). 2021-05-27 12:46:00  WARNING OGG-01003  Repositioning to rba 87058729 in seqno 91301. 3  编辑OGG进程参数，增加如下一行，并重新启动OGG进程discardfile ./dirrpt/yx/REPTEST_0527.dsc, append, megabytes 1000 ４查看产生的 REPTEST_0527.dsc文件内容 [oracle@host01 yx]$ more REPTEST_0527.dsc Oracle GoldenGate Delivery for Oracle process started, group REPTEST discard file opened: 2021-05-27 14:19:20 Current time: 2021-05-27 14:19:21 Discarded record from action ABEND on error 1 OCI Error ORA-00001: unique constraint (TEST01.PK_TEST) violated (status = 1). INSERT INTO "TEST01"."SA_TEST_INST" ("MEMO3","MEMO2","MEMO1" ,"BUSINESS_PROCESS_ID","BUSINESS_TYPE","ORG_NO","INIT_TIME","BUSINESS_OBJ_ID","GROUP_ID","TOKEN_I D","SOURCE_CHANGE_TIME","TARGET_WRITE_TIME") VALUES (:a0,:a1,:a2,:a3,:a4, :a5,:a6,:a7,:a8,:a9,:a10,:a11) Aborting transaction on ./dirdat/yx/ya beginning at seqno 91301 rba 87058729                          error at seqno 91301 rba 87058729 Problem replicating SOURCE.SA_TEST_INST to TEST01.SA_TEST_INST Mapping problem with insert record (target format)... * MEMO3 = NULL MEMO2 = NULL MEMO1 = NULL BUSINESS_PROCESS_ID = NULL BUSINESS_TYPE = 511 ORG_NO = 61102 INIT_TIME = 2021-05-27 11:42:42 BUSINESS_OBJ_ID = 1102212088     --报错时唯一索引变量的值 GROUP_ID = 511                                --报错时唯一索引变量的值 TOKEN_ID = 367650                           --报错时唯一索引变量的值 SOURCE_CHANGE_TIME = 2021-05-27 11:42:42 TARGET_WRITE_TIME = 2021-05-27 14:19:21 * 5 查看表创建索引对应的列，并在目标端查看是否唯一键重复INDEX_OWNER        INDEX_NAME        TABLE_OWNER        TABLE_NAME   COLUMN_NAME                    STATU------------------ ------------------------------------ ------------ ------------------------------ -----TEST01              PK_TEST                      TEST01           SA_TEST_INST   BUSINESS_OBJ_ID                VALIDTEST01              PK_TEST                      TEST01           SA_TEST_INST   GROUP_ID                       VALIDTEST01              PK_SA_TEST_INST       TEST01           SA_TEST_INST   TOKEN_ID                       VALID PK_SA_TEST_INST唯一索引没有重复值 SYS@host01 >select a.token_id from  TEST01.SA_TEST_INST a where a.token_id='367650'; no rows selected PK_TEST 唯一索引发现有重复，对应的主键为270305，可以确定就是由于重复值导致OGG报错 SYS@host01 >select a.token_id,a.GROUP_ID,a.BUSINESS_OBJ_ID  from  TEST01.SA_TEST_INST a where a.GROUP_ID='511' and a.BUSINESS_OBJ_ID='1102212088';   TOKEN_ID GROUP_ID     BUSINESS_OBJ_ID      ---------- ------------ --------------------     270305 511          1102212088       6 在源端查询对应的数据，发现源端只有367650的数据，没有270305 主键对应的数据，故认为目标端数据同源端数据不一致。可以删除270305主键相关的数据。 SQL> select a.token_id from  SOURCE.SA_TEST_INST a where a.token_id='367650';   TOKEN_ID ----------     367650 SQL>  select a.token_id,a.GROUP_ID,a.BUSINESS_OBJ_ID from SOURCE.SA_TEST_INST a where a.GROUP_ID='511' and a.BUSINESS_OBJ_ID='1102212088';      TOKEN_ID GROUP_ID         BUSINESS_OBJ_ID----------      ---------------- --------------------     367650        511              1102212088 7  备份目标端对应的数据，并删除。备份的数据相关的SQL： insert into TEST01.SA_TEST_INST (MEMO3, MEMO2, MEMO1, BUSINESS_PROCESS_ID, BUSINESS_TYPE, ORG_NO, INIT_TIME, BUSINESS_OBJ_ID, GROUP_ID, TOKEN_ID, SOURCE_CHANGE_TIME, TARGET_WRITE_TIME, SOURCE_CHANGE_TIME2, TARGET_WRITE_TIME2) values (null, null, null, null, '511', '61102', to_date('19-04-2018 11:56:59', 'dd-mm-yyyy hh24:mi:ss'), '1102212088', '511', 270305, to_date('19-04-2018 11:56:58', 'dd-mm-yyyy hh24:mi:ss'), to_date('19-04-2018 11:57:02', 'dd-mm-yyyy hh24:mi:ss'), null, null); 删除对应的数据： SYS@host01 >delete from TEST01.SA_TEST_INST a where a.GROUP_ID='511' and a.BUSINESS_OBJ_ID='1102212088'; 1 row deleted. SYS@host01 >commit; Commit complete. 8 启动OGG，OGG进程恢复正常。