oracle ogg同步表空间,OGG同步Oracle到SQLServer

安装OGG软件

选择数据库版本

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

数据库配置

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

2

3SQL> 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

7create 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

4GGSCI>edit params mgr

port 7809

DYNAMICPORTLIST 7840-7850

PURGEOLDEXTRACTS /u01/ogg/dirdat/*,usecheckpoints,minkeepdays 7

编辑defgen参数

1

2

3

4GGSCI> edit params defgen

defsfile /u01/ogg/dirdef/sync.def

userid ogg,password ogg?123

table TEST.TAB3;

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

1$ /defgen paramfile /oraogg/app/dirprm/defgen.prm

抽取进程配置

1

2

3

4

5

6

7

8

9

10

11

12

13GGSCI>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

11GGSCI >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)

1INSTALL ADDSERVICE

MGR管理进程配置

1

2

3

4

5

6GGSCI >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

2GGSCI >dblogin lu userid sa password abcd123#

GGSCI >add checkpointtable dbo.ckpttab

复制进程配置

1

2

3

4

5

6

7

8

9

10GGSCI >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

2GGSCI>dblogin userid ogg,password ogg

GGSCI>add trandata TEST.*

源端创建初始化进程

1

2

3

4

5

6

7GGSCI >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

7GGSCI >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

2GGSCI>start extest01

GGSCI>start putest01

启动源端初始化进程

1GGSCI>start exinit

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

1GGSCI>start retest01

同步完成后取消HANDLECOLLISIONS参数

1GGSCI>SEND RETEST01,NOHANDLECOLLISIONS