@源端
GGSCI (rhlinux) 3> view params ext_1
extract ext_1
userid ogg,password ogg
exttrail ./dirdat/e1
table scott.emp;
table scott.dept;
table scott.t;
table scott.tv;
table scott.t2;
GGSCI (rhlinux) 4> view params pump_1
extract pump_1
userid ogg ,password ogg
rmthost 192.168.73.134, mgrport 7811
rmttrail ./dirdat/p1
--passthru
table scott.emp;
table scott.t;
table scott.dept;
table scott.tv;
table scott.t2;
@目标端
GGSCI (ogg) 2> view params rep_1
replicat rep_1
userid ogg,password ogg
assumetargetdefs
discardfile /opt/ogg/discard/e1.log,append,megabytes 10
handlecollisions
map scott.emp , target scott.emp;
map scott.dept, target scott.dept;
map scott.t,target scott.t;
map scott.t2,target scott.t2;
scott.t是通过dba_users创建的，user_id是主键。
源端数据为：
SQL> select user_id from scott.t;
no rows selected
目标端数据为：
SQL> select user_id from scott.t;
USER_ID
----------
0
5
1.源端增加数据，目标端发现相同主键数据。如果目标端有handlecollisions参数，不报错；否则报错
GGSCI (ogg) 3> stats rep_1,hourly
Sending STATS request to REPLICAT REP_1 ...
Start of Statistics at 2015-07-19 01:21:05.
Replicating from SCOTT.T to SCOTT.T:
*** Hourly statistics since 2015-07-19 01:00:00 ***
Total inserts 38.00
Total updates 0.00
Total deletes 0.00
Total discards 0.00
Total operations 38.00
Total insert collisions 2.00 --有两条冲突的，未插入
End of Statistics.
2.源端更新数据，目标端数据主键相同，但其他字段不同，可以正常更新：
@源端
SQL> select username ,user_id from scott.t where user_id=0;
USERNAME USER_ID
------------------------------ ----------
SYS 0
@目标端
SQL> select username ,user_id from scott.t where user_id=0;
USERNAME USER_ID
------------------------------ ----------
SYS1 0
@源端
SQL> update scott.t set username=username||'a';
38 rows updated.
SQL> commit;
Commit complete.
SQL> select username ,user_id from scott.t where user_id=0;
USERNAME USER_ID
------------------------------ ----------
SYSa 0
@目标端
SQL> select username ,user_id from scott.t where user_id=0;
USERNAME USER_ID
------------------------------ ----------
SYSa 0
GGSCI (ogg) 4> stats rep_1,hourly
Sending STATS request to REPLICAT REP_1 ...
Start of Statistics at 2015-07-19 01:23:59.
Replicating from SCOTT.T to SCOTT.T:
*** Hourly statistics since 2015-07-19 01:00:00 ***
Total inserts 38.00
Total updates 38.00 --新增38条更新
Total deletes 0.00
Total discards 0.00
Total operations 76.00
Total insert collisions 2.00
End of Statistics.
3.源端删除数据，目标端不存在该数据，不报错
@目标端
SQL> delete from scott.t where user_id=5;
1 row deleted.
SQL> commit;
@源端
SQL> select username ,user_id from scott.t where user_id=5;
USERNAME USER_ID
------------------------------ ----------
SYSTEMa 5
SQL> delete from scott.t where user_id=5;
1 row deleted.
SQL> commit;
Commit complete.
@目标端
GGSCI (ogg) 5> stats rep_1,hourly
Sending STATS request to REPLICAT REP_1 ...
Start of Statistics at 2015-07-19 01:33:08.
Replicating from SCOTT.T to SCOTT.T:
*** Hourly statistics since 2015-07-19 01:00:00 ***
Total inserts 38.00
Total updates 38.00
Total deletes 1.00
Total discards 0.00
Total operations 77.00
Total insert collisions 2.00
Total delete collisions 1.00
End of Statistics.
--------------------------------------------------------------------------------
二、编辑目标端，去掉handlecollisions
GGSCI (ogg) 7> edit params rep_1
GGSCI (ogg) 8> stop rep_1
Sending STOP request to REPLICAT REP_1 ...
Request processed.
GGSCI (ogg) 9> start rep_1
Sending START request to MANAGER ...
REPLICAT REP_1 starting
GGSCI (ogg) 10> view params rep_1
replicat rep_1
userid ogg,password ogg
assumetargetdefs
discardfile /opt/ogg/discard/e1.log,append,megabytes 10
map scott.emp , target scott.emp;
map scott.dept, target scott.dept;
map scott.t,target scott.t;
map scott.t2,target scott.t2;
0.源端和目标端清除scott.t数据，目标端插入一条
SQL> delete from scott.t;
37 rows deleted.
SQL> commit;
Commit complete.
SQL> select * from scott.t;
no rows selected
@目标端
insert into scott.t select * from dba_users where user_id=0;
commit;
1.源端插入数据，目标端存在相同主键的数据
@源端
insert into scott.t select * from dba_users;
commit;
@目标端
rep_1进程abend，ggserr.log中报错为：
2015-07-19 01:39:30 WARNING OGG-00869 Oracle GoldenGate Delivery for Oracle, rep_1.prm: OCI Error ORA-00001: unique constraint (SCOTT.PK_T) violated (status = 1). INSERT INTO "SCOTT"."T" ("USERNAME","USER_ID","PASSWORD","ACCOUNT_STATUS","LOCK_DATE","EXPIRY_DATE","DEFAULT_TABLESPACE","TEMPORARY_TABLESPACE","CREATED","PROFILE","INITIAL_RSRC_CONSUMER_GROUP","EXTERNAL_NAME","PASSWORD_VERSIONS","EDITIONS_ENABLED","AUTHENTICATION_TYPE") VALUES (:a0,:a1,:a2,:a3,:a4,:a5,:a6,:a7,:a8,:a9,:a10,:a11,:a12,:a13,:a14).
2015-07-19 01:39:30 WARNING OGG-01004 Oracle GoldenGate Delivery for Oracle, rep_1.prm: Aborted grouped transaction on 'SCOTT.T', Database error 1 (OCI Error ORA-00001: unique constraint (SCOTT.PK_T) violated (status = 1). INSERT INTO "SCOTT"."T" ("USERNAME","USER_ID","PASSWORD","ACCOUNT_STATUS","LOCK_DATE","EXPIRY_DATE","DEFAULT_TABLESPACE","TEMPORARY_TABLESPACE","CREATED","PROFILE","INITIAL_RSRC_CONSUMER_GROUP","EXTERNAL_NAME","PASSWORD_VERSIONS","EDITIONS_ENABLED","AUTHENTICATION_TYPE") VALUES (:a0,:a1,:a2,:a3,:a4,:a5,:a6,:a7,:a8,:a9,:a10,:a11,:a12,:a13,:a14)).
2015-07-19 01:39:30 WARNING OGG-01003 Oracle GoldenGate Delivery for Oracle, rep_1.prm: Repositioning to rba 24279 in seqno 2.
2015-07-19 01:39:30 WARNING OGG-01154 Oracle GoldenGate Delivery for Oracle, rep_1.prm: SQL error 1 mapping SCOTT.T to SCOTT.T OCI Error ORA-00001: unique constraint (SCOTT.PK_T) violated (status = 1). INSERT INTO "SCOTT"."T" ("USERNAME","USER_ID","PASSWORD","ACCOUNT_STATUS","LOCK_DATE","EXPIRY_DATE","DEFAULT_TABLESPACE","TEMPORARY_TABLESPACE","CREATED","PROFILE","INITIAL_RSRC_CONSUMER_GROUP","EXTERNAL_NAME","PASSWORD_VERSIONS","EDITIONS_ENABLED","AUTHENTICATION_TYPE") VALUES (:a0,:a1,:a2,:a3,:a4,:a5,:a6,:a7,:a8,:a9,:a10,:a11,:a12,:a13,:a14).
2015-07-19 01:39:30 WARNING OGG-01003 Oracle GoldenGate Delivery for Oracle, rep_1.prm: Repositioning to rba 24279 in seqno 2.
2015-07-19 01:39:30 ERROR OGG-01296 Oracle GoldenGate Delivery for Oracle, rep_1.prm: Error mapping from SCOTT.T to SCOTT.T.
2015-07-19 01:39:30 ERROR OGG-01668 Oracle GoldenGate Delivery for Oracle, rep_1.prm: PROCESS ABENDING.
处理方法很多，包括：
1）如果此类问题不会再出现，或者希望出现类似问题时停止同步并人工处理，则在目标端删除该条数据
2）如果希望忽略此类问题，则rep_1的参数文件中添加：
REPERROR (-1,DISCARD)
3）如果仅希望忽略该表的此类问题，则可以放入例外表，如
map scott.tl,target scott.tl;
map scott.tl,target scott.tl_exception,exceptionsonly
GGSCI (ogg) 2> view params rep_1
replicat rep_1
userid ogg,password ogg
assumetargetdefs
reperror(DEFAULT, EXCEPTION)
discardfile /opt/ogg/discard/e1.log,append,megabytes 10
map scott.emp , target scott.emp;
map scott.dept, target scott.dept;
map scott.t,target scott.t;
map scott.t,target scott.t_exception,exceptionsonly;
map scott.t2,target scott.t2;
或者
MAP scott.tl,target scott.tl,
MAPEXCEPTION (TARGET scott.t_exceptions); --实验未成功
2.更新不存在的数据
目标端报错，进程abend：
2015-07-19 04:33:19 WARNING OGG-01004 Oracle GoldenGate Delivery for Oracle, rep_1.prm: Aborted grouped transaction on 'SCOTT.T', Database error 1403 (OCI Error ORA-01403: no data found, SQL <UPDATE "SCOTT"."T" SET "USERNAME" = :a0 WHERE "USER_ID" = :b0>).
2015-07-19 04:33:19 WARNING OGG-01003 Oracle GoldenGate Delivery for Oracle, rep_1.prm: Repositioning to rba 102740 in seqno 2.
2015-07-19 04:33:19 WARNING OGG-01154 Oracle GoldenGate Delivery for Oracle, rep_1.prm: SQL error 1403 mapping SCOTT.T to SCOTT.T OCI Error ORA-01403: no data found, SQL <UPDATE "SCOTT"."T" SET "USERNAME" = :a0 WHERE "USER_ID" = :b0>.
2015-07-19 04:33:19 WARNING OGG-01003 Oracle GoldenGate Delivery for Oracle, rep_1.prm: Repositioning to rba 102740 in seqno 2.
2015-07-19 04:33:19 ERROR OGG-01296 Oracle GoldenGate Delivery for Oracle, rep_1.prm: Error mapping from SCOTT.T to SCOTT.T.
2015-07-19 04:33:19 ERROR OGG-01668 Oracle GoldenGate Delivery for Oracle, rep_1.prm: PROCESS ABENDING.
删除不存在的数据结果一样。
可以指定reperror(1403,discard)将出错信息丢弃。
总结：
ogg中常见的报错是源端插入的数据在目标端存在（根据主键判断），报错ORA-00001;源端更新或删除数据时找不到数据，报错ORA-1403。
可以使用reperror进行过滤。
当使用handlecollisions参数时，此类报错会被忽略。
使用exceptionsonly可以将出错数据插入到其他表。
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/26451536/viewspace-1811761/ ，如需转载，请注明出处，否则将追究法律责任。
请登录后发表评论
登录
全部评论
