OGG同步ORACLE至SQLSERVER(转)

系统环境

源库：ORACLE 11.2.0.4+RedHat 6.5

目标库：SQLSERVER 2014+windows

ogg for oracle

链接: https://pan.baidu.com/s/1kWuhBsN 密码: h5q1

ogg for mssql

链接: https://pan.baidu.com/s/1gg3Jmcr 密码: nc93

OGG下载地址：https://www.oracle.com/technetwork/middleware/goldengate/downloads/index.html

源库配置

数据库设置为归档模式

ALTER DATASBASE ARCHIVELOG;

 

开启强制日志

ALTER DATABASE FORCE LOGGING;

 

开启附加日志

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

 

修改参数

ALTER SYSTEM SET ENABLE_GOLDENGATE_REPLICATION=TRUE;

 

创建用户

1.  
   --创建用户表空间
2.  
   create tablespace ogg datafile '/u01/app/oracle/oradata/ogg.dbf' size 2G autoextend off;
3.  
   --创建用户
4.  
   create user ogg identified by ogg default tablespace ogg;
5.  
   --添加权限
6.  
   grant connect,resource,create session,alter session,select any dictionary,select any table,flashback any table,alter any table,insert any table,update any table,delete any table,select any transaction,execute on DBMS_CAPTURE_ADM to ogg;

解压安装

配置MGR进程

1.  
   GGSCI>edit params mgr
2.  
   port 7809
3.  
   DYNAMICPORTLIST 7840-7850
4.  
   PURGEOLDEXTRACTS /u01/ogg/testogg/dirdat/*,usecheckpoints,minkeepdays 7

创建defgen文件

1.  
   a.编辑defgen参数
2.  
   GGSCI> edit params defgen
3.  
   defsfile /u01/ogg/dirdef/sync.def
4.  
   userid ogg,password ogg?123
5.  
   table DC_DC.DC_RA_MER_BASE;
6.  
   b.生成defgen文件
7.  
   ./defgen paramfile /oraogg/app/dirprm/defgen.prm
8.  
   c.将sync.def复制到目标端dirdef目录下

添加表附加日志

GGSCI>add trandata DC_DC.DC_RA_MER_BASE; 

 

配置抽取进程

1.  
   a.创建trail文件目录
2.  
   mkdir -p /u01/ogg/dirdat/EXMSSQL
3.  
    
4.  
   b.添加抽取进程
5.  
   GGSCI>add extract extest01,tranlog,threads 1,begin now
6.  
   GGSCI>add exttrail /u01/ogg/dirdat/EXMSSQL/EX,extract extest01,megabytes 200
7.  
   GGSCI>edit param extest01
8.  
   extract extest01
9.  
   SETENV(NLS_LANG="AMERICAN_AMERICA.AL32UTF8")
10.  
    SETENV(ORACLE_SID="source")
11.  
    USERID ogg,PASSWORD ogg
12.  
    TRANLOGOPTIONS DBLOGREADER
13.  
    TRANLOGOPTIONS DBLOGREADER LOGRETENTION ENABLED
14.  
    warnlongtrans 4h,checkinterval 10m
15.  
    EXTTRAIL /u01/ogg/dirdat/EXMSSQL/EX
16.  
    GETTRUNCATES
17.  
    table DC_DC.DC_RA_MER_BASE;

配置投递进程

1.  
   GGSCI >add extract putest01,EXTTRAILSOURCE /u01/ogg/dirdat/EXMSSQL/EX
2.  
   GGSCI >add rmttrail R:oggdirdatREMSSQLRE,ext putest01,megabytes 200
3.  
   GGSCI >edit param PUMSSQL
4.  
   extract PUMSSQL
5.  
   USERID ogg, PASSWORD ogg?123
6.  
   SETENV (NLS_LANG="AMERICAN_AMERICA.ZHS16GBK")
7.  
   SETENV (ORACLE_SID = "source")
8.  
   rmthost 182.168.8.1, mgrport 7809
9.  
   rmttrail F:OGGdirdatREMSSQLRE
10.  
    PASSTHRU
11.  
    table DC_DC.DC_RA_MER_BASE;

目标库配置

配置ODBC接口

创建服务

>INSTALL ADDSERVICE 

 

编辑GLOBALS

1.  
   GGSCI >edit param ./GLOBALS
2.  
   checkpointtable dbo.ckpttab

创建checkpoint表

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

配置MGR进程

1.  
   GGSCI >edit params mgr
2.  
   port 7809
3.  
   DYNAMICPORTLIST 7840-7850
4.  
   ACCESSRULE, PROG SERVER, ALLOW
5.  
   autorestart er *, retries 5, waitminutes 3
6.  
   purgeoldextracts F:OGGdirdat*,usecheckpoints, minkeepdays 7

配置复制进程

1.  
   a. 创建trail存放目录
2.  
   F:oggdirdatREMSSQL
3.  
   b.添加复制进程
4.  
   GGSCI >add replicat REMSSQL,exttrail F:OGGdirdatREMSSQLRE,begin now,checkpointtable dbo.ckpttab
5.  
   GGSCI >edit param REMSSQL
6.  
   replicat REMSSQL
7.  
   sourcedefs F:OGGdirdefsync.def
8.  
   targetdb lu userid sa, password abcd123#
9.  
   reperror default,discard
10.  
    discardfile F:OGGdirrptREMSSQL.dsc,append,megabytes 100
11.  
    gettruncates
12.  
    map DC_DC.DC_RA_MER_BASE,target dbo.DC_RA_MER_BASE;

初始化数据

1.  
   a.在SQLSERVER创建相应表
2.  
   b.源端导出相应表初始化数据
3.  
   GGSCI> edit param exinit
4.  
   SOURCEISTABLE
5.  
   userid ogg, password ogg?123
6.  
   rmthost 182.168.8.1, mgrport 7809
7.  
   RMTFILE F:OGGdirdatREMSSQLEX,MAXFILES 1024, MEGABYTES 1024
8.  
   table test.dc_ra_mer_base;
9.  
    
10.  
    --OGG INSTALL DIRECTORY
11.  
    $ ./extract paramfile ./dirprm/exinit.prm reportfile ./dirrpt/exinit.rpt
12.  
    c.启动源端进程
13.  
    GGSCI>start *
14.  
    d.目标段导入相应初始化数据并启动进程
15.  
    replicat paramfile ./dirprm/reinit.prm
16.  
    GGSCI>start *

------- 作者：TouchYoni 来源：CSDN 原文：https://blog.csdn.net/touchyoni/article/details/79084983?utm_source=copy 版权声明：本文为博主原创文章，转载请附上博文链接！