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                VALID

TEST01              PK_TEST                      TEST01           SA_TEST_INST   GROUP_ID                       VALID

TEST01              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进程恢复正常。