文章詳情頁
Oracle數據遷移MySQL的三種簡單方法
瀏覽：6日期：2023-09-18 20:52:52
目錄前言:測試遷移方法:遷移方式一(navicat)遷移方式二(navicat+sqluldr+load data infile)遷移方式三(navicat+Oracle GoldenGate(OGG))總結前言:
現今，Oracle數據遷移MySQL的需求已經越來越普遍，主要的遷移場景大致可以分為三類，第一類是涉及小表以及少量表的一次性遷移，無需進行增量同步，第二類是涉及大表以及多表的一次性遷移，第三類是涉及增量實時同步，而對于數據的遷移方法，常見的方式有使用第三方的同步工具CDC進行Oracle到MySQL的數據遷移、使用開源的同步工具以及應用層面進行遷移同步。
基于作者的遷移實施經驗，本文接下來將講述三種操作相對簡單，可落地的Oracle數據遷移MySQL的方法。
測試遷移方法:遷移方法適合場景測試場景navicat操作簡單，同步效率一般，適合小表的一次性遷移Oracle一次性同步表test.test1到MySQL下的db1.test1navicat+sqluldr+load data infile操作較復雜，同步效率高，適合少量大表的一次性遷移Oracle一次性同步表test.test1到MySQL下的db1.test1navicat+Oracle GoldenGate(OGG)操作復雜，同步效率較高，適合需要大批量的大表進行遷移以及需要實時增量同步Oracle全量+實時增量同步表test.test1,test2到MySQL下的db1.test1,test2遷移方式一(navicat)這種遷移方式主要適合小表的一次性遷移，navicat的同步效率速度一般。
遷移開始之前，我們需要先安裝navicat，在一臺能訪問Oracle源端和MySQL目標端的機器上安裝即可。
安裝完navicat之后，配置源端Oracle連接
配置目標端MySQL連接
開始進行遷移，選擇工具--->數據傳輸
選擇源端Oracle以及目標端MySQL
下一步選擇要同步的表TEST，也可以選擇同步全部表
傳輸模式選擇自動，點下一步開始進行同步
傳輸同步完成，整個同步的效率還是較慢的，7.2W的數據，用了1分鐘。
遷移方式二(navicat+sqluldr+load data infile)這種遷移方式主要適合少量大表的一次性遷移，通過navicat工具進行Oracle-->MySQL表結構轉化,再通過sqluldr將Oracle數據導出到本地文件，最后再通過load data infile將數據導入MySQL。
注:navicat工具雖然有同步數據的功能，但在實際的操作過程中，同步數據的效率以及成功率都很低，所以這里只作為數據字典轉化的工具。
使用navicat工具進行表結構同步，步驟可以參考遷移方式一里面的操作，主要在數據傳輸同步時，選項里面只同步表結構，不創建記錄。
點擊開始，完成表結構同步
接下來進行數據的導出導入，先安裝導出工具sqluldr
---解壓安裝包unzip sqluldr2linux64.zip ./sqluldr2linux64.bin --help---拷貝sqluldr2linux64.bin到$ORACLE_HOME的bin目錄cp -rp sqluldr2linux64.bin $ORACLE_HOME/bin---重命名為sqluldr2.binmv 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的數據，只需要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數據庫
LOAD DATA INFILE '/tmp/test_001.csv' INTO TABLE test FIELDS TERMINATED BY ',' ENCLOSED BY ''';導入過程很快，58w的數據，只需要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整個數據同步過程還是較快的，但操作步驟較為繁瑣，不太適合多表操作。
遷移方式三(navicat+Oracle GoldenGate(OGG))這種遷移方式適合大批量的大表或者需要增量同步的表進行遷移，支持全量初始化+Oracle GoldenGate(OGG)增量同步，通過navicat工具進行Oracle-->MySQL表結構轉化，再通過數據同步工具OGG進行全量表初始化以及后續的增量同步。
注意:使用增量方式同步的表都需要有主鍵，確保每行數據的唯一。
先使用navicat進行表結構的轉化，具體參考遷移方式二里面的步驟。
Oracle源端配置OGG準備
1 數據庫開啟歸檔模式---查看是否開啟歸檔模式archive log list---開啟歸檔模式startup mountalter database archvielog ;alter database open;?2 數據庫開啟force_logging---查看是否開啟force loggingselect force_logging from v$database;----開啟force loggingalter database force logging;alter system switch logfile;?3 數據庫開啟補充日志supplemental logging---查看補充日志SELECT supplemental_log_data FROM v$database; ---開啟補充日志ALTER DATABASE ADD SUPPLEMENTAL LOG DATA; Alter system switch logfile;4 開啟ogg參數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 = 2560MMySQL目標端配置OGG準備
1 開啟bin_log---確認是否開啟bin_logshow variables like 'log_bin';2 開啟bin_log(需要重啟生效)在my,cnf 中 [mysqld] 添加如下[mysqld]# binlog configurationlog-bin = /usr/local/var/mysql/logs/mysql-bin.logexpire-logs-days = 14max-binlog-size = 500Mserver-id = 1?2 確認binlog_format----確認格式為rowshow variables like 'binlog_format';?3 確認sql_mode ----確認包含STRICT_TRANS_TABLESshow variables like 'sql_mode';?4 確認版本----確認版本，5.7.10之后才支持部分DDL（CREATE TABLE, ALTER TABLE, and DROP TABLE operations are supported.）select version();創建ogg同步用戶
1 oracle源端同步用戶創建
create tablespace ogg_tbs datafile size 1g;create user ogg identified by 'oggoracle';grant resource,dba,connect to ogg;2 mysql目標端同步用戶創建
CREATE USER ogg IDENTIFIED by 'oggmysql';GRANT ALL PRIVILEGES ON *.* TO 'ogg'@'%';Oracle源端安裝ogg軟件
1 配置環境變量
---/home/oracle/.bash_profileexport OGG_HOME=/u01/app/oggexport PATH=$OGG_HOME:$PATH2 解壓安裝ogg軟件
---解壓安裝ogg軟件，安裝包:p31766135_191004_Linux-x86-64.zipcd /tmp/ unzip p31766135_191004_Linux-x86-64.zip cd 31766135/mv files/* /u01/app/ogg/---驗證oracle@rac19b ~]$ ggsci ?Oracle GoldenGate Command Interpreter for OracleVersion 19.1.0.0.4 31637694_FBOLinux, x64, 64bit (optimized), Oracle 19c on Aug 19 2020 20:08:53Operating system character set identified as UTF-8.?Copyright (C) 1995, 2019, Oracle and/or its affiliates. All rights reserved.?GGSCI (rac19b) 1>3 創建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/./ggsciGGSCI (rac19b) 1> edit params mgr---配置以下參數PORT 7809 autorestart extract * ,waitminutes 2,resetminutes 5PURGEOLDEXTRACTS /u01/app/ogg/dirdat/*,USECHECKPOINTS,MINKEEPDAYS 5LAGREPORTHOURS 1LAGINFOMINUTES 30LAGCRITICALMINUTES 45 ---啟動mgr進程GGSCI (rac19b) 1> start mgrManager started.?GGSCI (rac19b) 2> info all?Program Status Group Lag at Chkpt Time Since Chkpt?MANAGER RUNNING?MySQL目標端安裝ogg軟件
1 配置環境變量
---/etc/profileexport OGG_HOME=/opt/oggexport PATH=$OGG_HOME:$PATH2 解壓安裝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 MySQLVersion 19.1.0.0.3 OGGCORE_19.1.0.0.0_PLATFORMS_190907.0144Linux, x64, 64bit (optimized), MySQL Enterprise on Sep 7 2019 08:41:32Operating system character set identified as UTF-8.?Copyright (C) 1995, 2019, Oracle and/or its affiliates. All rights reserved.3 創建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/./ggsciGGSCI (rac19b) 1> edit params mgr---配置以下參數PORT 7809 AUTOSTART REPLICAT *AUTORESTART REPLICAT *,RETRIES 5,WAITMINUTES 2,RESETMINUTES 10PURGEOLDEXTRACTS /opt/ogg/dirdat/*,USECHECKPOINTS,MINKEEPDAYS 5ACCESSRULE, PROG *, IPADDR 192.168.2.*, ALLOWLAGREPORTHOURS 1LAGINFOMINUTES 30LAGCRITICALMINUTES 45---啟動mgr進程GGSCI (rac19a) 2> start mgrManager started.??GGSCI (rac19a) 3> info all?Program Status Group Lag at Chkpt Time Since Chkpt?MANAGER RUNNING5 配置全局文件以及檢查表
---ogg連接MySQLGGSCI (rac19a DBLOGIN as ogg) 8> dblogin sourcedb db1@192.168.2.201:3306,userid ogg,password oggmysqlSuccessfully logged into database.---創建檢查表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.checkpointOracle源端配置抽取以及投遞進程(增量進程)
1 對同步表添加補充日志
---ogg連接OracleGGSCI (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 創建EXTRACT抽取進程
GGSCI (rac19b) 2> edit params e_test?extract E_TESTSETENV (ORACLE_HOME = '/u01/app/oracle/product/19.0.0/dbhome_1')SETENV (ORACLE_SID = 'testdb')SETENV (NLS_LANG = 'AMERICAN_AMERICA.AL32UTF8')userid ogg, password oggoracleexttrail ./dirdat/es?gettruncates TRANLOGOPTIONS INCLUDEREGIONID,EXCLUDEUSER oggTRANLOGOPTIONS BUFSIZE 2048000TRANLOGOPTIONS DBLOGREADER,DBLOGREADERBUFSIZE 2048000?DISCARDFILE ./dirrpt/E_TEST.dsc,APPEND,MEGABYTES 1000DISCARDROLLOVER AT 6:00REPORTROLLOVER AT 6:00REPORTCOUNT EVERY 1 HOURS,RATE??FETCHOPTIONS MISSINGROW ABENDSTATOPTIONS REPORTFETCH?WARNLONGTRANS 1H,CHECKINTERVAL 10m?DYNAMICRESOLUTIONTABLE TEST.TEST1;TABLE TEST.TEST2;3 設置EXTRACT抽取進程參數
GGSCI (rac19b) 2> edit params e_test?extract E_TESTSETENV (ORACLE_HOME = '/u01/app/oracle/product/19.0.0/dbhome_1')SETENV (ORACLE_SID = 'testdb')SETENV (NLS_LANG = 'AMERICAN_AMERICA.AL32UTF8')userid ogg, password oggoracleexttrail ./dirdat/es?gettruncates TRANLOGOPTIONS INCLUDEREGIONID,EXCLUDEUSER oggTRANLOGOPTIONS BUFSIZE 2048000TRANLOGOPTIONS DBLOGREADER,DBLOGREADERBUFSIZE 2048000?DISCARDFILE ./dirrpt/E_TEST.dsc,APPEND,MEGABYTES 1000DISCARDROLLOVER AT 6:00REPORTROLLOVER AT 6:00REPORTCOUNT EVERY 1 HOURS,RATE??FETCHOPTIONS MISSINGROW ABENDSTATOPTIONS REPORTFETCH?WARNLONGTRANS 1H,CHECKINTERVAL 10m?DYNAMICRESOLUTIONTABLE TEST.TEST1;TABLE TEST.TEST2;4 創建EXTRACT投遞進程
GGSCI (rac19b) 3> add extract P_TEST,exttrailsource ./dirdat/esEXTRACT added.?GGSCI (rac19b) 4> add RMTTRAIL ./dirdat/rs,ext P_TEST,megabytes 1000RMTTRAIL added.?GGSCI (rac19b) 5>?5 設置EXTRACT投遞進程參數
extract P_TESTuserid ogg, password oggoraclermthost 192.168.2.201, mgrport 7809rmttrail /opt/ogg/dirdat/rspassthru?DISCARDFILE ./dirrpt/P_TEST.dsc,APPEND,MEGABYTES 1000DISCARDROLLOVER AT 6:00?REPORTROLLOVER AT 6:00REPORTCOUNT 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?---確認狀態正常runningGGSCI (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 -rlthtotal 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:042 配置復制進程參數
GGSCI (rac19a DBLOGIN as ogg) 13> edit params r_test?replicat r_testsetenv (MYSQL_HOME='/usr/local/mysql')setenv (MYSQL_UNIX_PORT='/opt/mysql/data/mysql.sock')dboptions host 192.168.2.201,connectionport 3306targetdb db1,userid ogg, password oggmysql?discardfile /opt/ogg/dirrpt/r_test.dsc,append,megabytes 1000DISCARDROLLOVER AT 6:00?REPERROR (DEFAULT, ABEND)?MAXTRANSOPS 5000 HANDLECOLLISIONSREPORTROLLOVER AT 6:00REPORTCOUNT EVERY 1 HOURS, RATE MAP test.test1, TARGET db1.test1;MAP test.test2, TARGET db1.test2;3 HANDLECOLLISIONS參數說明
參數是實現 OGG 全量數據與增量數據銜接的關鍵，其實現原理是在全量數據初始完成之后，開啟增量抽取進程，應用全量數據初始化期間產生的 redo log。
當全量應用完成后，開啟增量回放進程，應用全量期間的增量數據。可能會出現數據沖突的情況，這就是為什么表一定要有主鍵或者唯一鍵，使用該參數后增量回放 DML 語句時主要有以下沖突場景及處理邏輯：
1 目標端不存在 delete 語句的記錄，忽略該問題并不記錄到 discardfile。
2 目標端丟失 update 記錄，更新的是主鍵值，update 轉換成 insert，更新的鍵值是非主鍵，忽略該問題并不記錄到 discardfile。
3 目標端重復 insert 已存在的主鍵值，這將被 replicat 進程轉換為 UPDATE 現有主鍵值的。
4 在初始化數據，并追完增量數據之后，建議把HANDLECOLLISIONS參數去掉，Oracle官方建議不要一直使用該參數,這可能導致數據不準。
Oracle源端配置數據初始化進程(數據全量初始化進程)
1 添加初始化進程
GGSCI (rac19b) 17> add extract e_init,sourceistableEXTRACT added.2 配置初始化進程
GGSCI (rac19b) 20> edit params e_init?extract e_initSETENV (ORACLE_HOME = '/u01/app/oracle/product/19.0.0/dbhome_1')SETENV (ORACLE_SID = 'testdb')SETENV (NLS_LANG = 'AMERICAN_AMERICA.AL32UTF8')userid ogg, password oggoracleRMTHOST 192.168.2.201,MGRPORT 7809RMTTASK REPLICAT,GROUP r_init?table test.test1;table test.test2;MySQL目標端配置數據初始化進程(數據全量初始化進程)
1 添加初始化進程
GGSCI (rac19b) 17> add extract e_init,sourceistableEXTRACT added.2 配置初始化進程
GGSCI (rac19b) 20> edit params e_init?extract e_initSETENV (ORACLE_HOME = '/u01/app/oracle/product/19.0.0/dbhome_1')SETENV (ORACLE_SID = 'testdb')SETENV (NLS_LANG = 'AMERICAN_AMERICA.AL32UTF8')userid ogg, password oggoracleRMTHOST 192.168.2.201,MGRPORT 7809RMTTASK REPLICAT,GROUP r_init?table test.test1;table test.test2;全量數據初始化
數據初始化會將全表的數據通過創建的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_initsetenv (MYSQL_HOME='/usr/local/mysql')setenv (MYSQL_UNIX_PORT='/opt/mysql/data/mysql.sock')dboptions host 192.168.2.201,connectionport 3306targetdb db1,userid ogg, password oggmysqldiscardfile /opt/ogg/dirrpt/r_init.dsc,append,megabytes 1000MAXTRANSOPS 5000 MAP test.test1, TARGET db1.test1;MAP test.test2, TARGET db1.test2;3 同步完成，會輸出總的數量
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: 0From Table TEST.TEST2: # inserts: 1000000 # updates: 0 # deletes: 0 # upserts: 0 # discards: 0??REDO Log Statistics Bytes parsed 0 Bytes output 285986537增量數據同步
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:012 查看增量同步信息
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解決沖突數據的行數*** Total statistics since 2022-10-13 14:45:17 *** Total inserts0.00 Total updates0.00 Total deletes 99.00 Total upserts0.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 inserts0.00 Total updates 10.00 Total deletes0.00 Total upserts0.00 Total discards 0.00 Total operations 10.00??End of Statistics.?3 注釋去除HANDLECOLLISIONS參數
注:要在增量同步進程應用完初始化期間產生的日志以及實時同步之后，再去除參數。
#編輯配置文件，注釋---HANDLECOLLISIONSGGSCI (rac19a DBLOGIN as ogg) 71> edit params r_test?replicat r_testsetenv (MYSQL_HOME='/usr/local/mysql')setenv (MYSQL_UNIX_PORT='/opt/mysql/data/mysql.sock')dboptions host 192.168.2.201,connectionport 3306targetdb db1,userid ogg, password oggmysql?discardfile /opt/ogg/dirrpt/r_test.dsc,append,megabytes 1000DISCARDROLLOVER AT 6:00?REPERROR (DEFAULT, ABEND)?MAXTRANSOPS 5000??---HANDLECOLLISIONSREPORTROLLOVER AT 6:00REPORTCOUNT 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 測試數據同步情況
Oracle源端刪除999行數據，當前數據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目標端同步刪除的操作，數據 一致都為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數據遷移MySQL的三種簡單方法的文章就介紹到這了,更多相關Oracle數據遷移MySQL內容請搜索好吧啦網以前的文章或繼續瀏覽下面的相關文章希望大家以后多多支持好吧啦網！
排行榜
網公網安備