1. 项目介绍公司启动了某个大项目需要把生产数据实时或者非实时的抽取到一台镜像库供研发部门抽取到Doris以供下游系统使用。公司的数据库环境类型比较多有OracleMySQLpostgresqlSQL server等主要常见的集中数据库。针对这种数据同步OGG是一个非常成熟易用的工具不过有时候问题也会比较多。当然也有其他的同步工具有开源的非开源的等。各种软件都有各自的学习成本与维护成本。原理都差不多CDC抽取日志等方式。在之前的文章中有研究过了OracleMySQLpostgresql的同步也有测试了相关的可执行方案。SQL server类型的环境相对较少一直没有找机会测试验证方案近期需要同步的环境是SQL server的在网上找了不少案例但是执行起来都非常的难感觉留了不少。为此仅以此篇记录实验操作记录以便网友分享。也希望以大伙一起学习进步如果你觉得文章哪些地方有写得不对的地方或者不清楚的地方请留言给我我及时改进希望能写一些简单易懂的方案一起分享学习。2. 实验环境项目值操作系统WINDWOS SERVER 2016源端数据库Microsoft SQL Server 2016 (SP2) (KB4052908) - 13.0.5026.0 (X64) Mar 18 2018 09:11:49 Copyright © Microsoft Corporation Enterprise Edition (64-bit) on Windows Server 2016 Datacenter 10.0 (Build 14393: ) (Hypervisor)目标端数据库Microsoft SQL Server 2016 (SP2) (KB4052908) - 13.0.5026.0 (X64) Mar 18 2018 09:11:49 Copyright © Microsoft Corporation Enterprise Edition (64-bit) on Windows Server 2016 Datacenter 10.0 (Build 14393: ) (Hypervisor)OGG版本ogg 19.1 , ogg 21.43. 实验前准备4. 实验步骤在执行到添加trandata的时候出现如下错误但是实际上我已经添加了该参数了ERROR OGG-05263 No GGSCHEMA Clause Was Specified In The GLOBALS File. Please Specify A GGSCHEMA (Doc ID 2317616.1)根据MOS的提示create schema ggs. 回想到刚才在创建OGG同步用户的时候只是创建了一个login用户而么有创建schema。对于sqlserver版本的OGG, GLOBALS必须配置schema其他版本的貌似不设置也可以正常运行目前没发现什么问题。create schema schemname;再次成功添加trandata.接下来执行清理通过OGG创建一个JOB配置抽取进程--创建抽取进程 EDIT PARAMS extsql EXTRACT extsql sourcedb s_mssql useridalias s_mssql EXTTRAIL ./dirdat/et TABLE dbo.ztab; ADD EXTRACT extsql, TRANLOG, BEGIN NOW ADD EXTTRAIL ./dirdat/et, EXTRACT extsql --登录源端并启动抽取进程 dblogin sourcedb s_mssql useridalias s_mssql start extsql但是启动进程失败了。暂未找到解决方法。尝试更新OGG版本为ogg 21.4在第一次启动的时候仍然出现同样的问题2025-07-11 08:01:46 ERROR OGG-05338 The versions of the Oracle GoldenGate CDC objects on database, zdb, are not consistent with the expected version, 6. The following version(s) were found: 2. Rerun ADD TRANDATA for all tables previously enabled, including heartbeat, heartbeat seed, and filter tables. 2025-07-11 08:01:46 ERROR OGG-01668 PROCESS ABENDING.尝试重建CDC之后再次启动抽取进程正常。GGSCI (WIN-IPVE8GMRJ8Q as oggadminS_MSSQL) 14 info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING EXTRACT RUNNING EXTSQL 00:00:00 00:00:00 GGSCI (WIN-IPVE8GMRJ8Q as oggadminS_MSSQL) 15 info extsql Extract EXTSQL Last Started 2025-07-11 08:07 Status RUNNING Checkpoint Lag 00:00:00 (updated 00:00:03 ago) Process ID 4064 VAM Read Checkpoint 2025-07-11 08:06:58.546666 LSN: 00000026:00005b45:0001-ffffffff-00000026:00005b45:0001, Tran: 0000:00001f5b创建应用进程GGSCI (WIN-IPVE8GMRJ8Q) 5 dblogin sourcedb t_mssql useridalias t_mssql2 2025-07-11 09:01:15 INFO OGG-03036 Database character set identified as windows-936. Locale: zh_CN. 2025-07-11 09:01:15 INFO OGG-03037 Session character set identified as GBK. Successfully logged into database. GGSCI (WIN-IPVE8GMRJ8Q as toggadminT_MSSQL) 7 edit param repsql GGSCI (WIN-IPVE8GMRJ8Q as toggadminT_MSSQL) 8 ADD CHECKPOINTTABLE ggs.oggcheck Successfully created checkpoint table ggs.oggcheck. GGSCI (WIN-IPVE8GMRJ8Q as toggadminT_MSSQL) 9 GGSCI (WIN-IPVE8GMRJ8Q as toggadminT_MSSQL) 9 view param repsql REPLICAT repsql TARGETDB t_mssql useridalias t_mssql2 MAP dbo.ztab, TARGET dbo.ztab; GGSCI (WIN-IPVE8GMRJ8Q as toggadminT_MSSQL) 10 GGSCI (WIN-IPVE8GMRJ8Q as toggadminT_MSSQL) 10 ADD REPLICAT repsql, EXTTRAIL ./dirdat/et, CHECKPOINTTABLE ggs.oggcheck Replicat added.启动应用进程失败提示不支持SQLSERVER 2008根据提示更换数据库版本之后启动正常。但是发现一个问题抽取进程刚开始启动执行正常后面又有点不正常了。貌似是卡住了延迟时间越来越长。2025-07-11 09:08:09 ERROR OGG-05310 Oracle GoldenGate does not support SQL Server 2008. Supported versions are SQL Server 2012, SQL Server 2014, SQL Server 2016, SQL Server 2017, SQL Server 2019. 2025-07-11 09:08:09 ERROR OGG-01668 PROCESS ABENDING.GGSCI (WIN-IPVE8GMRJ8Q as oggtargetuserTGTMSSQL) 27 info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING EXTRACT RUNNING EXTSQL 00:00:00 00:32:07 REPLICAT RUNNING REPSQL 00:36:17 00:00:06抽取进程窗口应用进程窗口验证数据同步源端插入数据目标端没有同步。尝试停止进程也失败了use zdb go insert into ztab values(4,zname,zinfonew); select * from zdb.dbo.ztab; select * from targetdb.dbo.ztab;GGSCI (WIN-IPVE8GMRJ8Q as oggtargetuserTGTMSSQL) 33 stop extsql Sending STOP request to Extract group EXTSQL ... 2025-07-11 09:37:40 ERROR OGG-15163 There was a problem sending a message to application EXTRACT, group EXTSQL, detail: (Timeout waiting for message).直接关闭抽取进程窗口后进程停止。重新启动进程正常。查询目标端数据已经同步过来。再次尝试DML操作验证同步正常。执行删除操作的时候失败了是因为源端有一条数据没有初始化到目标端所以提示找不到数据。2025-07-11 09:45:44 WARNING OGG-01154 SQL error 100 mapping source table dbo.ztab to target tabledbo.ztab. Database error: No data found. Source Context : SourceModule : [er.replicat.errors] SourceID : [er/replicat/reperrors.cpp] SourceMethod : [ggs::er::ReplicatContext::repError] SourceLine : [1832] ThreadBacktrace : [16] elements : [D:\ogg21\gglog.dll(??1CContextItemUEAAXZ)] : [D:\ogg21\gglog.dll(?CreateMessageCMessageFactoryQEAAPEAVCMessagePEAVCSourceContextIZZ)] : [D:\ogg21\gglog.dll(?_MSG_QualTableName_QualTableNameYAPEAVCMessagePEAVCSourceContextHAEBV?$CQualDBObjName$00ggappgglibggs1W4MessageDispositionCMessageFactoryZ)] : [D:\ogg21\replicat.exe(ERCALLBACK)] : [D:\ogg21\replicat.exe(ERCALLBACK)] : [D:\ogg21\replicat.exe(ERCALLBACK)] : [D:\ogg21\replicat.exe(ERCALLBACK)] : [D:\ogg21\replicat.exe(ERCALLBACK)] : [D:\ogg21\replicat.exe(ERCALLBACK)] : [D:\ogg21\replicat.exe(_ggTryDebugHook)] : [D:\ogg21\replicat.exe(_ggTryDebugHook)] : [D:\ogg21\replicat.exe(_ggTryDebugHook)] : [D:\ogg21\replicat.exe(ERCALLBACK)] : [D:\ogg21\replicat.exe(CommonLexerNewSSD)] : [C:\Windows\System32\KERNEL32.DLL(BaseThreadInitThunk)] : [C:\Windows\SYSTEM32\ntdll.dll(RtlUserThreadStart)] 2025-07-11 09:45:44 ERROR OGG-01296 Error mapping from dbo.ztab to dbo.ztab.把数据补会目标端之后重启进程正常。写在最后在前面提到抽取进程运行一会就会自动停止从report中查看到如下错误信息Source Context : SourceModule : [gglib.ggdbsql.driver.odbc] SourceID : [ggdbsql/odbc/ODBCDriver.cpp] SourceMethod : [ggs::gglib::ggdbsql::CODBCResult::ExecuteDirect] SourceLine : [1975] ThreadBacktrace : [13] elements : [D:\ogg21\gglog.dll(??1CContextItemUEAAXZ)] : [D:\ogg21\gglog.dll(?CreateMessageCMessageFactoryQEAAPEAVCMessagePEAVCSourceContextIZZ)] : [D:\ogg21\gglog.dll(?_MSG_String_String_String_Int32YAPEAVCMessagePEAVCSourceContextHPEBD11HW4MessageDispositionCMessageFactoryZ)] : [D:\ogg21\extract.exe(VAMVersion)] : [D:\ogg21\extract.exe(VAMVersion)] : [D:\ogg21\extract.exe(VAMVersion)] : [D:\ogg21\extract.exe(VAMVersion)] : [D:\ogg21\extract.exe(VAMVersion)] : [D:\ogg21\extract.exe(VAMVersion)] : [D:\ogg21\extract.exe(VAMVersion)] : [D:\ogg21\extract.exe(_ggTryDebugHook)] : [C:\Windows\System32\KERNEL32.DLL(BaseThreadInitThunk)] : [C:\Windows\SYSTEM32\ntdll.dll(RtlUserThreadStart)] 2025-07-11 10:02:39 ERROR OGG-00551 Database operation failed: Execute direct. ODBC error: SQLSTATE 22007 native database error 241. [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]从字符串转换日期和/或时间时转换失败。. ***********************************************************************根据以上的错误信息在MOS上查找到相关的文档在OGG 19.1 21版本均存在这个BUG参考文档https://docs.oracle.com/en/middleware/goldengate/core/19.1/oggmp/oracle-goldengate-classic-sql-server.html#GUID-948C5BEE-E7A0-4CE2-BE09-F83145677D18https://docs.oracle.com/en/middleware/goldengate/core/21.3/ggcab/other-programs-and-settings-sql-server-19.1.htmlhttps://learn.microsoft.com/en-us/sql/connect/odbc/linux-mac/installing-the-microsoft-odbc-driver-for-sql-server?viewsql-server-2016tabsalpine18-install%2Credhat17-install%2Credhat7-install%2Credhat7-13-install%2Crhel7-offlinehttps://docs.oracle.com/en/middleware/goldengate/core/19.1/obcdc/https://docs.oracle.com/en/middleware/goldengate/core/21.3/release-notes/bugs-fixed.html#GUID-B2B68FC6-7AFE-4218-A8E8-DB6C0824C0FBhttps://docs.oracle.com/en/middleware/goldengate/core/19.1/release-notes/bugs-fixed.html#GUID-6594ACD7-AADA-4011-895E-EC1EF37C86B1附详细的实验测试过程脚本请转移到个人文档中下载问题记录在SQL server 2017 RTM版本添加TRANDATA的时候出现如下错误提示需要打补丁最低要CU4.在SQL server 2016 RTM版本添加TRANDATA的时候出现如下错误提示需要打补丁最低要CU5.GGSCI (WIN-H8J332K8OJQ as oggadminSSDB) 11 ADD TRANDATA dbo.users FILEGROUP cdcfg2025-07-13 00:11:25 ERROR OGG-05376 Oracle GoldenGate support for SQL Server 2016 RTM requires a database patch CU5 or above. For details, see https://support.microsoft.com/en-us/help/3030352/fix-the-change-table-is-ordered-incorrectly-for-updated-rows-after-you-enable-change-data-capture-for-a-microsoft-sql-server-database.GGSCI (WIN-H8J332K8OJQ as oggadminSSDB) 12