sqlserver 2014 to sqlserver 2014 goldengate 12.2.0.1.1 ALO模式
os: Microsoft Windows Server 2012 r2_Datacenter
介质：cn_windows_server_2012_r2_vl_with_update_x64_dvd_6052729.iso
sqlserver:
Microsoft SQL Server 2014 - 12.0.2000.8 (X64)
Feb 20 2014 20:04:26
Copyright (c) Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)
介质：cn_sql_server_2014_enterprise_edition_x64_dvd_3932882.iso
goldengate:
Oracle GoldenGate Command Interpreter for SQL Server
Version 12.2.0.1.1 OGGCORE_12.2.0.1.0_PLATFORMS_151211.1401
Windows x64 (optimized), Microsoft SQL Server on Dec 11 2015 15:50:22
Copyright (C) 1995, 2015, Oracle and/or its affiliates. All rights reserved.
介质：V100722-01.zip
概述：
node1上源端数据库source，node2上目标端数据库target,node1计划任务备份数据库source日志至共享文件夹，node2 extlocal ALO 模式抽取共享文件夹上的日志实现
数据同步，replocal进程同步数据至target数据库
调整两台虚拟机的以下配置：
node1:192.168.1.113
node2:192.168.1.114
调整主机名
调整ip地址
关闭防火墙，或打开对应端口
node2建立目录c:\ogg并上传ogg软件至此目录下，解压选择提取至ogg 目录
node2主机配置启动mgr:
Windows PowerShell
版权所有 (C) 2014 Microsoft Corporation。保留所有权利。
PS C:\Users\Administrator> cmd
Microsoft Windows [版本 6.3.9600]
(c) 2013 Microsoft Corporation。保留所有权利。
C:\Users\Administrator>cd /d c:\ogg
c:\ogg>ggsci.exe
Oracle GoldenGate Command Interpreter for SQL Server
Version 12.2.0.1.1 OGGCORE_12.2.0.1.0_PLATFORMS_151211.1401
Windows x64 (optimized), Microsoft SQL Server on Dec 11 2015 15:50:22
Operating system character set identified as GBK.
Copyright (C) 1995, 2015, Oracle and/or its affiliates. All rights reserved.
GGSCI (node2) 1>
GGSCI (node2) 1> create subdirs
Creating subdirectories under current directory c:\ogg
Parameter file c:\ogg\dirprm: created.
Report file c:\ogg\dirrpt: created.
Checkpoint file c:\ogg\dirchk: created.
Process status files c:\ogg\dirpcs: created.
SQL script files c:\ogg\dirsql: created.
Database definitions files c:\ogg\dirdef: created.
Extract data files c:\ogg\dirdat: created.
Temporary files c:\ogg\dirtmp: created.
Credential store files c:\ogg\dircrd: created.
Masterkey wallet files c:\ogg\dirwlt: created.
Dump files c:\ogg\dirdmp: created.
GGSCI (node2) 2> edit params mgr
GGSCI (node2) 3> view params mgr
port 7809
GGSCI (node2) 4> start mgr
Manager started.
源库配置：
建库
node1:
源库：source
注意日志模式 full
备份
C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\source.bak
node2:
目标库target：
source ,target 分别执行demo_mss_create.sql创建测试用表执行前分别添加
use source (node1)
use target (node2)
node2创建数据源 source 、target
开始--管理工具---ODBC 数据源（64位)--系统DSN--Microsoft SQL Server Native Client 版本 11.00.6518
---添加
远程数据源 source：
名称:source 服务器：node1 next
集成Windows身份验证 next
更改默认数据库 source next
缺省值 finish
本地数据源 target：
名称:target 服务器：node2 next
集成Windows身份验证 next
更改默认数据库 target next
缺省值 finish
node1 主机上创建source库日志备份目录c:\backup，调整属性为共享，配置归档日志备份计划，每3分钟备份一次
参考<<日志备份维护计划及共享文件夹设置.fbr>>录像
node2:
调整相关表属性：
c:\ogg>ggsci.exe
Oracle GoldenGate Command Interpreter for SQL Server
Version 12.2.0.1.1 OGGCORE_12.2.0.1.0_PLATFORMS_151211.1401
Windows x64 (optimized), Microsoft SQL Server on Dec 11 2015 15:50:22
Operating system character set identified as GBK.
Copyright (C) 1995, 2015, Oracle and/or its affiliates. All rights reserved.
GGSCI (node2) 1> info all
Program Status Group Lag at ChkptTime Since Chkpt
MANAGER RUNNING
GGSCI (node2) 2> info all
Program Status Group Lag at ChkptTime Since Chkpt
MANAGER RUNNING
GGSCI (node2) 3> dblogin sourcedb source
2017-10-01 13:52:25INFO OGG-03036Database character set identified as windows-936. Locale: zh_CN.
2017-10-01 13:52:25INFO OGG-03037Session character set identified as GBK.
Successfully logged into database.
GGSCI (node2) 4> add trandata dbo.tcustmer
Logging of supplemental log data is enabled for table dbo.tcustmer
GGSCI (node2) 5> add trandata dbo.tcustord
Logging of supplemental log data is enabled for table dbo.tcustord
GGSCI (node2) 6>
GGSCI (node2) 37> edit params ./GLOBALS
GGSCI (node2) 38> view params ./GLOBALS
MGRSERVNAME OGG
GGSCI (node2) 39> shell install addservice
Service 'OGG' created.
Install program terminated normally.
GGSCI (node2) 12> info all
Program Status Group Lag at ChkptTime Since Chkpt
MANAGER RUNNING
GGSCI (node2) 7> edit params extlocal
GGSCI (node2) 10> view params extlocal
EXTRACT extlocal
SOURCEDB source
TRANLOGOPTIONS ARCHIVEDLOGONLY
TRANLOGOPTIONS ALTARCHIVELOGDEST ("//node1/backup")
TRANLOGOPTIONS MANAGESECONDARYTRUNCATIONPOINT
EXTTRAIL ./dirdat/rt
TABLE dbo.tcustmer;
TABLE dbo.tcustord;
GGSCI (node2) 9> add extract extlocal,tranlog,begin now
EXTRACT added.
GGSCI (node2) 10> add exttrail ./dirdat/rt,extract extlocal
EXTTRAIL added.
GGSCI (node2) 11> info all
Program Status Group Lag at ChkptTime Since Chkpt
MANAGER RUNNING
EXTRACT STOPPED EXT2016 00:00:00 00:00:21
GGSCI (node2) 16> info all
Program Status Group Lag at ChkptTime Since Chkpt
MANAGER RUNNING
EXTRACT STOPPED extlocal 00:00:00 00:02:28
GGSCI (node2) 17>
查看各进程状态正常：
GGSCI (node2) 37> INFO ALL
Program Status Group Lag at ChkptTime Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING extlocal 00:00:00 00:00:03
GGSCI (node2) 16> dblogin sourcedb target
2014-05-07 23:01:10INFO OGG-03036Database character set identified as win
dows-936. Locale: zh_Hans_CN.
2014-05-07 23:01:10INFO OGG-03037Session character set identified as GBK.
Successfully logged into database.
GGSCI (node2) 17> info all
Program Status Group Lag at ChkptTime Since Chkpt
MANAGER RUNNING
编辑复制进程：
GGSCI (node2) 18> edit params replocal
GGSCI (node2) 11> view params replocal
REPLICAT REPLOCAL
DBOPTIONS USEREPLICATIONUSER
TARGETDB TARGET
ASSUMETARGETDEFS
MAP dbo.*, TARGET dbo.*;
目标端添加检查点表:
GGSCI (node2) 20> DBLOGIN SOURCEDB target
2014-05-07 23:05:50INFO OGG-03036Database character set identified as win
dows-936. Locale: zh_Hans_CN.
2014-05-07 23:05:50INFO OGG-03037Session character set identified as GBK.
Successfully logged into database.
GGSCI (node2) 21> add checkpointtable dbo.OGGCHKPT
Successfully created checkpoint table dbo.OGGCHKPT.
GGSCI (node2) 23> ADD REPLICAT REPLOCAL, EXTTRAIL ./dirdat/rt, CHECKPOINTTABLE dbo.OGGCHKPT
REPLICAT added.
GGSCI (node2) 13> info all
Program Status Group Lag at ChkptTime Since Chkpt
MANAGER RUNNING
EXTRACT STOPPED extlocal 00:00:00 00:00:03
REPLICAT STOPPED replocal 00:00:00 00:00:02
启动抽取、复制进程：
GGSCI (node2) 14> start *
GGSCI (node2) 15> info all
Program Status Group Lag at ChkptTime Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING extlocal 00:00:00 00:00:03
REPLICAT RUNNING replocal 00:00:00 00:00:02
测试：源端source运行 demo_mss_insert.sql (注意切换数据库use source)，目标端targetdb 查看相应表的行数，同步成功。
问题一：
node2抽取进程读取的日志备份来源于node1主机上的维护计划的每次备份，依靠sql server agent完成，所以
sql server agent 配置为开机自动启动（通过sqlserver 配置管理器）
问题二：
start extlocal 报错 OGG-05209Failed to open the SQL Server process, 1,204, with Windows system error 87
2017-10-02 08:38:19INFO OGG-00178VAM Client Report <Opening files for DSN: source, Server: NODE1, Da
e>.
Source Context :
SourceModule :
SourceID :
SourceFunction :
SourceLine :
ThreadBacktrace : elements
: ]
: [c:\ogg\gglog.dll(?CreateMessage@CMessageFactory@@QEAAPEAVCMessage@@PEAVCSource
0x561) ]
: [c:\ogg\gglog.dll(?_MSG_ERR_MSSQL_LI_CANNOT_OPEN_SQL_SERVER_PROCESS@@YAPEAVCMes
urceContext@@IIW4MessageDisposition@CMessageFactory@@@Z+0x48) ]
: ]
: ]
: ]
: ]
: ]
: ]
: ]
: ]
: ]
: ]
: ]
: ]
: ]
: ]
: [c:\ogg\extract.exe(??0_Mutex@std@@QEAA@W4_Uninitialized@1@@Z+0x42a) [0x00007FF
: [C:\Windows\system32\KERNEL32.DLL(BaseThreadInitThunk+0x22) [0x00007FF9608413D2
: ]
2017-10-02 08:38:19ERROR OGG-05209Failed to open the SQL Server process, 1,204, with Windows system e
2017-10-02 08:38:19ERROR OGG-01668PROCESS ABENDING.
处理：
extlocal 参数文件少加了下面的参数
TRANLOGOPTIONS ARCHIVEDLOGONLY
另一种模式：
源端库ALO模式抽取，日志备份维护计划不变
node1 上创建本地数据源source、远程数据源target
GGSCI (node1) 36> info all
Program Status Group Lag at ChkptTime Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EXTLOCAL 00:01:14 00:00:11
REPLICAT RUNNING REPLOCAL 00:00:00 00:00:02
GGSCI (node1) 37> view params extlocal
EXTRACT extlocal
SOURCEDB source
TRANLOGOPTIONS ARCHIVEDLOGONLY
TRANLOGOPTIONS ALTARCHIVELOGDEST ("c:\backup")
TRANLOGOPTIONS MANAGESECONDARYTRUNCATIONPOINT
EXTTRAIL ./dirdat/rt
TABLE dbo.tcustmer;
TABLE dbo.tcustord;
GGSCI (node1) 38> view params replocal
REPLICAT REPLOCAL
DBOPTIONS USEREPLICATIONUSER
TARGETDB TARGET
ASSUMETARGETDEFS
MAP dbo.*, TARGET dbo.*;
页:
[1]