目录
环境介绍
OS：CentOS7.3
OGG：OGG19.1.0.0.0
MySQL：MySQL5.7
Oracle：Oracle11.2.0.4.0
软件安装
1、MySQL数据库安装
略（mysql开启二进制日志、创建OGG用户和test数据库等操作可以一起做）
我的MySQL配置示例如下：
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html
[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
server_id=1
innodb_flush_log_at_trx_commit=1
sync_binlog=1
log-bin=mysql-bin
binlog_format=row
slave_skip_errors = all
max_connections = 65535
max_connections = 1000
table_open_cache = 128k
max_allowed_packet = 52M
binlog_cache_size = 1M
max_heap_table_size = 64M
read_buffer_size = 2M
read_rnd_buffer_size = 16M
sort_buffer_size = 16M
join_buffer_size = 8M
query_cache_size = 64M
query_cache_limit = 2M
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
2、Oracle数据库安装
略（开启归档、开启附加日志创建OGG用户）
3、OGG软件安装
在Oracle服务器安装OGG19.1.0.0.0，解压安装介质之后直接运行runInstaller进行安装。如下图所示：
选择对应的Oracle版本
然后一直下一步就OK了
如果不能调用图形界面。可以采用静默方式安装。这里就不描述了。自行百度哈！
在MySQL数据库服务器安装OGG19.1.0.0.0，直接解压安装介质就OK了
创建OGG工作目录命令：create subdirs
注意：在Oracle数据库服务器不用执行上述create subdirs命令。因为界面安装时已经完成了改步骤
配置Oracle到MySQL的进程
#以下为Oracle服务器的配置
#添加表附加日志，我这里添加过了。所以显示already enabled for table OGG.STUDENT
GGSCI (oracle) 52> dblogin userid ogg,password ogg
Successfully logged into database.
GGSCI (oracle) 53> add trandata ogg.student
Logging of supplemental redo log data is already enabled for table OGG.STUDENT.
GGSCI (oracle) 54>
#配置global
GGSCI (oracle) 54> edit params ./GLOBAL
#添加文件内容如下
ggschema ogg
#创建管理进程，并启动
GGSCI (oracle) 56> edit param mgr
#添加文件内容如下
port 8006
dynamicportlist 8006-8010
autorestart er *, retries 5, waitminutes 3
purgeoldextracts /home/ogg/dirdat/**,usecheckpoints,minkeepfiles 10
LAGREPORTHOURS 5
LAGINFOMINUTES 10
LAGCRITICALMINUTES 15
#启动
GGSCI (oracle) 57> start mgr
#查看状态
GGSCI (oracle) 58> info all
#可以看到状态为running
#创建extract进程，并启动
GGSCI (oracle) 59> edit params ext1
#文件内容
extract ext1
setenv(NLS_LANG = "SIMPLIFIED CHINESE.AL32UTF8")
userid ogg,password ogg
exttrail ./dirdat/e1
table ogg.student;
#table后面的分号要注意
GGSCI (oracle) 60> add extract ext1,tranlog,begin now
GGSCI (oracle) 61> add exttrail ./dirdat/e1,extract ext1
#查看
GGSCI (oracle) 62> info all
#此时可以看到有个EXTRACT（EXT1）stopped
#启动
GGSCI (oracle) 63> start ext1
#创建pump进程，并启动
GGSCI (oracle) 64> edit params push
#文件内容
extract push
setenv(NLS_LANG = "SIMPLIFIED CHINESE.AL32UTF8")
passthru
userid ogg,password ogg
rmthost 目标数据库服务器IP,mgrport 8006
rmttrail /root/ogg/dirdat/e1
table ogg.student;
#exttrailsource /home/ogg/dirdat/e1 路径与ext1进程抽取到的数据路径一致
GGSCI (oracle) 65> add extract push,exttrailsource /home/ogg/dirdat/e1
#rmttrail /root/mysql/ogg/dirdat/e1 路径与mysql端获取数据的路径一致
GGSCI (oracle) 66> add rmttrail /root/ogg/dirdat/e1,extract push
GGSCI (oracle) 67> info all
#此时可以看到有个EXTRACT（PUSH）stopped
#启动
GGSCI (oracle) 68> start push
#创建要同步的表的定义
#创建参数文件
GGSCI (oracle) 69> edit param test
#文件内容
defsfile ./dirdef/test.def
userid ogg, password ogg
table ogg.student;
GGSCI (oracle) 70> exit
#生成表定义文件
./defgen paramfile ./dirprm/test.prm
#此时在目录dirdef生成文件test.def
#传至目标库目录
scp /home/oracle/ogg/dirdef/test.def root@目标数据库服务器IP:/root/ogg/dirdef/
#以下是MySQL服务器的配置
#以下配置mgr在配置Oracle到MySQL的P进程之前就要配置好并且启动否则会报错
#创建管理进程，并启动
GGSCI (mysql) 2> edit param mgr
#文件内容
port 8006
dynamicportlist 8006-8010
autorestart er *, retries 5, waitminutes 3
purgeoldextracts /root/ogg/dirdat/**,usecheckpoints,minkeepfiles 10
LAGREPORTHOURS 5
LAGINFOMINUTES 10
LAGCRITICALMINUTES 15
#启动
GGSCI (mysql) 3> start mgr
#配置global
GGSCI (mysql) 4> edit params ./GLOBALS
#文件内容
ENABLEMONITORING
GGSCI (mysql) 5> edit params ./globals
#文件内容
checkpointtable test.checkpoint_table
#配置检查点
#登录
GGSCI (mysql) 6> dblogin sourcedb test@MySQL数据库IP:3306 userid ogg password Root!!2018
#配置检查点
GGSCI (mysql) 7> add checkpointtable test.checkpoint_table
#查看检查点
GGSCI (mysql) 8> info checkpointtable test.checkpoint_table
#创建replicat进程并启动
GGSCI (mysql) 9> edit params REP1
#文件内容
replicat rep1
sourcedefs /root/ogg/dirdef/test.def
TARGETDB test@MySQL数据库IP:3306,userid ogg,password Root!!2018
reperror default,discard
discardfile /root/ogg/dirrpt/rep1.dsc,append,megabytes 50
allownoopupdates
map ogg.student,target test.student;
GGSCI (mysql) 10> add replicat rep1,exttrail /root/ogg/dirdat/e1,checkpointtable test.checkpoint_table
#启动
GGSCI (mysql) 11> start rep1
配置MySQL到Oracle的进程
#以下是MySQL服务器的配置
GGSCI (mysql) 1> edit param ggext
#文件内容
extract ggext
setenv (MYSQL_HOME=”/usr/share/mysql”)
tranlogoptions altlogdest /var/lib/mysql/mysql-bin.index
sourcedb test@MySQL服务器IP:3306,userid ogg,password Root!!2018
exttrail /root/ogg/dirdat/my
dynamicresolution
gettruncates
table test.student;
GGSCI (mysql) 2> add extract ggext,tranlog,begin now
GGSCI (mysql) 3> add exttrail /root/ogg/dirdat/my,extract ggext
GGSCI (mysql) 4> edit param ggpump
#文件内容
extract ggpump
rmthost 目标数据库服务器,mgrport 8006
rmttrail /home/ogg/dirdat/my
passthru
gettruncates
table test.student;
GGSCI (mysql) 5> add extract ggpump,exttrailsource /root/ogg/dirdat/my
GGSCI (mysql) 6> add rmttrail /home/ogg/dirdat/my,extract ggpump
#异构平台配置defgen
GGSCI (mysql) 7> edit params defgen
#文件内容
defsfile /root/ogg/dirdef/defgen.prm
sourcedb test@MySQL服务器IP:3306,userid ogg,password Root!!2018
table test.student;
#exit退出ogg命令界面，执行一下语句
./defgen paramfile ./dirprm/defgen.prm
#创建之后将产生的defgen.prm文件传到Oracle ogg的dirdef目录下
scp /root/mysql/ogg/dirdef/defgen.prm root@Oracle服务器IP:/home/ogg/dirdef
启动extract和pump进程
GGSCI (mysql) 8> start EXTRACT GGEXT
GGSCI (mysql) 9> start EXTRACT GGPUMP
#以下是Oracle服务器的配置
#配置p进程
GGSCI (oracle) 2> dblogin userid ogg password ogg
Successfully logged into database.
GGSCI (oracle as ogg@orcl) 3> add checkpointtable ogg.checkpoint_table
GGSCI (oracle as ogg@orcl) > edit param ./GLOBALS
#文件内容
checkpointtable ogg.CHECKPOINT_TABLE
GGSCI (oracle as ogg@orcl) 5> edit param ggrep
#文件内容
replicat ggrep
sourcedefs /home/ogg/dirdef/defgen.prm
userid ogg,password ogg
reperror default,discard
discardfile /home/ogg/dirrpt/ggrep.dsc,append,megabytes 50
dynamicresolution
map test.student, target ogg.student;
GGSCI (oracle as ogg@orcl) 6> add replicat ggrep,exttrail /home/ogg/dirdat/my,checkpointtable ogg.checkpoint_table
GGSCI (oracle as ogg@orcl) 7> start replicat ggrep
注意：启动进程的过程中可能会出现权限方面的问题。将相关权限赋权给对应的用户即可。
完成以上的步骤后。Oracle服务器的进程如下图所示：
完成以上的步骤后。MySQL服务器的进程如下图所示：
测试数据同步
通过MySQL连接工具加一条id为5的数据
可以看到Oracle数据库也有同样一条数据被插入了
在Oracle数据库插入一条id为6的数据
可以看到MySQL数据也同样被插入了一条数据
软件下载链接地址
https://www.oracle.com/technetwork/middleware/goldengate/downloads/index.html
下载如下图所示的软件：
写在最后
其实这种异库之间很少情况下会做双向同步。双向同步增加了数据出现异常的可能性！
扫一扫
96
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
