OGG同步Oracle到SQLServer

安装OGG软件

选择数据库版本 ogg_install01

指定安装路径以及数据库路径 ogg_install02

OGG FOR SQL Server Download View Password：j6mh

数据库配置

开启强制日志、附加日志、并确定处于归档模式

1
2
3


SQL> ALTER DATABASE FORCE LOGGING;  
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA; 
SQL> alter system set enable_goldengate_replication=true;

源端和目标端创建表空间和用户

1
2
3
4
5
6
7


create tablespace ogg datafile '/u01/app/oracle/oradata ogg.dbf' size 5G autoextend off;
create user ogg identified by ogg default tablespace ogg;
grant connect,resource,create session,alter session,select any dictionary to ogg;
grant select any table,flashback any table,alter any table to ogg;
grant insert any table,update any table,delete any table,select any transaction to ogg;
grant execute on DBMS_CAPTURE_ADM to ogg;
grant execute on dbms_streams_adm to ogg;

源端进程配置

MGR管理进程配置

1
2
3
4


GGSCI>edit params mgr
port 7809
DYNAMICPORTLIST 7840-7850
PURGEOLDEXTRACTS /u01/ogg/dirdat/*,usecheckpoints,minkeepdays 7

编辑defgen参数

1
2
3
4


GGSCI> edit params defgen
defsfile /u01/ogg/dirdef/sync.def
userid ogg,password ogg?123
table TEST.TAB3; 

生成defgen文件并复制到目标端dirdef目录下

1

$ <OGG_HOME>/defgen paramfile /oraogg/app/dirprm/defgen.prm

抽取进程配置

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13


GGSCI>add extract extest01,tranlog,threads 1,begin now
GGSCI>add exttrail /u01/ogg/dirdat/EX,extract extest01,megabytes 200
GGSCI>edit param extest01
extract extest01
SETENV(NLS_LANG="AMERICAN_AMERICA.ZHS16GBK")
SETENV(ORACLE_SID="source")
USERID ogg,PASSWORD ogg
TRANLOGOPTIONS DBLOGREADER
TRANLOGOPTIONS DBLOGREADER LOGRETENTION ENABLED
warnlongtrans 4h,checkinterval 10m
EXTTRAIL /u01/ogg/dirdat/EX
GETTRUNCATES
table TEST.TAB3;

投递进程配置

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11


GGSCI >add extract putest01,EXTTRAILSOURCE /u01/ogg/dirdat/EX
GGSCI >add rmttrail R:\ogg\dirdat\RE,ext putest01,megabytes 200
GGSCI >edit param PUTEST01
extract PUTEST01
USERID ogg, PASSWORD ogg?123
SETENV (NLS_LANG="AMERICAN_AMERICA.ZHS16GBK")
SETENV (ORACLE_SID = "source")
rmthost 182.168.8.1, mgrport 7809
rmttrail F:\OGG\dirdat\RE
PASSTHRU
table TEST.TAB3;

目标端进程配置

创建ODBC

运行->odbcad32->系统DSN->SQL SERVER Native Client->创建一个连接到SQL Server的ODBC链接

创建服务(cmd)

1

INSTALL ADDSERVICE

MGR管理进程配置

1
2
3
4
5
6


GGSCI >edit params mgr
port 7809
DYNAMICPORTLIST 7840-7850
ACCESSRULE, PROG SERVER, ALLOW
autorestart er *, retries 5, waitminutes 3
purgeoldextracts F:\OGG\dirdat\*,usecheckpoints, minkeepdays 7

创建checkpoint表(lu为ODBC名称)

1
2


GGSCI >dblogin lu userid sa password abcd123#
GGSCI >add checkpointtable dbo.ckpttab

复制进程配置

 1
 2
 3
 4
 5
 6
 7
 8
 9
10


GGSCI >add replicat REMSSQL,exttrail F:\OGG\dirdat\RE,begin now,checkpointtable dbo.ckpttab
GGSCI >edit param REMSSQL
replicat REMSSQL
HANDLECOLLISIONS
sourcedefs F:\OGG\dirdef\sync.def
targetdb lu userid sa, password abcd123#
reperror default,discard
discardfile F:\OGG\dirrpt\REMSSQL.dsc,append,megabytes 100
gettruncates
map TEST.TAB3,target dbo.TAB3;

同步初始化

源端同步表添加附加日志

1
2


GGSCI>dblogin userid ogg,password ogg
GGSCI>add trandata TEST.*

源端创建初始化进程

1
2
3
4
5
6
7


GGSCI >add extract exinit,sourceistable
GGSCI >edit param exinit
extract exinit
userid ogg, password ogg
rmthost 192.168.1.112, mgrport 7809
rmttask replicat, group porarini
table TEST.TAB3;

目标端创建初始化进程

1
2
3
4
5
6
7


GGSCI >add replicate porarini, specialrun
GGSCI >edit param porarini
replicat poraini
assumetargetdefs
userid ogg, password ogg
discardfile ./dirrpt/poraini.dsc, purge
map TEST.TAB3,target dbo.TAB3;

启动源端所有进程

1
2


GGSCI>start extest01
GGSCI>start putest01

启动源端初始化进程

1

GGSCI>start exinit

初始化进程完成后启动目标端复制进程

1

GGSCI>start retest01

同步完成后取消HANDLECOLLISIONS参数

1

GGSCI>SEND RETEST01,NOHANDLECOLLISIONS