通过GoldenGate实现oracle到mysql的实施复制

通过GoldenGate实现oracle到mysql的实施复制


1.oracle端配置

oracle 打开归档日志

SQL>alter database add supplemental log data;

SQL>create user test identified by test;

SQL>grant dba to test;




2.mysql端配置

$mysql -uroot -p

mysql>grant all privileges on *.* to test@localhost identified by 'test';




3.Glodengate安装

分别在oracle用户和mysql用户下解压,oracle端路径 /u01/app/ggs,mysql端路径 /ggs

分别在两端运行
GGSCI>create subdirs

GGSCI>edit params mgr

内容
port 10000

GGSCI>start mgr




4.数据初始同步                                                                              --需要初始同步两端数据

4.1 oracle端增加trandata

GGSCI>dblogin userid test@test,password test

GGSCI>add trandata test.ts_cfg_info;                                                       --为复制表增加trandata

GGSCI>info trandata *

4.2oracle端配置初始同步extract

GGSCI>edit params init1

内容
extract init1
userid test@test,password test
rmthost 192.168.1.15, mgrport 10000
rmttask replicat, group repinit                                                            --指定远端replicat进程名,需要与下文建立的replicat名字相同
table test.ts_cfg_info;

GGSCI>add extract init1,sourceistable                                                      --从源表抽取数据

GGSCI>edit params tabledefs                                                                --为源表生成定义文件，使mysql端在replicate时参考源表定义

内容
defsfile ./dirdef/ts_cfg_info.def
userid test@test,password test
table test.ts_cfg_info;

$defgen paramfile ./dirprm/tabledefs.prm                                                   --生成的ts-cfg_info.def scp到mysql端/ggs目录下


4.3mysql端配置初始同步replicat

mysql>use datacenter;

mysql>source chkpt_mysql_create.sql                                                        --建立checkpoint表

GGSCI>edit params ./GLOBALS

内容
CHECKPOINTTABLE datacenter.GGS_CHECKPOINT

GGSCI>edit params repinit

内容
replicat repinit
dboptions host 127.0.0.1, connectionport 3306                                              --指定mysql数据库连接
sourceDB datacenter, USERID test, PASSWORD test
sourcedefs /ggs/ts_cfg_info.def                                                            --指定源表结构参考文件
MAP TEST.TS_CFG_INFO, TARGET datacenter.ts_cfg_info;                                       --映射，大小写敏感

GGSCI>add replicat repinit,specialrun                                                      --增加特殊replicat进程，有init1拉起，不需要启动

4.4初始同步步骤

oracle端

GGSCI>start init1;                                                                         --查看数据是否同步




5.数据实时同步配置

5.1oracle端extract配置

GGSCI>edit params ex1

内容
extract ex1
userid test@test, password test
exttrail ./dirdat/ex
table test.ts_cfg_info;

GGSCI>add extract ex1,tranlog,begin now

GGSCI>add exttrail ./dirdat/ex,extract ex1,megabytes 1000

GGSCI>start ex1                                                                

5.2oracle端datapump配置

GGSCI>edit params pmp1

内容
extract pmp1
passthru
rmthost 192.168.1.15,mgrport 10000
rmttrail ./dirdat/ex
table test.ts_cfg_info;

GGSCI>add extract pmp1,exttrailsource ./dirdat/ex                             

GGSCI>add rmttrail ./dirdat/ex,extract pmp1,megabytes 1000

GGSCI>start pmp1                                                                                    

5.3mysql端replicat配置

GGSCI>edit params rep1

内容
replicat rep1
sourcedefs /ggs/ts_cfg_info.def                                                              --mysql连接串
dboptions host 127.0.0.1, connectionport 3306
sourcedb datacenter,userid test,password test
MAP TEST.TS_CFG_INFO, TARGET datacenter.ts_cfg_info;
discardfile ./dirrpt/mysql.dsc,append,megabytes 1000                                         --丢弃文件位置，可供错误排查

GGSCI>dblogin sourcedb dblogin sourcedb datacenter@127.0.0.1:3306 userid test,password test

GGSCI>add replicat rep1,checkpointtable datacenter.GGS_CHECKPOINT,exttrail ./dirdat/ex       --增加replicat进程rep1

GGSCI>start rep1  


6.维护

GGSCI>view report init1,detail                                                               --查看初始同步情况

GGSCI>view report repinit,detail