Oracle GoldenGate 19.1 For Microsoft SQL Server 2019 安装配置手册

Microsoft Windows [版本 10.0.19041.329]
© 2020 Microsoft Corporation. 保留所有权利。
C:\Users\Win10> cd /d C:\OGGMSSQL
C:\OGGMSSQL> ggsci
Oracle GoldenGate Command Interpreter for SQL Server
Version 19.1.0.0.200204 OGGCORE_19.1.0.0.0OGGBP_PLATFORMS_200122.0600
Windows x64 (optimized), Microsoft SQL Server on Jan 22 2020 20:24:58
Operating system character set identified as GBK.
GGSCI (DEV_SERVER) 1> create subdirs
Creating subdirectories under current directory C:\OGGMSSQL
Parameter file C:\OGGMSSQL\dirprm: created.
Report file C:\OGGMSSQL\dirrpt: created.
Checkpoint file C:\OGGMSSQL\dirchk: created.
Process status files C:\OGGMSSQL\dirpcs: created.
SQL script files C:\OGGMSSQL\dirsql: created.
Database definitions files C:\OGGMSSQL\dirdef: created.
Extract data files C:\OGGMSSQL\dirdat: created.
Temporary files C:\OGGMSSQL\dirtmp: created.
Credential store files C:\OGGMSSQL\dircrd: created.
Masterkey wallet files C:\OGGMSSQL\dirwlt: created.
Dump files C:\OGGMSSQL\dirdmp: created.

创建GGSADMIN用户，取消密码安全策略，服务器角色:PUBLIC，SYSADMIN，用户映射如下：

A. 右键数据库—属性—选项—恢复模式—选择完整（Full Recovery Model）—确定
B. 备份事务日志 BACKUP LOG dbname TO DISK = “c:\folder\logbackup.trn”
C. 使用GGSCI 启动附加日志
C:\OGGMSSQL123> ggsci
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 Aug 5 2017 10:02:48
Operating system character set identified as GBK.
GGSCI (SVRSVN) 1> dblogin sourcedb mssql,userid ggsadmin,password ggsadmin

2020-07-22 15:31:56 INFO OGG-03036 Database character set identified as win
dows-936. Locale: zh_CN.
2020-07-22 15:31:56 INFO OGG-03037 Session character set identified as GBK.
Successfully logged into database.
GGSCI (SVRSVN as ggsadmin@MSSQL) 2> add trandata dbo.sex_dict
Logging of supplemental log data is enabled for table dbo.sex_dict
命令格式：ADD TRANDATA owner.table；可使用通配符：ADD TRANDATA owner.*

参考地址 ：https://blog.csdn.net/qq_25067099/article/details/95197484
A. 查询数据库是否启用CDC？
SELECT name,is_cdc_enabled FROM sys.databases t
B. 启用数据库级CDC，创建CDC架构及作业
use lisdb
EXECUTE sys.sp_cdc_enable_db;
C. 创建OGG管理员模式架构
USE lisdb
CREATE SCHEMA [ggsadmin] AUTHORIZATION [dbo]
D. （可选）开启表级CDC：
use lisdb
EXEC sys.sp_cdc_enable_table
@source_schema= ‘dbo’, --源表架构
@source_name = ‘SEX_DICT’, --源表
@role_name = ‘CDC’ --角色（将自动创建）
E. （可选）关闭表级CDC：
USE lisdb
EXEC sys.sp_cdc_disable_table
@source_schema = ‘dbo’,
@source_name = ‘sex_dict’,
@capture_instance = ‘all’;
效果图如下：

参考地址：https://jingyan.baidu.com/article/ed2a5d1f6d5cad09f6be17ab.html

C:\OGGMSSQL> INSTALL ADDSERVICE ADDEVENTS
Oracle GoldenGate messages installed successfully.
Service ‘OGGMSSQL2019’ created.
Install program terminated normally.
C:\OGGMSSQL> ggsci
Oracle GoldenGate Command Interpreter for SQL Server
Version 19.1.0.0.200204 OGGCORE_19.1.0.0.0OGGBP_PLATFORMS_200122.0600
Windows x64 (optimized), Microsoft SQL Server on Jan 22 2020 20:24:58
Operating system character set identified as GBK.

GGSCI (DEV_SERVER) 1> dblogin sourcedb mssql,userid ggsadmin,password ggsadmin
2020-07-16 15:55:22 INFO OGG-03036 Database character set identified as windows-1252. Locale: en_US.
2020-07-16 15:55:22 INFO OGG-03037 Session character set identified as GBK.
Successfully logged into database.
GGSCI (SVRSVN as ggsadmin@MSSQL) 5> add checkpointtable ggsadmin.checkpoint
Successfully created checkpoint table ggsadmin.checkpoint.

