sqlserver 2016 winserver 2012 goldengate 12.3 cdc capterue
1.建立文件夹，提取软件至相应目录2.新建数据库source，target
3.
源端：
USE
GO
CREATE SCHEMA AUTHORIZATION
GO
目标端：
USE
GO
CREATE SCHEMA AUTHORIZATION
GO
4.点击source，target数据库架构，确认ogg用户建立成功
5.创建测试表
use source;
create table dbo.test1 (id int primary key, name varchar(50));
create table dbo.test2( id int, name varchar(50), age int);
use target;
create table dbo.test1 (id int primary key, name varchar(50));
create table dbo.test2( id int, name varchar(50), age int);
6.配置并启动mgr on node1:
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.3.0.1.1 OGGCORE_12.3.0.1.0_PLATFORMS_170804.2007
Windows x64 (optimized), Microsoft SQL Server on Aug5 2017 10:02:48
Operating system character set identified as GBK.
Copyright (C) 1995, 2017, Oracle and/or its affiliates. All rights reserved.
GGSCI (node1) 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 (node1) 2> edit params mgr
GGSCI (node1) 3> view params mgr
port 7809
GGSCI (node1) 4> info all
Program Status Group Lag at ChkptTime Since Chkpt
MANAGER STOPPED
GGSCI (node1) 5> start mgr
Manager started.
GGSCI (node1) 6> info all
Program Status Group Lag at ChkptTime Since Chkpt
MANAGER RUNNING
GGSCI (node1) 7>
配置并启动mgr on node2:
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.3.0.1.1 OGGCORE_12.3.0.1.0_PLATFORMS_170804.2007
Windows x64 (optimized), Microsoft SQL Server on Aug5 2017 10:02:48
Operating system character set identified as GBK.
Copyright (C) 1995, 2017, Oracle and/or its affiliates. All rights reserved.
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.
GGSCI (node2) 5> info all
Program Status Group Lag at ChkptTime Since Chkpt
MANAGER RUNNING
GGSCI (node2) 6>
7
GGSCI (node1) 8> edit params ./GLOBALS
GGSCI (node1) 9> view params ./GLOBALS
GGSCHEMA OGG
GGSCI (node1) 10>
GGSCI (node2) 6> edit params ./GLOBALS
GGSCI (node2) 7> view params ./GLOBALS
GGSCHEMA OGG
windows 平台下会默认创建为./dirdat/globals.prm文件，切换到$OGG_HOME目录下手工创建正确的文件，创建后退出GGSCI,停止mgr,
重新登录ggsci并重新启动mgr
8.node1,node2 创建用户并赋权
node1:
USE
GO
CREATE LOGIN WITH PASSWORD=N'oggpsw', DEFAULT_DATABASE=, CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
EXEC master..sp_addsrvrolemember @loginame = N'oggsrc', @rolename = N'sysadmin'
GO
node2:
USE
GO
CREATE LOGIN WITH PASSWORD=N'oggpsw', DEFAULT_DATABASE=, CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
EXEC master..sp_addsrvrolemember @loginame = N'oggdest', @rolename = N'sysadmin'
GO
赋权部分参考截图
9.node1,node2 启动agent
10:
源库上启用cdc
use source
EXECUTE sys.sp_cdc_enable_db
OGG trandata
11.node1 node2 分别建立数据源source，target
12.源端启用cdc,开启表级别附加日志
GGSCI>dblogin sourcedb source, userid oggsrc, password oggpsw
GGSCI>add trandata dbo.test1
GGSCI>add trandata dbo.test2
执行完成之后，可以看到多了一张配置表
ogg.OracleGGTranTables will be added automatically.
13 node1:
创建OGG clean job
需要先删除DB自带的clean job
EXECUTE sys.sp_cdc_drop_job 'cleanup'
然后在OGG安装目录下，进入命令行，执行如下语句，其中(local)是默认的sqlserver实例
ogg_cdc_cleanup_setup.bat createjob oggsrc oggpsw source (local) ogg
GGSCI (node1 as oggsrc@SOURCE) 9> exit
c:\ogg>ogg_cdc_cleanup_setup.bat createjob oggsrc oggpsw source (local) ogg
Oracle GoldenGate CDC cleanup job setup script
==============================================
Microsoft (R) SQL Server 命令行工具
版本 13.0.1601.5 NT
版权所有(C) 2015 Microsoft。保留所有权利。
用法: Sqlcmd [-U 登录 ID] [-P 密码]
[-S 服务器] [-H 主机名] [-E 信任连接]
[-N 加密连接][-C 信任服务器证书]
[-d 使用数据库名称] [-l 登录超时值] [-t 查询超时值]
[-h 标题] [-s 列分隔符] [-w 屏幕宽度]
[-a 数据包大小] [-e 回显输入] [-I 允许带引号的标识符]
[-c 命令结束] [-L 列出服务器[清除输出]]
[-q "命令行查询"] [-Q "命令行查询" 并退出]
[-m 错误级别] [-V 严重级别] [-W 删除尾随空格]
[-u unicode 输出] [-r 发送到 stderr 的消息]
[-i 输入文件] [-o 输出文件] [-z 新密码]
[-f <代码页> | i:<代码页>[,o:<代码页>]] [-Z 新建密码并退出]
[-k 删除[替换]控制字符]
[-y 可变长度类型显示宽度]
[-Y 固定长度类型显示宽度]
[-p 打印统计信息[冒号格式]]
[-R 使用客户端区域设置]
[-K 应用程序意向]
[-M 多子网故障转移]
[-b 出错时中止批处理]
[-v 变量 = "值"...][-A 专用管理连接]
[-X 禁用命令、启动脚本、环境变量[并退出]]
[-x 禁用变量替换]
[-j 打印原始错误消息]
[-? 显示语法摘要]
Command: createjob
Oracle GoldenGate CDC cleanup job and its relevant table(s) and procedure(s) are created.
c:\ogg>
14.源端配置抽取进程
GGSCI (node1) 1> info all
Program Status Group Lag at ChkptTime Since Chkpt
MANAGER RUNNING
GGSCI (node1) 2> edit params ext01
GGSCI (node1) 3> view params ext01
extract ext01
sourcedb source, userid oggsrc, password oggpsw
exttrail ./dirdat/aa
table dbo.*;
GGSCI (node1) 4> add extract ext01,tranlog,begin now
EXTRACT added.
GGSCI (node1) 5> add exttrail ./dirdat/aa,extract ext01
EXTTRAIL added.
GGSCI (node1) 6> info all
Program Status Group Lag at ChkptTime Since Chkpt
MANAGER RUNNING
EXTRACT STOPPED EXT01 00:00:00 00:00:19
GGSCI (node1) 7> start ext01
Sending START request to MANAGER ...
EXTRACT EXT01 starting
GGSCI (node1) 8> info all
Program Status Group Lag at ChkptTime Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EXT01 00:00:26 00:00:06
GGSCI (node1) 9> info extract ext01 detail
EXTRACT EXT01 Last Started 2017-09-15 17:13 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:00:01 ago)
Process ID 4568
VAM Read Checkpoint2017-09-15 17:13:14.818000
Target Extract Trails:
Trail Name Seqno RBA Max MB Trail Type
./dirdat/aa 0 1275 500 EXTTRAIL
Extract Source Begin End
Not Available 2017-09-15 17:132017-09-15 17:13
Not Available * Initialized * 2017-09-15 17:13
Current directory c:\ogg
Report file c:\ogg\dirrpt\EXT01.rpt
Parameter file c:\ogg\dirprm\EXT01.prm
Checkpoint file c:\ogg\dirchk\EXT01.cpe
Process file c:\ogg\dirpcs\EXT01.pce
GGSCI (node1) 13> view report ext01
***********************************************************************
Oracle GoldenGate Capture for SQL Server
SQL Server Log Mining Method: CDC
Version 12.3.0.1.1 OGGCORE_12.3.0.1.0_PLATFORMS_170804.2007
Windows x64 (optimized), Microsoft SQL Server on Aug5 2017 11:14:34
Copyright (C) 1995, 2017, Oracle and/or its affiliates. All rights reserved.
Starting at 2017-09-15 17:13:41
***********************************************************************
Operating System Version:
Microsoft Windows Server 2012 R2, on x64
Version 6.3 (Build 9600)
Process id: 4568
Description:
***********************************************************************
** Running with the following parameters **
***********************************************************************
2017-09-15 17:13:41INFO OGG-03059Operating system character set identified as GBK.
2017-09-15 17:13:41INFO OGG-02695ANSI SQL parameter syntax is used for parameter parsing.
extract ext01
sourcedb source, userid oggsrc, password ***
2017-09-15 17:13:41WARNING OGG-05236ODBC Warning: The specified DSN 'source' uses a client driver that may be inc
atible with the database server. Microsoft SQL Server requires SQLNCLI11.DLL,MSODBCSQL11.DLL,MSODBCSQL13.DLL or a mor
ecent version.
2017-09-15 17:13:41INFO OGG-03036Database character set identified as windows-936. Locale: zh_CN.
2017-09-15 17:13:41INFO OGG-03037Session character set identified as GBK.
exttrail ./dirdat/aa
table dbo.*;
2017-09-15 17:13:41INFO OGG-01851filecaching started: thread ID: 54567200.
2017-09-15 17:13:41INFO OGG-01815Virtual Memory Facilities for: COM
anon alloc: MapViewOfFileanon free: UnmapViewOfFile
file alloc: MapViewOfFilefile free: UnmapViewOfFile
target directories:
c:\ogg\dirtmp.
2017-09-15 17:13:41WARNING OGG-01842CACHESIZE PER DYNAMIC DETERMINATION (16G) LESS THAN RECOMMENDED: 64G (64bit s
em)
vm found: 29.44G
Check swap space. Recommended swap/extract: 128G (64bit system).
Database Version:
Microsoft SQL Server
Version 13.00.4446
ODBC Version 03.80.0000
Driver Information:
SQLSRV32.DLL
Version 06.03.9600
ODBC Version 03.52
2017-09-15 17:13:41INFO OGG-01052No recovery is required for target file ./dirdat/aa000000000, at RBA 0 (file
opened).
2017-09-15 17:13:41INFO OGG-01478Output file ./dirdat/aa is using format RELEASE 12.3.
2017-09-15 17:13:41INFO OGG-00182VAM API running in single-threaded mode.
2017-09-15 17:13:41INFO OGG-01515Positioning to begin time 2017年9月15日 下午5:13:14.
2017-09-15 17:13:41INFO OGG-05264Opening DSN connection: source, Server: NODE1, Database: source.
2017-09-15 17:13:41INFO OGG-05255Current CDC Capture Settings - job name cdc.source_capture, maxtrans: 500, ma
ans: 10, continuous: 1, polling interval: 5.
2017-09-15 17:13:41INFO OGG-05257For CDC tuning best practices, please see https://technet.microsoft.com/en-us
brary/dd266396%28v=sql.100%29.aspx.
2017-09-15 17:13:41INFO OGG-05281Current OGG cleanup Job Settings - Job Name: OracleGGCleanup_source_Job, JobS
dRec: , JobSchedFreq: , DatabaseName: source, Tranlogoption managecdccleanup: 0, threshold: 500, retention: 4,320.
***********************************************************************
** Run Time Messages **
***********************************************************************
2017-09-15 17:15:14INFO OGG-01021Command received from GGSCI: STATS.
15.配置投递进程
配置泵取进程：
GGSCI (node1) 14> edit params pmp01
GGSCI (node1) 15> view params pmp01
EXTRACT PMP01
PASSTHRU
RMTHOST 192.168.1.112 MGRPORT 7809
RMTTRAIL ./dirdat/rt
TABLE dbo.*;
GGSCI (node1) 16> ADD EXTRACT PMP01, EXTTRAILSOURCE ./dirdat/aa
EXTRACT added.
GGSCI (node1) 17> ADD RMTTRAIL ./dirdat/rt, EXTRACT PMP01
RMTTRAIL added.
GGSCI (node1) 18> info all
Program Status Group Lag at ChkptTime Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EXT01 00:00:00 00:00:09
EXTRACT STOPPED PMP01 00:00:00 00:00:19
GGSCI (node1) 19> start pmp01
Sending START request to MANAGER ...
EXTRACT PMP01 starting
GGSCI (node1) 20> info all
Program Status Group Lag at ChkptTime Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EXT01 00:00:00 00:00:07
EXTRACT RUNNING PMP01 00:00:00 00:00:08
16 node2配置复制进程
GGSCI (node2) 19> info all
Program Status Group Lag at ChkptTime Since Chkpt
MANAGER RUNNING
GGSCI (node2) 21> dblogin sourcedb target, userid oggdest, password oggpsw
2017-09-15 17:31:06WARNING OGG-05236ODBC Warning: The specified DSN 'target' uses a client driver that may be incomp
atible with the database server. Microsoft SQL Server requires SQLNCLI11.DLL,MSODBCSQL11.DLL,MSODBCSQL13.DLL or a more r
ecent version.
2017-09-15 17:31:06INFO OGG-03036Database character set identified as windows-936. Locale: zh_CN.
2017-09-15 17:31:06INFO OGG-03037Session character set identified as GBK.
Successfully logged into database.
GGSCI (node2 as oggdest@TARGET) 22> info all
Program Status Group Lag at ChkptTime Since Chkpt
MANAGER RUNNING
GGSCI (node2 as oggdest@TARGET) 23> edit params rep01
GGSCI (node2 as oggdest@TARGET) 24> view params rep01
REPLICAT REP01
targetdb target, userid oggdest, password oggpsw
ASSUMETARGETDEFS
MAP dbo.*, TARGET dbo.*;
GGSCI (node2 as oggdest@TARGET) 25> add checkpointtable dbo.OGGCHKPT
Successfully created checkpoint table dbo.OGGCHKPT.
GGSCI (node2 as oggdest@TARGET) 26> ADD REPLICAT REP01, EXTTRAIL ./dirdat/rt, CHECKPOINTTABLE dbo.OGGCHKPT
REPLICAT added.
GGSCI (node2 as oggdest@TARGET) 27> info all
Program Status Group Lag at ChkptTime Since Chkpt
MANAGER RUNNING
REPLICAT STOPPED REP01 00:00:00 00:00:18
GGSCI (node2 as oggdest@TARGET) 28> start rep01
Sending START request to MANAGER ...
REPLICAT REP01 starting
GGSCI (node2 as oggdest@TARGET) 31> edit params rep01
GGSCI (node2 as oggdest@TARGET) 32> view params rep01
REPLICAT REP01
targetdb target, userid oggdest, password oggpsw
ASSUMETARGETDEFS
MAP dbo.*, TARGET dbo.*;
GGSCI (node2 as oggdest@TARGET) 33> start rep01
Sending START request to MANAGER ...
REPLICAT REP01 starting
GGSCI (node2 as oggdest@TARGET) 64> info all
Program Status Group Lag at ChkptTime Since Chkpt
MANAGER RUNNING
REPLICAT RUNNING REP01 00:00:00 00:00:03
GGSCI (node2 as oggdest@TARGET) 65> view params rep01
REPLICAT REP01
targetdb target, userid oggdest, password oggpsw
ASSUMETARGETDEFS
MAP dbo.*, TARGET dbo.*;
GGSCI (node2 as oggdest@TARGET) 66>
注意：
1.重启后sqlserver agent 默认不启动，可以配置为自动启动或人工启动，cdc 抽取模式依赖代理，不启动会有问题
2.数据源配置
源端数据源 odbc 抽取进程正常，无警告信息正常
sqlserver抽取进程正常，含有警告信息
目标端数据源 sqlserver native clent 正常，有警告信息
odbc 此模式配置报错，待以后测试
sqlserver 此模式配置报错，待以后测试
页:
[1]