Oracle GoldenGate 将 SQLServer 数据实时同步到 ORACLE

╰+攻爆jí腚メ 2023-07-20 13:47 20阅读 0赞

转载：[https://blog.csdn.net/kk185800961/article/details/53426348][https_blog.csdn.net_kk185800961_article_details_53426348]

[http://blog.itpub.net/26655292/viewspace-2120803/][http_blog.itpub.net_26655292_viewspace-2120803]  
含有许多ogg参数解释的文档

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 之间复制事务  
————————————————  
版权声明：本文为CSDN博主「薛定谔的DBA」的原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接及本声明。  
原文链接：https://blog.csdn.net/kk185800961/article/details/53426348

[https_blog.csdn.net_kk185800961_article_details_53426348]: https://blog.csdn.net/kk185800961/article/details/53426348
[http_blog.itpub.net_26655292_viewspace-2120803]: http://blog.itpub.net/26655292/viewspace-2120803/