OGG Oracle 分区压缩表到 MySQL分表的实现

最新推荐文章于 2022-12-24 17:58:38 发布

csi83845

最新推荐文章于 2022-12-24 17:58:38 发布

阅读量473

点赞数

文章标签：数据库

需求描述：

同步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