Oracle數(shù)據(jù)遷移MySQL的三種簡單方法

更新時間：2023年06月05日 10:58:16 作者：牛牛的筆記

對于許多企業(yè)而言,遷移數(shù)據(jù)庫時最大的挑戰(zhàn)之一是如何從一個數(shù)據(jù)庫平臺順利遷移到另一個平臺,下面這篇文章主要給大家介紹了關于Oracle數(shù)據(jù)遷移MySQL的三種簡單方法,需要的朋友可以參考下

前言:

現(xiàn)今，Oracle數(shù)據(jù)遷移MySQL的需求已經(jīng)越來越普遍，主要的遷移場景大致可以分為三類，第一類是涉及小表以及少量表的一次性遷移，無需進行增量同步，第二類是涉及大表以及多表的一次性遷移，第三類是涉及增量實時同步，而對于數(shù)據(jù)的遷移方法，常見的方式有使用第三方的同步工具CDC進行Oracle到MySQL的數(shù)據(jù)遷移、使用開源的同步工具以及應用層面進行遷移同步。

基于作者的遷移實施經(jīng)驗，本文接下來將講述三種操作相對簡單，可落地的Oracle數(shù)據(jù)遷移MySQL的方法。

測試遷移方法:

遷移方法	適合場景	測試場景
navicat	操作簡單，同步效率一般，適合小表的一次性遷移	Oracle一次性同步表test.test1到MySQL下的db1.test1
navicat+sqluldr+load data infile	操作較復雜，同步效率高，適合少量大表的一次性遷移	Oracle一次性同步表test.test1到MySQL下的db1.test1
navicat+Oracle GoldenGate(OGG)	操作復雜，同步效率較高，適合需要大批量的大表進行遷移以及需要實時增量同步	Oracle全量+實時增量同步表test.test1,test2到MySQL下的db1.test1,test2

遷移方式一(navicat)

這種遷移方式主要適合小表的一次性遷移，navicat的同步效率速度一般。

遷移開始之前，我們需要先安裝navicat，在一臺能訪問Oracle源端和MySQL目標端的機器上安裝即可。

安裝完navicat之后，配置源端Oracle連接

配置目標端MySQL連接

開始進行遷移，選擇工具--->數(shù)據(jù)傳輸

選擇源端Oracle以及目標端MySQL

下一步選擇要同步的表TEST，也可以選擇同步全部表

傳輸模式選擇自動，點下一步開始進行同步

傳輸同步完成，整個同步的效率還是較慢的，7.2W的數(shù)據(jù)，用了1分鐘。

遷移方式二(navicat+sqluldr+load data infile)

這種遷移方式主要適合少量大表的一次性遷移，通過navicat工具進行Oracle-->MySQL表結構轉化,再通過sqluldr將Oracle數(shù)據(jù)導出到本地文件，最后再通過load data infile將數(shù)據(jù)導入MySQL。

注:navicat工具雖然有同步數(shù)據(jù)的功能，但在實際的操作過程中，同步數(shù)據(jù)的效率以及成功率都很低，所以這里只作為數(shù)據(jù)字典轉化的工具。

使用navicat工具進行表結構同步，步驟可以參考遷移方式一里面的操作，主要在數(shù)據(jù)傳輸同步時，選項里面只同步表結構，不創(chuàng)建記錄。

點擊開始，完成表結構同步

接下來進行數(shù)據(jù)的導出導入，先安裝導出工具sqluldr

---解壓安裝包
unzip sqluldr2linux64.zip 
 ./sqluldr2linux64.bin --help
---拷貝sqluldr2linux64.bin到$ORACLE_HOME的bin目錄
cp -rp sqluldr2linux64.bin $ORACLE_HOME/bin
---重命名為sqluldr2.bin
mv sqluldr2linux64.bin sqluldr2.bin

測試安裝成功

[oracle@rac19a ~]$ sqluldr2.bin --help
?
SQL*UnLoader: Fast Oracle Text Unloader (GZIP, Parallel), Release 4.0.1
(@) Copyright Lou Fangxin (AnySQL.net) 2004 - 2010, all rights reserved.
?
License: Free for non-commercial useage, else 100 USD per server.
?
Usage: SQLULDR2 keyword=value [,keyword=value,...]
?
Valid Keywords:
   user    = username/password@tnsname
   sql     = SQL file name
   query   = select statement
   field   = separator string between fields
   record  = separator string between records
   rows    = print progress for every given rows (default, 1000000) 
   file    = output file name(default: uldrdata.txt)
   log     = log file name, prefix with + to append mode
   fast    = auto tuning the session level parameters(YES)
   text    = output type (MYSQL, CSV, MYSQLINS, ORACLEINS, FORM, SEARCH).
   charset = character set name of the target database.
   ncharset= national character set name of the target database.
   parfile = read command option from parameter file 
