问题描述
在前期的文章中，实验了一个通过OGG工具实现SQL server到SQL server的实时同步案例测试，但是在测试过程中，遇到如下问题，在抽取进程和应用进程运行了一会之后，抽取进程就会出现如下提示，导致抽取进程状态显示RUNNING，但实际是没有同步数据的。checkpoint time延迟时间越来越长。尝试更换过SQL server的ODBC驱动也未解决问题，为此专门写这篇文档记录和解决该问题。
GGSCI (WIN-IPVE8GMRJ8Q) 1> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EXTSQL 00:00:00 01:14:26
REPLICAT RUNNING REPSQL 00:00:00 00:18:37
ODBC Driver 17 for SQL Server
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]从字符串转换日期和/或时间时，转换失败。.
SQL Server Native Client 11.0
2025-07-11 16:02:51 ERROR OGG-00551 Database operation failed: Execute direct. ODBC error: SQLSTATE 22007 native database error 241.
[Microsoft][SQL Server Native Client 11.0][SQL Server]从字符串转换日期和/或时间时，转换失败。.
具体的实验过程以及相关的脚本请参考：前期文章链接:
【绝对无坑版，亲测流程】oracle goldengate同步SQL server到SQL server的实时数据同步
错误日志：
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(??1CContextItem@@UEAA@XZ)]
: [D:\ogg21\gglog.dll(?CreateMessage@CMessageFactory@@QEAAPEAVCMessage@@PEAVCSourceContext@@IZZ)]
: [D:\ogg21\gglog.dll(?_MSG_String_String_String_Int32@@YAPEAVCMessage@@PEAVCSourceContext@@HPEBD11HW4MessageDisposition@CMessageFactory@@@Z)]
: [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的一个BUG。
Bug 33884867: SQL Server - Extract abends with "OGG-00551 Database operation failed: Execute direct. ODBC error: SQLSTATE 22007 native database error 241. [Microsoft][ODBC Driver 17 for SQL Server]["
Fixed an issue with translating data from different languages by setting the session language as us_english for manager connection.
根据MOS文档介绍：
Bug 33884867: SQL Server - Extract abends with “OGG-00551 Database operation failed: Execute direct. ODBC error: SQLSTATE 22007 native database error 241. [Microsoft][ODBC Driver 17 for SQL Server][”
Fixed an issue with translating data from different languages by setting the session language as us_english for manager connection.
根据ggserr.log日志显示，初步判断可能的错误触发点可能是Positioning ，或者LSN记录的时间。结合以上BUG的提示，可能的原因是操作的时间设置问题或者NLS_LANG参数配置问题，尝试在启动MGR进程之前set NLS_LANG参数，并在抽取进程，应用进程加上setenv参数指定NLS_LANG参数。
问题仍旧出现，重新启动进程之后运行几分钟之后就出现了时间转换错误的提示，同步停止。
setenv (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)
setenv (NLS_LANG = AMERICAN_AMERICA.AL32UTF8)
set NLS_LANG = AMERICAN_AMERICA.AL32UTF8
ggserr.log
2025-07-11T08:07:49.751+0800 INFO OGG-01515 Oracle GoldenGate Capture for SQL Server, EXTSQL.prm: Positioning to begin time **2025年7月11日 上午8:01:38.** <<<<<<<<<<<<<
2025-07-11T08:07:49.767+0800 INFO OGG-05352 Oracle GoldenGate Capture for SQL Server, EXTSQL.prm: Checking Oracle GoldenGate CDC object versions.
2025-07-11T08:07:49.798+0800 INFO OGG-05255 Oracle GoldenGate Capture for SQL Server, EXTSQL.prm: Current CDC Capture Settings - job name cdc.zdb_capture, maxtrans: 500, maxscans: 10, continuous: 1, polling interval: 5.
2025-07-11T08:07:49.814+0800 INFO OGG-05257 Oracle GoldenGate Capture for SQL Server, EXTSQL.prm: For CDC tuning best practices, please see https://technet.microsoft.com/en-us/library/dd266396%28v=sql.100%29.aspx.
2025-07-11T08:07:49.830+0800 INFO OGG-05332 Oracle GoldenGate Capture for SQL Server, EXTSQL.prm: The Oracle GoldenGate CDC Cleanup job, 'OracleGGCleanup_zdb_Job', created on 2025-07-11 08:07:33.297, is enabled in database, 'zdb'.
2025-07-11T08:07:49.830+0800 WARNING OGG-05280 Oracle GoldenGate Capture for SQL Server, EXTSQL.prm: Could not retrieve the Oracle GoldenGate CDC Cleanup Job settings for database zdb. Ensure that the Oracle GoldenGate CDC Cleanup Job exists and is enabled on the database.
2025-07-11T08:07:49.907+0800 INFO OGG-01517 Oracle GoldenGate Capture for SQL Server, EXTSQL.prm: Position of first record processed LSN: 00000026:00005b45:0001-00000000-00000026:00005302:0001, Tran: 0000:00001f5b, **2025年7月11日 上午8:06:55.** <<<<<<<<<<<<<
2025-07-11T08:07:55.189+0800 INFO OGG-00987 Oracle GoldenGate Command Interpreter for SQL Server: GGSCI command (Administrator): info all.
2025-07-11T08:07:58.689+0800 INFO OGG-00987 Oracle GoldenGate Command Interpreter for SQL Server: GGSCI command (Administrator): info all.
2025-07-11T08:08:00.611+0800 INFO OGG-00987 Oracle GoldenGate Command Interpreter for SQL Server: GGSCI command (Administrator): info all.
2025-07-11T08:08:03.439+0800 INFO OGG-00987 Oracle GoldenGate Command Interpreter for SQL Server: GGSCI command (Administrator): info extsql.
2025-07-11T08:12:49.923+0800 ERROR OGG-00551 Oracle GoldenGate Capture for SQL Server, EXTSQL.prm: Database operation failed: Execute direct. ODBC error: SQLSTATE 22007 native database error 241. [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]从字符串转换日期和/或时间时，转换失败。.
2025-07-11T08:12:49.955+0800 ERROR OGG-01668 Oracle GoldenGate Capture for SQL Server, EXTSQL.prm: PROCESS ABENDING.
2025-07-11T08:49:07.347+0800 INFO OGG-00987 Oracle GoldenGate Command Interpreter for SQL Server: GGSCI command (Administrator): info all.
2025-07-11T08:49:13.080+0800 INFO OGG-00987 Oracle GoldenGate Command Interpreter for SQL Server: GGSCI command (Administrator): info all.
尝试更新OGG patch 21.9.0.0，重新启动。运行了将近3个小时持续观察，进程正常运行。
GGSCI (WIN-IPVE8GMRJ8Q) 36> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EXTSQL 00:00:00 00:00:00
REPLICAT RUNNING REPSQL 00:00:00 00:00:06
GGSCI (WIN-IPVE8GMRJ8Q) 16> view report extsql
***********************************************************************
Oracle GoldenGate Capture for SQL Server
SQL Server Log Mining Method: CDC
Version 21.9.0.0.1 OGGCORE_21.9.0.0.0OGGRU_PLATFORMS_230120.0600
Windows x64 (optimized), Microsoft SQL Server on Jan 20 2023 21:20:48
Copyright (C) 1995, 2023, Oracle and/or its affiliates. All rights reserved.
Starting at 2025-07-11 17:30:18
***********************************************************************
Operating System Version:
Microsoft Windows Server 2016, on x64
Version 10.0 (Build 14393)
Process id: 4988
Description:
***********************************************************************
** Running with the following parameters **
***********************************************************************
2025-07-11 17:30:18 INFO OGG-03059 Operating system character set identified as GBK.
2025-07-11 17:30:18 INFO OGG-02695 ANSI SQL parameter syntax is used for parameter parsing.
2025-07-11 17:30:18 INFO OGG-02095 Successfully set environment variable NLS_LANG=AMERICAN_AMERICA.AL32UTF8.
EXTRACT extsql
setenv (NLS_LANG = AMERICAN_AMERICA.AL32UTF8)
sourcedb ssdb useridalias s_mssql
2025-07-11 17:30:18 WARNING OGG-05318 The SQL Server Native Client 11.0 (sqlncli11.dll) driver is not supported for SQL Server 2016. Supported drivers are ODBC Driver 13 for SQL Server (msodbcsql13.dll), ODBC Driver 17 for SQL Server (msodbcsql17.dll), ODBC Driver 18 for SQL Server (msodbcsql18.dll), DataDirect 8.0 SQL Server Wire Protocol (GGsqls25.dll).
2025-07-11 17:30:18 WARNING OGG-05203 The SQL Server Native Client 11 driver is known to cause a memory leak when used to connect to SQL Server 2012 or later.
2025-07-11 17:30:18 INFO OGG-03036 Database character set identified as windows-936. Locale: zh_CN.
2025-07-11 17:30:18 INFO OGG-03037 Session character set identified as GBK.
EXTTRAIL ./dirdat/et
TABLE dbo.users;
2025-07-11 17:30:18 INFO OGG-02734 Using heartbeat table from schema ggs.
2025-07-11 17:30:18 INFO OGG-01851 filecaching started: thread ID: 2272961484064.
2025-07-11 17:30:18 INFO OGG-01815 Virtual Memory Facilities for: COM
anon alloc: MapViewOfFile anon free: UnmapViewOfFile
file alloc: MapViewOfFile file free: UnmapViewOfFile
target directories:
D:\ogg21\dirtmp.
2025-07-11 17:30:18 WARNING OGG-01842 CACHESIZE PER DYNAMIC DETERMINATION (4G) LESS THAN RECOMMENDED: 64G (64bit system)
vm found: 5.13G
Check swap space. Recommended swap/extract: 128G (64bit system).
2025-07-11 17:30:18 INFO OGG-25340
Database Version:
Microsoft SQL Server - Enterprise Edition
Version 13.00.5026 (2016)
ODBC Version 03.80.0000
Driver Information:
sqlncli11.dll
Version 11.00.6518
ODBC Version 03.80.
2025-07-11 17:30:18 INFO OGG-25341
Database Language and Character Set:
Language = "zho"
Country = "CHN"
Collation Name = "Chinese_PRC_CI_AS"
Collation Description = "Chinese-PRC, case-insensitive, accent-sensitive, kanatype-insensitive, width-insensitive".
2025-07-11 17:30:18 INFO OGG-06604 Connected to database Microsoft SQL Server zdb, server WIN-IPVE8GMRJ8Q, DSN ssdb, CPU info: CPU Count 2, CPU Core Count 2, CPU Socket Count 1.
2025-07-11 17:30:18 INFO OGG-01055 Recovery initialization completed for target file ./dirdat/et000000019, at RBA 1419.
2025-07-11 17:30:18 INFO OGG-01478 Output file ./dirdat/et is using format RELEASE 19.1/21.1.
2025-07-11 17:30:18 INFO OGG-01026 Rolling over remote file ./dirdat/et000000019.
2025-07-11 17:30:18 INFO OGG-01053 Recovery completed for target file ./dirdat/et000000020, at RBA 1419.
2025-07-11 17:30:18 INFO OGG-01057 Recovery completed for all targets.
2025-07-11 17:30:18 INFO OGG-00182 VAM API running in single-threaded mode.
2025-07-11 17:30:18 INFO OGG-01513 Positioning to LSN: 00000026:0000708e:0003-ffffffff-00000026:0000708e:0003, Tran: 0000:00002a6b.
2025-07-11 17:30:18 WARNING OGG-05318 The SQL Server Native Client 11.0 (sqlncli11.dll) driver is not supported for SQL Server 2016. Supported drivers are ODBC Driver 13 for SQL Server (msodbcsql13.dll), ODBC Driver 17 for SQL Server (msodbcsql17.dll), ODBC Driver 18 for SQL Server (msodbcsql18.dll), DataDirect 8.0 SQL Server Wire Protocol (GGsqls25.dll).
2025-07-11 17:30:18 WARNING OGG-05203 The SQL Server Native Client 11 driver is known to cause a memory leak when used to connect to SQL Server 2012 or later.
2025-07-11 17:30:18 INFO OGG-05352 Checking Oracle GoldenGate CDC object versions.
2025-07-11 17:30:18 INFO OGG-05255 Current CDC Capture Settings - job name cdc.zdb_capture, maxtrans: 500, maxscans: 10, continuous: 1, polling interval: 5.
2025-07-11 17:30:18 INFO OGG-05257 For CDC tuning best practices, please see https://technet.microsoft.com/en-us/library/dd266396%28v=sql.100%29.aspx.
2025-07-11 17:30:18 INFO OGG-05332 The Oracle GoldenGate CDC Cleanup job, 'OracleGGCleanup_zdb_Job', created on 2025-07-11 08:07:33.297, is enabled in database, 'zdb'.
2025-07-11 17:30:18 WARNING OGG-05280 Could not retrieve the Oracle GoldenGate CDC Cleanup Job settings for database zdb. Ensure that the Oracle GoldenGate CDC Cleanup Job exists and is enabled on the database.
***********************************************************************
** Run Time Messages **
***********************************************************************
2025-07-11 17:30:48 INFO OGG-01971 The previous message, 'WARNING OGG-05280', repeated 1 times.
2025-07-11 17:31:19 INFO OGG-01517 Position of first record processed LSN: 00000026:0000772e:0003-00000000-00000026:0000772e:0001, Tran: 0000:00003025, 2025年7月11日 下午5:31:16.
2025-07-11 17:31:19 INFO OGG-05353 Checking Oracle GoldenGate CDC object versions for table dbo.users.
2025-07-11 17:31:19 INFO OGG-06507 MAP (TABLE) resolved (entry dbo.users): TABLE dbo.users.
2025-07-11 17:31:19 INFO OGG-06509 Using the following key columns for source table dbo.users: zid.
GGSCI (WIN-IPVE8GMRJ8Q) 17>
尝试做DML操作，验证数据同步正常。
参考文档
https://docs.oracle.com/en/middleware/goldengate/core/19.1/release-notes/bugs-fixed.html#GUID-24ABEA47-6078-4BE9-A007-4EDE0D7BA5DF
https://docs.oracle.com/en/middleware/goldengate/core/21.3/release-notes/bugs-fixed.html#GUID-B2B68FC6-7AFE-4218-A8E8-DB6C0824C0FB