📢📢📢📣📣📣
哈喽！大家好，我是【IT邦德】，江湖人称jeames007，10余年DBA及大数据工作经验
一位上进心十足的【大数据领域博主】！😜😜😜
中国DBA联盟(ACDU)成员，目前服务于工业互联网
擅长主流Oracle、MySQL、PG、高斯及Greenplum运维开发，备份恢复，安装迁移，性能优化、故障应急处理等。
✨ 如果有对【数据库】感兴趣的【小可爱】，欢迎关注【IT邦德】💞💞💞
❤️❤️❤️感谢各位大可爱小可爱！❤️❤️❤️
文章目录
前言
本文详细阐述了基于OGG实现Oracle实时同步MySQL全过程
1.架构设计
2.OGG安装部署
2.1 OGG for Oracle
1.OGG下载地址：
https://www.oracle.com/middleware/technologies/goldengate-downloads.html
创建OGG使用目录
chown -R oracle:oinstall /oraogg
chmod 775 -R /oraogg
2.环境变量如下
vi .bash_profile
exportGG_HOME=/oraogg/goldengate
exportPATH=$PATH:$HOME/bin:$ORACLE_HOME/bin:$GG_HOMEexportLD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib
aliasggsci='cd $GG_HOME;ggsci'source .bash_profile
3.静默安装
cd /oraogg/fbo_ggs_Linux_x64_Oracle_shiphome/Disk1/response
vi /oraogg/fbo_ggs_Linux_x64_Oracle_shiphome/Disk1/response/oggcore.rsp
--只修改如下3个地方即可。
INSTALL_OPTION=ora19c
SOFTWARE_LOCATION=/oraogg/goldengate
INVENTORY_LOCATION=/u01/app/oraInventory
/u01/app/oracle/product/19.3.0/db_1
使用如下命令静默安装：
/oraogg/fbo_ggs_Linux_x64_Oracle_shiphome/Disk1/runInstaller -silent
-responseFile /oraogg/fbo_ggs_Linux_x64_Oracle_shiphome/Disk1/response/oggcore.rsp
OGG初始化
cd /ogg
./ggsci
create subdirs
2.2 OGG for MySQL
1.安装客户端
yum install https://dev.mysql.com/get/mysql80-community-release-el7-3.noarch.rpm
yum install mysql-community-server --nogpgcheck
2.创建oggm用户运行OGG for MySQL软件，安装目录为/oggmysql
groupadd -g 1005 ogg
useradd -g ogg -u 1005 -m oggm
mkdir -p /oggmysql
chown oggm:ogg /oggmysql
3.解压缩安装
cd /oggmysql/
unizp 213000_ggs_Linux_x64_MySQL_64bit.zip
tar -xf ggs_Linux_x64_MySQL_64bit.tar
[root@ogg21all oggmysql]# ./ggsci -V
Oracle GoldenGate Command Interpreter for MySQL
Version 21.3.0.0.0 OGGCORE_21.3.0.0.0_PLATFORMS_210728.1047
Oracle Linux 7, x64, 64bit (optimized), MySQL on Jul 28 2021 18:17:46
Copyright © 1995, 2021, Oracle and/or its affiliates. All rights reserved.
4.配置
su - oggm
$ /oggmysql/ggsci
GGSCI (ogg21all) 1> create subdirs
5.环境变量设置
vi .bash_profile
exportGG_HOME=/oggmysql
exportPATH=$PATH:$HOME/bin:$GG_HOMEaliasggsci='cd $GG_HOME;ggsci'source .bash_profile
3.Oracle相关配置
3.1 参数调整
– oracle数据库配置
1.开启数据库归档–如果没有开启
2.开启数据库级别附加日志–如果没有开始最小附加日志
3.开启强制日志–如果没有开启强制日志
4.设置ENABLE_GOLDENGATE_REPLICAT参数为TRUE
5.创建OGG用户包括包括源端用户、目标端用户以及OGG抽取用户
alter database add supplemental log data;
alter database add supplemental log data (all) columns;
alter database force logging;
alter system set enable_goldengate_replication=TRUE;
##修改归档路径
mkdir -p /home/oracle/arch
SYS@oradb> alter system set log_archive_dest_1=‘location=/home/oracle/arch’;
System altered.
SYS@oradb> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/oracle/arch
Oldest online log sequence 3
Next log sequence to archive 5
Current log sequence 5
SYS@oradb>select name,supplemental_log_data_min , force_logging, log_mode from v$database;
NAME SUPPLEMENTAL_LOG FORCE_LOGGING LOG_MODE
------------------ ---------------- ----------------- ------------------------
ORCLCDB YES YES ARCHIVELOG
##关闭回收站
SQL> SHOW PARAMETER recyclebin;
SQL> ALTER SYSTEM SET recyclebin = OFF SCOPE = SPFILE;
SQL> show recyclebin;
SQL> PURGE recyclebin;
3.2 新增用户
-- OGG管理用户
SYS@oradb> alter session set container=ORCLPDB1;
CREATE USER ogg identified by oracle;
GRANT DBA to ogg;
grant SELECT ANY DICTIONARY to ogg;
GRANT EXECUTE ON SYS.DBMS_LOCK TO ogg;
grant select any transaction to ogg;
grant select any table to ogg;
grant flashback any table to ogg;
grant alter any table to ogg;
exec dbms_goldengate_auth.grant_admin_privilege('OGG','*',TRUE);
-- 业务用户
CREATE USER rptuser identified by oracle;
GRANT DBA to rptuser ;
grant SELECT ANY DICTIONARY to rptuser;
GRANT EXECUTE ON SYS.DBMS_LOCK TO rptuser;
4.MySQL数据初始化
1.生成MySQL端DDL语句
可以使用Navicat的数据传输功能或其它工具直接从Oracle端生成MySQL类型的建表语句如下：
mysql -uroot -proot
create database rptdb;
mysql -uroot -proot -h 172.18.12.91 -D rptdb -f < ddl.sql
2.DDL语句如下
SET NAMES utf8;
SET FOREIGN_KEY_CHECKS = 0;
DROP TABLE IF EXISTS `ADDRESSES`;
CREATE TABLE `ADDRESSES`
( `ADDRESS_ID` decimal(12, 0) NOT NULL,
`CUSTOMER_ID` decimal(12, 0) NOT NULL,
`DATE_CREATED` datetime NOT NULL,
`HOUSE_NO_OR_NAME` varchar(60) NULL,
`STREET_NAME` varchar(60) NULL,
`TOWN` varchar(60) NULL,
`COUNTY` varchar(60) NULL,
`COUNTRY` varchar(60) NULL,
`POST_CODE` varchar(12) NULL,
`ZIP_CODE` varchar(12) NULL,
PRIMARY KEY (`ADDRESS_ID`),
INDEX `ADDRESS_CUST_IX`(`CUSTOMER_ID` ASC)
);
----- Table structure for CARD_DETAILS ----
DROP TABLE IF EXISTS `CARD_DETAILS`;
CREATE TABLE `CARD_DETAILS`
( `CARD_ID` decimal(12, 0) NOT NULL,
`CUSTOMER_ID` decimal(12, 0) NOT NULL,
`CARD_TYPE` varchar(30) NOT NULL,
`CARD_NUMBER` decimal(12, 0) NOT NULL,
`EXPIRY_DATE` datetime NOT NULL,
`IS_VALID` varchar(1) NOT NULL,
`SECURITY_CODE` decimal(6, 0) NULL,
PRIMARY KEY (`CARD_ID`),
INDEX `CARDDETAILS_CUST_IX`(`CUSTOMER_ID` ASC)
);
---- Table structure for CUSTOMERS ----
DROP TABLE IF EXISTS `CUSTOMERS`;
CREATE TABLE `CUSTOMERS`
( `CUSTOMER_ID` decimal(12, 0) NOT NULL,
`CUST_FIRST_NAME` varchar(40) NOT NULL,
`CUST_LAST_NAME` varchar(40) NOT NULL,
`NLS_LANGUAGE` varchar(3) NULL,
`NLS_TERRITORY` varchar(30) NULL,
`CREDIT_LIMIT` decimal(9, 2) NULL,
`CUST_EMAIL` varchar(100) NULL,
`ACCOUNT_MGR_ID` decimal(12, 0) NULL,
`CUSTOMER_SINCE` datetime NULL,
`CUSTOMER_CLASS` varchar(40) NULL,
`SUGGESTIONS` varchar(40) NULL,
`DOB` datetime NULL, `MAILSHOT` varchar(1) NULL,
`PARTNER_MAILSHOT` varchar(1) NULL,
`PREFERRED_ADDRESS` decimal(12, 0) NULL,
`PREFERRED_CARD` decimal(12, 0) NULL,
PRIMARY KEY (`CUSTOMER_ID`),
INDEX `CUST_ACCOUNT_MANAGER_IX`(`ACCOUNT_MGR_ID` ASC),
INDEX `CUST_DOB_IX`(`DOB` ASC),
INDEX `CUST_EMAIL_IX`(`CUST_EMAIL` ASC)
);
5.Oracle OGG设置
[root@ogg21all /]# su - oracle
[oracle@ogg21all ~]$ ggsci
GGSCI (ogg21all) 2> edit params mgr
PORT 7809
add credentialstore
alter credentialstore add user ogg@172.18.12.90/oradb, password oracle alias ora19c
INFO CREDENTIALSTORE
GGSCI (ogg21all) 7> INFO CREDENTIALSTORE
Reading from credential store:
Default domain: OracleGoldenGate
Alias: ora19c
Userid: ogg@172.18.12.90/oradb
dblogin useridalias ora19c
ADD SCHEMATRANDATA RPTUSER
INFO SCHEMATRANDATA RPTUSER
list tables RPTUSER.*
6.MySQL OGG设置
GGSCI (ogg21all) 1> edit params mgr
port 8809
GGSCI (ogg21all) 2> start mgr
Manager started.
GGSCI (ogg21all) 3> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
7.全量同步数据
Oracle全量同步到MySQL
注意：在此阶段，源端需要停业务，不能产生新数据。
-- oracle端
edit params ext0
EXTRACT ext0
USERIDALIAS ora19c
rmthost 127.0.0.1,mgrport 8809
rmttask replicat,group rep0
TABLE RPTUSER.ADDRESSES;
TABLE RPTUSER.CARD_DETAILS;
TABLE RPTUSER.CUSTOMERS;
add extract ext0 ,sourceistable
delete extract ext0
-- MySQL端
edit params rep0
replicat rep0
targetdb [email protected]:3306 userid root password root
map RPTUSER.ADDRESSES, target rptdb.ADDRESSES;
map RPTUSER.CARD_DETAILS, target rptdb.CARD_DETAILS;
map RPTUSER.CUSTOMERS, target rptdb.CUSTOMERS;
add replicat rep0 ,specialrun
delete replicat rep0
-- 直接启动源端ext0即可，rep0不用启动,MGR会自动启动它，等同步结束，它会自动关闭
start ext0
-- 查看日志
info rep0,showch
view report rep0
--登录验证数据
mysql -uroot -proot -h 172.18.12.91 -D rptdb
mysql> select count(*) from ADDRESSES;
+----------+
| count(*) |
+----------+
| 150 |
+----------+
1 row in set (0.04 sec)
mysql> select count(*) from CARD_DETAILS;
+----------+
| count(*) |
+----------+
| 150 |
+----------+
1 row in set (0.05 sec)
mysql> select count(*) from CUSTOMERS;
+----------+
| count(*) |
+----------+
| 100 |
+----------+
1 row in set (0.04 sec)
GGSCI (ogg21all as ogg@oradb) 21> info ext0
Extract EXT0 Last Started 2023-11-25 18:58 Status STOPPED
Checkpoint Lag Not Available
Log Read Checkpoint Table RPTUSER.CUSTOMERS
2023-11-25 18:58:46 Record 100
Task SOURCEISTABLE
8.增量时实同步
8.1 Oracle端
ADD EXTRACT exto INTEGRATED TRANLOG BEGIN NOW
ADD EXTTRAIL ./dirdat/eo EXTRACT exto
dblogin useridalias ora19c
REGISTER EXTRACT exto DATABASE
edit params exto
EXTRACT exto
USERIDALIAS ora19c
TRANLOGOPTIONS FETCHPARTIALLOB
EXTTRAIL ./dirdat/eo
TABLE RPTUSER.ADDRESSES;
TABLE RPTUSER.CARD_DETAILS;
TABLE RPTUSER.CUSTOMERS;
启动exto
start exto
GGSCI (ogg21all as ogg@oradb) 12> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EXTO 00:02:19 00:00:08
8.2 MySQL端
edit params repm
replicat repm
targetdb rptdb@172.18.12.91:3306 userid root password root
map RPTUSER.ADDRESSES, target rptdb.ADDRESSES;
map RPTUSER.CARD_DETAILS, target rptdb.CARD_DETAILS;
map RPTUSER.CUSTOMERS, target rptdb.CUSTOMERS;
add rep repm, exttrail /oraogg/goldengate/dirdat/eo, NODBCHECKPOINT
delete rep repm
start repm
9.测试同步
1.Oracle端测试产生数据
SYS@oradb> DELETE FROM RPTUSER.ADDRESSES WHERE ADDRESS_ID=150;
GGSCI (ogg21all as ogg@oradb) 21> stats exto,total
Sending STATS request to Extract group EXTO ...
Start of statistics at 2023-11-25 19:36:13.
Output to ./dirdat/eo:
Extracting from RPTUSER.ADDRESSES to RPTUSER.ADDRESSES:
*** Total statistics since 2023-11-25 19:25:02 ***
Total inserts 0.00
Total updates 0.00
Total deletes 1.00
Total upserts 0.00
Total discards 0.00
Total operations 1.00
End of statistics.
2.mysql端插入数据
mysql -uroot -proot -h 172.18.12.91 -D rptdb
mysql> select count(*) from ADDRESSES;
GGSCI (ogg21all) 18> stats repm,total
Sending STATS request to Replicat group REPM ...
Start of statistics at 2023-11-25 19:56:10.
Replicating from RPTUSER.ADDRESSES to rptdb.ADDRESSES:
*** Total statistics since 2023-11-25 19:54:49 ***
Total inserts 0.00
Total updates 0.00
Total deletes 1.00
Total upserts 0.00
Total discards 0.00
Total operations 1.00
End of statistics.
mysql> SELECT * FROM ADDRESSES WHERE ADDRESS_ID=150;
Empty set (0.00 sec)
mysql> select database();
+------------+
| database() |
+------------+
| rptdb |
+------------+
1 row in set (0.00 sec)
版权归原作者 IT邦德 所有， 如有侵权，请联系我们删除。