基于OGG實(shí)現(xiàn)Oracle實(shí)時(shí)同步MySQL的全過(guò)程
更新時(shí)間：2023年11月27日 09:24:44 作者：IT邦德
這篇文章詳細(xì)闡述了基于OGG實(shí)現(xiàn)Oracle實(shí)時(shí)同步MySQL全過(guò)程,文中通過(guò)圖文結(jié)合和代碼示例給大家講解的非常詳細(xì),對(duì)大家的學(xué)習(xí)或工作有一定的幫助,需要的朋友可以參考下
1.架構(gòu)設(shè)計(jì)
2.OGG安裝部署
2.1 OGG for Oracle
1.OGG下載地址：
https://www.oracle.com/middleware/technologies/goldengate-downloads.html創(chuàng)建OGG使用目錄
chown -R oracle:oinstall /oraogg chmod 775 -R /oraogg
2.環(huán)境變量如下
vi .bash_profile export 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 ggsci='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個(gè)地方即可。 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.創(chuàng)建oggm用戶運(yùn)行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.環(huán)境變量設(shè)置
vi .bash_profile export GG_HOME=/oggmysql export PATH=$PATH:$HOME/bin:$GG_HOME alias ggsci='cd $GG_HOME;ggsci' source .bash_profile
3.Oracle相關(guān)配置
3.1 參數(shù)調(diào)整
– oracle數(shù)據(jù)庫(kù)配置
1.開(kāi)啟數(shù)據(jù)庫(kù)歸檔–如果沒(méi)有開(kāi)啟
2.開(kāi)啟數(shù)據(jù)庫(kù)級(jí)別附加日志–如果沒(méi)有開(kāi)始最小附加日志
3.開(kāi)啟強(qiáng)制日志–如果沒(méi)有開(kāi)啟強(qiáng)制日志
4.設(shè)置ENABLE_GOLDENGATE_REPLICAT參數(shù)為T(mén)RUE
5.創(chuàng)建OGG用戶包括包括源端用戶、目標(biāo)端用戶以及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
##關(guān)閉回收站 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);
-- 業(yè)務(wù)用戶
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數(shù)據(jù)初始化
1.生成MySQL端DDL語(yǔ)句 可以使用Navicat的數(shù)據(jù)傳輸功能或其它工具直接從Oracle端生成MySQL類型的建表語(yǔ)句如下： mysql -uroot -proot create database rptdb; mysql -uroot -proot -h 172.18.12.91 -D rptdb -f < ddl.sql 2.DDL語(yǔ)句如下 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設(shè)置
[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設(shè)置
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.全量同步數(shù)據(jù)
Oracle全量同步到MySQL
注意：在此階段，源端需要停業(yè)務(wù)，不能產(chǎn)生新數(shù)據(jù)。
-- 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 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 replicat rep0 ,specialrun
delete replicat rep0
-- 直接啟動(dòng)源端ext0即可，rep0不用啟動(dòng),MGR會(huì)自動(dòng)啟動(dòng)它，等同步結(jié)束，它會(huì)自動(dòng)關(guān)閉
start ext0
-- 查看日志
info rep0,showch
view report rep0
--登錄驗(yàn)證數(shù)據(jù)
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.增量時(shí)實(shí)同步
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;
啟動(dòng)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.測(cè)試同步
1.Oracle端測(cè)試產(chǎn)生數(shù)據(jù)
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端插入數(shù)據(jù)
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)
以上就是基于OGG實(shí)現(xiàn)Oracle實(shí)時(shí)同步MySQL的全過(guò)程的詳細(xì)內(nèi)容，更多關(guān)于Oracle實(shí)時(shí)同步MySQL的資料請(qǐng)關(guān)注腳本之家其它相關(guān)文章！
相關(guān)文章
Oracle數(shù)值型函數(shù)之CEIL(x)函數(shù)的使用
本文詳細(xì)介紹了Oracle數(shù)據(jù)庫(kù)中的CEIL(x)函數(shù),該函數(shù)用于向上取整,返回大于或等于x的最小整數(shù),文中通過(guò)示例代碼介紹的非常詳細(xì),需要的朋友們下面隨著小編來(lái)一起學(xué)習(xí)學(xué)習(xí)吧2025-07-07
這篇文章主要介紹了ORACLE錯(cuò)誤一覽表 方便查詢,需要的朋友可以參考下2015-12-12
oracle數(shù)據(jù)庫(kù)id自增及生成uuid問(wèn)題
這篇文章主要介紹了oracle數(shù)據(jù)庫(kù)id自增及生成uuid問(wèn)題，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。如有錯(cuò)誤或未考慮完全的地方，望不吝賜教2023-05-05
Linux中Oracle啟動(dòng)偵聽(tīng)報(bào)錯(cuò)TNS:permission denied的解決方法
這篇文章主要介紹了Linux中Oracle啟動(dòng)偵聽(tīng)時(shí)報(bào)錯(cuò)TNS:permission denied的解決方法，文中給出了詳細(xì)的解決方法，相信對(duì)大家具有一定的參考價(jià)值，需要的朋友們下面來(lái)一起看看吧。2017-02-02
PLSQL創(chuàng)建新用戶并導(dǎo)入導(dǎo)出.dmp文件全過(guò)程
plsql導(dǎo)入.sql和.dmp文件時(shí)會(huì)經(jīng)常用到,對(duì)于初學(xué)者來(lái)說(shuō)可能沒(méi)有那么簡(jiǎn)單,畢竟oracle數(shù)據(jù)庫(kù)比較麻煩,這篇文章主要給大家介紹了關(guān)于PLSQL創(chuàng)建新用戶并導(dǎo)入導(dǎo)出.dmp文件的相關(guān)資料,需要的朋友可以參考下2023-11-11
基于Oracle數(shù)據(jù)泵(expdp/impdp)的數(shù)據(jù)導(dǎo)入導(dǎo)出指南
數(shù)據(jù)泵(expdp/impdp)是Oracle 10g 之后進(jìn)行高效數(shù)據(jù)遷移的標(biāo)準(zhǔn)工具,支持多線程操作與靈活參數(shù)配置?,利用數(shù)據(jù)泵可以做到邏輯的備份,數(shù)據(jù)遷移,傳輸表空間等,本文給大家介紹了基于Oracle數(shù)據(jù)泵(expdp/impdp)的數(shù)據(jù)導(dǎo)入導(dǎo)出指南,需要的朋友可以參考下2025-10-10
Oracle使用MyBatis中RowBounds實(shí)現(xiàn)分頁(yè)查詢功能
這篇文章主要介紹了Oracle使用MyBatis中RowBounds實(shí)現(xiàn)分頁(yè)查詢 ,本文通過(guò)實(shí)例代碼給大家介紹的非常詳細(xì)，具有一定的參考借鑒價(jià)值，需要的朋友可以參考下2019-07-07
Oracle創(chuàng)建只讀賬號(hào)的詳細(xì)步驟
本文介紹了Oracle創(chuàng)建只讀賬號(hào)的詳細(xì)步驟，有此需求的朋友可以參考下過(guò)程2021-06-06
Windows系統(tǒng)下Oracle?11g完全卸載教程(推薦!)
安裝軟件是首要,雖然不是特別重要,但是錯(cuò)誤的安裝也是相當(dāng)費(fèi)勁和抓狂的,所以這篇文章主要給大家介紹了關(guān)于Windows系統(tǒng)下Oracle?11g完全卸載的相關(guān)資料,文中通過(guò)圖文介紹的非常詳細(xì),要的朋友可以參考下2024-06-06
distinct這個(gè)關(guān)鍵字來(lái)過(guò)濾掉多余的重復(fù)記錄只保留一條，但往往只用它來(lái)返回不重復(fù)記錄的條數(shù)，而不是用它來(lái)返回不重記錄的所有值。其原因是distinct只有用二重循環(huán)查詢來(lái)解決，而這樣對(duì)于一個(gè)數(shù)據(jù)量非常大的站來(lái)說(shuō)，無(wú)疑是會(huì)直接影響到效率的。2015-09-09