goldengate sqlserver mysql_Oracle GoldenGate 将 SQLServer 数据实时同步到 ORACLE

Oracle GoldenGate  (简称 OGG)使在不同关系型数据库之间能进行实时同步复制可谓非常强大。

OGG 支持 oracle、sql server、mysql、db2、Sybase 等关系数据库直接的数据复制。OGG  这种灵活特性能够支持多种业务场景。如下图：

OGG 实现原理如图：

OGG 有几个进程使数据同步或转移：Extract，Data pump，Replicat，Trails or extract files，Checkpoints，Manager，Collector

(更多参考官方文档：Introduction to Oracle GoldenGate)

以下进行一个小测试： Windows 平台利用 GoldenGate 同步 SQLServer 到 ORACLE

OGG 同步sql server 原理： 启用 Extract 进程后，sql server 中数据库及表将启用 变更数据捕获(CDC)，并读取相关日志中表的日志信息。本例中使用的是 pump 进程传输同步文件记录，这样Extract进程读取出来的记录都保存到文件夹 dirdat 中，保证即使中断也不会影响到数据库截断。(否则：Oracle GoldenGate For SQL Server 未提交事务导致MSSQL日志不截断)

服务器信息：

源端:   192.168.1.123服务器: windows server 2008 x64    数据库: MSSQL 2008 R2

目标端: 192.168.1.89 服务器: windows server 2008 x64    数据库: ORACLE oracle 11g.2.0.4.0

GoldenGate下载地址：

http://www.oracle.com/technetwork/cn/middleware/goldengate/downloads/index.html

https://edelivery.oracle.com/osdc/faces/SearchSoftware

本次测试下载的两个 OGG ：

ggs_121210_Windows_x64_MSSQL_64bit.zip (解压可用)

适用于 Windows(64 位)上 SQL Server 的 Oracle GoldenGate 12.1.2.1.0 版 (71 MB)

121210_ggs_Windows_x64_shiphome.zip (安装可用)

适用于 Windows(64 位)上 Oracle 的 Oracle GoldenGate 12.1.2.1.0 版 (239 MB)

使用 https://edelivery.oracle.com/osdc/faces/SearchSoftware  下载，参考：

SQL SERVER 源端重要条件:

企业版：SQL Server Enterprise Edition

启动 SQL Server 代理 (自动)

有权启用：Change Data Capture (CDC)

数据库须为完整回复(FULL)模式

MSSQL端权限：Extract：sysadmin ; REPLICAT：db_owner

当前都使用SQL认证(dblogin ……)

ORACLE 目标端重要条件:

启用归档模式

启用GoldenGate复制

创建用于OGG账户并授予相关权限

/*####################################################################

##  SQL Server源端配置

#####################################################################*/

--创建测试数据

use master

go

CREATE DATABASE Demo

go

use Demo

go

CREATE TABLE [dbo].[tab](

[id] [int] NOT NULL identity(1,1) primary key,

[birthDate] [datetime] NULL,

[age] [int] NULL,

[name] [varchar](50) NULL

) ON [PRIMARY]

GO

CREATE TABLE [dbo].[tab2](

[id] [int] NOT NULL identity(1,1) primary key,

[birthDate] [datetime] NULL,

[age] [int] NULL,

[name] [varchar](50) NULL

) ON [PRIMARY]

GO

INSERT INTO [dbo].[tab] VALUES(GETDATE()+RAND(),99,'kk')

GO 10

INSERT INTO [dbo].[tab2] VALUES(GETDATE()+RAND(),99,'kk')

GO 10

--关闭数据库 'trunc. log on chkpt'，

use master

go

EXEC sp_dboption 'Demo', 'trunc. log on chkpt', 'false'

go

--数据库须为完整回复(FULL)模式

use master

go

alter database Demo set recovery full

go

--创建数据库账号

USE [master]

GO

CREATE LOGIN [ogguser] WITH PASSWORD=N'oggpsw', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

GO