?
  for field and record, you can use '0x' to specify hex character code,
  \r=0x0d \n=0x0a |=0x7c ,=0x2c, \t=0x09, :=0x3a, #=0x23, "=0x22 '=0x27

使用sqluldr導出為文本類型為MYSQL

sqluldr2.bin user=test/oracle@pdb1  query="select * from test"   text=MYSQL  field=',' charset=AL32UTF8 head='NO' file=/home/oracle/test_001.csv log=test.log

導出過程很快，58w的數(shù)據(jù)，只需要7秒

 0 rows exported at 2022-10-12 22:18:14, size 0 MB.
      583680 rows exported at 2022-10-12 22:18:21, size 108 MB.
         output file /home/oracle/test_001.csv closed at 583680 rows, size 108 MB.

再將從Oracle導出的MYSQL文件導入MySQL數(shù)據(jù)庫

LOAD DATA INFILE '/tmp/test_001.csv' INTO TABLE test FIELDS TERMINATED BY ',' ENCLOSED BY '"';

導入過程很快，58w的數(shù)據(jù)，只需要13秒

test@mysql.sock 22:38:  [db1]>LOAD DATA INFILE '/tmp/test_001.csv' INTO TABLE test FIELDS TERMINATED BY ',' ENCLOSED BY '"';
Query OK, 583680 rows affected (13.43 sec)
Records: 583680  Deleted: 0  Skipped: 0  Warnings: 0

整個數(shù)據(jù)同步過程還是較快的，但操作步驟較為繁瑣，不太適合多表操作。

遷移方式三(navicat+Oracle GoldenGate(OGG))

這種遷移方式適合大批量的大表或者需要增量同步的表進行遷移，支持全量初始化+Oracle GoldenGate(OGG)增量同步，通過navicat工具進行Oracle-->MySQL表結構轉化，再通過數(shù)據(jù)同步工具OGG進行全量表初始化以及后續(xù)的增量同步。

注意:使用增量方式同步的表都需要有主鍵，確保每行數(shù)據(jù)的唯一。

先使用navicat進行表結構的轉化，具體參考遷移方式二里面的步驟。

Oracle源端配置OGG準備

