oracle-->mysql
需求:把oralce HR用户下面的二张表同步到MYSQL5.6 hr 下(DML)
gg2 centos6.0 192.168.8.16 11.2.0.1.0 ogg AL32UTF8 sys Com_123456 归档模式
mysql5.6 192.168.8.11
--清除原有的MYSQL上的GOLDENGDATE 环境
[root@mysql56 ogg]# ./ggsci
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 (mysql56) 1> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER STOPPED
EXTRACT ABENDED EXT5 00:00:00 09:35:29
EXTRACT ABENDED PUMP5 00:00:00 08:18:52
GGSCI (mysql56) 2> dblogin sourcedb sure@mysql56:3306,userid root,password Com_123456
Successfully logged into database.
GGSCI (mysql56 DBLOGIN as root) 5> delete extract ext5
Deleted EXTRACT EXT5.
GGSCI (mysql56 DBLOGIN as root) 6> delete extract pump5
Deleted EXTRACT PUMP5.
cd /sf/ogg/dirdat
rm -rf *
/sf/ogg/dirprm
rm -rf *
--
rm -rf /sf/ogg/dirdef/*
GGSCI (mysql56 DBLOGIN as root) 13> start mgr
ERROR: Parameter file /sf/ogg/dirprm/mgr.prm does not exist.
--=================================================
oracle端操作
mkdir /sf/ogg -p
--把软件安装到这个目录
./ggsci
先配置环境变量
export GG_HOME=/sf/ogg
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$GG_HOME:/usr/lib
http://www.cnblogs.com/suredandan/p/3640678.html
create subdirs
3.修改oracle数据库的参数
3.1 修改数据库为归档模式
3.2 打开辅助日志
alter database add supplemental log data;
alter system switch logfile;
3.3 关闭回收站
alter system set recyclebin=off scope=both;
6. 确认源库为FORCE_LOGGIN。
1. SQL> alter database force logging;
2.
3. Database altered.
4.
5. SQL> select force_logging from v$database;
1. SQL> show parameter recyclebin
2.
3. NAME TYPE VALUE
4. ------------------------------------ ----------- ------------------------------
5. recyclebin string on
6. SQL> alter system set recyclebin=off;
7.
8. alter system set recyclebin=off scope=spfile; --ok
9. startup force;
10. SQL> show parameter recyclebin
--创建相应表空间
create tablespace oggtbs datafile '/u01/oracle/oradata/ogg/oggtbs01.dbf' size 500M autoextend on;
--授权
create user ggs identified by ggs default tablespace oggtbs;
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;
在源库上执行:
GGSCI (ora11g) 2> edit params ./globals
在统计模式下输入并保存:ggschema ggs
cd /sf/ogg
在SQLPLUS 下去运行:
sql> @sequence.sql 根据提示输入:ggs
sqlplus / as sysdba
sql> alter system set recyclebin=off deferred scope=both; #必须,针对ddl复制
sql> @marker_setup.sql prompt: ggs
sql> @ddl_setup.sql prompt: ggs
sql> @role_setup.sql
sql> grant GGS_GGSUSER_ROLE to ggs;
SQL> @ddl_enable.sql
10g需要安装dbms_share_pool包:
sql> @?/rdbms/admin/dbmspool.sql
sql> @ddl_pin ggs;
--mysql配置
mysql> use mysql
Database changed
mysql> UPDATE user SET Password=PASSWORD('123456') where USER='root' and host='root' or host='localhost';
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2 Changed: 2 Warnings: 0
mysql> show grants for root@localhost;
+---------------------------------------------------------------------+
| Grants for root@localhost |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION |
+---------------------------------------------------------------------+
rows in set (0.00 sec)
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> exit
3.因为复制需要二进制日志,所以启动mysql的时候需要启用二进制日志
mysqld_safe --user=mysql --log-bin=/tmp/binlog.001 --log-bin-index=/tmp/binlog.index --max-binlog-size=10M --binlog-do-db=mysql &
或
/usr/my.cnf
4.创建ogg的初始化目录
源端配置大致分为如下三个步骤:配置mgr,配置抽取进程,配置投递进程
在源端先创建一张表,记得带主键:
drop user hr cascade;
create user hr identified by hr;
grant connect,dba,resource to hr;
SQL> create table hr.ah4(id int ,name varchar(10),primary key(id));
insert into hr.ah4 values(1,'xny1');
commit;
create table hr.ah5(id int ,name varchar(10));
insert into hr.ah5 values(11,'xny11');
commit;
--1.登陆ogg,配置全局设置
[oracle@ora11g 11.2]$ ./ggsci
GGSCI (ora11g) 1> dblogin userid ggs password ggs
Successfully logged into database.
GGSCI (ora11g) 2> view params ./globals
ggschema ggs
GGSCI (ora11g) 3> edit params mgr
PORT 7809
DYNAMICPORTLIST 7810-7909
--AUTOSTART ER *
AUTORESTART EXTRACT *,RETRIES 5,WAITMINUTES 3
PURGEOLDEXTRACTS ./dirdat/*,usecheckpoints, minkeepdays 3
LAGREPORTHOURS 1
LAGINFOMINUTES 30
LAGCRITICALMINUTES 45
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分钟,则把它作为警告写到错误日志中。
3.启动mgr,并查看状态
start mgr
info all
dblogin userid ggs password ggs
GGSCI (ora11g) 8>
add trandata hr.ah4
add trandata hr.ah5
2015-09-08 18:50:41 WARNING OGG-06439 No unique key is defined for table AH5.
All viable columns will be used to represent the key, but may not guarantee uniqueness. KEYCOLS may be used to define the key.
GGSCI (ora11g) 9> info trandata hr.*
Logging of supplemental redo log data is enabled for table HR.AH1.
Columns supplementally logged for table HR.AH1: ID.
Logging of supplemental redo log data is enabled for table HR.AH2.
Columns supplementally logged for table HR.AH2: ID.
Logging of supplemental redo log data is enabled for table HR.AH3.
Columns supplementally logged for table HR.AH3: ID.
Logging of supplemental redo log data is enabled for table HR.AH4.
Columns supplementally logged for table HR.AH4: ID.
Logging of supplemental redo log data is disabled for table HR.COUNTRIES.
Logging of supplemental redo log data is disabled for table HR.DEPARTMENTS.
Logging of supplemental redo log data is disabled for table HR.EMPLOYEES.
Logging of supplemental redo log data is disabled for table HR.JOBS.
Logging of supplemental redo log data is disabled for table HR.JOB_HISTORY.
Logging of supplemental redo log data is disabled for table HR.LOCATIONS.
Logging of supplemental redo log data is disabled for table HR.REGIONS.
Logging of supplemental redo log data is disabled for table HR.SURE1.
GGSCI (ora11g) 10>
--配置抽取进程
GGSCI (ora11g) 11> edit params ext3
extract ext3
dynamicresolution
userid ggs,password ggs
exttrail /sf/ogg/dirdat/xs
table hr.ah4;
table hr.ah5;
GGSCI (ora11g) 13> add extract ext3,tranlog,begin now
EXTRACT added.
GGSCI (ora11g) 14> add exttrail /sf/ogg/dirdat/xs,extract ext3
EXTTRAIL added.
PS:
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;
SETENV:配置系统环境变量
USERID/ PASSWORD: 指定OGG连接数据库的用户名和密码,这里使用3.4部分中创建的数据库用户OGG;
COMMENT:注释行,也可以用--来代替;
TABLE:定义需复制的表,后面需以;结尾
TABLEEXCLUDE:定义需要排除的表,如果在TABLE参数中使用了通配符,可以使用该参数指定排除掉得表。
GETUPDATEAFTERS|IGNOREUPDATEAFTERS:
是否在队列中写入后影像,缺省复制
GETUPDATEBEFORES| IGNOREUPDATEBEFORES:
是否在队列中写入前影像,缺省不复制
GETUPDATES|IGNOREUPDATES:
是否复制UPDATE操作,缺省复制
GETDELETES|IGNOREDELETES:
是否复制DELETE操作,缺省复制
GETINSERTS|IGNOREINSERTS:
是否复制INSERT操作,缺省复制
GETTRUNCATES|IGNORETRUNDATES:
是否复制TRUNCATE操作,缺省不复制;
--配置投递进程
GGSCI (ora11g) 17> edit params push3
extract push3
passthru
dynamicresolution
userid ggs,password ggs
rmthost 192.168.8.11,mgrport 7809
rmttrail /sf/ogg/dirdat/xs
table hr.ah4;
table hr.ah5;
GGSCI (ora11g) 18> add extract push3,exttrailsource /sf/ogg/dirdat/xs
EXTRACT added.
GGSCI (ora11g) 19> add rmttrail /sf/ogg/dirdat/xs,extract push3
RMTTRAIL added.
PS:
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;
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小时的进行警告;
7.配置define文件
因为是异构,所以define作为2个数据库之间表的关系映射,是必不可少的。
GGSCI (ora11g) 21> edit params ah4
defsfile /sf/ogg/dirdef/ah4.prm
userid ggs,password ggs
table hr.ah4;
table hr.ah5;
./defgen paramfile dirprm/ah4.prm
Definitions generated for 2 tables in /sf/ogg/dirdef/ah4.prm.
scp /sf/ogg/dirdef/ah4.prm 192.168.8.11:/sf/ogg/dirdef
目的端配置大致分为如下三个步骤:配置mgr,配置checkpoint table,配置应用进程
在目的端先创建一张表,记得带主键:
mysql> create database hr;
Query OK, 1 row affected (0.00 sec)
mysql> use hr
Database changed
mysql> create table ah4(id int ,name varchar(10),primary key(id));
Query OK, 0 rows affected (0.00 sec)
mysql> show tables;
mysql>create table ah5(id int ,name varchar(10));
GGSCI (nosql2) 2> edit params mgr
edit params mgr
PORT 7809
DYNAMICPORTLIST 7810-7909
--AUTOSTART ER *
AUTORESTART EXTRACT *,RETRIES 5,WAITMINUTES 3
PURGEOLDEXTRACTS ./dirdat/*,usecheckpoints, minkeepdays 3
LAGREPORTHOURS 1
LAGINFOMINUTES 30
LAGCRITICALMINUTES 45
GGSCI (nosql2) 3> start mgr
Manager started.
GGSCI (nosql2) 4> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
2.配置checkpoint table
GGSCI (nosql2) 7> edit params ./GLOBALS
CHECKPOINTTABLE hr.checkpoint
--先登录MYSQL dblogin sourcedb hr@mysql56:3306,userid root,password Com_123456
GGSCI (nosql2) 8> add checkpointtable hr.checkpointtab
GGSCI (nosql2) 9> info checkpointtable hr.checkpointtab
在相应的mysql数据库中,也可以看到相应的表被添加了:
mysql> show tables;
+---------------+
| Tables_in_hr |
+---------------+
| ah4 |
| checkpointtab |
+---------------+
rows in set (0.00 sec)
3.配置应用进程:
GGSCI (nosql2) 10> edit params rep3
replicat rep3
sourcedefs /sf/ogg/dirdef/ah4.prm
--SOURCEDB hr,userid root,password Com_123456
sourcedb hr@mysql56:3306,userid root,password Com_123456
reperror default,discard
discardfile /sf/ogg/dirrpt/rep4.dsc,append,megabytes 50
map hr.ah4, target hr.ah4;
map hr.ah5, target hr.ah5;
GGSCI (nosql2) 13> add replicat rep3,exttrail /sf/ogg/dirdat/xs,checkpointtable hr.checkpointtab
REPLICAT added.
PS:
REPLICAT进程参数配置说明:
ASSUMETARGETDEFS:假定两端数据结构一致使用此参数;
SOURCEDEFS:假定两端数据结构不一致,使用此参数指定源端的数据结构定义文件,该文件需要由GlodenGate工具产生。
MAP:用于指定源端与目标端表的映射关系;
MAPEXCLUDE:用于使用在MAP中使用*匹配时排除掉指定的表;
REPERROR:定义出错以后进程的响应,一般可以定义为两种:
ABEND,即一旦出现错误即停止复制,此为缺省配置;
DISCARD,出现错误后继续复制,只是把错误的数据放到discard文件中。
DISCARDFILE:定义discardfile文件位置,如果处理中油记录出错会写入到此文件中;
SQLEXEC:在进程运行时首先运行一个SQL语句;
GROUPTRANSOPS:将小交易合并成一个大的交易进行提交,减少提交次数,降低系统IO消耗。
MAXTRANSOPS:将大交易拆分,每XX条记录提交一次。
4.测试
在目的端启动rep3进程,在源端启动ext3和push3进程。
在源端的ah4表中插入一条数据,看是否在目的端的ah4表中能看到。
cat /sf/ogg/ggserr.log
测试
oracle端执行:
insert into hr.ah5 values(10,'xny10');
insert into hr.ah4 values(10,'xny10');
commit;
--mysql端查询:
select * from ah4
select * from ah5
select * from checkpointtab
--测试2:
update 操作
update hr.ah4 set name='xny100';
update hr.ah5 set name='xny101';
commit;
--mysql这边查询,发现数据已更新
--ddl操作
--oracle
alter table hr.ah4 add age int;
update hr.ah4 set age=28;
commit;
--mysql
alter table ah4 add age int;
此时数据无法过来,需要手工同步,
--再次UPDATE ORACLE端
update hr.ah4 set age=29;
commit;
--发现数据无法更新,age字段并没有更新过来
--两个字段同时UPDATE
update hr.ah4 set age=30,name='xny30';
commit;
--mysql 查询
select * from ah4
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/28999336/viewspace-2052034/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/28999336/viewspace-2052034/