两台rhel 6.4虚拟机,分别异构oracle到mysql数据库同步测试
ip:192.168.0.23 部署oracle 11.2.0.4,goldgate 12.1.2.1.0 for oracle
ip:192.168.0.25 部署mysql5.5.9,goldengate 12.1.2.1.0 for mysql
char|datetime|varchar|timestamp|int|binarytinyint|varbinarysmall int|textmedium int|tinytextbig int|mediumtextdecimal|longtextfloat|blobdouble|tinyblobdate|mediumblobtime|longblobyear|enum|bit(m)
•the xml, set, and geometry data types and similar are not supported.
•there is limited support for the interval data type.
mysql字符集:
database create database test charset utf8;
table create table test( id int, name char(100)) charset utf8;
column create table test ( id int, name1 char(100) charset gbk, name2 char(100) charset utf8));
insert, update, delete on target tables
create table
select any table or select on database.table
oracle goldengate supports innodb storage engine for a source mysql database,goldengate对mysql只支持innodb引擎,所以,在创建mysql端的表的时候,要指定表为innodb引擎。create table mysql (name char(10)) engine=innodb;
当然5.9默认的存储引擎就是innodb。
将ogg压缩包(v47367-01.zip)解压到 /u01 下
ogg 12c的安装和以前的版本安装不同,使用了oui的安装方式,以前的版本直接解压就可以了.所以最好要有x环境.如果没有,也支持静默安装.
安装步骤如下:
[oracle@oggtest disk1]$ ./runinstaller
显示欢迎界面,点下一步,
gg 12c只支持 oracle 11g和oracle 12c,选择合适的oracle版本.
software location定义了ogg将要安装的位置,database location定义了oracle数据库软件的安装位置,并且定义manager的端口.
出现安装信息汇总,选择"install"安装.
把gg的安装目录分别到加ld_library_path和path变量中.整个安装过程还是十分简单,虽然也是oui的安装方式,只是在software和database location处有可能会产生误解。
下面是oracle用户的环境变量设置
[oracle@oggtest ~]$ cat .bash_profile
bash_profile:
<code></code>
<code>if [ -f ~/.bashrc ]; then</code>
<code>fi</code>
<code>path=$path:$home/bin</code>
<code>export path</code>
<code>oracle_base=/u01/app/oracle; export oracle_base oracle_home=$oracle_base/product/11.2.0/db_1; export oracle_home ogg_home=$oracle_base/product/11.2.0/ogg12c; export ogg_home oracle_sid=oggtt; export oracle_sid oracle_term=xterm; export oracle_term path=$oracle_home/bin:$ogg_home:$base_path:/usr/sbin:$path; export path</code>
<code>ld_library_path=$oracle_home/lib:$ogg_home:/lib:/usr/lib; export ld_library_path classpath=$oracle_home/jre:$oracle_home/jlib:$oracle_home/rdbms/jlib; export classpath</code>
create subdirectories:
<code>oracle goldengate command interpreter for oracle version 12.1.2.1.0 oggcore_12.1.2.1.0_platforms_140727.2135.1_fbo linux, x64, 64bit (optimized), oracle 11g on aug 7 2014 09:14:25 operating system character set identified as utf-8.</code>
<code>copyright (c) 1995, 2014, oracle and/or its affiliates. all rights reserved. ggsci (oggtest) 1> create subdirs</code>
<code>creating subdirectories under current directory /u01/app/oracle/product/11.2.0/ogg12c</code>
<code>parameter files /u01/app/oracle/product/11.2.0/ogg12c/dirprm: already exists report files /u01/app/oracle/product/11.2.0/ogg12c/dirrpt: already exists checkpoint files /u01/app/oracle/product/11.2.0/ogg12c/dirchk: already exists process status files /u01/app/oracle/product/11.2.0/ogg12c/dirpcs: already exists sql script files /u01/app/oracle/product/11.2.0/ogg12c/dirsql: already exists database definitions files /u01/app/oracle/product/11.2.0/ogg12c/dirdef: already exists extract data files /u01/app/oracle/product/11.2.0/ogg12c/dirdat: already exists temporary files /u01/app/oracle/product/11.2.0/ogg12c/dirtmp: already exists credential store files /u01/app/oracle/product/11.2.0/ogg12c/dircrd: already exists masterkey wallet files /u01/app/oracle/product/11.2.0/ogg12c/dirwlt: already exists dump files /u01/app/oracle/product/11.2.0/ogg12c/dirdmp: already exists</code>
3.1 修改数据库为归档模式,略过。
3.2 打开辅助日志
打开辅助日志:
3.3 关闭回收站
关闭回收站:
3.4 创建复制用的用户,并授权
create user:
<code>user created.</code>
<code>sql> grant dba to ggs;</code>
<code>grant succeeded. 测试系统,授予了dba权限,科用以下权限替代dba权限 grant create table to ggs; grant connect to ggs; grant alter any table to ggs; grant alter session to ggs; grant create session to ggs; grant flashback any table to ggs; grant select any dictionary to ggs; grant select any table to ggs; grant resource to ggs; grant delete any table to ggs; grant insert any table to ggs; grant update any table to ggs; grant restricted session to ggs; system altered.</code>
3.5 登陆到ogg,执行初始化
初始化:
1.安装mysql5.5.9略过
2.给mysqlroot配置密码:
mysql端安装:
<code>mysql> show grants for root@localhost;grants for root@localhostgrant all privileges on . to 'root'@'localhost' with grant optiongrant proxy on ''@'' to 'root'@'localhost' with grant option</code>
<code>2 rows in set (0.08 sec)</code>
<code>mysql> flush privileges; query ok, 0 rows affected (0.01 sec) mysql> exit</code>
3.因为复制需要二进制日志,所以启动mysql的时候需要启用二进制日志
查看mysql是否开启了二进制文件
二进制文件:
<code>mysql> show variables like 'log_bin';variable_namevaluelog_binon</code>
<code>1 row in set (0.00 sec)</code>
<code></code><code>mysql> show master status;filepositionbinlog_do_dbbinlog_ignore_dbmysql-bin.000003487</code>
<code>1 row in set (0.05 sec)</code>
<code>[root@oggtest ~]# mysqlbinlog mysql-bin.000003</code>
初始化备库端:
<code>oracle goldengate command interpreter for mysql version 12.1.2.1.0 oggcore_12.1.2.1.0_platforms_140920.0203 linux, x64, 64bit (optimized), mysql enterprise on sep 20 2014 03:43:22 operating system character set identified as utf-8. copyright (c) 1995, 2014, oracle and/or its affiliates. all rights reserved. ggsci (ttmysql) 1> create subdirs</code>
<code>creating subdirectories under current directory /ogg</code>
<code>parameter files /ogg/dirprm: already exists report files /ogg/dirrpt: created checkpoint files /ogg/dirchk: created process status files /ogg/dirpcs: created sql script files /ogg/dirsql: created database definitions files /ogg/dirdef: created extract data files /ogg/dirdat: created temporary files /ogg/dirtmp: created credential store files /ogg/dircrd: created masterkey wallet files /ogg/dirwlt: created dump files /ogg/dirdmp: created</code>
第一部分安装和基本配置完成。
源端配置大致分为如下三个步骤:配置mgr,配置抽取进程,配置投递进程。
在源端先创建一张表,记得带主键:
oracle建表:
<code>table created.</code>
oracle配置全局设置:
<code>copyright (c) 1995, 2014, oracle and/or its affiliates. all rights reserved.</code>
<code>ggsci (oggtest) 1>dblogin userid ggs password ggs successfully logged into database.</code>
<code>ggsci (oggtest as ggs@oggtt) 2> view params ./globals</code>
<code>ggschema ggs ggsci (oggtest as ggs@oggtt) 3></code>
配置mgr:
manager进程参数配置说明:
port:指定服务监听端口;这里以7809为例,默认端口为7809
dynamicportlist:动态端口:可以制定最大256个可用端口的动态列表,当指定的端口不可用时,管理进程将会从列表中选择一个可用的端口,源端和目标段的collector、replicat、ggsci进程通信也会使用这些端口;
comment:注释行,也可以用--来代替;
autostart:指定在管理进程启动时自动启动哪些进程;
autorestart:自动重启参数设置:本处设置表示每3分钟尝试重新启动所有extract进程,共尝试5次;
purgeoldextracts:定期清理trail文件设置:本处设置表示对于超过3天的trail文件进行删除。
lagreport、laginfo、lagcritical:
定义数据延迟的预警机制:本处设置表示mgr进程每隔1小时检查extract的延迟情况,如果超过了30分钟就把延迟作为信息记录到错误日志中,如果延迟超过了45分钟,则把它作为警告写到错误日志中。
启动mgr:
<code>program status group lag at chkpt time since chkpt</code>
<code>manager running</code>
添加并查看需要复制的表:
<code>ggsci (oggtest as ggs@oggtt) 8> info trandata chen.* logging of supplemental redo log data is enabled for table chen.student. columns supplementally logged for table chen.student: id. ggsci (oggtest as ggs@oggtt) 9></code>
如果不执行add trandata,insert同步没有问题(oracle数据库),但是在同步update或delete操作时,就会因为丢失主键报同步错误。不开启表级的最小附加日志,update的redo信息不记录没有进行更新的字段信息,如主键不更新的话主键不记录在redo中,所以会导致同步失败。
配置抽取进程:
<code>ggsci (oggtest as ggs@oggtt) 10> add extract ext3,tranlog,begin now extract added.</code>
<code>ggsci (oggtest as ggs@oggtt) 11> add exttrail /u01/app/oracle/product/11.2.0/ogg12c/dirdat/xs,extract ext3 exttrail added.</code>
<code>ext的模板可以是: extract extmb setenv (nls_lang = "american_america.utf8") setenv (oracle_home = "/u01/oracle/product/11.2.0/db_1") setenv (oracle_sid = "orcl") userid ggs, password ggs --gettruncates reportcount every 1 minutes, rate discardfile ./dirrpt/extmb.dsc,append,megabytes 1024 --threadoptions maxcommitpropagationdelay 60000 iolatens 60000 dboptions allowunusedcolumn warnlongtrans 2h,checkinterval 3m exttrail ./dirdat/mb --tranlogoptions excludeuser username fetchoptions nousesnapshot tranlogoptions convertucs2clobs table hr.emp;</code>
setenv:配置系统环境变量
userid/ password: 指定ogg连接数据库的用户名和密码,这里使用3.4部分中创建的数据库用户ogg;
table:定义需复制的表,后面需以;结尾
tableexclude:定义需要排除的表,如果在table参数中使用了通配符,可以使用该参数指定排除掉得表。
getupdateafters|ignoreupdateafters:
是否在队列中写入后影像,缺省复制
getupdatebefores| ignoreupdatebefores:
是否在队列中写入前影像,缺省不复制
getupdates|ignoreupdates:
是否复制update操作,缺省复制
getdeletes|ignoredeletes:
是否复制delete操作,缺省复制
getinserts|ignoreinserts:
是否复制insert操作,缺省复制
gettruncates|ignoretrundates:
是否复制truncate操作,缺省不复制;
配置投递进程:
<code>ggsci (oggtest as ggs@oggtt) 13> add extract push3,exttrailsource /u01/app/oracle/product/11.2.0/ogg12c/dirdat/xs extract added. ggsci (oggtest as ggs@oggtt) 14> add rmttrail /ogg/dirdat/xs,extract push3 rmttrail added.</code>
<code>push的模板: extract pushmb setenv (nls_lang = "american_america.utf8") userid ggs, password ggs passthru rmthost 192.168.0.165, mgrport 7809, compress rmttrail /u01/ogg/11.2/dirdat/xs table hr.ah4;</code>
rmthost:指定目标系统及其goldengate manager进程的端口号,还用于定义是否使用压缩进行传输,本例中的compress为压缩传输;
rmttrail:指定写入到目标断的哪个队列;
exttrail:指定写入到本地的哪个队列;
sqlexec:在extract进程运行时首先运行一个sql语句;
passthru:禁止extract进程与数据库交互,适用于data pump传输进程;
report:定义自动定时报告;
statoptions:定义每次使用stat时统计数字是否需要重置;
reportcount:报告已经处理的记录条数统计数字;
tltrace:打开对于数据库日志的跟踪日志;
discardfile:定义discardfile文件位置,如果处理中油记录出错会写入到此文件中;
dboptions:指定对于某种特定数据库所需要的特殊参数;
tranlogoptions:指定在解析数据库日志时所需要的特殊参数,例如:对于裸设备,可能需要加入以下参数 rawdeviceoggset 0
warnlongtrans:指定对于超过一定时间的长交易可以在gsserr.log里面写入警告信息,本处配置为每隔3分钟检查一次场交易,对于超过2小时的进行警告;
因为是异构关系,所以define两个数据库之间表的关系映射,是必不可少的。
配置define文件:
并将生成的/u01/app/oracle/product/11.2.0/ogg12c/dirdef/chen.student传到目的端的相应目录中去
scp /u01/app/oracle/product/11.2.0/ogg12c/dirdef/chen.student 192.168.0.25:/ogg/dirdef/
注意:mysql端的ogg我是装在oracle用户下的。
目的端配置大致分为如下三个步骤:配置mgr,配置checkpoint table,配置应用进程
在目的端先创建一张表,记得带主键:
mysql创建表:
<code>mysql> show tables;tables_in_chenstudent</code>
<code>ggsci (oggtest) 2> info error: invalid command.</code>
<code>ggsci (oggtest) 3> info all</code>
配置checkpoint table:
配置应用进程:
<code>replicat rep3 sourcedefs /ogg/dirdef/chen.student sourcedb chen,userid root,password 123456 reperror default,discard discardfile /ogg/dirrpt/f,append,megabytes 50 map chen.student,target chen.student;</code>
<code>ggsci (ttmysql dblogin as root) 11> add replicat rep3,exttrail /ogg/dirdat/xs,checkpointtable chen.checkpointtab replicat added.</code>
replicat进程参数配置说明:
assumetargetdefs:假定两端数据结构一致使用此参数;
sourcedefs:假定两端数据结构不一致,使用此参数指定源端的数据结构定义文件,该文件需要由glodengate工具产生。
map:用于指定源端与目标端表的映射关系;
mapexclude:用于使用在map中使用*匹配时排除掉指定的表;
reperror:定义出错以后进程的响应,一般可以定义为两种:
abend,即一旦出现错误即停止复制,此为缺省配置;
discard,出现错误后继续复制,只是把错误的数据放到discard文件中。
sqlexec:在进程运行时首先运行一个sql语句;
grouptransops:将小交易合并成一个大的交易进行提交,减少提交次数,降低系统io消耗。
maxtransops:将大交易拆分,每xx条记录提交一次。
在目的端启动rep3进程,在源端启动ext3和push3进程。
在源端的student表中插入一条数据,看是否在目的端的student表中能看到。
源端进程
源端进程:
<code>manager running extract running ext3 00:00:00 00:00:09 extract running push3 00:00:00 00:00:09 源端插入数据 sql> insert into student values(3,'xinxin');</code>
<code>1 row created.</code>
目标端进程
目标端进程:
<code>manager running replicat running rep3 00:00:00 00:00:03 查询目标端数据库</code>
<code></code><code>mysql> select * from student;idname2jia3xinxin</code>
<code>2 rows in set (0.00 sec)</code>
到此完成ogg异构oracle到mysql数据库准实时数据同步。
本测试主要描述了针对一个测试表异构从oracle到mysql实现准实时同步数据。为了实现数据同步,需要配好ogg的几个进程。
1、manager管理进程在两端开启,监控和重启其他进程;分配数据存储和报告错误及事件(进程不能run,需要仔细查看ggserr.log,此日志在ogg安装目录下)。
2、extract进程从日志中抓取并传输到target端事务数据;extract日志抓取根据参数配置可分为本地和异地。本文档在源端配置了两个ext进程,ext3(本地),push3(异地)。
3、server collector进程在target(接受)端接受数据并写入trail文件;
4、replicat进程读取trail文件,并应用到traget数据库;
5、trail文件时gg自己抓捕信息的文件,是一个os文件,存放在./dirdat/下,以xs00000命名,n顺序1,2,3…此文件用完可配置参数自动删除。
sqlpus下执行
create table chen.t1 as select * from chen.student;
alter table chen.t1 add constraint pk_id primary key (id);
配置源端抽取参数:
<code>extract ext3 dynamicresolution userid ggs,password ggs exttrail /u01/app/oracle/product/11.2.0/ogg12c/dirdat/xs table chen.student; table chen.t1;</code>
<code>ggsci (oggtest) 14> view params push3</code>
<code>extract push3 passthru dynamicresolution userid ggs,password ggs rmthost 192.168.0.25,mgrport 7809 rmttrail /ogg/dirdat/xs table chen.student; table chen.t1;</code>
添加需要同步的表:
重新生成异构表的关系映射:
<code>defsfile /u01/app/oracle/product/11.2.0/ogg12c/dirdef/chen.student userid ggs,password ggs table chen.student; table chen.t1;</code>
用命令生成映射(注意因为与原映射文件名相同,原映射文件需要改名备份)
[oracle@oggtest ogg12c]$ ./defgen paramfile dirprm/chen.prm
show create table chen.studentg,查看,改表名就行。
目标端修改复制进程参数:
<code>replicat rep3 sourcedefs /ogg/dirdef/chen.student sourcedb chen,userid root,password 123456 reperror default,discard discardfile /ogg/dirrpt/rep4.dsc,append,megabytes 50 map chen.student,target chen.student; map chen.t1,target chen.t1;</code>
先info all查看进程情况
目标端:
start mgr
start rep3
源端:
start ext3
start push3
分别查看ggserr.log,没有报错,进程正常
查看目标端复制情况,因为源端是用create table as select建表,带有数据,但发现原数据没有同步到目标端,随后在源端insert的数据能同步到目标端。
第六部分:表数据在源端的导出和目标端的导入
前提:需要在源端配置相关mgr,ext等进程工作正常,涉及到的异构表的转换文件准备好,目标端rep进程配置好,但不启用。(以上设置参考前几部分的配置)
建测试表和测试sql:
<code>sql> begin</code>
<code>insert into tt01 values(seqt1.nextt01al,200000*dbms_random.value,'nanfang is updating'); commit; insert into tt01 values(seqt1.nextt01al,300000*dbms_random.value,'nanfang is updating'); commit; update tt01 set t2=t2+10 where rownum=1; commit; dbms_lock.sleep(1); end loop; end; /</code>
从源端导出表数据:
<code>add extract einit,sourceistable</code>
<code>/ 以sourceistable选项创建extract /</code>
<code>view params einit extract einit sourceistable userid ggs, password ggs rmthost 59.202.48.46, mgrport 7809 rmtfile /u01/app/gg12c/dirdat/chentt1 table chen.tt1;</code>
<code>/ 之后我们需要在命令行界面下调用该extract / extract paramfile dirprm/einit.prm 3.在目标端导入数据 首先需要在mysql下建表 use chen create table tt01 (t1 int primary key,t2 int,t3 varchar(30)); / 接下来我们要在target上配置initial load使用的replicat,以导入之前的extfile / ggsci下操作 add replicat rinit,specialrun</code>
<code>异构导入的参数配置 edit params rinit replicat rinit specialrun sourcedefs /u01/app/gg12c/dirdef/chen.tt1 sourcedb chen,userid root,password 123456 extfile /u01/app/gg12c/dirdat/chentt1 assumetargetdefs map chen.tt1, target chen.tt1;</code>
<code>/ 之后我们在命令行上调用该replicat / os $gghome下操作 replicat paramfile dirprm/rinit.prm</code>
利用ogg将原表数据的导出,再异构导入,后启动复制进程,追同步日志,这一原理能实现在线oracle到mysql的数据迁移。