GoldenGate SQLServer 到 SQLServer 同步

 

测试环境：

源、目标端都是win2003+ SQLServer2008

 

1.      数据库准备：源端目标端分别创建数据库”oggdb”，恢复模式为”完整“，并创建测试表t1

-------------------------------------------------------------------------------------------------------

create table t1(ID INT, NAME VARCHAR(50))

-------------------------------------------------------------------------------------------------------

 

2.      源端目标端分别创建ODBC连接

l  主对话框中一定要选择”系统DSN”，而不是默认的”用户DSN”

l  数据源驱动程序选”SQLServer Native Client 10.0”

l  源端连接名：”src-sqlserver”,目标端连接名：”target-sqlserver”

l  服务器选择正确的实例名

l  为简化测试，直接通过”sa”用户连接数据库

l  默认数据库均指向”oggdb”

 

3.      源端目标端分别解压安装包” ogg_112101_ggs_Windows_x86_SqlServer2008_win2003.zip”到”c:\ogg”目录

 

4.      源端启动管理进程

执行”c:\ogg\ggsci.exe” (win7系统需要以管理员身份运行)

GGSCI > CREATE SUBDIRS

GGSCI > EDIT PARAMS MGR

-------------------------------------------------------------------------------------------------------

PORT 7809

-------------------------------------------------------------------------------------------------------

GGSCI > START MANAGER

GGSCI > INFO MANAGER

 

5.      目标端启动管理进程

执行”c:\ogg\ggsci.exe” (win7下以管理员身份运行)

GGSCI > CREATE SUBDIRS

GGSCI > EDIT PARAMS MGR

-------------------------------------------------------------------------------------------------------

PORT 7809

-------------------------------------------------------------------------------------------------------

GGSCI > START MANAGER

GGSCI > INFO MANAGER

 

6.      源端数据库，插入些数据，通过SQLServer管理器执行一次全备

 

7.      源端开启附加日志

GGSCI > DBLOGIN SOURCEDBsrc-sqlserver, userid sa, password sasa

GGSCI > ADD TRANDATA dbo.t1

GGSCI > INFO TRANDATA dbo.t1

 

8.      全同步源端配置

GGSCI (duanbb-DSG) 12> ADD EXTRACT EINI1, SOURCEISTABLE

GGSCI> INFO EXTRACT EINI1

GGSCI > EDIT PARAMS EINI1

-------------------------------------------------------------------------------------------------------

--

-- GoldenGate Initial Data Capture

-- for T1

--

EXTRACT EINI1

SOURCEDB src-sqlserver, USERID sa,PASSWORD sasa

RMTHOST 192.168.2.144, MGRPORT 7809

RMTTASK REPLICAT, GROUP RINI1

TABLE dbo.t1;

-------------------------------------------------------------------------------------------------------

 

9.      全同步目标端配置

GGSCI > ADD REPLICAT RINI1, SPECIALRUN

GGSCI > INFO RINI1, TASKS

GGSCI > EDIT PARAMS RINI1

-------------------------------------------------------------------------------------------------------

-- GoldenGate Initial Data LoadDelivery

--

REPLICAT RINI1

ASSUMETARGETDEFS

TARGETDB target-sqlserver, USERIDsa, PASSWORD sasa

DISCARDFILE ./dirrpt/RINI1.txt,PURGE

MAP dbo.t1, TARGET dbo.t1;

-------------------------------------------------------------------------------------------------------

 

10.  源端执行全同步

GGSCI (duanbb-DSG) 21> START EXTRACT EINI1

 

11.  查看全同步结果

源端: GGSCI > VIEW REPORT EINI1

目标端: GGSCI > VIEW REPORT RINI1

 

12.  源端配置增量同步

GGSCI> ADD EXTRACT EMSS1,TRANLOG, BEGIN NOW

GGSCI> INFO EXTRACT EMSS1

GGSCI> EDIT PARAM EMSS1

-------------------------------------------------------------------------------------------------------

--

-- Parameter file to capture

-- T1 Changes

--

EXTRACT EMSS1

TRANLOGOPTIONS MANAGESECONDARYTRUNCATIONPOINT

SOURCEDB src-sqlserver, USERID sa, PASSWORD sasa

RMTHOST 192.168.2.144, MGRPORT 7809

RMTTRAIL ./dirdat/ms

TABLE dbo.t1;

-------------------------------------------------------------------------------------------------------

GGSCI> ADD RMTTRAIL ./dirdat/ms,EXTRACT EMSS1, MEGABYTES 50

GGSCI > INFO RMTTRAIL *

 

13.  源端启动增量同步进程(目标端虽然还未配置，但是源端可以先启动同步进程)

GGSCI> START EXTRACT EMSS1

GGSCI> INFO EXTRACT EMSS1,DETAIL

 

14.  增量同步目标端配置

GGSCI> EDIT PARAMS ./GLOBALS

-------------------------------------------------------------------------------------------------------

CHECKPOINTTABLE dbo.ggschkpt

-------------------------------------------------------------------------------------------------------

GGSCI>EXIT

重新运行ggsci.exe

GGSCI> DBLOGIN SOURCEDB target-sqlserver,USERID sa, PASSWORD sasa

GGSCI> ADD CHECKPOINTTABLE

GGSCI> ADD REPLICAT RMSS1,EXTTRAIL ./DIRDAT/ms

GGSCI> EDIT PARAM RMSS1

-------------------------------------------------------------------------------------------------------

REPLICAT RMSS1

TARGETDB target-sqlserver, USERID sa, PASSWORD sasa

HANDLECOLLISIONS

ASSUMETARGETDEFS

DISCARDFILE ./DIRRPT/RMSS1.DSC, PURGE

MAP dbo.t1, TARGET dbo.t1;

-------------------------------------------------------------------------------------------------------

 

15.  目标端启动增量复制进程

GGSCI> START REPLICAT RMSS1

GGSCI> INFO REPLICAT RMSS1

 

16.  在源端添加修改数据，观察同步情况

17.  注册服务，开机自动启动”MGR”管理组进程(源端、目标端配置方法相同)

GGSCI> EDIT PARAMS ./GLOBALS  

添加以下内容，原有内容不要删除

-------------------------------------------------------------------------------------------------------

MGRSERVNAME OGG

-------------------------------------------------------------------------------------------------------

GGSCI> SHELL INSTALL ADDSERVICE ADDEVENTS

 

18.  源端开机自动启动增量同步进程

GGSCI> EDIT PARAMS MGR

-------------------------------------------------------------------------------------------------------

PORT 7809

AUTOSTART EXTRACT EMSS1

-------------------------------------------------------------------------------------------------------

”EMSS1”为前面配置的源端增量同步进程标识

注：如果要启动所有导出进程，用"AUTOSTART EXTRACT *"

 

19.  目标端开机自动启动增量同步进程

GGSCI> EDIT PARAMS MGR

-------------------------------------------------------------------------------------------------------

PORT 7809

AUTOSTART REPLICATRMSS1

-------------------------------------------------------------------------------------------------------

“RMSS1”为前面配置的目标端增量同步进程标识