OGG配置从sqlserver到oracle12c同步

 介绍

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

 

 

二 服务器信息

服务器信息:

 端:172.16.1.111    服务器: win10       数据库: SQLSERVER V17.8

目标端:192.168.163.23  服务器:centos7.4     数据库: ORACLE 12.2.0.1.0

 

GoldenGate下载地址:

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

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

本次下载的两个 OGG :

V975837-01.zip 用户linux x64上oracle 12C使用

123016_ggs_Windows_x64_MSSQL_64bit_CDC.zip用户windows 64位sqlserver使用

 

Sqlserver源端重要条件:

企业版:SQL Server Enterprise Edition  

启动 SQL Server 代理 (自动)  

有权启用:Change Data Capture (CDC)  

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

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

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

ORACLE 目标端重要条件:

启用归档模式  

启用GoldenGate复制  

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

三 初始化配置

1 oracle 目标环境准备

SQL>ALTER SYSTEM SET ENABLE_GOLDENGATE_REPLICATION = TRUE SCOPE=BOTH;

SQL> alter database force logging;

SQL> alter database add supplemental log data;

Database altered.

SQL> alter system switch logfile;

System altered.

SQL> select supplemental_log_data_min from v$database;

SUPPLEME

--------

YES

SQL>create tablespace ggs datafile  '/u01/app/oradata/orcl/ggs01.dbf' size 3G autoextend on next 2G maxsize 20G  extent management local SEGMENT SPACE MANAGEMENT AUTO;

SQL>create user ogguser identified by ogguser default tablespace ggs temporary tablespace temp;

SQL>grant connect,resource,dba to ogguser;

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

SQL>grant execute on utl_file to ogguser;

SQL>grant execute on dbms_streams to ogguser;

SQL>grant execute on dbms_streams_adm to ogguser;

2 SQL Server 源端准备环境

--创建测试数据

use master

go

CREATE DATABASE crm01

go

use crm01

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

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

SELECT name, recovery_model_desc 

FROM sys.databases 

WHERE name = 'crm01' ; 

GO 

use master

go

alter database crm01 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

USE crm01

GO

alter schema ogguser TRANSFER OBJECT::dbo.tab;

GO

USE crm01

GO

alter schema ogguser TRANSFER OBJECT::dbo.tab2;

GO

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

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

数据源名称:crm01_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 [crm01] TO DISK= N'D:\MSSQL\Demo.bak' WITH CHECKSUM,COMPRESSION

GO

3 SQL Server 源端安装ogg

解压下载的安装包到D盘

3.1 创建相关目录

D:\OGG>ggsci.exe

 

Oracle GoldenGate Command Interpreter for SQL Server

Version 12.3.0.1.6 OGGCORE_12.3.0.1.0_PLATFORMS_180531.2007

Windows x64 (optimized), Microsoft SQL Server on Jun  1 2018 13:03:19

Operating system character set identified as GBK.

 

Copyright (C) 1995, 2018, Oracle and/or its affiliates. All rights reserved.

GGSCI (DESKTOP-N2A76JM) 1>  CREATE SUBDIRS

Creating subdirectories under current directory D:\ogg

 

Parameter files D:\ogg\dirprm: already exists

Report files D:\ogg\dirrpt: created

Checkpoint files D:\ogg\dirchk: created

Process status files D:\ogg\dirpcs: created

SQL script files D:\ogg\dirsql: created

Database definitions files D:\ogg\dirdef: created

Extract data files D:\ogg\dirdat: created

Temporary files D:\ogg\dirtmp: created

Stdout files D:\ogg\dirout: created

GGSCI (DESKTOP-N2A76JM) 2> edit param GLOBALS

GGSCHEMA ogguser

 

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

D:\ogg>INSTALL ADDSERVICE

Service 'GGSMGR' created.

Install program terminated normally.

3.3 配置 Manager 参数文件

GGSCI (MSSQL)> EDIT PARAM mgr

BOOTDELAYMINUTES 1

autostart extract *

AUTORESTART EXTRACT * ,RETRIES 20, RESETMINUTES 60

PORT 7809

DYNAMICPORTLIST 7840-7999

