使用goldengate从mysql同步数据到oracle

1. 环境信息

源端 migu-mysqlmaster-801259907-zflo5  数据库mysql 5.6.34  ogg版本:12.1.2.1.0
目标端 192.168.119.6   数据库 oracle 11.2.0.4   ogg版本:12.1.2.1.0

2.mysql安装配置
(1) 解压mysql安装文件mysql-5.6.34-linux-glibc2.5-x86_64.tar.gz
(2)准备参数文件
[mysqld]
log-bin=mysql-bin
server-id=1
innodb_flush_log_at_trx_commit=1
sync_binlog=1
basedir=/home/aspire/apps/mysql
datadir=/home/aspire/apps/mysql/data
port=3306
socket=/home/aspire/apps/mysql/tmp/mysql.sock
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

#做从同时给其他从做主时候必须使用，否则其他从无法同步数据 
log-slave-updates

#master slave error
slave_skip_errors = all
max_connections = 65535
binlog_format = row

(3)数据库初始化
cd /home/aspire/apps/mysql/scripts
./mysql_install_db --user=aspire --basedir=/home/aspire/apps/mysql --datadir=/home/aspire/apps/mysql/data


(4)修改root密码，创建aspire用户
update user set Password = password('123456') where User='root';
flush privileges;

grant all privileges on migu_point_exchange2.* to aspire@'%' identified by 'aspire';
GRANT REPLICATION SLAVE ON *.* TO 'aspire'@'%';

3. oracle 安装配置
(1)创建ogg用户,并授权
create user ogg identified by ogg default tablespace users;
grant dba to ogg;

(2)创建测试表

4.ogg安装


--源端为mysql，mysql版本的ogg直接解压到安装目录下即可。
--源端为oracle，如果无法启动图形界面，需要进行静默安装，首先准备响应文件:
[oracle@BJ-FT-1F-119-6 response]$ grep -v "#" oggcore.rsp|grep -v "^$"

oracle.install.responseFileVersion=/oracle/install/rspfmt_ogginstall_response_schema_v12_1_2
INSTALL_OPTION=ORA11g
SOFTWARE_LOCATION=/home/oracle/install/ggs
START_MANAGER=false
MANAGER_PORT=7809
DATABASE_LOCATION=/apps/oracle/product/11.2.0
INVENTORY_LOCATION=/apps/oraInventory
UNIX_GROUP_NAME=

--创建安装目录
mkdir home/oracle/install/ggs

--执行静默安装
cd fbo_ggs_Linux_x64_shiphome/Disk1
./runInstaller -silent -responseFile /home/oracle/install/fbo_ggs_Linux_x64_shiphome/Disk1/response/oggcore.rsp

5.源端ogg配置

(1) 创建子目录
./ggsci
create subdirs

(2) 配置manager进程
./ggsci

dblogin sourcedb test,userid root,password 123456

edit param mgr

port 7809
dynamicportlist 7800-8000
autorestart extract *,waitminutes 2,resetminutes 5

start mgr

info mgr
info all

(3) 配置extract进程
edit param ggext

extract ggext
setenv (MYSQL_HOME=”/home/aspire/apps/mysql”)
tranlogoptions altlogdest /home/aspire/apps/mysql/data/mysql-bin.index
sourcedb test@localhost:3306,userid root,password 123456
exttrail ./dirdat/e2
dynamicresolution
gettruncates
table test.t1;
table migu_point_exchange2.test;

add extract ggext,tranlog,begin now
add exttrail ./dirdat/e2,extract ggext

(4) 配置pump进程
edit params ggpump

extract ggpump
rmthost 192.168.119.6,mgrport 7809
rmttrail ./dirdat/e2
passthru
gettruncates
table test.t1;
table migu_point_exchange2.test;
 
add extract ggpump,exttrailsource ./dirdat/e2
add rmttrail ./dirdat/e2,extract ggpump

(5)异构平台配置defgen：
edit params defgen

defsfile /home/oracle/install/ogg/dirdef/defgen.prm
sourcedb test@localhost:3306, userid root,password 123456
table test.t1;
table migu_point_exchange2.test;


./defgen  paramfile ./dirprm/defgen.prm

--创建之后将产生的defgen.prm文件传到目标端ogg的dirdef目录下

(6)启动extract和pump进程

start EXTRACT GGEXT
start EXTRACT GGPUMP


6.目标端ogg配置(oracle)
1) 创建子目录
./ggsci
create subdirs

(2) 配置manager进程
./ggsci
edit param mgr

port 7809
dynamicportlist 7800-8000
autorestart extract *,waitminutes 2,resetminutes 5

start mgr

info mgr
info all

(3)配置replicat进程组：
./ggsci

dblogin userid ogg password ogg
add checkpointtable ogg.checkpoint

edit param ./GLOBALS
checkpointtable ogg.checkpoint

edit param ggrep
replicat ggrep
sourcedefs ./dirdef/defgen.prm
userid ogg,password ogg
reperror default,discard
discardfile ./dirrpt/ggrep.dsc,append,megabytes 50
dynamicresolution
map test.t1, target scott.t1;
map  migu_point_exchange2.test, target scott.test;
 
add replicat ggrep,exttrail ./dirdat/e2

start replicat ggrep
info all

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/10972173/viewspace-2127442/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/10972173/viewspace-2127442/