利用OGG实现Oracle和MySQL之间的双向同步

目录

环境介绍

软件安装

配置Oracle到MySQL的进程

配置MySQL到Oracle的进程

测试数据同步

软件下载链接地址

写在最后

---

环境介绍

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

下载如下图所示的软件：

写在最后

其实这种异库之间很少情况下会做双向同步。双向同步增加了数据出现异常的可能性！