系统环境

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 goldengate identified by goldengate;
5. --添加权限
6. grant dba to goldengate;
   
   
   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
   port 7809
   DYNAMICPORTLIST 7840-7850
   PURGEOLDEXTRACTS /ogg/software/dirdat/e_tab/*,usecheckpoints,minkeepdays 7

创建defgen文件

1. a.编辑defgen参数
   
   
   GGSCI> edit params defgen

   defsfile /ogg/software/dirdef/tab.def

   userid goldengate,password goldengate

   table EAS2.CT_DSY_REPORTSYN001;

   table EAS2.CT_DSY_REPORTSYN002;

   table EAS2.CT_DSY_REPORTSYN003;

   table EAS2.CT_DSY_REPORTSYN004;

   table EAS2.CT_DSY_REPORTSYN005;

   table EAS2.CT_DSY_REPORTSYN006;

   table EAS2.CT_DSY_REPORTSYN007;

   table EAS2.CT_DSY_REPORTSYN008;

   table EAS2.CT_DSY_REPORTSYN009;

   table EAS2.CT_DSY_REPORTSYN010;

   table EAS2.CT_DSY_REPORTSYN011;

   table EAS2.CT_DSY_REPORTSYN012;

   table EAS2.CT_DSY_REPORTSYN013;

   table EAS2.CT_DSY_REPORTSYN014;

   table EAS2.CT_DSY_REPORTSYN015;

   table EAS2.CT_DSY_REPORTSYN016;

   table EAS2.CT_DSY_REPORTSYN017;

   table EAS2.CT_DSY_REPORTSYN018;

   table EAS2.CT_DSY_REPORTSYN019;

   table EAS2.CT_DSY_REPORTSYN020;

   table EAS2.CT_DSY_REPORTSYN020S1;

   table EAS2.CT_DSY_REPORTSYN021;

   table EAS2.CT_DSY_REPORTSYN021S1;

   table EAS2.CT_DSY_REPORTSYN022;

   table EAS2.CT_DSY_REPORTSYN023;

   table EAS2.CT_DSY_REPORTSYN024;

   table EAS2.CT_DSY_REPORTSYN025;

   table EAS2.CT_DSY_REPORTSYN026;

   table EAS2.CT_DSY_REPORTSYN027;

   table EAS2.CT_DSY_REPORTSYN028;

   table EAS2.CT_DSY_REPORTSYN029;

   table EAS2.CT_DSY_REPORTSYN030;

   table EAS2.CT_DSY_REPORTSYN031;

   table EAS2.CT_DSY_REPORTSYN032;

   table EAS2.CT_DSY_REPORTSYN033;

   table EAS2.CT_DSY_REPORTSYN034;

   table EAS2.CT_DSY_REPORTSYN035;

   table EAS2.CT_DSY_REPORTSYN036;

   
   

   
   
   b.生成defgen文件
   ./defgen paramfile /ogg/software/dirprm/defgen.prm
   c.将tab.def复制到目标端dirdef目录下

添加表附加日志

GGSCI>dblogin userid goldengate, password goldengate
add trandata   EAS2.CT_DSY_REPORTSYN001
add trandata   EAS2.CT_DSY_REPORTSYN002
add trandata   EAS2.CT_DSY_REPORTSYN003
add trandata   EAS2.CT_DSY_REPORTSYN004
add trandata   EAS2.CT_DSY_REPORTSYN005
add trandata   EAS2.CT_DSY_REPORTSYN006
add trandata   EAS2.CT_DSY_REPORTSYN007
add trandata   EAS2.CT_DSY_REPORTSYN008
add trandata   EAS2.CT_DSY_REPORTSYN009
add trandata   EAS2.CT_DSY_REPORTSYN010
add trandata   EAS2.CT_DSY_REPORTSYN011
add trandata   EAS2.CT_DSY_REPORTSYN012
add trandata   EAS2.CT_DSY_REPORTSYN013
add trandata   EAS2.CT_DSY_REPORTSYN014
add trandata   EAS2.CT_DSY_REPORTSYN015
add trandata   EAS2.CT_DSY_REPORTSYN016
add trandata   EAS2.CT_DSY_REPORTSYN017
add trandata   EAS2.CT_DSY_REPORTSYN018
add trandata   EAS2.CT_DSY_REPORTSYN019
add trandata   EAS2.CT_DSY_REPORTSYN020
add trandata   EAS2.CT_DSY_REPORTSYN020S1
add trandata   EAS2.CT_DSY_REPORTSYN021
add trandata   EAS2.CT_DSY_REPORTSYN021S1
add trandata   EAS2.CT_DSY_REPORTSYN022
add trandata   EAS2.CT_DSY_REPORTSYN023
add trandata   EAS2.CT_DSY_REPORTSYN024
add trandata   EAS2.CT_DSY_REPORTSYN025
add trandata   EAS2.CT_DSY_REPORTSYN026
add trandata   EAS2.CT_DSY_REPORTSYN027
add trandata   EAS2.CT_DSY_REPORTSYN028
add trandata   EAS2.CT_DSY_REPORTSYN029
add trandata   EAS2.CT_DSY_REPORTSYN030
add trandata   EAS2.CT_DSY_REPORTSYN031
add trandata   EAS2.CT_DSY_REPORTSYN032
add trandata   EAS2.CT_DSY_REPORTSYN033
add trandata   EAS2.CT_DSY_REPORTSYN034
add trandata   EAS2.CT_DSY_REPORTSYN035
add trandata   EAS2.CT_DSY_REPORTSYN036

配置抽取进程

1. a.创建trail文件目录
2. mkdir -p /u01/ogg/dirdat/EXMSSQL
4. b.添加抽取进程
5. GGSCI>add extract e_tab,tranlog,threads 1,begin now
   
   
   GGSCI>add exttrail /ogg/software/dirdat/e_tab/ex,extract e_tab,megabytes 200
   
   
   GGSCI>edit param e_tab
   
   
   
   

   extract e_tab

   SETENV (ORACLE_HOME = "/data/oracle/product/11.2.0/db_1")

   SETENV (ORACLE_SID = "eastest")

   SETENV (NLS_LANG = "AMERICAN_AMERICA.AL32UTF8")

   userid goldengate,password goldengate

   exttrail /ogg/software/dirdat/e_tab/ex

   TRANLOGOPTIONS DBLOGREADER

   EOFDELAYCSECS 50

   FLUSHCSECS 50

   dynamicresolution

   defsfile /ogg/software/dirdef/tab.def

   userid goldengate,password goldengate

   table EAS2.CT_DSY_REPORTSYN001;

   table EAS2.CT_DSY_REPORTSYN002;

   table EAS2.CT_DSY_REPORTSYN003;

   table EAS2.CT_DSY_REPORTSYN004;

   table EAS2.CT_DSY_REPORTSYN005;

   table EAS2.CT_DSY_REPORTSYN006;

   table EAS2.CT_DSY_REPORTSYN007;

   table EAS2.CT_DSY_REPORTSYN008;

   table EAS2.CT_DSY_REPORTSYN009;

   table EAS2.CT_DSY_REPORTSYN010;

   table EAS2.CT_DSY_REPORTSYN011;

   table EAS2.CT_DSY_REPORTSYN012;

   table EAS2.CT_DSY_REPORTSYN013;

   table EAS2.CT_DSY_REPORTSYN014;

   table EAS2.CT_DSY_REPORTSYN015;

   table EAS2.CT_DSY_REPORTSYN016;

   table EAS2.CT_DSY_REPORTSYN017;

   table EAS2.CT_DSY_REPORTSYN018;

   table EAS2.CT_DSY_REPORTSYN019;

   table EAS2.CT_DSY_REPORTSYN020;

   table EAS2.CT_DSY_REPORTSYN020S1;

   table EAS2.CT_DSY_REPORTSYN021;

   table EAS2.CT_DSY_REPORTSYN021S1;

   table EAS2.CT_DSY_REPORTSYN022;

   table EAS2.CT_DSY_REPORTSYN023;

   table EAS2.CT_DSY_REPORTSYN024;

   table EAS2.CT_DSY_REPORTSYN025;

   table EAS2.CT_DSY_REPORTSYN026;

   table EAS2.CT_DSY_REPORTSYN027;

   table EAS2.CT_DSY_REPORTSYN028;

   table EAS2.CT_DSY_REPORTSYN029;

   table EAS2.CT_DSY_REPORTSYN030;

   table EAS2.CT_DSY_REPORTSYN031;

   table EAS2.CT_DSY_REPORTSYN032;

   table EAS2.CT_DSY_REPORTSYN033;

   table EAS2.CT_DSY_REPORTSYN034;

   table EAS2.CT_DSY_REPORTSYN035;

   table EAS2.CT_DSY_REPORTSYN036;

   
   

   
   

   
   

   
   

   
   

配置投递进程

1. GGSCI >add extract p_tab,EXTTRAILSOURCE /ogg/software/dirdat/e_tab/ex
2. GGSCI >add rmttrail E:\ogg\software\dirdat\r_tab\re,ext p_tab,megabytes 200
3. GGSCI >edit param p_tab
   
   
   extract p_tab

   rmthost 196.1.121.73,mgrport 7809

   passthru

   FLUSHCSECS 40

   EOFDELAYCSECS 10

   rmttrail E:\ogg\software\dirdat\r_tab\re

   table EAS2.*;

目标库配置

配置ODBC接口

创建服务

>INSTALL ADDSERVICE 

编辑GLOBALS

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

创建checkpoint表

1. GGSCI >dblogin sourcedb   ogg_odbc userid goldengate password goldengate
   
   
   GGSCI >add checkpointtable dbo.ckpttab

配置MGR进程

1. 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

配置复制进程

1. a. 创建trail存放目录
2. 
   
   F:\ogg\dirdat\REMSSQL
3. b.添加复制进程
4. GGSCI >add replicat r_tab,exttrail F:\OGG\dirdat\REMSSQL\RE,begin now,checkpointtable dbo.ckpttab
   GGSCI >edit param r_tab
   
   
   replicat r_tab
   
   
   sourcedefs F:\OGG\dirdef\sync.def
   
   
   targetdb lu userid goldengate , password goldengate
   
   
   reperror default,discard
   
   
   discardfile F:\OGG\dirrpt\REMSSQL.dsc,append,megabytes 100
   gettruncates
   
   
   map DC_DC.DC_RA_MER_BASE,target dbo.DC_RA_MER_BASE;

初始化数据

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