==============================================
Command: createJob
Neither the SQL Server Change Data Capture job nor Replication Log Reader Agent are found in database xxx.
Ensure that supplemental logging is enabled for database ‘xxx’ and that either the SQL Server Change Data Capture job or the Replication Log Reader Agent exists, if the database is Published for Transactional Replication, and re-run this program.
Also, ensure that the SQL Server login is a member of the sysadmin server role.

修改CDC数据库数据文件的所有者

C:\OGGMSSQL> ogg_cdc_cleanup_setup.bat createJob sa 123456 xxx dev_server ggsadmin

==============================================
Command: createJob
The Oracle GoldenGate CDC Cleanup job and its relevant tables and procedures have been created.
注册待同步表的附加日志
GGSCI (DEV_SERVER as ggsadmin@MSSQL) 2> add trandata dbo.agent
2020-07-16 15:55:24 ERROR OGG-25168 The specified GGSCHEMA name ‘ggsadmin’ in the GLOBALS file does not exist in the database, or you do not have permission to use it. Specify a valid GGSCHEMA name in the GLOBALS file.

GGSCI (DEV_SERVER as ggsadmin@MSSQL) 4> add trandata dbo.agent
2020-07-16 15:59:08 ERROR OGG-05268 Change data capture failed for the table ‘dbo.Agent’ in the database ‘xxx’: error: SQLCODE 50000. SQL Error [Microsoft][ODBC SQL Server Driver][SQL Server]无法更新数据库 xxx 的元数据来指示已添加某变更数据捕获作业。执行命令 ‘sp_add_jobstep_internal’ 时失败。返回的错误为 14234: ‘指定的 ‘@srv’ 无效(有效值由 sp_helpserver 返回)。’。请使用此操作和错误来确定失败的原因并重新提交请求。.
2020-07-16 15:59:08 WARNING OGG-00782 Error in changing transaction logging for table: dbo.Agent.
ERROR: ODBC Error occurred. See event log for details…

SELECT * FROM master.dbo.sysservers
SELECT SERVERPROPERTY(‘ServerName’)
修复方法：
IF serverproperty(‘servername’)<>@@servername
BEGIN
DECLARE @server SYSNAME
SET @server=@@servername
EXEC sp_dropserver @server=@server
SET @server=cast(serverproperty(‘servername’) AS SYSNAME)
EXEC sp_addserver @server=@server,@local=‘LOCAL’
PRINT ‘实例名与主机名一致，无需修改！’

https://blog.csdn.net/badly9/article/details/51324705

GGSCI (DEV_SERVER as ggsadmin@MSSQL) 5> edit param ./GLOBALS

GGSCI (DEV_SERVER as ggsadmin@MSSQL) 7> dblogin sourcedb mssql,userid ggsadmin,password ggsadmin
2020-07-16 15:55:10 INFO OGG-03036 Database character set identified as windows-1252. Locale: en_US.
2020-07-16 15:55:10 INFO OGG-03037 Session character set identified as GBK.
Successfully logged into database.

GGSCI (DEV_SERVER as ggsadmin@MSSQL) 4> edit param mgr
GGSCI (DEV_SERVER as ggsadmin@MSSQL) 5> start mgr
Manager started.
GGSCI (DEV_SERVER as ggsadmin@MSSQL) 6> edit param mgr
GGSCI (DEV_SERVER as ggsadmin@MSSQL) 7> add trandata dbo.agent
2020-07-16 16:41:27 INFO OGG-05321 Logging of supplemental log data is already enabled for table ‘dbo.Agent’ in filegroup ‘PRIMARY’.
Logging of supplemental log data is enabled for table dbo.Agent in filegroup PRIMARY

GGSCI (DEV_SERVER as ggsadmin@MSSQL) 8> start mgr
Manager started.

GGSCI (DEV_SERVER as ggsadmin@MSSQL) 9> edit params ex01
GGSCI (DEV_SERVER as ggsadmin@MSSQL) 10> add extract ex01,tranlog,begin now
EXTRACT added.
GGSCI (DEV_SERVER as ggsadmin@MSSQL) 12> add exttrail ./dirdat/ms,extract ex01, megabytes 2000
EXTTRAIL added.

GGSCI (DEV_SERVER as ggsadmin@MSSQL) 13> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT STOPPED EX01 00:00:00 00:00:32