1 數(shù)據(jù)庫開啟歸檔模式
---查看是否開啟歸檔模式
archive log list
---開啟歸檔模式
startup mount
alter database archvielog ;
alter database open;
?
2 數(shù)據(jù)庫開啟force_logging
---查看是否開啟force logging
select force_logging from v$database;
----開啟force logging
alter database force logging;
alter system switch logfile;
?
3 數(shù)據(jù)庫開啟補充日志supplemental logging
---查看補充日志
SELECT supplemental_log_data FROM v$database; 
---開啟補充日志
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA; 
Alter system switch logfile;
4 開啟ogg參數(shù)
alter system set enable_goldengate_replication=true scope=both;
5 配置stream_pool大小
(MAX_SGA_SIZE * # of integrated Extracts) + 25% head room 
For example, using the default values for the MAX_SGA_SIZE with two integrated Extracts: 
( 1GB * 2 ) * 1.25  = 2.50GB STREAMS_POOL_SIZE = 2560M

MySQL目標端配置OGG準備

1 開啟bin_log
---確認是否開啟bin_log
show variables like 'log_bin';
2 開啟bin_log(需要重啟生效)
在my,cnf 中 [mysqld]  添加如下
[mysqld]
# binlog configuration
log-bin = /usr/local/var/mysql/logs/mysql-bin.log
expire-logs-days = 14
max-binlog-size = 500M
server-id = 1
?
2 確認binlog_format
----確認格式為row
show variables like 'binlog_format';
?
3 確認sql_mode  
----確認包含STRICT_TRANS_TABLES
show variables like 'sql_mode';
?
4 確認版本
----確認版本，5.7.10之后才支持部分DDL
（CREATE TABLE, ALTER TABLE, and DROP TABLE operations are supported.）
select version();

創(chuàng)建ogg同步用戶

1 oracle源端同步用戶創(chuàng)建

create tablespace ogg_tbs datafile size 1g;
create user ogg identified by "oggoracle";
grant resource,dba,connect to ogg;

2 mysql目標端同步用戶創(chuàng)建

CREATE USER ogg IDENTIFIED by "oggmysql";
GRANT ALL PRIVILEGES ON *.* TO 'ogg'@'%';

Oracle源端安裝ogg軟件

1 配置環(huán)境變量

---/home/oracle/.bash_profile
export OGG_HOME=/u01/app/ogg
export PATH=$OGG_HOME:$PATH

2 解壓安裝ogg軟件

---解壓安裝ogg軟件，安裝包:p31766135_191004_Linux-x86-64.zip
cd /tmp/ 
unzip p31766135_191004_Linux-x86-64.zip 
cd 31766135/
mv files/* /u01/app/ogg/
---驗證
oracle@rac19b ~]$ ggsci 
?
Oracle GoldenGate Command Interpreter for Oracle
Version 19.1.0.0.4 31637694_FBO
Linux, x64, 64bit (optimized), Oracle 19c on Aug 19 2020 20:08:53
Operating system character set identified as UTF-8.
?
Copyright (C) 1995, 2019, Oracle and/or its affiliates. All rights reserved.
?
GGSCI (rac19b) 1>

3 創(chuàng)建ogg配置目錄

GGSCI (rac19b) 10> create subdirs
?
Creating subdirectories under current directory /home/oracle
?
Parameter file                 /u01/app/ogg/dirprm: created.
Report file                    /u01/app/ogg/dirrpt: created.
Checkpoint file                /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.
Credential store files         /u01/app/ogg/dircrd: created.
Masterkey wallet files         /u01/app/ogg/dirwlt: created.
Dump files                     /u01/app/ogg/dirdmp: created.
?
GGSCI (rac19b) 11>

4 啟動MGR進程

---編輯mgr配置
cd /u01/app/ogg/
./ggsci
GGSCI (rac19b) 1> edit params mgr
---配置以下參數(shù)
PORT 7809 
autorestart extract * ,waitminutes 2,resetminutes 5
PURGEOLDEXTRACTS  /u01/app/ogg/dirdat/*,USECHECKPOINTS,MINKEEPDAYS 5
LAGREPORTHOURS 1
LAGINFOMINUTES 30
LAGCRITICALMINUTES 45 
---啟動mgr進程
GGSCI (rac19b) 1> start mgr
Manager started.
?
GGSCI (rac19b) 2> info all
?
Program     Status      Group       Lag at Chkpt  Time Since Chkpt
?
MANAGER     RUNNING
?

MySQL目標端安裝ogg軟件

1 配置環(huán)境變量

---/etc/profile
export OGG_HOME=/opt/ogg
export PATH=$OGG_HOME:$PATH

2 解壓安裝ogg軟件

---解壓安裝ogg軟件，安裝包:ggs_Linux_x64_MySQL_64bit.tar 
cd ogg/
tar xvf /tmp/ggs_Linux_x64_MySQL_64bit.tar 
---驗證
[mysql@rac19a ~]$ ggsci 
?
Oracle GoldenGate Command Interpreter for MySQL
Version 19.1.0.0.3 OGGCORE_19.1.0.0.0_PLATFORMS_190907.0144
Linux, x64, 64bit (optimized), MySQL Enterprise on Sep  7 2019 08:41:32
Operating system character set identified as UTF-8.
?
Copyright (C) 1995, 2019, Oracle and/or its affiliates. All rights reserved.

3 創(chuàng)建ogg配置目錄

GGSCI (rac19a) 1> create subdirs
?
Creating subdirectories under current directory /home/mysql
?
Parameter file                 /opt/ogg/dirprm: created.
Report file                    /opt/ogg/dirrpt: created.
Checkpoint file                /opt/ogg/dirchk: created.
Process status files           /opt/ogg/dirpcs: created.
SQL script files               /opt/ogg/dirsql: created.
Database definitions files     /opt/ogg/dirdef: created.
Extract data files             /opt/ogg/dirdat: created.
Temporary files                /opt/ogg/dirtmp: created.
Credential store files         /opt/ogg/dircrd: created.
Masterkey wallet files         /opt/ogg/dirwlt: created.
Dump files                     /opt/ogg/dirdmp: created.

4 啟動MGR進程

---編輯mgr配置
cd /opt/ogg/
./ggsci
GGSCI (rac19b) 1> edit params mgr
---配置以下參數(shù)
PORT 7809 
AUTOSTART REPLICAT *
AUTORESTART REPLICAT *,RETRIES 5,WAITMINUTES 2,RESETMINUTES 10
PURGEOLDEXTRACTS  /opt/ogg/dirdat/*,USECHECKPOINTS,MINKEEPDAYS 5
ACCESSRULE, PROG *, IPADDR 192.168.2.*, ALLOW
LAGREPORTHOURS 1
LAGINFOMINUTES 30
LAGCRITICALMINUTES 45
---啟動mgr進程
GGSCI (rac19a) 2> start mgr
Manager started.
?
?
GGSCI (rac19a) 3> info all
?
Program     Status      Group       Lag at Chkpt  Time Since Chkpt
?
MANAGER     RUNNING

5 配置全局文件以及檢查表

---ogg連接MySQL
GGSCI (rac19a DBLOGIN as ogg) 8> dblogin sourcedb db1@192.168.2.201:3306,userid ogg,password oggmysql
Successfully logged into database.
---創(chuàng)建檢查表
GGSCI (rac19a DBLOGIN as ogg) 9> ADD CHECKPOINTTABLE db1.checkpoint
?
Successfully created checkpoint table db1.checkpoint.
?
GGSCI (rac19a DBLOGIN as ogg) 10> 
---配置文件設置全局檢查表
GGSCI (rac19a DBLOGIN as ogg) 10> edit params ./GLOBALS 
---添加以下配置
CHECKPOINTTABLE db1.checkpoint

Oracle源端配置抽取以及投遞進程(增量進程)

1 對同步表添加補充日志

---ogg連接Oracle
GGSCI (rac19b) 3> dblogin userid ogg password oggoracle
Successfully logged into database.
---為表test.test1添加同步日志
GGSCI (rac19b as ogg@testdb) 4> add trandata test.test1
?
2022-10-13 13:08:58 INFO OGG-15132 Logging of supplemental redo data enabled for table TEST.TEST1.
?
2022-10-13 13:08:58 INFO OGG-15133 TRANDATA for scheduling columns has been added on table TEST.TEST1.
?
2022-10-13 13:08:58 INFO OGG-15135 TRANDATA for instantiation CSN has been added on table TEST.TEST1.
?
2022-10-13 13:08:59 INFO OGG-10471 ***** Oracle Goldengate support information on table TEST.TEST1 *****
Oracle Goldengate support native capture on table TEST.TEST1.
Oracle Goldengate marked following column as key columns on table TEST.TEST1: ID.
---為表test.test2添加同步日志
GGSCI (rac19b as ogg@testdb) 5> add trandata test.test2
?
2022-10-13 13:09:04 INFO OGG-15132 Logging of supplemental redo data enabled for table TEST.TEST2.
?
2022-10-13 13:09:04 INFO OGG-15133 TRANDATA for scheduling columns has been added on table TEST.TEST2.
?
2022-10-13 13:09:04 INFO OGG-15135 TRANDATA for instantiation CSN has been added on table TEST.TEST2.
?
2022-10-13 13:09:04 INFO OGG-10471 ***** Oracle Goldengate support information on table TEST.TEST2 *****
Oracle Goldengate support native capture on table TEST.TEST2.
Oracle Goldengate marked following column as key columns on table TEST.TEST2: ID.
?
GGSCI (rac19b as ogg@testdb) 6>

2 創(chuàng)建EXTRACT抽取進程

GGSCI (rac19b) 2> edit params e_test
?
extract E_TEST
SETENV (ORACLE_HOME = "/u01/app/oracle/product/19.0.0/dbhome_1")
SETENV (ORACLE_SID = "testdb")
SETENV (NLS_LANG = "AMERICAN_AMERICA.AL32UTF8")
userid ogg, password oggoracle
exttrail ./dirdat/es
?
gettruncates 
TRANLOGOPTIONS INCLUDEREGIONID,EXCLUDEUSER ogg
TRANLOGOPTIONS BUFSIZE 2048000
TRANLOGOPTIONS DBLOGREADER,DBLOGREADERBUFSIZE 2048000
?
DISCARDFILE ./dirrpt/E_TEST.dsc,APPEND,MEGABYTES 1000
DISCARDROLLOVER AT 6:00
REPORTROLLOVER AT 6:00
REPORTCOUNT EVERY 1 HOURS,RATE
?
?
FETCHOPTIONS MISSINGROW ABEND
STATOPTIONS REPORTFETCH
?
WARNLONGTRANS 1H,CHECKINTERVAL 10m                                                
?
DYNAMICRESOLUTION
TABLE  TEST.TEST1;
TABLE  TEST.TEST2;

3 設置EXTRACT抽取進程參數(shù)

GGSCI (rac19b) 2> edit params e_test
?
extract E_TEST
SETENV (ORACLE_HOME = "/u01/app/oracle/product/19.0.0/dbhome_1")
SETENV (ORACLE_SID = "testdb")
SETENV (NLS_LANG = "AMERICAN_AMERICA.AL32UTF8")
userid ogg, password oggoracle
exttrail ./dirdat/es
?
gettruncates 
TRANLOGOPTIONS INCLUDEREGIONID,EXCLUDEUSER ogg
TRANLOGOPTIONS BUFSIZE 2048000
TRANLOGOPTIONS DBLOGREADER,DBLOGREADERBUFSIZE 2048000
?
DISCARDFILE ./dirrpt/E_TEST.dsc,APPEND,MEGABYTES 1000
DISCARDROLLOVER AT 6:00
REPORTROLLOVER AT 6:00
REPORTCOUNT EVERY 1 HOURS,RATE?
?
FETCHOPTIONS MISSINGROW ABEND
STATOPTIONS REPORTFETCH
?
WARNLONGTRANS 1H,CHECKINTERVAL 10m                                                
?
DYNAMICRESOLUTION
TABLE  TEST.TEST1;
TABLE  TEST.TEST2;

4 創(chuàng)建EXTRACT投遞進程

GGSCI (rac19b) 3> add extract P_TEST,exttrailsource ./dirdat/es
EXTRACT added.
?
GGSCI (rac19b) 4> add RMTTRAIL ./dirdat/rs,ext P_TEST,megabytes 1000
RMTTRAIL added.
?
GGSCI (rac19b) 5>
?

5 設置EXTRACT投遞進程參數(shù)

extract P_TEST
userid ogg, password oggoracle
rmthost 192.168.2.201, mgrport 7809
rmttrail /opt/ogg/dirdat/rs
passthru
?
DISCARDFILE ./dirrpt/P_TEST.dsc,APPEND,MEGABYTES 1000
DISCARDROLLOVER AT 6:00
?
REPORTROLLOVER AT 6:00
REPORTCOUNT EVERY 1 HOURS,RATE
?
TABLE  TEST.TEST1;
TABLE  TEST.TEST2;

6 啟動源端抽取以及投遞進程

---啟動抽取以及投遞進程
GGSCI (rac19b) 8> start *test
?
Sending START request to MANAGER ...
EXTRACT E_TEST starting
?
Sending START request to MANAGER ...
EXTRACT P_TEST starting
?
---確認狀態(tài)正常running
GGSCI (rac19b) 14> info all
?
Program     Status      Group       Lag at Chkpt  Time Since Chkpt
?
MANAGER     RUNNING                                           
EXTRACT     RUNNING     E_TEST      00:00:02      00:00:07    
EXTRACT     RUNNING     P_TEST      00:00:00      00:00:03    
?
GGSCI (rac19b) 15> 
---確認目標端能接收到隊列文件
?
[mysql@rac19a dirdat]$ ls -rlth
total 20K
-rw-r----- 1 mysql mysql 19K Oct 13 13:24 rs000000000
[mysql@rac19a dirdat]$

MySQL目標端配置復制進程(增量進程)

1 添加復制進程

GGSCI (rac19a DBLOGIN as ogg) 11> add replicat r_test,exttrail /opt/ogg/dirdat/rs,checkpointtable db1.checkpoint 
REPLICAT added.
?
GGSCI (rac19a DBLOGIN as ogg) 12> info all
?
Program     Status      Group       Lag at Chkpt  Time Since Chkpt
?
MANAGER     RUNNING                                           
REPLICAT    STOPPED     R_TEST      00:00:00      00:00:04

2 配置復制進程參數(shù)

GGSCI (rac19a DBLOGIN as ogg) 13> edit params r_test
?
replicat r_test
setenv (MYSQL_HOME="/usr/local/mysql")
setenv (MYSQL_UNIX_PORT="/opt/mysql/data/mysql.sock")
dboptions host 192.168.2.201,connectionport 3306
targetdb db1,userid ogg, password oggmysql
?
discardfile /opt/ogg/dirrpt/r_test.dsc,append,megabytes 1000
DISCARDROLLOVER AT 6:00
?
REPERROR (DEFAULT, ABEND)
?
MAXTRANSOPS 5000
       
HANDLECOLLISIONS
REPORTROLLOVER AT 6:00
REPORTCOUNT EVERY 1 HOURS, RATE
 MAP test.test1,  TARGET  db1.test1;                                                   
 MAP test.test2,  TARGET  db1.test2;

3 HANDLECOLLISIONS參數(shù)說明

參數(shù)是實現(xiàn) OGG 全量數(shù)據(jù)與增量數(shù)據(jù)銜接的關鍵，其實現(xiàn)原理是在全量數(shù)據(jù)初始完成之后，開啟增量抽取進程，應用全量數(shù)據(jù)初始化期間產(chǎn)生的 redo log。

當全量應用完成后，開啟增量回放進程，應用全量期間的增量數(shù)據(jù)?？赡軙霈F(xiàn)數(shù)據(jù)沖突的情況，這就是為什么表一定要有主鍵或者唯一鍵，使用該參數(shù)后增量回放 DML 語句時主要有以下沖突場景及處理邏輯：

1 目標端不存在 delete 語句的記錄，忽略該問題并不記錄到 discardfile。

2 目標端丟失 update 記錄，更新的是主鍵值，update 轉換成 insert，更新的鍵值是非主鍵，忽略該問題并不記錄到 discardfile。

3 目標端重復 insert 已存在的主鍵值，這將被 replicat 進程轉換為 UPDATE 現(xiàn)有主鍵值的。

4 在初始化數(shù)據(jù)，并追完增量數(shù)據(jù)之后，建議把HANDLECOLLISIONS參數(shù)去掉，Oracle官方建議不要一直使用該參數(shù),這可能導致數(shù)據(jù)不準。

Oracle源端配置數(shù)據(jù)初始化進程(數(shù)據(jù)全量初始化進程)

1 添加初始化進程

GGSCI (rac19b) 17> add extract e_init,sourceistable
EXTRACT added.

2 配置初始化進程

GGSCI (rac19b) 20> edit params e_init
?
extract e_init
SETENV (ORACLE_HOME = "/u01/app/oracle/product/19.0.0/dbhome_1")
SETENV (ORACLE_SID = "testdb")
SETENV (NLS_LANG = "AMERICAN_AMERICA.AL32UTF8")
userid ogg, password oggoracle
RMTHOST 192.168.2.201,MGRPORT 7809
RMTTASK REPLICAT,GROUP r_init
?
table  test.test1;
table  test.test2;

MySQL目標端配置數(shù)據(jù)初始化進程(數(shù)據(jù)全量初始化進程)

1 添加初始化進程

GGSCI (rac19b) 17> add extract e_init,sourceistable
EXTRACT added.

2 配置初始化進程

GGSCI (rac19b) 20> edit params e_init
?
extract e_init
SETENV (ORACLE_HOME = "/u01/app/oracle/product/19.0.0/dbhome_1")
SETENV (ORACLE_SID = "testdb")
SETENV (NLS_LANG = "AMERICAN_AMERICA.AL32UTF8")
userid ogg, password oggoracle
RMTHOST 192.168.2.201,MGRPORT 7809
RMTTASK REPLICAT,GROUP r_init
?
table  test.test1;
table  test.test2;

全量數(shù)據(jù)初始化

數(shù)據(jù)初始化會將全表的數(shù)據(jù)通過創(chuàng)建的ogg進程e_init,r_init從Oracle源端同步到MySQL目標端。

1 啟動Oracle源端的e_init初始化進程

GGSCI (rac19a DBLOGIN as ogg) 17> add replicat r_init,specialrun 
REPLICAT added.

2 目標端查看同步的進度

GGSCI (rac19a DBLOGIN as ogg) 18> edit params r_init
?
replicat r_init
setenv (MYSQL_HOME="/usr/local/mysql")
setenv (MYSQL_UNIX_PORT="/opt/mysql/data/mysql.sock")
dboptions host 192.168.2.201,connectionport 3306
targetdb db1,userid ogg, password oggmysql
discardfile /opt/ogg/dirrpt/r_init.dsc,append,megabytes 1000
MAXTRANSOPS 5000
 MAP test.test1,  TARGET  db1.test1;                                                   
 MAP test.test2,  TARGET  db1.test2;

3 同步完成，會輸出總的數(shù)量

Report at 2022-10-13 14:35:19 (activity since 2022-10-13 14:30:57)
?
Output to r_init:
?
From Table TEST.TEST1:
       #                   inserts:    999901
       #                   updates:         0
       #                   deletes:         0
       #                   upserts:         0
       #                  discards:         0
From Table TEST.TEST2:
       #                   inserts:   1000000
       #                   updates:         0
       #                   deletes:         0
       #                   upserts:         0
       #                  discards:         0?
?
REDO Log Statistics
  Bytes parsed                    0
  Bytes output            285986537

增量數(shù)據(jù)同步

1 啟動目標端復制進程r_test

GGSCI (rac19a DBLOGIN as ogg) 65> start r_test
?
Sending START request to MANAGER ...
REPLICAT R_TEST starting?
?
GGSCI (rac19a DBLOGIN as ogg) 66> info all
?
Program     Status      Group       Lag at Chkpt  Time Since Chkpt
?
MANAGER     RUNNING                                           
REPLICAT    RUNNING     R_TEST      00:00:00      00:00:01

2 查看增量同步信息

GGSCI (rac19a DBLOGIN as ogg) 67> stats r_test
?
Sending STATS request to REPLICAT R_TEST ...
?
Start of Statistics at 2022-10-13 14:45:24.
?
Replicating from TEST.TEST1 to db1.test1:
?
---collisions解決沖突數(shù)據(jù)的行數(shù)
*** Total statistics since 2022-10-13 14:45:17 ***
  Total inserts                                0.00
  Total updates                                0.00
  Total deletes                               99.00
  Total upserts                                0.00
  Total discards                               0.00
  Total operations                            99.00
  Total delete collisions                     99.00
?
?
Replicating from TEST.TEST2 to db1.test2:
---增量update了10行
*** Total statistics since 2022-10-13 14:45:17 ***
  Total inserts                                0.00
  Total updates                               10.00
  Total deletes                                0.00
  Total upserts                                0.00
  Total discards                               0.00
  Total operations                            10.00?
?
End of Statistics.
?

3 注釋去除HANDLECOLLISIONS參數(shù)

注:要在增量同步進程應用完初始化期間產(chǎn)生的日志以及實時同步之后，再去除參數(shù)。

#編輯配置文件，注釋---HANDLECOLLISIONS
GGSCI (rac19a DBLOGIN as ogg) 71> edit params r_test
?
replicat r_test
setenv (MYSQL_HOME="/usr/local/mysql")
setenv (MYSQL_UNIX_PORT="/opt/mysql/data/mysql.sock")
dboptions host 192.168.2.201,connectionport 3306
targetdb db1,userid ogg, password oggmysql
?
discardfile /opt/ogg/dirrpt/r_test.dsc,append,megabytes 1000
DISCARDROLLOVER AT 6:00
?
REPERROR (DEFAULT, ABEND)
?
MAXTRANSOPS 5000?
?
---HANDLECOLLISIONS
REPORTROLLOVER AT 6:00
REPORTCOUNT EVERY 1 HOURS, RATE
 MAP test.test1,  TARGET  db1.test1;
 MAP test.test2,  TARGET  db1.test2;
?
#重啟進程生效
GGSCI (rac19a DBLOGIN as ogg) 73> stop r_test
?
Sending STOP request to REPLICAT R_TEST ...
Request processed.
?
?
GGSCI (rac19a DBLOGIN as ogg) 74> start r_test
?
Sending START request to MANAGER ...
REPLICAT R_TEST starting?
?
GGSCI (rac19a DBLOGIN as ogg) 75>

4 測試數(shù)據(jù)同步情況

Oracle源端刪除999行數(shù)據(jù)，當前數(shù)據(jù)99001

SQL> select count(*) from test.test2;
?
  COUNT(*)
----------
   1000000
?
SQL> delete from test.test2 where rownum<1000;
?
999 rows deleted.
?
SQL> commit;
?
Commit complete.
?
SQL> select count(*) from test.test2;
?
  COUNT(*)
----------
    999001
?
SQL>

MySQL目標端同步刪除的操作，數(shù)據(jù) 一致都為999001

root@mysql.sock 14:33:  [db1]>select count(*) from db1.test2;
+----------+
| count(*) |
+----------+
|   999001 |
+----------+
1 row in set (0.17 sec)
?
root@mysql.sock 14:54:  [db1]>
?

使用navicat+Oracle GoldenGate(OGG)的方式，操作步驟比較復雜，但如果需要遷移的表多，并且需要實時的增量同步，那么還是比較適合的。

總結

到此這篇關于Oracle數(shù)據(jù)遷移MySQL的三種簡單方法的文章就介紹到這了,更多相關Oracle數(shù)據(jù)遷移MySQL內容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！

您可能感興趣的文章:

window10 安裝Oracle19C 和SQL Developer 的圖文教程
這篇文章主要介紹了window10 安裝Oracle19C 和SQL Developer 教程（圖文詳解）,本文給大家介紹的非常詳細，對大家的學習或工作具有一定的參考借鑒價值，需要的朋友可以參考下
2021-03-03
Oracle查詢表空間大小及每個表所占空間的大小語句示例
Oracle表空間大小的查看方法應該是我們都需要掌握的知識,下面這篇文章主要給大家介紹了關于Oracle查詢表空間大小及每個表所占空間的大小語句的相關資料,文中通過示例代碼介紹的非常詳細,需要的朋友可以參考下
2022-12-12
zabbix監(jiān)控oracle表空間的操作方法
Zabbix是一款開源的網(wǎng)絡監(jiān)控和管理系統(tǒng),可以用于監(jiān)控各種網(wǎng)絡設備、服務器和應用程序等,本文給大家介紹了zabbix監(jiān)控oracle表空間的操作方法,并通過代碼示例和圖文講解的非常詳細,需要的朋友可以參考下
2024-04-04
oracle 合并查詢事務 sql函數(shù)小知識學習
oracle 合并查詢事務 sql函數(shù)小知識學習，需要的朋友可以參考下
2012-12-12
Oracle查詢表占用空間的三種方法
在Oracle數(shù)據(jù)庫管理中,了解特定表或索引所占用的空間對于性能調優(yōu)、存儲規(guī)劃以及資源分配至關重要,本文檔介紹了三種常用的方法來查詢Oracle數(shù)據(jù)庫中表占用的空間,感興趣的小伙伴跟著小編一起來看看吧
2024-12-12
MySQL實現(xiàn)按分秒統(tǒng)計數(shù)據(jù)量方式
在MySQL中,通過使用GROUP BY結合時間處理函數(shù),可以有效統(tǒng)計每秒、每分鐘、每5分鐘、每10分鐘和每30分鐘的交易量,通過對transaction_time字段進行格式化和分段,可以靈活調整統(tǒng)計的時間間隔,此方法適用于需要精確時間段統(tǒng)計的場景,如性能監(jiān)控、數(shù)據(jù)分析等
2024-10-10
oracle11g用戶登錄時被鎖定問題的解決方法（ora-28000 the account is locked）
最近在操作oracle11g的使用出現(xiàn)錯誤的現(xiàn)象：ora-28000 the account is locked,既用戶無法登錄問題，如何解決此問題呢？下面小編給大家?guī)砹薿racle11g用戶登錄時被鎖定問題的解決方法，感興趣的朋友一起看看吧
2017-07-07
Oracle外鍵不加索引引起死鎖示例
這篇文章主要介紹了Oracle外鍵不加索引引起死鎖的情況及解決,需要的朋友可以參考下
2014-05-05
Weblogic控制臺修改用戶密碼的教程分享
這篇文章主要為大家詳細介紹了Weblogic控制臺修改用戶密碼的方法,文中通過圖片進行了詳細的講解,感興趣的小伙伴可以跟隨小編一起學習一下
2023-08-08
Oracle中在pl/sql developer修改表的2種方法
Oracle中在pl/sql developer修改表的2種方法，需要的朋友可以參考一下
2013-03-03