PURGEOLDEXTRACTS ./dirdat/*,USECHECKPOINTS

LAGCRITICALSECONDS 10

3.4 启动 GGSMGR 服务

GGSCI (MSSQL)> START MGR

 

4 oracle目标端ogg准备

4.1 目标表创建

SQL> conn crm/crm

CREATE TABLE crm.tab(

id number NOT NULL,

birthDate Date,

age number,

name varchar2(50),

constraint tab_pk primary key(id));

CREATE TABLE crm.tab2(

id number NOT NULL,

birthDate Date,

age number,

name varchar2(50),

constraint tab2_pk primary key(id));




CREATE TABLE crm.ihs_crm01(

id int,

name char(30),

sex char(30));

SQL> select table_name from user_tables;

 

TABLE_NAME

------------------------------

TAB

TAB2

 

4.2 目标端安装ogg

将目标端OGG安装至/u01/app/ogg目录下

进入OGG,创建必要的目录

[oracle@orabbix ~]$ cd /u01/app/ogg/

[oracle@orabbix ogg]$ ./ggsci

GGSCI (orabbix) 1> create subdirs

 

Creating subdirectories under current directory /u01/app/ogg

 

Parameter files                /u01/app/ogg/dirprm: already exists

Report files                   /u01/app/ogg/dirrpt: created

Checkpoint files               /u01/app/ogg/dirchk: created

Process status files           /u01/app/ogg/dirpcs: created

SQL script files               /u01/app/ogg/dirsql: created

Database definitions files     /u01/app/ogg/dirdef: created

Extract data files             /u01/app/ogg/dirdat: created

Temporary files                /u01/app/ogg/dirtmp: created

Stdout files                   /u01/app/ogg/dirout: created

GGSCI (orabbix) 2> edit param mgr

PORT 7809

DYNAMICPORTLIST 7840-7899

PURGEOLDEXTRACTS ./dirdat/*,USECHECKPOINTS

LAGCRITICALSECONDS 10

ACCESSRULE, PROG *, IPADDR 192.168.163.1 ALLOW

 

 

5 ORACLE 目标端

5.1 配置GLOBAL

GGSCI (orabbix) 5> edit params ./GLOBALS

GGSCI (orabbix) 6> view params ./GLOBALS

GGSCHEMA ogguser

CHECKPOINTTABLE ogguser.oggchkpt

添加完需要先退出再进入

5.2 添加检查表:

GGSCI (orabbix) 1> dblogin userid ogguser,password ogguser;

GGSCI (orabbix) 2> add checkpointtable ogguser.oggchkpt

5.3 配置MGR进程:

GGSCI (orabbix) 3> view param mgr

 

PORT 7809

DYNAMICPORTLIST 7840-7899

PURGEOLDEXTRACTS ./dirdat/*,USECHECKPOINTS

LAGCRITICALSECONDS 10

ACCESSRULE, PROG *, IPADDR 192.168.163.1 ALLOW

5.4 配置投递进程:

[oracle@ecbak ogg]$ mkdir discard




GGSCI (orabbix) 4> edit param rep_pa

REPLICAT rep_pa

USERID ogguser, PASSWORD ogguser

DISCARDFILE ./discard/repora.dsc, append, megabytes 10

SOURCEDEFS ./dirdef/crm_tables1.def

REPERROR (DEFAULT, DISCARD)

MAP ogguser.tab, TARGET crm.tab;

MAP ogguser.tab2, TARGET crm.tab2;

 

5.5 添加投递进程

GGSCI (orabbix) 6> ADD REPLICAT rep_pa,CHECKPOINTTABLE ogguser.oggchkpt,EXTTRAIL ./dirdat/pa

5.6 启动投递进程:

GGSCI (orabbix) 9> start replicat rep_pa

GGSCI (orabbix) 11> info all

Program Status Group Lag at Chkpt Time Since Chkpt

 

MANAGER RUNNING

REPLICAT RUNNING REP_PA 00:00:00 00:00:09

 

 

6 数据初始化

6.1 SQL Server源端服务器配置

6.1.1 从事务日志识别表信息

GGSCI (iaas-win2008-qa) 7> dblogin sourcedb crm01_PDBC userid ogguser password oggpsw

GGSCI (iaas-win2008-qa) 8> LIST TABLES ogguser.*

 

ogguser.tab

ogguser.tab2

GGSCI (iaas-win2008-qa) 9> ADD TRANDATA ogguser.tab

GGSCI (iaas-win2008-qa) 10> ADD TRANDATA ogguser.tab2 

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

GGSCI(MSSQL)> edit params defegen01

sourcedb crm01 userid ogguser password oggpsw

defsfile D:\ogg\dirdef\crm_tables1.def

table dbo.TAB;

table dbo.TAB2;

6.1.3 生成表定义文件 D:\ggs\dirdef\crm_tables1.def 和 D:\ggs\dirdef\crm_tables2.def

GGSCI(MSSQL)> exit

D:\ogg>defgen.exe paramfile D:\ogg\dirprm\defegen01.prm

/*

将生成的文件 D:\ogg\dirdef\crm_tables1.def复制到目标OGG目录 /u01/app/ogg/dirdef

修改权限:

[root@orabbix dirdef]# chown -Rf oracle:oinstall /u01/app/ogg/dirdef/*

 

6.2 配置抽取和传递进程

6.2.1 配置 Extract 文件

GGSCI(MSSQL)> view param ext_ea

EXTRACT ext_ea

SETENV (GGS_CacheRetryCount = 20)

SETENV (GGS_CacheRetryDelay = 3000)

SOURCEDB crm01, userid ogguser, password oggpsw

EXTTRAIL ./dirdat/ea

table ogguser.TAB;

table ogguser.TAB2;

 

6.2.2 添加 Extract 进程

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

GGSCI(MSSQL)> ADD EXTTRAIL ./dirdat/ea, EXTRACT ext_ea
GGSCI(MSSQL)> edit param ppm_pa

EXTRACT ppm_pa

RMTHOST 192.168.163.23, MGRPORT 7809, COMPRESS

RMTTRAIL ./dirdat/pa

-- PASSTHRU

SOURCEDB crm01, userid ogguser, password oggpsw

table ogguser.TAB;

table ogguser.TAB2;

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

6.2.4 添加 pump 进程

GGSCI(MSSQL)> ADD EXTRACT ppm_pa,EXTTRAILSOURCE ./dirdat/ea, BEGIN NOW

GGSCI(MSSQL)> ADD RMTTRAIL ./dirdat/pa, EXTRACT ppm_pa

6.2.5 启动进程

GGSCI(MSSQL)> START ext_ea

GGSCI(MSSQL)> START ppm_pa

6.3 初始化数据

6.3.1 源: 初始提取配置

GGSCI(MSSQL)> EDIT PARAMS INIT_EXT

SOURCEISTABLE

sourcedb crm01 userid ogguser, password oggpsw

RMTHOST 192.168.163.23, MGRPORT 7809

RMTFILE /u01/app/ogg/dirdat/ex

TABLE ogguser.tab;

TABLE ogguser.tab2;

6.3.2 目标: 初始加载配置

GGSCI (orabbix) 1> EDIT PARAMS INNIT_LOAD1

SPECIALRUN

END RUNTIME

USERID crm, PASSWORD crm

EXTFILE /u01/app/ogg/dirdat/ex

SOURCEDEFS /u01/app/ogg/dirdef/crm_tables1.def

MAP ogguser.tab,target crm.tab;

MAP ogguser.tab2,target crm.tab2;

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

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

 

6.3.4 目标: 加载数据到表

[oracle@orabbix ogg]$ ./replicat paramfile dirprm/innit_load1.prm

四 测试

1 源端MSSQL模拟插入新数据

USE crm01

GO

set identity_insert ogguser.tab on

insert into ogguser.tab(ID,BIRTHDATE,AGE,NAME) values(13,'2018-10-29',33,'tz凯亚');

set identity_insert ogguser.tab off

GO

2 目标端查看数据情况

 

3 源端删除数据测试

USE crm01

GO

delete from ogguser.tab where  id=13;

commit

GO

4 目标端查看数据删除情况

 

posted @   Eddie小陈  阅读(1072)  评论(0编辑  收藏  举报
相关博文:
阅读排行:
· 一个费力不讨好的项目,让我损失了近一半的绩效!
· 清华大学推出第四讲使用 DeepSeek + DeepResearch 让科研像聊天一样简单!
· 实操Deepseek接入个人知识库
· CSnakes vs Python.NET:高效嵌入与灵活互通的跨语言方案对比
· Plotly.NET 一个为 .NET 打造的强大开源交互式图表库
点击右上角即可分享
微信分享提示