需求描述:
同步Oracle的分区表到MySQL,并且该分区表是压缩表,要同步到MySQL的分表。
软件版本:
Oracle 11.2.0.3
MySQL 5.6.26
OGG for Oracle 12.1.2.1.0
OGG for MySQL 12.1.2.1.0
具体过程:
数据库安装部分略过,注意我的实验中Oracle字符集是AL32UTF8,MySQL是UTF8,所以配置期间一律使用默认字符集,如果两边字符集不一样,注意设置字符集。
下载软件包:
121210_fbo_ggs_Linux_x64_shiphome.zip
ggs_121210_Linux_x64_MySQL_64bit.zip
p16764834_112030_Linux-x86-64.zip
p16764834是Oracle的补丁包,因为ogg抽取压缩表的需要用
integrated的抽取方式,该抽取方式是ogg 12c版本的新特性,需要数据库11.2.0.3之后的版本才可以使用,11.2.0.3需要打上该补丁才可以支持该功能。
软件安装:
source端
Oracle用户解压补丁包,并打上补丁
关闭数据库和监听
SQL> shutdown immediate;
[oracle@testdb ~]$ lsnrctl stop
[oracle@testdb ~]$ unzip p16764834_112030_Linux-x86-64.zip
[oracle@testdb ~]$ cd 16764834
[oracle@testdb 16764834]$ $ORACLE_HOME/OPatch/opatch apply
SQL> @?/sqlpatch/16764834/postinstall.sql
解压OGG的安装包,使用图形界面安装OGG12c
[oracle@testdb ~]$ unzip 121210_fbo_ggs_Linux_x64_shiphome.zip
[oracle@testdb ~]$ cd fbo_ggs_Linux_x64_shiphome/Disk1/
[oracle@testdb Disk1]$ ./runInstaller
安装过程很简单,注意选择数据库版本是11g,安装ogg的目录时最好使用自己创建的目录,此次使用的是/u01/app/ogg
修改配置文件
[oracle@testdb ~]$ vi ~/.bash_profile
export SHLIB_PATH=/u01/app/ogg:$ORACLE_HOME/lib
export PATH=$ORACLE_HOME/bin:/bin:/usr/bin:/usr/sbin:/usr/local/bin:
/u01/app/ogg:$PATH
创建ogg目录
[oracle@testdb ~]$ source ~/.bash_profile
[oracle@testdb ~]$ cd /u01/app/ogg
[oracle@testdb ogg]$ ./ggsci
GGSCI (tbrac01) 1>
create subdirs
Creating subdirectories under current directory /u01/app/ogg
Parameter files /u01/app/ogg/dirprm: already exists
Report files /u01/app/ogg/dirrpt: created
Checkpoint files /u01/app/ogg/dirchk: created
Process status files /u01/app/ogg/dirpcs: created
SQL script files /u01/app/ogg/dirsql: created
Database definitions files /u01/app/ogg/dirdef: created
Extract data files /u01/app/ogg/dirdat: created
Temporary files /u01/app/ogg/dirtmp: created
Stdout files /u01/app/ogg/dirout: created
GGSCI> EDIT PARAM MGR
PORT 7809
target端
root用户解压安装包
[root@Mysql-Test ~]# mkdir /ogg
[root@Mysql-Test ~]# mv ggs_121210_Linux_x64_MySQL_64bit.zip /ogg
[root@Mysql-Test ~]# cd /ogg
[root@Mysql-Test ogg]# unzip ggs_121210_Linux_x64_MySQL_64bit.zip
[root@Mysql-Test ogg]# tar xvf ggs_Linux_x64_MySQL_64bit.tar
[root@Mysql-Test ogg]# vi ~/.bash_profile
export GGHOME=/ogg
export PATH=$PATH:$GGHOME
export LD_LIBRARY_PATH=/ogg:$LD_LIBRARY_PATH
[root@Mysql-Test ogg]# ./ggsci
GGSCI (Mysql-Test
) >
create subdirs
GGSCI> EDIT PARAM MGR
PORT 7809
异构文件配置
source端
GGSCI > edit param defgen
userid oggadmin,password oggadmin
defsfile /u01/app/ogg/dirdef/oracle.def
table FOG.T_AVAILALLOW;
[oracle@testdb ~]$ cd /u01/app/ogg
[oracle@testdb ogg]$ ./defgen paramfile /u01/app/ogg/dirprm/defgen.prm
...
userid ggs,password ***
defsfile /u01/app/ogg/dirdef/oracle.def
...
将上面的oracle.def文件copy只target端,放到ogg的目录下 即/ogg/dirdef/oracle.def
OGG配置
source端
开启主库的force logging和补充日志功能
SQL> ALTER DATABASE ARCHIVELOG;
SQL> ALTER DATABASE OPEN;
SQL> ALTER DATABASE FORCE LOGGING;
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;
SQL> SELECT name,open_mode,force_logging,supplemental_log_data_min FROM v$database;
NAME OPEN_MODE FOR SUPPLEME
--------- -------------------- --- --------
TRAC READ WRITE YES YES
创建ogg的管理用户
SQL> CREATE TABLESPACE goldengate DATAFILE '/u01/app/oracle/oradata/goldengate01.dbf' SIZE 100M AUTOEXTEND ON;
SQL> CREATE USER oggadmin IDENTIFIED BY oggadmin DEFAULT TABLESPACE goldengate;
SQL> GRANT dba TO oggadmin;
SQL> grant execute on UTL_FILE to oggadmin;
执行oggadmin授权脚本
[
oracle@testdb
~]$ cd /u01/app/ogg/
[oracle@testdb ogg]$ sqlplus / as sysdba
SQL> @marker_setup.sql
SQL> @ddl_setup.sql
SQL> @role_setup.sql
SQL> grant ggs_ggsuser_role to oggadmin;
SQL> @ddl_enable.sql
SQL> EXEC DBMS_GOLDENGATE_AUTH.GRANT_ADMIN_PRIVILEGE( grantee => 'oggadmin', privilege_type => 'CAPTURE', grant_select_privileges=> true, do_grants => TRUE);
创建抽取进程
GGSCI > dblogin userid oggadmin,password oggadmin
GGSCI > register extract fm1 database
GGSCI > add extract fm1 integrated tranlog,begin now
GGSCI > add exttrail /u01/app/ogg/dirdat/fm,extract fm1
GGSCI > edit params fm1
EXTRACT fm1
USERID oggadmin, PASSWORD oggadmin
TRANLOGOPTIONS INTEGRATEDPARAMS (MAX_SGA_SIZE 100)
EXTTRAIL /u01/app/ogg/dirdat/fm
DYNAMICRESOLUTION
DDL INCLUDE MAPPED
DDLOPTIONS ADDTRANDATA,REPORT
FETCHOPTIONS, USESNAPSHOT, NOUSELATESTVERSION, MISSINGROW REPORT
STATOPTIONS REPORTFETCH
WARNLONGTRANS 1H, CHECKINTERVAL 5M
GETTRUNCATES
TABLE FOG.T_AVAILALLOW;
创建投递进程
GGSCI> add extract df1 exttrailsource /u01/app/ogg/ogg/dirdat/fm
GGSCI > add rmttrail /ogg/dirdat/f1,extract df1
GGSCI > edit params df1
EXTRACT df1
USERID oggadmin, PASSWORD oggadmin
RMTHOST 192.168.20.60, MGRPORT 7809
RMTTRAIL /ogg/dirdat/f1
passthru
table FOG.T_AVAILALLOW;
target端
创建要同步的数据库
mysql> create database availallow;
按照规划建好分表,表结构要跟主库的结构相同,有些字段类型Oracle和MySQL不同需要做相应修改。我这里按日期每年一张表。
mysql> show tables;
+----------------------+
| Tables_in_availallow |
+----------------------+
| checkpoint |
| checkpoint_lox |
| t_availallow_2013 |
| t_availallow_2014 |
| t_availallow_2015 |
| t_availallow_2016 |
| t_availallow_2017 |
| t_availallow_2018 |
| t_availallow_2019 |
| t_availallow_2020 |
| t_availallow_2021 |
| t_availallow_2022 |
| t_availallow_2023 |
| t_availallow_2024 |
| t_availallow_2025 |
| t_availallow_2026 |
| t_availallow_2027 |
| t_availallow_2028 |
| t_availallow_2029 |
| t_availallow_2030 |
| t_availallow_2031 |
| t_availallow_2032 |
| t_availallow_2033 |
| t_availallow_2034 |
| t_availallow_2035 |
| t_availallow_2036 |
| t_availallow_2037 |
| t_availallow_2038 |
| t_availallow_2039 |
| t_availallow_2040 |
| t_availallow_max |
| t_availallow_min |
+----------------------+
32 rows in set (0.00 sec)
GGSCI > dblogin sourcedb availallow@localhost:3306 userid root password test123
创建checkpoint表
GGSCI > add checkpointtable availallow.checkpoint
添加应用进程
GGSCI > add replicat rp1,exttrail /ogg/dirdat/f1,checkpointtable availallow.checkpoin
GGSCI > edit params rp1
replicat rp1
targetdb availallow@localhost:3306 userid root password
test123
sourcedefs /ogg/dirdef/oracle.def
handlecollisions
discardfile /ogg/dirrpt/R3.dsc,purge
map fog.t_availallow,target availallow.t_availallow_min,where (availid < 20140000000000000000);
map fog.t_availallow,target availallow.t_availallow_2014,where (availid >= 20140000000000000000 and availid < 20150000000000000000);
map fog.t_availallow,target availallow.t_availallow_2015,where (availid >= 20150000000000000000 and availid < 20160000000000000000);
map fog.t_availallow,target availallow.t_availallow_2016,where (availid >= 20160000000000000000 and availid < 20170000000000000000);
map fog.t_availallow,target availallow.t_availallow_2017,where (availid >= 20170000000000000000 and availid < 20180000000000000000);
map fog.t_availallow,target availallow.t_availallow_2018,where (availid >= 20180000000000000000 and availid < 20190000000000000000);
map fog.t_availallow,target availallow.t_availallow_2019,where (availid >= 20190000000000000000 and availid < 20200000000000000000);
map fog.t_availallow,target availallow.t_availallow_2020,where (availid >= 20200000000000000000 and availid < 20210000000000000000);
map fog.t_availallow,target availallow.t_availallow_2021,where (availid >= 20210000000000000000 and availid < 20220000000000000000);
map fog.t_availallow,target availallow.t_availallow_2022,where (availid >= 20220000000000000000 and availid < 20230000000000000000);
map fog.t_availallow,target availallow.t_availallow_2023,where (availid >= 20230000000000000000 and availid < 20240000000000000000);
map fog.t_availallow,target availallow.t_availallow_2024,where (availid >= 20240000000000000000 and availid < 20250000000000000000);
map fog.t_availallow,target availallow.t_availallow_2025,where (availid >= 20250000000000000000 and availid < 20260000000000000000);
map fog.t_availallow,target availallow.t_availallow_2026,where (availid >= 20260000000000000000 and availid < 20270000000000000000);
map fog.t_availallow,target availallow.t_availallow_2027,where (availid >= 20270000000000000000 and availid < 20280000000000000000);
map fog.t_availallow,target availallow.t_availallow_2028,where (availid >= 20280000000000000000 and availid < 20290000000000000000);
map fog.t_availallow,target availallow.t_availallow_2029,where (availid >= 20290000000000000000 and availid < 20300000000000000000);
map fog.t_availallow,target availallow.t_availallow_2030,where (availid >= 20300000000000000000 and availid < 20310000000000000000);
map fog.t_availallow,target availallow.t_availallow_2031,where (availid >= 20310000000000000000 and availid < 20320000000000000000);
map fog.t_availallow,target availallow.t_availallow_2032,where (availid >= 20320000000000000000 and availid < 20330000000000000000);
map fog.t_availallow,target availallow.t_availallow_2033,where (availid >= 20330000000000000000 and availid < 20340000000000000000);
map fog.t_availallow,target availallow.t_availallow_2034,where (availid >= 20340000000000000000 and availid < 20350000000000000000);
map fog.t_availallow,target availallow.t_availallow_2035,where (availid >= 20350000000000000000 and availid < 20360000000000000000);
map fog.t_availallow,target availallow.t_availallow_2036,where (availid >= 20360000000000000000 and availid < 20370000000000000000);
map fog.t_availallow,target availallow.t_availallow_2037,where (availid >= 20370000000000000000 and availid < 20380000000000000000);
map fog.t_availallow,target availallow.t_availallow_2038,where (availid >= 20380000000000000000 and availid < 20390000000000000000);
map fog.t_availallow,target availallow.t_availallow_2039,where (availid >= 20390000000000000000 and availid < 20400000000000000000);
map fog.t_availallow,target availallow.t_availallow_max,where (availid >= 20400000000000000000);
初始化数据
source端
GGSCI > add extract init,sourceistable
GGSCI > edit params init
extract init
userid oggadmin password oggadmin
rmthost 192.168.20.60,mgrport 7809
rmttask replicat,group repinit
table fog.t_availallow;
target端
GGSCI > add replicat repinit,specialrun
GGSCI > edit params repinit
replicat repinit
targetdb availallow@localhost:3336 userid root password
test123
sourcedefs /ogg/dirdef/oracle.def
discardfile /ogg/dirrpt/repinit.dsc,purge
map fog.t_availallow,target availallow.t_availallow_min,where (availid < 20140000000000000000);
map fog.t_availallow,target availallow.t_availallow_2014,where (availid >= 20140000000000000000 and availid < 20150000000000000000);
map fog.t_availallow,target availallow.t_availallow_2015,where (availid >= 20150000000000000000 and availid < 20160000000000000000);
map fog.t_availallow,target availallow.t_availallow_2016,where (availid >= 20160000000000000000 and availid < 20170000000000000000);
map fog.t_availallow,target availallow.t_availallow_2017,where (availid >= 20170000000000000000 and availid < 20180000000000000000);
map fog.t_availallow,target availallow.t_availallow_2018,where (availid >= 20180000000000000000 and availid < 20190000000000000000);
map fog.t_availallow,target availallow.t_availallow_2019,where (availid >= 20190000000000000000 and availid < 20200000000000000000);
map fog.t_availallow,target availallow.t_availallow_2020,where (availid >= 20200000000000000000 and availid < 20210000000000000000);
map fog.t_availallow,target availallow.t_availallow_2021,where (availid >= 20210000000000000000 and availid < 20220000000000000000);
map fog.t_availallow,target availallow.t_availallow_2022,where (availid >= 20220000000000000000 and availid < 20230000000000000000);
map fog.t_availallow,target availallow.t_availallow_2023,where (availid >= 20230000000000000000 and availid < 20240000000000000000);
map fog.t_availallow,target availallow.t_availallow_2024,where (availid >= 20240000000000000000 and availid < 20250000000000000000);
map fog.t_availallow,target availallow.t_availallow_2025,where (availid >= 20250000000000000000 and availid < 20260000000000000000);
map fog.t_availallow,target availallow.t_availallow_2026,where (availid >= 20260000000000000000 and availid < 20270000000000000000);
map fog.t_availallow,target availallow.t_availallow_2027,where (availid >= 20270000000000000000 and availid < 20280000000000000000);
map fog.t_availallow,target availallow.t_availallow_2028,where (availid >= 20280000000000000000 and availid < 20290000000000000000);
map fog.t_availallow,target availallow.t_availallow_2029,where (availid >= 20290000000000000000 and availid < 20300000000000000000);
map fog.t_availallow,target availallow.t_availallow_2030,where (availid >= 20300000000000000000 and availid < 20310000000000000000);
map fog.t_availallow,target availallow.t_availallow_2031,where (availid >= 20310000000000000000 and availid < 20320000000000000000);
map fog.t_availallow,target availallow.t_availallow_2032,where (availid >= 20320000000000000000 and availid < 20330000000000000000);
map fog.t_availallow,target availallow.t_availallow_2033,where (availid >= 20330000000000000000 and availid < 20340000000000000000);
map fog.t_availallow,target availallow.t_availallow_2034,where (availid >= 20340000000000000000 and availid < 20350000000000000000);
map fog.t_availallow,target availallow.t_availallow_2035,where (availid >= 20350000000000000000 and availid < 20360000000000000000);
map fog.t_availallow,target availallow.t_availallow_2036,where (availid >= 20360000000000000000 and availid < 20370000000000000000);
map fog.t_availallow,target availallow.t_availallow_2037,where (availid >= 20370000000000000000 and availid < 20380000000000000000);
map fog.t_availallow,target availallow.t_availallow_2038,where (availid >= 20380000000000000000 and availid < 20390000000000000000);
map fog.t_availallow,target availallow.t_availallow_2039,where (availid >= 20390000000000000000 and availid < 20400000000000000000);
map fog.t_availallow,target availallow.t_availallow_max,where (availid >= 20400000000000000000);
首次初始化启动OGG
source端先启动抽取进程和投递进程
GGSCI > start fm1
GGSCI > start df1
source端启动初始化进程
GGSCI > start init
target端观察数据初始化情况,查看数据是否完成初始化
GGSCI > view report repinit
待数据初始化完成,启动target端的应用进程
GGSCI > start rp1
确认初始化数据加载完成后关闭改变同步Replicat进程rep1的handlecollisions。
GGSCI (gc.yxd.com) 1> send rp1,nohandlecollisions
GGSCI > edit params repinit
handlecollisions修改为nohandlecollisions
小结:
1、ogg在抽取和应用进程中有可以使用where和filter来现在数据同步的条件,filter相较where更加灵活,但是在实验中发现使用filter对比id的时候,数字类型超过20位会抛出异常,
ERROR OGG-01072 NUMCNV_getNumeric: Buffer overflow, needed: 20, allocated: 19
查的是OGG的bug所致,较高版本中才得以修复,MySQL版本的ogg又比较低,所以无奈才使用where方式。
2、数据初始化非常重要,注意首次开启ogg的顺序,合理使用handlecollisions参数保证数据完整可用。
3、选择filter或者where进行操作数据时,注意选择的条件要尽量是主键,如果不是主键需要在抽取数据时加上FETCHBEFOREFILTER参数,比如FETCHBEFOREFILTER, FETCHCOLS (age)
参考资料地址:
GoldenGate 之 Oracle to Mysql:
Mapping and Manipulating Data:
OGG-01072 bug:
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29320885/viewspace-2125993/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/29320885/viewspace-2125993/