EXEC master..sp_addsrvrolemember @loginame = N'ogguser', @rolename = N'sysadmin'

GO

--创建ODBC 数据源 (开始-管理工具-数据源odbc)

/* 注：源端数据库驱动为 SQL Server (若sql server 作为目标端，则目标端驱动为 SQL Server Native Client 10.0)

数据源名称：Demo_ODBC

数据库账号：ogguser

数据库密码：oggpsw

*/

-- 查看数据库或表是否启用cdc (不需要启动,配置完成自动启动)

SELECT name,is_cdc_enabled FROM sys.databases WHERE is_cdc_enabled = 1

SELECT name,is_tracked_by_cdc FROM Demo.sys.tables WHERE is_tracked_by_cdc = 1

GO

--首次备份数据库(配置完成前不要截断日志)

BACKUP DATABASE [Demo] TO DISK= N'D:\MSSQL\Demo.bak' WITH CHECKSUM,COMPRESSION

GO

/*####################################################################

##  ORACLE目标端配置

#####################################################################*/

--查看是否归档

SQL> archive log list;

数据库日志模式非存档模式

自动存档禁用

存档终点 USE_DB_RECOVERY_FILE_DEST

最早的联机日志序列8

当前日志序列10

--设置归档模式

SQL> shutdown immediate;

SQL> startup mount;

SQL> alter database archivelog;

SQL> alter database open;

--查看日志附加属性

SQL> select supplemental_log_data_min,force_logging from v$database;

--设置日志附加属性

SQL> alter database add supplemental log data;

SQL> alter database force logging;

SQL> alter system switch logfile;

--启用 goldengate

SQL> alter system set enable_goldengate_replication = true scope=both;

--启用账号 scott (本测试以 scott 为例,真实环境另建!)

SQL> alter user scott identified by tiger account unlock;

SQL> grant connect, resource to scott;

SQL> grant select any dictionary,select any table to scott;

SQL> grant execute on utl_file to scott;

SQL> grant execute on dbms_streams to scott;

SQL> grant execute on dbms_streams_adm to scott;

--创建目标表

SQL> conn scott/tiger

CREATE TABLE scott.tab(

id number NOT NULL,

birthDate Date,

age number,

name varchar2(50),

constraint tab_pk primary key(id));

CREATE TABLE scott.tab2(

id number NOT NULL,

birthDate Date,

age number,

name varchar2(50),

constraint tab2_pk primary key(id));

/*####################################################################

##  SQL Server源端及 oracle 目标端配置(都为windows系统)

#####################################################################*/

--源端安装 GoldenGate: 解压 ggs_121210_Windows_x64_MSSQL_64bit.zip ,更名并移动到d盘(D:\ggs)

--目标端安装 GoldenGate: 解压 121210_ggs_Windows_x64_shiphome.zip 安装到d盘(D:\ggs)

--本测试中,源端和目标端的 ggs 位置相同,所以以下配置注意区别.

--创建相关目录

D:\ggs> GGSCI

GGSCI (MSSQL)> CREATE SUBDIRS

/*执行结果：

Parameter files D:\ggs\dirprm: created

Report files D:\ggs\dirrpt: created

Checkpoint files D:\ggs\dirchk: created

Process status files D:\ggs\dirpcs: created

SQL script files D:\ggs\dirsql: created

Database definitions files D:\ggs\dirdef: created

Extract data files D:\ggs\dirdat: created

Temporary files D:\ggs\dirtmp: created

Credential store files D:\ggs\dircrd: created

Masterkey wallet files D:\ggs\dirwlt: created

Dump files D:\ggs\dirdmp: created

*/

--将 Manager 进程添加为 Windows 服务(名称为: GGSMGR)

D:\ggs> INSTALL ADDSERVICE

/*执行结果：

Service 'GGSMGR' created.

Install program terminated normally.

*/

--配置 Manager 参数文件(内容如下)

GGSCI (MSSQL)> EDIT PARAM mgr

PORT 7809

DYNAMICPORTLIST 7840-7850

