Oracle GoldenGate 之 MySQL -> MySQL 初始化同步數(shù)據(jù)

新手安裝配置請參考：在Windows中 基于Oracle GoldenGate （OGG）進行MySQL->MySQL數(shù)據(jù)庫同步配置（超詳細）

一、【源端】

1、配置MGR管理器進程：EDIT PARAMS MGR

GGSCI (SC-201708231409) 1> EDIT PARAMS MGR

2、填寫內(nèi)容

port 7809
dynamicportlist 7840-7939
autorestart extract *,waitminutes 2,retries 5

3、啟動MGR管理器進程：start mgr

GGSCI (SC-201708231409) 3> start mgr
Starting Manager as service ('OGGMGR')...
Service started.

4、查看MGR管理器進程：info mgr

GGSCI (SC-201708231409) 6> info mgr
Manager is running (IP port SC-201708231409.7809, Process ID 7584).

5、創(chuàng)建采集投遞進程：ADD EXTRACT EJWINIT, SOURCEISTABLE

GGSCI (SC-201708231409) 7> ADD EXTRACT EJWINIT, SOURCEISTABLE

6、查看創(chuàng)建的進程：INFO EXTRACT *, TASKS

GGSCI (SC-201708231409) 8> INFO EXTRACT *, TASKS
EXTRACT    EJWINIT   Last Started 2019-02-03 11:10   Status STOPPED
Checkpoint Lag       Not Available
Log Read Checkpoint  Table project.nbarankings2013
                     2019-02-03 11:10:17  Record 20
Task                 SOURCEISTABLE

7、編輯配置文件：EDIT PARAMS EJWINIT

GGSCI (SC-201708231409) 9> EDIT PARAMS EJWINIT

8、填寫內(nèi)容

EXTRACT EJWINIT
sourcedb project@127.0.0.1:3306 userid project password project
RMTHOST 172.16.94.37, MGRPORT 7809
RMTTASK REPLICAT, GROUP RJWINIT
TABLE project.nbarankings2013;

說明
sourcedb：源端數(shù)據(jù)庫連接信息
RMTHOST：目標端ip
MGRPORT：目標端ogg端口號
TABLE：同步的數(shù)據(jù)庫和表，表名可以使用通配符，如table project.*

9、啟動進程【注意：需在配置完目標端的接收進程后在執(zhí)行】：START EXTRACT EJWINIT

GGSCI (SC-201708231409) 10> START EXTRACT EJWINIT
Sending START request to MANAGER ('OGGMGR') ...
EXTRACT EJWINIT starting

10、查看運行情況 ：VIEW REPORT EJWINIT

GGSCI (SC-201708231409) 11> VIEW REPORT EJWINIT

2019-02-03 14:00:37  INFO    OGG-01017  Wildcard resolution set to IMMEDIATE because SOURCEISTABLE is used.

***********************************************************************
                  Oracle GoldenGate Capture for MySQL
      Version 12.3.0.1.2 OGGCORE_12.3.0.1.0_PLATFORMS_171208.0005
   Windows x64 (optimized), MySQL Enterprise on Dec  8 2017 11:57:03

Copyright (C) 1995, 2017, Oracle and/or its affiliates. All rights reserved.


                    Starting at 2019-02-03 14:00:37
***********************************************************************

Operating System Version:
Microsoft Windows 10, on x64
Version 10.0 (Build 14393)

Process id: 8152

Description:

***********************************************************************
**            Running with the following parameters                  **
***********************************************************************

2019-02-03 14:00:37  INFO    OGG-03059  Operating system character set identified as GBK.

2019-02-03 14:00:37  INFO    OGG-02695  ANSI SQL parameter syntax is used for parameter parsing.

2019-02-03 14:00:37  INFO    OGG-01360  EXTRACT is running in Initial Load mode.