GGSCI (DEV_SERVER as ggsadmin@MSSQL) 14> start ex01
Sending START request to MANAGER …
EXTRACT EX01 starting
GGSCI (DEV_SERVER as ggsadmin@MSSQL) 15> info all

Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT STOPPED EX01 00:00:00 00:00:42

GGSCI (DEV_SERVER as ggsadmin@MSSQL) 16> edit params ex01
GGSCI (DEV_SERVER as ggsadmin@MSSQL) 17> info ex01
EXTRACT EX01 Initialized 2020-07-16 16:43 Status STOPPED
Checkpoint Lag 00:00:00 (updated 00:03:41 ago)
VAM Read Checkpoint 2020-07-16 16:43:33.206000

GGSCI (DEV_SERVER as ggsadmin@MSSQL) 18> info ex01 detail

EXTRACT EX01 Initialized 2020-07-16 16:43 Status STOPPED
Checkpoint Lag 00:00:00 (updated 00:03:45 ago)
VAM Read Checkpoint 2020-07-16 16:43:33.206000
Target Extract Trails:
Trail Name Seqno RBA Max MB Trail Type
./dirdat/ms 0 0 2000 EXTTRAIL
Extract Source Begin End
Not Available * Initialized * 2020-07-16 16:43

Current directory C:\OGGMSSQL
Report file C:\OGGMSSQL\dirrpt\EX01.rpt (does not yet exist)
Parameter file C:\OGGMSSQL\dirprm\EX01.prm
Checkpoint file C:\OGGMSSQL\dirchk\EX01.cpe
Process file
Error log C:\OGGMSSQL\ggserr.log

GGSCI (DEV_SERVER as ggsadmin@MSSQL) 19> start ex01

Sending START request to MANAGER …
EXTRACT EX01 starting

GGSCI (DEV_SERVER as ggsadmin@MSSQL) 20> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT ABENDED EX01 00:00:00 00:11:16

GGSCI (DEV_SERVER as ggsadmin@MSSQL) 21> stop mgr
Manager process is required by other GGS processes.
Are you sure you want to stop it (y/n)?y

Sending STOP request to MANAGER …
Request processed.
Manager stopped.

GGSCI (DEV_SERVER as ggsadmin@MSSQL) 22> add trandata dbo. *

Logging of supplemental log data is enabled for table dbo.Categories in filegroup PRIMARY

Logging of supplemental log data is enabled for table dbo.Issues_Associated_With_Update in filegroup PRIMARY

Logging of supplemental log data is enabled for table dbo.SubCategories in filegroup PRIMARY

Logging of supplemental log data is enabled for table dbo.SystemSpec in filegroup PRIMARY

Logging of supplemental log data is enabled for table dbo.Dynamic_App_Properties in filegroup PRIMARY

Logging of supplemental log data is enabled for table dbo.PnPDevice_Installed_Driver in filegroup PRIMARY

Logging of supplemental log data is enabled for table dbo.PnPDevice_PnPID in filegroup PRIMARY

Logging of supplemental log data is enabled for table dbo.File_Opens_Url in filegroup PRIMARY

Logging of supplemental log data is enabled for table dbo.PnPID_Status in filegroup PRIMARY

安装结束，OGG for MSSQL 19.1.0.0.200204暂不支持MSSQL2019 (版本15.0.4043.16)

========================================================================
Oracle GoldenGate Capture for SQL Server
SQL Server Log Mining Method: CDC
Version 19.1.0.0.200204 OGGCORE_19.1.0.0.0OGGBP_PLATFORMS_200122.0600
Windows x64 (optimized), Microsoft SQL Server on Jan 22 2020 21:38:19
Starting at 2020-07-16 16:54:32

========================================================================
Driver Information:
SQLSRV32.DLL
Version 10.00.19041
ODBC Version 03.52.

2020-07-16 16:54:33 INFO OGG-01055 Recovery initialization completed for target file ./dirdat/ms000000000, at RBA 1337.
2020-07-16 16:54:33 INFO OGG-01478 Output file ./dirdat/ms is using format RELEASE 19.1.
2020-07-16 16:54:33 INFO OGG-01026 Rolling over remote file ./dirdat/ms000000000.
2020-07-16 16:54:33 INFO OGG-01053 Recovery completed for target file ./dirdat/ms000000001, at RBA 1396.
2020-07-16 16:54:33 INFO OGG-01057 Recovery completed for all targets.
2020-07-16 16:54:33 INFO OGG-00182 VAM API running in single-threaded mode.
2020-07-16 16:54:33 INFO OGG-01515 Positioning to begin time 2020年7月16日 下午4:43:33.