安装OGG软件 选择数据库版本
指定安装路径以及数据库路径
OGG FOR SQL Server Download View Password:j6mh
数据库配置 开启强制日志、附加日志、并确定处于归档模式
SQL> ALTER DATABASE FORCE LOGGING; SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA; SQL> alter system set enable_goldengate_replication=true;
源端和目标端创建表空间和用户
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管理进程配置
GGSCI>edit params mgr port 7809 DYNAMICPORTLIST 7840-7850 PURGEOLDEXTRACTS /u01/ogg/dirdat/*,usecheckpoints,minkeepdays 7
编辑defgen参数
GGSCI> edit params defgen defsfile /u01/ogg/dirdef/sync.def userid ogg,password ogg?123 table TEST.TAB3;
生成defgen文件并复制到目标端dirdef目录下
$ <OGG_HOME>/defgen paramfile /oraogg/app/dirprm/defgen.prm
抽取进程配置
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;
投递进程配置
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)
MGR管理进程配置
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名称)
GGSCI >dblogin lu userid sa password abcd123# GGSCI >add checkpointtable dbo.ckpttab
复制进程配置
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;
同步初始化 源端同步表添加附加日志
GGSCI>dblogin userid ogg,password ogg GGSCI>add trandata TEST.*
源端创建初始化进程
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;
目标端创建初始化进程
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;
启动源端所有进程
GGSCI>start extest01 GGSCI>start putest01
启动源端初始化进程
初始化进程完成后启动目标端复制进程
同步完成后取消HANDLECOLLISIONS参数
GGSCI>SEND RETEST01,NOHANDLECOLLISIONS