数据迁移 - sqlserver ogg迁移到oracle - 《Oracle 优化教程》

预先准备工作
oracle 安装配置ogg
ogg配置
生产环境正式配置

环境介绍

数据库版本	主机	ogg版本
sql server 2012 enterprise	windows server 2016	19.1
oracle 19c	oracle linux 7	19.1

预先准备工作

1.安装sqlserver

sqlserver 2008-2014 必须企业版才能支持cdc ,2016 standard 必须修复bug才能做ogg
https://www.oracle.com/webfolder/technetwork/tutorials/obe/fmw/goldengate/12c/sql_cdcrep/sql_cdcrep.html

2.创建数据库

create database test

3.创建同步使用ogg用户

注意必须添加sysadmin

4.创建测试数据


create table [dbo].[emp] (
     [id] [smallint] not null,
     [first_name] varchar(50) not null,
     [last_name] varchar(50) not null,
constraint [emp_pk] primary key clustered (
     [id] asc
) with (pad_index = off, statistics_norecompute=off, ignore_dup_key=off, allow_row_locks=on, allow_page_locks=on) on [primary]
) on [primary]
go
-- TEST DATA
INSERT INTO [dbo].[emp] ([id], [first_name], [last_name]) VALUES (1,'Dave','Mustaine')
INSERT INTO [dbo].[emp] ([id], [first_name], [last_name]) VALUES (2,'Chris','Broderick')
INSERT INTO [dbo].[emp] ([id], [first_name], [last_name]) VALUES (3,'David','Ellefson')
INSERT INTO [dbo].[emp] ([id], [first_name], [last_name]) VALUES (4,'Shawn','Drover')
GO

oracle 安装配置ogg

1.添加环境变量

Linux 上安装 Oracle GoldenGate 与您刚才在 Windows 上进行的安装大同小异。您需要下载适用于 Linux 上的 Oracle 的 GoldenGate 介质包。创建一个安装目录并将压缩包解压缩到该目录。在本示例中，我使用 /opt/ogg 目录，因为 ORACLE_BASE 指向 /opt/ogg。完成该操作后，需要设置 PATH 和 LD_LIBRARY_PATH 环境变量，如下所示：
12c之后ogg可以进行图形化界面安装

export PATH=$PATH:/opt/ogg
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/opt/ogg
alias ggsci='rlwrap ggsci'

2.添加用户创建表结构

create  tablespace ogg;
create user ogg IDENTIFIED by "12345" DEFAULT TABLESPACE ogg;
grant  connect,resource,dba to ogg;
create table ogg.emp (id number not null, first_name varchar2(50), last_name varchar2(50));

3.修改数据库enable_goldengate_replication 参数

alter system set enable_goldengate_replication=TRUE scope=both;

ogg配置

sqlserver ogg配置

1.安装

在希望安装 Oracle GoldenGate 的位置（在本示例中为 C:\GG）解压缩下载的压缩包。然后打开命令提示符，转到 C:\ogg 目录，启动 GGSCI（GoldenGate 命令界面）：

C:\ogg>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.
Copyright (C) 1995, 2019, Oracle and/or its affiliates. All rights reserved.
GGSCI (WIN-0Q34MJVFKBA) 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 (WIN-0Q34MJVFKBA) 2> exit

2.创建管理服务

根据官方文档，GGSCI 支持每个 Oracle GoldenGate 实例最多 300 个并发的 Extract 和 Replicat 进程。不过，有一个进程负责控制其他进程；这个进程被称作 Manager 进程。虽然您可以手动运行此进程，但最好将其安装为服务，否则当启动该进程的用户注销时，该进程将停止。
要将 Manager 进程添加为 Windows 服务，请在 GoldenGate 安装目录中执行 INSTALL ADDSERVICE 命令。