AUTORESTART ER *, WAITMINUTES 5, RETRIES 5

--启动 GGSMGR 服务 (删除服务: sc delete GGSMGR)

GGSCI (MSSQL)> START MGR

/*执行结果：

Starting Manager as service ('GGSMGR')...

Service started.

*/

--查看进程

GGSCI (MSSQL)> INFO ALL

GGSCI (MSSQL)> INFO MGR

注：

参数文件都存储在 dirprm 文件夹中; 错误信息存储在 dirrpt 文件夹中;

从安装 GoldenGate 到 启动 GGSMGR 服务,源端和目标端都一样配置(都为windows系统).

/*####################################################################

##  SQL Server源端配置

#####################################################################*/

--从事务日志识别表信息

GGSCI(MSSQL)> dblogin sourcedb Demo_ODBC userid ogguser password oggpsw

GGSCI(MSSQL)> LIST TABLES dbo.*

GGSCI(MSSQL)> ADD TRANDATA dbo.tab

GGSCI(MSSQL)> ADD TRANDATA dbo.tab2

--配置定义文件(内容如下)

GGSCI(MSSQL)> edit params defgen

sourcedb Demo_ODBC userid ogguser password oggpsw

defsfile D:\ggs\dirdef\Demo_tabless.def

table dbo.tab;

table dbo.tab2;

--生成表定义文件 D:\ggs\dirdef\Demo_tabless.def

GGSCI(MSSQL)> exit

D:\ggs> defgen paramfile D:\ggs\dirprm\defgen.prm

/*

将生成的文件 D:\ggs\dirdef\Demo_tabless.def 复制到目标OGG目录 D:\ggs\dirdef

Demo_tabless.def 文件内容如下:

*+- Defgen version 4.0, Encoding GBK

*

* Definitions created/modified 2016-12-01 01:14

*

* 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 dbo.tab

Record length: 109

Syskey: 0

Columns: 4

id 134 23 0 0 0 1 0 8 8 8 0 0 0 0 1 0 1 0 4 -1 0 0 0

birthDate 192 29 11 3 0 1 0 29 29 29 0 6 0 0 1 0 0 0 11 -1 0 0 0

age 134 23 43 0 0 1 0 8 8 8 0 0 0 0 1 0 0 0 4 -1 0 0 0

name 64 50 54 0 0 1 0 50 50 0 0 0 0 0 1 0 0 0 12 -1 0 0 0

End of definition

*

Definition for table dbo.tab2

Record length: 109

Syskey: 0

Columns: 4

id 134 23 0 0 0 1 0 8 8 8 0 0 0 0 1 0 1 0 4 -1 0 0 0

birthDate 192 29 11 3 0 1 0 29 29 29 0 6 0 0 1 0 0 0 11 -1 0 0 0

age 134 23 43 0 0 1 0 8 8 8 0 0 0 0 1 0 0 0 4 -1 0 0 0

name 64 50 54 0 0 1 0 50 50 0 0 0 0 0 1 0 0 0 12 -1 0 0 0

End of definition

*/

--配置 Extract 文件(内容如下)

GGSCI(MSSQL)> EDIT PARAMS EXTA

EXTRACT EXTA

SETENV ( NLS_LANG = "SIMPLIFIED CHINESE_CHINA.ZHS16GBK" )

sourcedb Demo_ODBC userid ogguser password oggpsw

TRANLOGOPTIONS MANAGESECONDARYTRUNCATIONPOINT

EXTTRAIL D:\ggs\dirdat\pr

EOFDELAYCSECS 10

table dbo.tab;

table dbo.tab2;

--添加 Extract 进程

GGSCI(MSSQL)> ADD EXTRACT EXTA, TRANLOG, BEGIN NOW

GGSCI(MSSQL)> ADD RMTTRAIL D:\ggs\dirdat\pr, EXTRACT EXTA

--配置 pump 文件(配置 REPLICAT 文件)

GGSCI(MSSQL)> EDIT PARAMS PUMPA

extract PUMPA