2019-02-03 14:00:37  INFO    OGG-01889  Flush size (max message size) is set to 27,985.
EXTRACT EJWINIT
sourcedb project@172.16.94.14:3306 userid project password ***
RMTHOST 172.16.94.37, MGRPORT 7809
RMTTASK REPLICAT, GROUP RJWINIT
TABLE project.nbarankings2013;

2019-02-03 14:00:38  INFO    OGG-01851  filecaching started: thread ID: 57860608.

2019-02-03 14:00:38  INFO    OGG-01815  Virtual Memory Facilities for: COM
    anon alloc: MapViewOfFile  anon free: UnmapViewOfFile
    file alloc: MapViewOfFile  file free: UnmapViewOfFile
    target directories:
    E:\OGG\oggmsql\dirtmp.
-- More  --

二、【目標端】

1、配置MGR管理器進程：EDIT PARAMS MGR

GGSCI (DESKTOP-LDTMM0I) 1> EDIT PARAMS MGR

2、填寫內(nèi)容

port 7809
dynamicportlist 7840-7939
autorestart er *, retries 5, waitminutes 3
purgeoldextracts D:\OGG\oggmsql\dirdat*,usecheckpoints, minkeepdays 2
ACCESSRULE, PROG *, IPADDR 172.16.94.14, ALLOW

3、啟動MGR管理器進程：start mgr

GGSCI (DESKTOP-LDTMM0I) 2> start mgr
Starting Manager as service ('OGGMGR')...
Service started.

4、查看MGR管理器進程：info mgr

GGSCI (DESKTOP-LDTMM0I) 3> info mgr
Manager is running (IP port DESKTOP-LDTMM0I.7809, Process ID 10488).

5、創(chuàng)建接收進程：add replicat RJWINIT,specialrun

GGSCI (DESKTOP-LDTMM0I) 4> add replicat RJWINIT,specialrun

6、查看創(chuàng)建的進程：INFO REPLICAT *, TASKS

GGSCI (DESKTOP-LDTMM0I) 5> INFO REPLICAT *, TASKS

REPLICAT   RJWINIT   Initialized   2019-02-03 10:07   Status STOPPED
Checkpoint Lag       00:00:00 (updated 03:59:44 ago)
Log Read Checkpoint  Not Available
Task                 SPECIALRUN

7、編輯配置文件：edit params RJWINIT

GGSCI (DESKTOP-LDTMM0I) 6> edit params RJWINIT

8、填寫內(nèi)容

REPLICAT RJWINIT
targetdb project_target@127.0.0.1:3306 userid projectTarget password projectTarget
ASSUMETARGETDEFS
DISCARDFILE D:\OGG\oggmsql\dirrpt\RJWINIT.dsc, PURGE
MAP project.NBArankings2013, TARGET project_target.NBArankings2013;

說明
targetdb：目標端數(shù)據(jù)庫連接信息
DISCARDFILE：初始化文件存放位置
MAP：源端需要同步的表
TARGET：目標端相對應的備份表
表名可以使用通配符，如MAP project.* , target ggs.*

9、啟動應用進程：START REPLICAT RJWINIT

GGSCI (custom04) 7> START REPLICAT RJWINIT

注意：只需要在首次配置時執(zhí)行本操作，之后將由源端投遞進程自動調(diào)用

10、查看運行情況 ：VIEW REPORT RJWINIT

GGSCI (DESKTOP-LDTMM0I) 8> VIEW REPORT RJWINIT

***********************************************************************
                 Oracle GoldenGate Delivery for MySQL
      Version 12.3.0.1.2 OGGCORE_12.3.0.1.0_PLATFORMS_171208.0005
   Windows x64 (optimized), MySQL Enterprise on Dec  8 2017 12:31:57

Copyright (C) 1995, 2017, Oracle and/or its affiliates. All rights reserved.


                    Starting at 2019-02-03 14:00:38
***********************************************************************

Operating System Version:
Microsoft Windows 10, on x64
Version 10.0 (Build 10586)

Process id: 11764

Description:

