OGG源端和目標端資料不一致的場景
@源端
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部落格 ” ，連結：http://blog.itpub.net/26451536/viewspace-1811761/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Goldengate實驗：源端目標端字符集不同Go
- cdc目標端資料庫初始化（backup restore）資料庫REST
- 由於源資料和目標資料的字符集不一致，引起無法匯入的問題
- SQL Server資料庫遠端更新目標表資料的儲存過程SQLServer資料庫儲存過程
- 服務端指南 資料儲存篇 | 聊聊 MongoDB 使用場景服務端MongoDB
- 服務端指南 資料儲存篇 | 聊聊 Redis 使用場景服務端Redis
- 使用RMAN或資料泵初始化OGG目標庫
- ogg不停業務重新初始化目標資料庫流程資料庫
- ogg停止業務重新初始化目標資料庫流程資料庫
- 乾貨：不同場景容器內獲取客戶端源IP的方法客戶端
- 資料治理的目標和原則
- 3D點雲資料的標註方法和應用場景 | 景聯文科技3D
- 智慧醫療的主要應用場景和資料標註的關係 | 景聯文科技
- 得物前端喚端業務場景和技術精講前端
- RMAN遠端連線目標資料庫遭遇RMAN-06429錯誤資料庫
- IO終端+感測器資料採集方案可以應用在哪些場景
- pytorch實現yolov3(5) 實現端到端的目標檢測PyTorchYOLO
- 1.4 基於OGG單表到分庫分表資料同步場景
- 助力AI技術場景化落地 | 資料標註AI
- 資料應用場景之標籤管理體系
- 智慧安防的主要應用場景和資料採集標註解決方案 | 景聯文科技
- Salesforce開源TransmogrifAI：用於結構化資料的端到端AutoML庫SalesforceAITOML
- 拓端：資料把握電影市場需求
- NFS導致的目標端檔案系統不可讀NFS
- 微信終端開源資料庫 WCDB – Swift 版本資料庫Swift
- 微信終端開源資料庫 WCDB - Swift 版本資料庫Swift
- 源端停資料庫，抽取程式會abbend嗎？資料庫
- DataWorksV2.0新增資料來源典型問題場景
- C# 之 服務端獲取遠端資源C#服務端
- 通過資料庫鏈查詢會導致源和目標庫SCN同步資料庫
- 我對前後端資料模型和資料流的理解後端模型
- ORA-16009: 遠端歸檔日誌目標必須為備用資料庫資料庫
- DTS-071007表結構在源庫和目標庫中不一致
- Angular 伺服器端渲染場景裡，伺服器端和客戶端渲染出的 HTML 原始碼有可能不完全一致Angular伺服器客戶端HTML原始碼
- 拓端：二手交易市場資料快照
- 目標檢測兩種常用的資料集COCO和VOC
- 獲取遠端圖片的Blob資源
- 遠端備份資料庫和檔案的方法資料庫