SETENV ( NLS_LANG = "SIMPLIFIED CHINESE_CHINA.ZHS16GBK" )

sourcedb Demo_ODBC userid ogguser, password oggpsw

rmthost 192.168.1.89, mgrport 7809, compress

rmttrail D:\ggs\dirdat\pr

EOFDELAYCSECS 10

table dbo.tab;

table dbo.tab2;

--添加 pump 进程

GGSCI(MSSQL)> ADD EXTRACT PUMPA,EXTTRAILSOURCE D:\ggs\dirdat\pr, BEGIN NOW

GGSCI(MSSQL)> ADD RMTTRAIL D:\ggs\dirdat\pr, EXTRACT PUMPA

--查看所有进程 或某个进程

GGSCI(MSSQL)> INFO ALL

GGSCI(MSSQL)> INFO MGR

--启动进程

GGSCI(MSSQL)> START EXTA

GGSCI(MSSQL)> START PUMPA

-- 查看数据库或表是否启用cdc

SELECT name,is_cdc_enabled FROM sys.databases WHERE is_cdc_enabled = 1

SELECT name,is_tracked_by_cdc FROM Demo.sys.tables WHERE is_tracked_by_cdc = 1

GO

/*####################################################################

##  SQL Server初始化数据

#####################################################################*/

--源: 初始提取配置

GGSCI(MSSQL)> EDIT PARAMS INEXT

SOURCEISTABLE

sourcedb Demo_ODBC userid ogguser, password oggpsw

RMTHOST 192.168.1.89, MGRPORT 7809

RMTFILE D:\ggs\dirdat\ex

TABLE dbo.tab;

TABLE dbo.tab2;

--目标: 初始加载配置

GGSCI(MSSQL)> EDIT PARAMS INLOAD

SPECIALRUN

END RUNTIME

USERID scott, PASSWORD tiger

EXTFILE D:\ggs\dirdat\ex

SOURCEDEFS D:\ggs\dirdef\Demo_tabless.def

MAP dbo.tab,target scott.tab;

MAP dbo.tab2,target scott.tab2;

--源: 提取数据到目标文件夹

D:\ggs> extract paramfile dirprm\inext.prm reportfile dirrpt\inext.rpt

--目标: 加载数据到表

D:\ggs> replicat paramfile dirprm/inload.prm

/*####################################################################

##  ORACLE 目标端配置

#####################################################################*/

--检查点用于存储 Extract 和 REPLICAT 进程的当前读/写位置

GGSCI(MSSQL)> DBLOGIN USERID scott, PASSWORD tiger

GGSCI(MSSQL)> ADD CHECKPOINTTABLE scott.chkpt

--配置同步进程

GGSCI(MSSQL)> EDIT PARAM MSREP

REPLICAT MSREP

SETENV ( NLS_LANG = "SIMPLIFIED CHINESE_CHINA.ZHS16GBK" )

USERID scott, PASSWORD tiger

SOURCEDEFS D:\ggs\dirdef\Demo_tabless.def

HANDLECOLLISIONS

ASSUMETARGETDEFS

MAP dbo.tab,target scott.tab;

MAP dbo.tab2,target scott.tab2;

--添加进程

GGSCI(MSSQL)> ADD REPLICAT MSREP,CHECKPOINTTABLE scott.chkpt,EXTTRAIL ./dirdat/pr

--启用进程

GGSCI(MSSQL)> START REPLICAT MSREP

--查看进程

GGSCI(MSSQL)> INFO ALL

GGSCI(MSSQL)> INFO MSREP

配置完成！！测试同步正常！！~~

注意：DDL 不支持

附图一： sql server 源端 OGG 进程信息

附图二： oracle 目标端 OGG 进程信息

附图三： oracle 目标端scott 表信息 (chkpt 及 chkpt_lox 为检查点记录创建的表)

参考：

Oracle GoldenGate 12c (12.1.2)

使用 Oracle GoldenGate 在 Microsoft SQL Server 和 Oracle Database 之间复制事务