***********************************************************************
**            Running with the following parameters                  **
***********************************************************************

2019-02-03 14:00:44  INFO    OGG-03059  Operating system character set identified as GBK.

2019-02-03 14:00:44  INFO    OGG-02695  ANSI SQL parameter syntax is used for parameter parsing.

2019-02-03 14:00:44  INFO    OGG-01360  REPLICAT is running in Remote Task mode.REPLICAT RJWINIT
targetdb project_target@127.0.0.1:3306 userid projectTarget password ***
ASSUMETARGETDEFS
DISCARDFILE D:\OGG\oggmsql\dirrpt\RJWINIT.dsc, PURGE
MAP project.NBArankings2013, target project_target.NBArankings2013;

2019-02-03 14:00:44  INFO    OGG-01815  Virtual Memory Facilities for: COM
    anon alloc: MapViewOfFile  anon free: UnmapViewOfFile
    file alloc: MapViewOfFile  file free: UnmapViewOfFile
    target directories:
    D:\OGG\oggmsql\dirtmp.

Database Version:
MySQL
Server Version: 8.0.13
Client Version: 5.6.14
Host Connection: 127.0.0.1 via TCP/IP
Protocol Version: 10

Database Language and Character Set:
CLIENT CHARACTER SET         = "gbk"
SERVER CHARACTER SET         = "utf8mb4"
DATABASE CHARACTER SET       = "utf8mb4"
-- More  --

三、注意事項

1、 在大量數(shù)據(jù)初始化的時候，注意目標端的歸檔日志空間大小。如果數(shù)據(jù)源為100G，理論上目標端的歸檔日志空間也至少大于100G才行。

如果歸檔日志滿了將會報錯：ORA-00257: archiver error. Connect internal only, until freed，解決方法：
https://blog.csdn.net/cw370008359/article/details/51023794
https://jingyan.baidu.com/article/29697b911dde4bab21de3c4d.html

2、需要注意的是目標端與源端的OGG版本最后保持一致。如何查看OGG版本？進入ggsci即可查看：
image.png
3、數(shù)據(jù)表中無主鍵需要在目標端設置：APPLYNOOPUPDATES。

該參數(shù)的含義就是不忽略出現(xiàn)的NO-OP操作，而是在目標數(shù)據(jù)庫強制執(zhí)行UPDATE修改操作，且只更新鍵值數(shù)據(jù)（主鍵或KEYCOLS指定的鍵值），如果表沒有主鍵，也沒有使用KEYCOLS關鍵字指定字段，那么將更新表數(shù)據(jù)的所有字段，如果沒有相應記錄，將INSERT新插入一條。

4、在操作ggsci時，一定要注意權(quán)限組是否一致，否則導致mgr等進程無法查看正常的運行狀態(tài)，也不能啟動停止。

ERROR：opening port for MGR MGR (Connection refused).

image.png

相關解釋：http://blog.itpub.net/29047826/viewspace-1808538/

5、OGG-01194錯誤

OGG-01194 Oracle GoldenGate Capture for Oracle, ext_sc0.prm: EXTRACT task REP_SC0 abended : File InitialLoad, with compatibility level 6, is not compatible with the current software version's compatibility level of 5. Modify the file writer's parameter file to generate the appropriate format using the FORMAT LEVEL 5 option.

在抽取進程中設置：

RMTTASK REPLICAT, GROUP REP_SC0,FORMAT LEVEL 5

或

EXTTRAIL /OGG/dirdat/e1,FORMAT LEVEL 5

還有一種情況，如果目標端是低版本，將不會支持FORMAT。所有你還是將版本更換一致最佳。

四、GoldenGate 相關資料

最后編輯于 ：
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時請結(jié)合常識與多方信息審慎甄別。
平臺聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點，簡書系信息發(fā)布平臺，僅提供信息存儲服務。

相關閱讀更多精彩內(nèi)容

友情鏈接更多精彩內(nèi)容