由于公司打算上GoldenGate,最近两天开始研究GoldenGate,做了一个简单的双向同步实验,目的只为能够实验成功,为了避免权限等不必要的麻烦,没有采用专门用户同步,直接使用了system用户,检查点表也存储在system模式下。被同步的表采用了HR模式的regions表,需要源和目标数据库都安装有HR模式,当然自己建个测试表也行。由于为初学,所以没有考虑DDL的同步和表以外对象的同步。以后会继续做相关实验并贴上来。
原打算源和目标数据库平台都采用Linux,但因为机子性能问题,源数据库采用了RHEL5(CentOS5)+Oracle11gR2,IP地址为192.168.10.100.目标数据库采用Windows xp+Oracle11gR2,IP地址为192.168.10.1。GoldenGate版本为11.1.1.1。由于Oracle不对大陆开放GoldenGate下载,所以需要翻墙。
下面只提供操作步骤,原理性的知识请参考文档。
一、Linux(源数据库):
1、安装GoldenGate:
Shell> mkdir -p /u01/app/ggs
Shell> cd /u01/app/ggs
Shell> unzip V26188-01
Shell> tar -xvof fbo_ggs_Linux_x86_ora11g_32bit.tar
Shell> cd /u01/app/ggs
GGSCI> create subdirs
GGSCI> exit
2、设置环境变量:
切换为Oracle用户,编辑.bash_profile:
Shell> vi .bash_profile
在其中加入:
ORACLE_SID=orcl
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=$ORACLE_BASE/product/11.2/db_1
PATH=$ORACLE_HOME/bin:/u01/app/ggs:$PATH
LD_LIBRARY_PATH=$ORACLE_HOME/lib
export ORACLE_SID ORACLE_BASE ORACLE_HOME PATH LD_LIBRARY_PATH
使环境变量生效
Shell> . .bash_profile
3、打开数据库补充日志和force logging
查询是否打开补充日志和force logging:
SQL> select supplemental_log_data_min,force_logging from v$database;
打开补充日志:
SQL> alter database add supplemental log data;
SQL> alter system switch logfile;
打开force logging:
SQL> alter database force logging;
确认已经打开补充日志和force logging:
SQL> select supplemental_log_data_min,force_logging from v$database;
4、配置manager进程:
GGSCI> edit params mgr
prot 7809
(保存)
GGSCI> start mgr
查看mgr进程是否启动:
GGSCI> info mgr
Manager is running (IP port orcl.wjw.com.7809).
5、配置extract进程:
登陆数据库:
GGSCI> dblogin userid system,password qweasd
GGSCI> add extract extbi,tranlog,begin now
GGSCI> edit params extbi
内容:
extract extbi
userid system,password qweasd
tranlogoptions excludeuser system
rmthost 192.168.10.1,mgrport 7809
rmttrail ./dirdat/bi
table hr.regions;
(保存)
GGSCI> add rmttrail ./dirdat/bi,extract extbi,megabytes 5
GGSCI> info rmttrail *
GGSCI> start extract extbi
查看结果:
GGSCI> info extract extbi,detail
GGSCI> view report extbi
GGSCI> info all
6、配置Replicat进程:
设置检查点表:
GGSCI> edit params ./GLOBALS
内容:
checkpointtable system.ggschkpt
(保存)
GGSCI> exit
Shell> ggsci
(重新登陆以激活GLOBALS参数)
GGSCI> dblogin userid system,password qweasd
GGSCI> add checkpointtable
GGSCI> add replicat repbi,exttrail ./dirdat/bi
GGSCI> edit params repbi
内容:
replicat repbi
userid system,password qweasd
handlecollisions
assumetargetdefs
discardfile ./dirrpt/repbi.dsc,purge
map hr.regions, target hr.regions;
(保存)
GGSCI> start replicat repbi
查看结果:
GGSCI> view report repbi
GGSCI> info replicat repbi,detail
GGSCI> info all
Program Status Group Lag Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EXTBI 00:00:00 00:00:08
REPLICAT RUNNING REPBI 00:00:00 00:00:05
二、Windows(目标数据库)
1、安装GoldenGate:
解压安装文件到GoldenGate安装目录,例如d:\oracle\product\ggs
> cd d:\oracle\product\ggs
> ggsci
GGSCI> create subdirs
GGSCI> exit
2、打开数据库补充日志和force logging:
SQL> select supplemental_log_data_min,force_logging from v$database;
打开补充日志:
SQL> alter database add supplemental log data;
SQL> alter system switch logfile;
打开force logging:
SQL> alter database force logging;
确认已经打开补充日志和force logging:
SQL> select supplemental_log_data_min,force_logging from v$database;
3、编辑GLOBALS参数文件,添加manager服务:
GGSCI> edit params ./GLOBALS
内容:
mgrservname mgr01
checkpointtable system.ggschkpt
(保存)
> install addservice addevents
(如果需要删除服务,使用install deleteservice deleteevents命令)
GGSCI> dblogin userid system,password qweasd
GGSCI> add checkpointtable
4、配置manager进程:
GGSCI> edit params mgr
内容:
port 7809
(保存)
GGSCI> start mgr
查看mgr进程是否启动:
GGSCI> info mgr
Manager is running (IP port wjw.7809).
5、配置extract进程:
登陆数据库:
GGSCI> dblogin userid system,password qweasd
GGSCI> add extract extbi,tranlog,begin now
GGSCI> edit params extbi
内容:
extract extbi
userid system,password qweasd
tranlogoptions excludeuser system
rmthost 192.168.10.100,mgrport 7809
rmttrail ./dirdat/bi
table hr.regions;
(保存)
GGSCI> add rmttrail ./dirdat/bi,extract extbi,megabytes 5
GGSCI> info rmttrail *
GGSCI> start extract extbi
查看结果:
GGSCI> info extract extbi,detail
GGSCI> view report extbi
GGSCI> info all
6、配置Replicat进程:
GGSCI> dblogin userid system,password qweasd
GGSCI> add replicat repbi,exttrail ./dirdat/bi
GGSCI> edit params repbi
内容:
replicat repbi
userid system,password qweasd
handlecollisions
assumetargetdefs
discardfile ./dirrpt/repbi.dsc,purge
map hr.regions, target hr.regions;
(保存)
GGSCI> start replicat repbi
查看结果:
GGSCI> view report repbi
GGSCI> info replicat repbi,detail
GGSCI> info all
Program Status Group Lag Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EXTBI 00:00:00 00:00:01
REPLICAT RUNNING REPBI 00:00:00 00:00:08
三、测试:
无论是在源数据库的regions表中插入记录,还是在目标数据库中的regions表中插入记录,新插入的记录都可以自动同步到对方数据库中。
原打算源和目标数据库平台都采用Linux,但因为机子性能问题,源数据库采用了RHEL5(CentOS5)+Oracle11gR2,IP地址为192.168.10.100.目标数据库采用Windows xp+Oracle11gR2,IP地址为192.168.10.1。GoldenGate版本为11.1.1.1。由于Oracle不对大陆开放GoldenGate下载,所以需要翻墙。
下面只提供操作步骤,原理性的知识请参考文档。
一、Linux(源数据库):
1、安装GoldenGate:
Shell> mkdir -p /u01/app/ggs
Shell> cd /u01/app/ggs
Shell> unzip V26188-01
Shell> tar -xvof fbo_ggs_Linux_x86_ora11g_32bit.tar
Shell> cd /u01/app/ggs
GGSCI> create subdirs
GGSCI> exit
2、设置环境变量:
切换为Oracle用户,编辑.bash_profile:
Shell> vi .bash_profile
在其中加入:
ORACLE_SID=orcl
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=$ORACLE_BASE/product/11.2/db_1
PATH=$ORACLE_HOME/bin:/u01/app/ggs:$PATH
LD_LIBRARY_PATH=$ORACLE_HOME/lib
export ORACLE_SID ORACLE_BASE ORACLE_HOME PATH LD_LIBRARY_PATH
使环境变量生效
Shell> . .bash_profile
3、打开数据库补充日志和force logging
查询是否打开补充日志和force logging:
SQL> select supplemental_log_data_min,force_logging from v$database;
打开补充日志:
SQL> alter database add supplemental log data;
SQL> alter system switch logfile;
打开force logging:
SQL> alter database force logging;
确认已经打开补充日志和force logging:
SQL> select supplemental_log_data_min,force_logging from v$database;
4、配置manager进程:
GGSCI> edit params mgr
prot 7809
(保存)
GGSCI> start mgr
查看mgr进程是否启动:
GGSCI> info mgr
Manager is running (IP port orcl.wjw.com.7809).
5、配置extract进程:
登陆数据库:
GGSCI> dblogin userid system,password qweasd
GGSCI> add extract extbi,tranlog,begin now
GGSCI> edit params extbi
内容:
extract extbi
userid system,password qweasd
tranlogoptions excludeuser system
rmthost 192.168.10.1,mgrport 7809
rmttrail ./dirdat/bi
table hr.regions;
(保存)
GGSCI> add rmttrail ./dirdat/bi,extract extbi,megabytes 5
GGSCI> info rmttrail *
GGSCI> start extract extbi
查看结果:
GGSCI> info extract extbi,detail
GGSCI> view report extbi
GGSCI> info all
6、配置Replicat进程:
设置检查点表:
GGSCI> edit params ./GLOBALS
内容:
checkpointtable system.ggschkpt
(保存)
GGSCI> exit
Shell> ggsci
(重新登陆以激活GLOBALS参数)
GGSCI> dblogin userid system,password qweasd
GGSCI> add checkpointtable
GGSCI> add replicat repbi,exttrail ./dirdat/bi
GGSCI> edit params repbi
内容:
replicat repbi
userid system,password qweasd
handlecollisions
assumetargetdefs
discardfile ./dirrpt/repbi.dsc,purge
map hr.regions, target hr.regions;
(保存)
GGSCI> start replicat repbi
查看结果:
GGSCI> view report repbi
GGSCI> info replicat repbi,detail
GGSCI> info all
Program Status Group Lag Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EXTBI 00:00:00 00:00:08
REPLICAT RUNNING REPBI 00:00:00 00:00:05
二、Windows(目标数据库)
1、安装GoldenGate:
解压安装文件到GoldenGate安装目录,例如d:\oracle\product\ggs
> cd d:\oracle\product\ggs
> ggsci
GGSCI> create subdirs
GGSCI> exit
2、打开数据库补充日志和force logging:
SQL> select supplemental_log_data_min,force_logging from v$database;
打开补充日志:
SQL> alter database add supplemental log data;
SQL> alter system switch logfile;
打开force logging:
SQL> alter database force logging;
确认已经打开补充日志和force logging:
SQL> select supplemental_log_data_min,force_logging from v$database;
3、编辑GLOBALS参数文件,添加manager服务:
GGSCI> edit params ./GLOBALS
内容:
mgrservname mgr01
checkpointtable system.ggschkpt
(保存)
> install addservice addevents
![](https://i-blog.csdnimg.cn/blog_migrate/85acc01c68641460dd913880df904f25.png)
(如果需要删除服务,使用install deleteservice deleteevents命令)
GGSCI> dblogin userid system,password qweasd
GGSCI> add checkpointtable
4、配置manager进程:
GGSCI> edit params mgr
内容:
port 7809
(保存)
GGSCI> start mgr
查看mgr进程是否启动:
GGSCI> info mgr
Manager is running (IP port wjw.7809).
5、配置extract进程:
登陆数据库:
GGSCI> dblogin userid system,password qweasd
GGSCI> add extract extbi,tranlog,begin now
GGSCI> edit params extbi
内容:
extract extbi
userid system,password qweasd
tranlogoptions excludeuser system
rmthost 192.168.10.100,mgrport 7809
rmttrail ./dirdat/bi
table hr.regions;
(保存)
GGSCI> add rmttrail ./dirdat/bi,extract extbi,megabytes 5
GGSCI> info rmttrail *
GGSCI> start extract extbi
查看结果:
GGSCI> info extract extbi,detail
GGSCI> view report extbi
GGSCI> info all
6、配置Replicat进程:
GGSCI> dblogin userid system,password qweasd
GGSCI> add replicat repbi,exttrail ./dirdat/bi
GGSCI> edit params repbi
内容:
replicat repbi
userid system,password qweasd
handlecollisions
assumetargetdefs
discardfile ./dirrpt/repbi.dsc,purge
map hr.regions, target hr.regions;
(保存)
GGSCI> start replicat repbi
查看结果:
GGSCI> view report repbi
GGSCI> info replicat repbi,detail
GGSCI> info all
Program Status Group Lag Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EXTBI 00:00:00 00:00:01
REPLICAT RUNNING REPBI 00:00:00 00:00:08
三、测试:
无论是在源数据库的regions表中插入记录,还是在目标数据库中的regions表中插入记录,新插入的记录都可以自动同步到对方数据库中。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/26194851/viewspace-711774/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/26194851/viewspace-711774/