edit params mgr
PORT 7809
DYNAMICPORTLIST 7840-7850
purgeoldextracts ./dirdat/*, usecheckpoints,minkeepdays 3
AUTORESTART EXTRACT *, RETRIES 3, WAITMINUTES 2, RESETMINUTES 30 
lagcriticalminutes 10 
lagreporthours 1 
ACCESSRULE, PROG *, IPADDR 192.168.5.*, ALLOW
--------------------------------------------------------------------------------------
C:\ogg>INSTALL ADDSERVICE
Service 'GGSMGR' created.
Install program terminated normally.

3.创建GLOBALS文件

注意如果不创建GLOBALS文件执行add trandata 命令会报错

GGSCI (WIN-0Q34MJVFKBA) 10> edit param ./GLOBALS
GGSCHEMA dbo      ##ogg为之前创建的schema
checkpointtable dbo.ogg_chkpt

4.设置端口

GGSCI (WIN-0Q34MJVFKBA) 6> edit params MGR
port 7809
AUTOSTART ER *     --MGR启动时自动启动所有进程
DYNAMICPORTLIST 7840-7850
AUTORESTART ER *, WAITMINUTES 5, RETRIES 5

5.sqlserver 服务器创建dsn

Microsoft SQL Server ODBC 驱动程序版本 10.00.14393
数据源名称: ogg
数据源描述: 1
服务器: 192.168.5.41
数据库: test
语言: (Default)
翻译字符数据: Yes
日志长运行查询: No
日志驱动程序统计: No
使用区域设置: No
预定义的语句选项: 在断开时删除临时存储过程
使用故障转移服务器: No
使用 ANSI 引用的标识符: Yes
使用 ANSI 的空值，填充和警告: Yes
数据加密: No

6.启用cdc

EXECUTE sys.sp_cdc_enable_db

7.删除数据库自带 cdc job

You must manually drop the SQL Server CDC cleanup job for the database because it may cause data loss for the Extract.

EXECUTE sys.sp_cdc_drop_job 'cleanup';

8.添加新的job

Use the ogg_cdc_cleanup_setup.bat utility (in the Oracle GoldenGate installation directory) to create the Oracle GoldenGate CDC cleanup job and associated objects. The ggschema name used must be the same that you used with the GGSCHEMA parameter of the GLOBALS file. You must use a SQL Server authenticated user that has sysadmin rights.

Example:
   ogg_cdc_cleanup_setup.bat createJob user1 pword1 dbname1 HOSTNAME\INSTANCE oggschema
   ogg_cdc_cleanup_setup.bat dropJob user1 pword1 dbname1 HOSTNAME\INSTANCE oggschema
C:\ogg>ogg_cdc_cleanup_setup.bat createjob ogg 12345 test 192.168.5.41   dbo
Oracle GoldenGate CDC cleanup job setup script
==============================================
Command: createjob
The Microsoft provided SQL Server Change Data Capture Cleanup job is enabled for database 'test'.
Disable or drop the Microsoft SQL Server Change Data Capture Cleanup job for database 'test' and re-run this program.

9.添加检查表

 dblogin sourcedb ogg    
add  checkpointtable dbo.ogg_chkpt
ADD TRANDATA dbo.ogg_chkpt

10.启动管理进程

如果创建了前面的ggsci 的服务则不用手动启动进程，在计算机启动时自动运行

ggsci
start mgr

11. 添加表

cd  c:ogg   > cmd
ggsci.exe
GGSCI (WIN-0Q34MJVFKBA) 1> dblogin sourcedb ogg
2021-01-13 15:11:05  INFO    OGG-03036  Database character set identified as windows-936. Locale: zh_CN.
2021-01-13 15:11:05  INFO    OGG-03037  Session character set identified as GBK.
Successfully logged into database.
add  trandata dbo.emp

12.创建defgen类型转换参数文件

edit params DEFGEN
 追加如下信息
-----------------------------------
defsfile c:\ogg\dirdef\emp.def
sourcedb ogg     ####ojdbc  名称
table dbo.emp;
------------------------------------
defgen paramfile c:\ogg\dirprm\defgen.prm    ##创建类型转换参数文件
C:\ogg>defgen paramfile c:\ogg\dirprm\defgen.prm       
***********************************************************************
      Oracle GoldenGate Table Definition Generator 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 21:27:01
Copyright (C) 1995, 2019, Oracle and/or its affiliates. All rights reserved.
                    Starting at 2021-01-06 21:19:28
***********************************************************************
Operating System Version:
Microsoft Windows Server 2016, on x64
Version 10.0 (Build 14393)
Process id: 1764
***********************************************************************
**            Running with the following parameters                  **
***********************************************************************
defsfile c:\ogg\dirdef\emp.def
sourcedb ogg
2021-01-06 21:19:29  INFO    OGG-03036  Database character set identified as windows-936. Locale: zh_CN.
2021-01-06 21:19:29  INFO    OGG-03037  Session character set identified as GBK.
table ogg.emp;
Retrieving definition for ogg.emp.
Definitions generated for 1 table in c:\ogg\dirdef\emp.def.

emp.def 文件内容如下所示

*+- Defgen version 7.0, Encoding GBK
*
* Definitions created/modified  2021-01-06 21:19
*
*  Field descriptions for each column entry:
*
*     1    Name
*     2    Data Type
*     3    External Length
*     4    Fetch Offset
*     5    Scale
*     6    Level
*     7    Null
*     8    Bump if Odd
*     9    Internal Length
*    10    Binary Length
*    11    Table Length
*    12    Most Significant DT
*    13    Least Significant DT
*    14    High Precision
*    15    Low Precision
*    16    Elementary Item
*    17    Occurs
*    18    Key Column
*    19    Sub Data Type
*    20    Native Data Type
*    21    Character Set
*    22    Character Length
*    23    LOB Type
*    24    Partial Type
*
Database type: MSSQL
Character set ID: windows-936
National character set ID: UTF-16
Locale: zh_CN
Case sensitivity: 00 00 00 00 00 00 00 00 00 00 00 00 11 00 00 00
TimeZone: Asia/Shanghai
*
Definition for table ogg.emp
Record length: 121
Syskey: 0
Columns: 3
id          134     23        0  0  0 1 0      8      8      8 0 0 0 0 1    0 1   0    5       -1      0 0 0
first_name   64     50       11  0  0 1 0     50     50      0 0 0 0 0 1    0 0   0   12     5001      0 0 0
last_name    64     50       66  0  0 1 0     50     50      0 0 0 0 0 1    0 0   0   12     5001      0 0 0
End of definition

拷贝文件到linux 目标端/opt/ogg/dirdef/ 目录下

13.配置 Extract 进程

首先在源计算机上配置 Extract 进程。将进程命名为 INEXT（表示 INitial EXTract，初始提取）。接下来，按照为 DEFGEN 实用程序创建参数文件的方式创建一个参数文件。文件名为 INEXT.PRM。

SOURCEISTABLE 参数指示 Extract 进程直接从表而不是从事务日志获取数据。这是我们为执行完整提取所期望的行为。SOURCEDB 指向包含数据的数据库。RMTHOST 和 MGRPORT 指定远程计算机和 Manager 端口。RMTFILE 指定所提取的数据将写入的文件。

EDIT PARAMS INEXT
SOURCEISTABLE
SOURCEDB ogg
RMTHOST 192.168.5.41, MGRPORT 7809
RMTFILE /opt/ogg/dirdat/ex
TABLE dbo.emp;

oracle ogg 配置

1.Manager 进程

ggsci
GGSCI (orcl) 1> EDIT PARAM MGR
PORT 7809

2.启动管理进程

GGSCI (orcl) 1> START MANAGER
Manager started.

3.配置replicat进程

接下来需要为 Replicat 进程设置参数。为此，创建一个新的参数文件并将其命名为 INLOAD（表示 INitial LOADing，初始加载）

SPECIALRUN 参数定义一个初始加载进程（这是不使用检查点的一次性加载）。文件中的下一行指示 Replicat 进程在加载完成后终止。
接下来提供了数据库用户和口令、提取文件以及表定义。最后一个参数 MAP 指示 Replicat 将表 dbo.EMP 重新映射到 ogg.EMP。

 EDIT PARAMS INLOAD
SPECIALRUN
END RUNTIME
USERID ogg, PASSWORD 12345
EXTFILE /opt/ogg/dirdat/ex
SOURCEDEFS /opt/ogg/dirdef/emp.def
MAP dbo.emp, TARGET ogg.emp;

初始化同步

sqlserver运行初始提取和加载

数据库和进程均已配置完成。现在可以启动初始加载，查看实际的数据复制过程。
首先需要运行 Extract 进程；它将提取 SQL Server 的 EMP 表中的所有数据，并将其写入 Linux 主机上的 RMTFILE (/opt/ogg/dirdat/ex) 中。
通过运行 EXTRACT 命令并提供参数和日志文件作为命令行参数来启动 Extract。

C:\ogg>extract paramfile dirprm\inext.prm reportfile dirrpt\inext.rpt
2021-01-14 08:36:53  INFO    OGG-01017  Wildcard resolution set to IMMEDIATE because SOURCEISTABLE is used.
***********************************************************************
               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
Copyright (C) 1995, 2019, Oracle and/or its affiliates. All rights reserved.
                    Starting at 2021-01-14 08:36:52
***********************************************************************
Operating System Version:
Microsoft Windows Server 2016, on x64
Version 10.0 (Build 14393)
Process id: 3236
Description:
***********************************************************************
**            Running with the following parameters                  **
***********************************************************************
2021-01-14 08:36:53  INFO    OGG-03059  Operating system character set identified as GBK.
2021-01-14 08:36:53  INFO    OGG-02695  ANSI SQL parameter syntax is used for parameter parsing.
2021-01-14 08:36:53  INFO    OGG-01360  EXTRACT is running in Initial Load mode.
2021-01-14 08:36:53  INFO    OGG-01889  Flush size (max message size) is set to 27,985.
2021-01-14 08:36:53  INFO    OGG-03036  Database character set identified as windows-936. Locale: zh_CN.
2021-01-14 08:36:53  INFO    OGG-03037  Session character set identified as GBK.
2021-01-14 08:36:53  INFO    OGG-01851  filecaching started: thread ID: 1498749716960.
2021-01-14 08:36:53  INFO    OGG-01815  Virtual Memory Facilities for: COM
    anon alloc: MapViewOfFile  anon free: UnmapViewOfFile
    file alloc: MapViewOfFile  file free: UnmapViewOfFile
    target directories:
    C:\ogg\dirtmp.
2021-01-14 08:36:54  WARNING OGG-01842  CACHESIZE PER DYNAMIC DETERMINATION (32G) LESS THAN RECOMMENDED: 64G (64bit system)
vm found: 50.85G
Check swap space. Recommended swap/extract: 128G (64bit system).
2021-01-14 08:36:54  INFO    OGG-25340
Database Version:
Microsoft SQL Server - Enterprise Edition
Version 11.00.2100 (2012)
ODBC Version 03.80.0000
Driver Information:
SQLSRV32.DLL
Version 10.00.14393
ODBC Version 03.52.
2021-01-14 08:36:54  INFO    OGG-06509  Using the following key columns for source table dbo.emp: id.
2021-01-14 08:37:01  INFO    OGG-01888  TCP network is configured as
                  OS DEFAULT          SPECIFIED           ACTUAL VALUE
         IP_DSCP    N/A                 N/A                 N/A
          IP_TOS    N/A                 N/A                 N/A
     TCP_NODELAY    768                 N/A                 768
       SO_SNDBUF    65536               N/A                 65536
       SO_RCVBUF    65536               N/A                 65536.
2021-01-14 08:37:01  INFO    OGG-01478  Output file /opt/ogg/dirdat/ex is using format RELEASE 19.1.
2021-01-14 08:37:02  INFO    OGG-02911  Processing table dbo.emp.
***********************************************************************
*                   ** Run Time Statistics **                         *
***********************************************************************
Report at 2021-01-14 08:37:02 (activity since 2021-01-14 08:36:54)
Output to /opt/ogg/dirdat/ex:
From Table dbo.emp:
       #                   inserts:         4
       #                   updates:         0
       #                   deletes:         0
       #                   upserts:         0
       #                  discards:         0

oracle 运行replicat

要将所提取的数据应用于目标数据库，请运行 replicat 命令并提供准备好的参数文件

 cd /opt/ogg
 ./replicat paramfile dirprm/inload.prm

确认数据导入到oracle

select   *  from ogg.emp;

实时数据捕获配置

Oracle Database 现在已有了 SQL Server 的 EMP 表的一个精确副本，可以创建实时捕获配置了。我们将 Extract 和 Replicat 进程配置成一直运行并不断传输/应用 EMP 表的更改。
为了实现这一新配置，需要为提取和复制创建新的参数文件。不过首先必须在 SQL Server 上另外执行两个步骤：确认数据库已设置为完全恢复，然后执行 EMP 数据库的完整数据库备份。如果执行完整备份失败，将阻碍 Extract 进程捕获实时数据更改

1. sqlserver创建新的extract 参数文件

这里不同的是我们省略了 SOURCEISTABLE 参数并引入一个新参数：TRANLOGOPTIONS MANAGESECONDARYTRUNCATIONPOINT。此选项告诉 Extract 进程定期检查和删除 CDC 捕获作业，从而提高性能并减小捕获数据所占用的空间

EDIT PARAMS MSEXT
EXTRACT MSEXT
SOURCEDB ogg
TRANLOGOPTIONS MANAGESECONDARYTRUNCATIONPOINT
RMTHOST 192.168.5.150, MGRPORT 7809
RMTTRAIL /opt/ogg/dirdat/ms
EOFDELAYCSECS 10
TABLE dbo.EMP;

2.添加日志组

ADD EXTRACT MSEXT, TRANLOG, BEGIN NOW
ADD RMTTRAIL /opt/ogg/dirdat/ms, EXTRACT MSEXT

3. oracle上创建一个检查点表

 DBLOGIN USERID ogg, PASSWORD 12345
 ADD CHECKPOINTTABLE ogg.chkpt

4.添加一个 Replicat 组并设置其参数

ADD REPLICAT MSREP, EXTTRAIL /opt/ogg/dirdat/ms, CHECKPOINTTABLE ogg.chkpt
EDIT PARAMS MSREP
REPLICAT MSREP
SOURCEDEFS /opt/ogg/dirdef/emp.def
USERID ogg, PASSWORD 12345
MAP dbo.emp, TARGET ogg.emp;

配置现已完成。下面我们来启动 Extract 和 Replicat 执行一些测试
准备实时同步
启动并测试联机事务复制

1.sqlserver 启动 Extract 进程

 start mgr 
 START EXTRACT MSEXT

2.oracle 启动 replicate 进程

START REPLICAT MSREP

重新配置同步

1.删除抽取进程和同步表

delete   msext
delete trandata dbo.emp1

2.删除同步进程

dblogin userid ogg password 12345
delete   msrep

3.初始化同步后重新添加抽取和同步进程

注意先删除之前初始化的文件，要不然会有重复数据

add trandata dbo.emp1    -----一定要在配置初始化同步之前添加，不然同步过程中新增的数据无法同步过去
ADD EXTRACT MSEXT, TRANLOG, BEGIN NOW
ADD RMTTRAIL /opt/ogg/dirdat/ms, EXTRACT MSEXT
--------------------------------------
ADD REPLICAT MSREP, EXTTRAIL /opt/ogg/dirdat/ms, CHECKPOINTTABLE ogg.chkpt

生产环境正式配置

1.sqlserver

1.GLOBALS文件

GGSCI (WIN-0Q34MJVFKBA) 10> edit param ./GLOBALS
GGSCHEMA dbo      ##ogg为之前创建的schema
checkpointtable dbo.ogg_chkpt

2.mgr文件

edit params mgr
PORT 7809
DYNAMICPORTLIST 7840-7850
purgeoldextracts c:\ogg\dirdat\*, usecheckpoints,minkeepdays 3
AUTORESTART EXTRACT *, RETRIES 3, WAITMINUTES 2, RESETMINUTES 30 
lagcriticalminutes 10 
lagreporthours 1 
ACCESSRULE, PROG *, IPADDR 192.168.5.*, ALLOW

2.extract 导出进程

EDIT PARAMS MSEXT
EXTRACT MSEXT
SOURCEDB ogg
TRANLOGOPTIONS MANAGESECONDARYTRUNCATIONPOINT
REPORTCOUNT EVERY 1 MINUTES, RATE
--每隔1分钟生成统计信息，用于STATS查看报告使用
EXTTRAIL c:\ogg\dirdat\ms
EOFDELAYCSECS 10
TABLE dbo.EMP;

ADD EXTRACT MSEXT, TRANLOG, BEGIN NOW 
ADD EXTTRAIL  C:\ogg\dirdat\ms, EXTRACT MSEXT

3.添加投递进程配置文件

edit  param mspump
EXTRACT mspump
RMTHOST 192.168.5.150, MGRPORT 7809
RMTTRAIL /opt/ogg/dirdat/cp
TABLE dbo.emp1;
TABLE dbo.emp;

4.注册投递进程

ADD EXTRACT mspump, EXTTRAILSOURCE C:\ogg\dirdat\ms
ADD RMTTRAIL  /opt/ogg/dirdat/cp, EXTRACT mspump,megabytes  20

oracle 配置

1.oracle mgr 配置文件

ggsci
GGSCI (orcl) 1> EDIT PARAM MGR
PORT 7809
DYNAMICPORTLIST 7840-7850
AUTORESTART ER *, WAITMINUTES 5, RETRIES 5
--自动重启所有的replicat进程，每隔5分钟重试，重试次数5次
purgeoldextracts   /opt/ogg/dirdat/*, usecheckpoints,minkeepdays 3

2.replicat配置文件

EDIT PARAM MSREP
REPLICAT MSREP
SOURCEDEFS /opt/ogg/dirdef/emp.def
USERID ogg, PASSWORD 12345
reperror  default,abend
discardfile /opt/ogg
/dirrpt/rep_error.dsc,append,megabytes 20
MAP dbo.emp, TARGET ogg.emp;
MAP dbo.emp1, TARGET ogg.emp1;

3.注册恢复进程

注意没有进行前面测试步骤直接进行配置生产环境的话需要添加checkpoint table

oracle上创建一个检查点表

 DBLOGIN USERID ogg, PASSWORD 12345
 ADD CHECKPOINTTABLE ogg.chkpt

ADD REPLICAT MSREP, EXTTRAIL ./dirdat/cp,CHECKPOINTTABLE ogg.chkpt

异常处理
1.2021-01-06 09:33:29 ERROR OGG-05263 No GGSCHEMA clause was specified in the GLOBALS file. Specify a GGSCHEMA schema name.
添加GLOBALS文件

edit param ./GLOBALS

2.extract 加入Manager管理进程start extract 失败
2021-01-14T23:17:48.102+0800 ERROR OGG-00551 Oracle GoldenGate Capture for SQL Server, MSEXT.prm: Database operation failed: Couldn’t connect to ogg. ODBC error: SQLSTATE 37000 native database error 4060. [Microsoft][ODBC SQL Server Driver][SQL Server]无法打开登录所请求的数据库 “test”。登录失败。.
2021-01-14T23:17:48.149+0800 ERROR OGG-01668 Oracle GoldenGate Capture for SQL Server, MSEXT.prm: PROCESS ABENDING.

打开Microsoft SQL Server Management Studio，在安全性->登录名->选择NT AUTHORITY\SYSTEM->右键->属性->服务器角色->勾选sysadmin就可以了
产生这个问题的原因在于GoldenGate的 manager(mgr)服务是以服务的方式运行的，在windows下是SYSTEM账户运行的，而SYSTEM账户可能没有权限访问Sql server数据库
3. The SQL Server (sqlsrv32.dll) driver is not supported for SQL Server 2012
2021-01-15T13:35:21.669+0800 ERROR OGG-05312 Oracle GoldenGate Capture for SQL Server, MSEXT.prm: The SQL Server (sqlsrv32.dll) driver is not supported for SQL Server 2012. Supported drivers are SQL Server Native Client 11.0 (sqlncli11.dll), ODBC Driver 11 for SQL Server (msodbcsql11.dll), ODBC Driver 13 for SQL Server (msodbcsql13.dll), ODBC Driver 17 for SQL Server (msodbcsql17.dll).
2021-01-15T13:35:21.702+0800 ERROR OGG-01668 Oracle GoldenGate Capture for SQL Server, MSEXT.prm: PROCESS ABENDING.
解决方法：
更换odbc驱动

异常处理

1.sql server端删除extract 抽取进程后重新添加进程

help add extract 对于sql server 通过lsn 添加抽取进程的描述


    Positioning by timestamp in a SQL Server transaction log is affected by the
    following characteristics of SQL Server:
      The timestamps recorded in the SQL Server transaction log
      use a 3.3333 microsecond (ms) granularity. This level of granularity may
      not allow positioning by time between two transactions, if the
      transactions began in the same 3.3333 ms time interval.
      Timestamps are not recorded in every SQL Server log
      record, but only in the records that begin and commit the transaction,
      as well as some others that do not contain data.
      SQL Server timestamps are not from the system clock, but
      instead are from an internal clock that is specific to the individual
      processors in use. This clock updates several times a second, but
      between updates it could get out of sync with the system clock. This
      further reduces the precision of positioning by time.
      Timestamps recorded for log backup files may not precisely
      correspond to times recorded inside the backup (however this imprecision
      is less than a second).
      Positioning to an LSN is precise.
LSN value
Valid for SQL Server. Specifies the LSN in a transaction log at which to
start capturing data. The specified LSN should exist in a log backup or the online
log. An alias for this option is EXTLSN.
For SQL Server, an LSN is composed of one of these, depending on how the
database returns it:
     Colon separated hex string (8:8:4) padded with
     leading zeroes and 0X prefix, as in
     0X00000d7e:0000036b:01bd
     Colon separated decimal string (10:10:5) padded with
     leading zeroes, as in 0000003454:0000000875:00445
     Colon separated hex string with 0X prefix and
     without leading zeroes, as in 0Xd7e:36b:1bd
     Colon separated decimal string without leading zeroes, as in
     3454:875:445
     Decimal string, as in 3454000000087500445
     In the preceding, the first value is the virtual log file number, the
     second is the segment number within the virtual log, and the third is the entry
     number.
     You can find the LSN for named transactions by using a query like:
     select [Current LSN], [Transaction Name], [Begin Time] from fn_dblog(null, null)
     where Operation = 'LOP_BEGIN_XACT' and [Begin Time] &gt;= 'time'
     The time format that you should use in the query should be similar to
     '2015/01/30 12:00:00.000' and not '2015-01-30 12:00:00.000'.
      You can determine the time that a particular transaction started, then
     find the relevant LSN, and then position between two transactions with the same
     begin time.
LSN value

(1)停止并删除抽取进程

stop   msext
delete msext 
查看停止进程的时间点

2、查看离停止时间最近的事务前一个事务
通过日志查看进程停止时间例如2021-06-30 12:52:16.205 注意将时间”-“改为”/“

 select [Current LSN], [Transaction Name], [Begin Time] from fn_dblog(null, null)
     where Operation = 'LOP_BEGIN_XACT' and [Begin Time]  between  '2021/06/30 12:52:00' 
     and '2021/06/30 12:53:00';

3.将lsn 16位事务日志转为10进制

DECLARE @LSN16 VARCHAR(50)
DECLARE @LSN10 NUMERIC(25, 0)
DECLARE @LSN VARCHAR(25)
Set @LSN16 = '000000d8:0000004a:0001' ---输入上面查询的lsn编号
SELECT @LSN10 = CONVERT(INT, CONVERT(VARBINARY, '0X' + SUBSTRING(@LSN16, 1,  8), 1)) * 1000000000000000
             +  CONVERT(INT, CONVERT(VARBINARY, '0X' + SUBSTRING(@LSN16, 10, 8), 1)) * 100000
             +  CONVERT(INT, CONVERT(VARBINARY, '0X' + SUBSTRING(@LSN16, 19, 4), 1))
SELECT @LSN10
SELECT @LSN = RIGHT('0000000000' + CONVERT(VARCHAR, @LSN10), 25)
SELECT CONVERT(VARCHAR(50), CONVERT(VARBINARY, CONVERT(INT, SUBSTRING(@LSN,  1, 10))), 2) + ':'
     + CONVERT(VARCHAR(50), CONVERT(VARBINARY, CONVERT(INT, SUBSTRING(@LSN, 11, 10))), 2) + ':'
     + CONVERT(VARCHAR(50), CONVERT(VARBINARY, CONVERT(INT, SUBSTRING(@LSN, 21, 5))), 2)

4.重新添加抽取进程

ADD EXTRACT MSEXT TRANLOG lsn 216000000007400001
--------------------------------------------------------------
如果是停业务状态，没有新事务，也可以直接用时间添加
ADD EXTRACT MSEXT, TRANLOG, BEGIN  2021-06-30 10:39:06

sqlserver ogg迁移到oracle