server新手引导 sql_Oracle GoldenGate 之 SQL Server -> SQL Server 初始化同步数据

一、 ODBC 数据源设置

1、打开ODBC数据源应用

odbc 数据源

2、选择系统 DSN > 添加

添加系统数据源

3、创建新数据源，根据SQL Server的版本选择相应的驱动程序

image.png

4、注意 “名称” ： DSN001，填写的名称后面需要使用。

image.png

5、根据引导添加完成后，开始配置OGG

二、【源端】

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

GGSCI (custom04) 1> EDIT PARAMS MGR

2、填写内容

port 2000

dynamicportlist 2001-2005

autorestart extract *,waitminutes 2,retries 5

3、启动MGR管理器进程：start mgr

GGSCI (custom04) 2> start mgr

Manager started.

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

GGSCI (custom04) 3> info mgr

Manager is running (IP port Win10.2000, Process ID 13820).

5、创建采集投递进程：ADD EXTRACT EJWINIT, SOURCEISTABLE

GGSCI (custom04) 4> ADD EXTRACT EJWINIT, SOURCEISTABLE

6、查看创建的进程：INFO EXTRACT *, TASKS

GGSCI (custom04) 5> INFO EXTRACT *, TASKS

EXTRACT EJWINIT Last Started 2019-01-31 12:37 Status STOPPED

Checkpoint Lag Not Available

Log Read Checkpoint Table JW_USER.T_USERINFO

2019-01-31 12:37:15 Record 5

Task SOURCEISTABLE

7、编辑配置文件：EDIT PARAMS EJWINIT

GGSCI (custom04) 6> EDIT PARAMS EJWINIT

8、填写内容【注意：1.DSN001 为上面添加ODBC数据源的名称，2.修改用户名、密码，3.新增表tab、tab2并添加10条数据】

EXTRACT EJWINIT

sourcedb DSN001 userid ogg, password 123456

RMTHOST 172.16.11.11, MGRPORT 2000

RMTTASK REPLICAT, GROUP RJWINIT

table dbo.tab;

table dbo.tab2;

9、启动进程【注意：需在配置完目标端的接收进程后在执行】：START EXTRACT EJWINIT

GGSCI (custom04) 7> START EXTRACT EJWINIT

10、查看运行情况 ：VIEW REPORT EJWINIT

GGSCI (custom04) 8> VIEW REPORT EJWINIT

....略

2019-01-31 12:37:15 INFO OGG-02911 Processing table JW_USER.T_USERINFO.

***********************************************************************

* ** Run Time Statistics ** *

***********************************************************************

Report at 2019-01-31 12:37:16 (activity since 2019-01-31 12:37:04)

Output to RJWINIT:

From Table JW_USER.T_USERINFO:

# inserts: 5

# updates: 0

# deletes: 0

# discards: 0

REDO Log Statistics

Bytes parsed 0

Bytes output 741

三、【目标端】

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

GGSCI (custom04) 1> EDIT PARAMS MGR

2、填写内容

port 2000

dynamicportlist 2001-2005

autorestart extract *,waitminutes 2,retries 5

ACCESSRULE, PROG *, IPADDR 172.16.11.11, ALLOW

3、启动MGR管理器进程：start mgr

GGSCI (custom04) 2> start mgr

Manager started.

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

GGSCI (custom04) 3> info mgr

Manager is running (IP port Win10.2000, Process ID 13820).

5、创建接收进程：add replicat RJWINIT,specialrun

GGSCI (custom04) 4> add replicat RJWINIT,specialrun

6、查看创建的进程：INFO REPLICAT *, TASKS

GGSCI (custom04) 5> INFO REPLICAT *, TASKS

REPLICAT RJWINIT Initialized 2019-01-31 12:38 Status STOPPED

Checkpoint Lag 00:00:00 (updated 00:42:44 ago)

Log Read Checkpoint Not Available

Task SPECIALRUN

7、编辑配置文件：edit params RJWINIT

GGSCI (custom04) 6> edit params RJWINIT

8、填写内容【注意：1.DSN001 为上面添加ODBC数据源的名称，2.修改用户名、密码，3.新增表tab、tab2】

REPLICAT RJWINIT

ASSUMETARGETDEFS

targetdb DSN001 userid ogg password 123456

DISCARDFILE ./dirrpt/RJWINIT.dsc, PURGE

MAP dbo.tab, target dbo.tab;

MAP dbo.tab2, target dbo.tab2;

9、启动进程：START REPLICAT RJWINIT

GGSCI (custom04) 7> START REPLICAT RJWINIT

10、查看运行情况 ：VIEW REPORT RJWINIT

GGSCI (custom04) 8> VIEW REPORT RJWINIT

....略

2019-01-31 12:37:14 INFO OGG-03522 Setting session time zone to source database time zone 'GMT'.

2019-01-31 12:37:14 WARNING OGG-02760 ASSUMETARGETDEFS is ignored because trail file contains table definitions.

2019-01-31 12:37:14 INFO OGG-06505 MAP resolved (entry JW_USER.T_USERINFO): MAP "JW_USER"."T_USERINFO", target JWUSER.T_USERINFO.

2019-01-31 12:37:14 INFO OGG-02756 The definition for table JW_USER.T_USERINFO is obtained from the trail file.

2019-01-31 12:37:14 INFO OGG-06511 Using following columns in default map by name: id, name.

2019-01-31 12:37:14 INFO OGG-06510 Using the following key columns for target table JWUSER.T_USERINFO: id.

2019-01-31 12:37:14 INFO OGG-03010 Performing implicit conversion of column data from character set zhs16gbk to UTF-8.

***********************************************************************

* ** Run Time Statistics ** *

***********************************************************************

Report at 2019-01-31 12:37:19 (activity since 2019-01-31 12:37:14)

From Table JW_USER.T_USERINFO to JWUSER.T_USERINFO:

# inserts: 5

# updates: 0

# deletes: 0

# discards: 0

CACHE OBJECT MANAGER statistics

....略