网站推广服务商,公司网站建设意见和建议,织梦cms手机网站源码,阳江网站制作公司#x1f4e2;#x1f4e2;#x1f4e2;#x1f4e3;#x1f4e3;#x1f4e3; 哈喽#xff01;大家好#xff0c;我是【IT邦德】#xff0c;江湖人称jeames007#xff0c;10余年DBA及大数据工作经验 一位上进心十足的【大数据领域博主】#xff01;#x1f61c; 哈喽大家好我是【IT邦德】江湖人称jeames00710余年DBA及大数据工作经验 一位上进心十足的【大数据领域博主】 中国DBA联盟(ACDU)成员目前服务于工业互联网 擅长主流Oracle、MySQL、PG、高斯及Greenplum运维开发备份恢复安装迁移性能优化、故障应急处理等。 ✨ 如果有对【数据库】感兴趣的【小可爱】欢迎关注【IT邦德】 ❤️❤️❤️感谢各位大可爱小可爱❤️❤️❤️ 文章目录 前言1.架构设计2.OGG安装部署2.1 OGG for Oracle2.2 OGG for MySQL 3.Oracle相关配置3.1 参数调整3.2 新增用户 4.MySQL数据初始化5.Oracle OGG设置6.MySQL OGG设置7.全量同步数据8.增量时实同步8.1 Oracle端8.2 MySQL端 9.测试同步 前言 本文详细阐述了基于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_profileexport GG_HOME/oraogg/goldengate
export PATH$PATH:$HOME/bin:$ORACLE_HOME/bin:$GG_HOME
export LD_LIBRARY_PATH$ORACLE_HOME/lib:/lib:/usr/lib
alias ggscicd $GG_HOME;ggscisource .bash_profile3.静默安装
cd /oraogg/fbo_ggs_Linux_x64_Oracle_shiphome/Disk1/response
vi /oraogg/fbo_ggs_Linux_x64_Oracle_shiphome/Disk1/response/oggcore.rsp--只修改如下3个地方即可。
INSTALL_OPTIONora19c
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 [rootogg21all 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_profileexport GG_HOME/oggmysql
export PATH$PATH:$HOME/bin:$GG_HOME
alias ggscicd $GG_HOME;ggscisource .bash_profile3.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_replicationTRUE; ##修改归档路径 mkdir -p /home/oracle/arch SYSoradb alter system set log_archive_dest_1‘location/home/oracle/arch’; System altered. SYSoradb 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 SYSoradb 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管理用户
SYSoradb alter session set containerORCLPDB1;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.sql2.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设置 [rootogg21all /]# su - oracle [oracleogg21all ~]$ ggsci GGSCI (ogg21all) 2 edit params mgr PORT 7809 add credentialstore alter credentialstore add user ogg172.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: ogg172.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 rptdb172.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 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 rptdbmysql 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 oggoradb) 21 info ext0Extract EXT0 Last Started 2023-11-25 18:58 Status STOPPED
Checkpoint Lag Not Available
Log Read Checkpoint Table RPTUSER.CUSTOMERS2023-11-25 18:58:46 Record 100
Task SOURCEISTABLE8.增量时实同步
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 oggoradb) 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 rptdb172.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端测试产生数据
SYSoradb DELETE FROM RPTUSER.ADDRESSES WHERE ADDRESS_ID150;GGSCI (ogg21all as oggoradb) 21 stats exto,totalSending 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.00Total updates 0.00Total deletes 1.00Total upserts 0.00Total discards 0.00Total operations 1.00End of statistics.2.mysql端插入数据
mysql -uroot -proot -h 172.18.12.91 -D rptdb
mysql select count(*) from ADDRESSES;GGSCI (ogg21all) 18 stats repm,totalSending 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.00Total updates 0.00Total deletes 1.00Total upserts 0.00Total discards 0.00Total operations 1.00End of statistics.mysql SELECT * FROM ADDRESSES WHERE ADDRESS_ID150;
Empty set (0.00 sec)mysql select database();
------------
| database() |
------------
| rptdb |
------------
1 row in set (0.00 sec)
