0%

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