使用ogg(goldengate)方案將Oracle遷移到MySQL

下面講講關于使用ogg(goldengate)方案將Oracle遷移到MySQL，文字的奧妙在于貼近主題相關。所以，閑話就不談了，我們直接看下文吧，相信看完使用ogg(goldengate)方案將Oracle遷移到MySQL這篇文章你一定會有所受益。

source源端 oracle 11.2.0.4  ogg12c
ip:10.200.3.193 系統centos 7.1

1. 創建source表和一些初始化數據
su - oracle
sqlplus / as sysdba
SQL>alter database add supplemental log data;
SQL>select supplemental_log_data_min from v$database;

SQL>alter database forcelogging;

SQL> select supplemental_log_data_min from v$database;
NO

SQL> alter database add supplemental log data;

Database altered.
SQL> alter system switch logfile;

System altered.

SQL> select supplemental_log_data_min from v$database;
YES
SQL> create tablespace ggs;
Tablespace created.
SQL> create user ggs identified by ggs default tablespace ggs;
User created.
SQL> grant connect,resource,dba to ggs;
SQL> alter system set undo_retention=86400;
System altered.

SQL> grant flashback any table to ggs;

先配置參數進行靜默安裝---參考centos7.1之goldengate12c的安裝

2.在source源端上創建Manager參數文件并指定端口和一些需要的參數選項：
cd /u01/app/ggs

./ggsci
創建相關目錄：GGSCI (testtech4-193) 1> create subdirs

配置mgr
view params mgr

port 7809
dynamicportlist 7810-7820,7830

啟動start mgr

GGSCI (testtech4-193) 1> dblogin userid ggs password ggs
Successfully logged into database.
GGSCI (testtech4-193) 1> REGISTER EXTRACT ext1 DATABASE
GGSCI (testtech4-193) 2> add trandata workflow.act_ru_execution

3.編輯defgen文件
vi /u01/app/ggs/dirprm/defgen.prm

defsfile    /u01/app/ggs/dirdef/table_del,purge
userid ggs, password ggs
table  WORKFLOW.act_ru_execution;

cd  /u01/app/ggs

defgen  paramfile /u01/app/ggs/dirprm/defgen.prm

cd  /app/ggs/dirdef/
把文件復制到目標端的/app/ggs/dirdef/

4.在source源端上執行以下命令添加一個Extract進程叫做init進行數據初始化：
cd /u01/app/ggs
GGSCI (testtech4-193)>./ggsci
GGSCI (testtech4-193)>add extract init,sourceistable
GGSCI (testtech4-193)>info extract *, task
GGSCI (testtech4-193)>edit params init

extract init
userid ggs password ggs
rmthost 10.200.3.178,mgrport 8809
rmttask replicat,group repinit
table WORKFLOW.act_ru_execution;

目標端target mysql5.6     ogg 12c  
ip：10.200.3.178 系統centos 7.1

1.創建目標端target數據庫和相應的表
mysql -uroot -p
創建用于OGG使用登陸的數據庫，例如需要存放checkpoint table

mysql> create database ogg;
Query OK, 1 row affected (0.00 sec)

mysql> grant all on . to ogg@10.200.3.178 identified by 'ggs';
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.02 sec)
2.創建目標端target應用進程
GGSCI (techtest3-178)>dblogin sourcedb ogg@10.200.3.178:3306 userid ogg password ggs
GGSCI (techtest3-178)>add checkpointtable ogg.ck_table

GGSCI (techtest3-178)>add replicat repdel,exttrail /u01/app/ggs/dirdat/dl,checkpointtable   ogg.ck_table
GGSCI (techtest3-178)>edit params repdel

replicat repdel
targetdb ogg@10.200.3.178:3306 userid ogg password ggs
handlecollisions
SOURCECHARSET zhs16gbk ----源端字符集，一定要設置，特別是源端和目標端字符集不一致的情況，此參數必設置。
sourcedefs /u01/app/ggs/dirdef/table_def
discardfile /u01/app/ggs/dirrpt/repdel.dsc,purge
map WORKFLOW.act_ru_execution, target activiti.act_ru_execution;

由于是異構環境，所以要指定sourcedefs參數，使用source數據庫的數據定義文件。
handlecollisions參數來處理初始化加載的結果與實時改變同步捕獲數據的沖突。

目標端：view params mgr

port 8809
DYNAMICPORTLIST 8840-8850
ACCE×××ULE, PROG REPLICAT, IPADDR 10.200.3.139 ALLOW

3.目標端target創建初始化加載交付參數文件

GGSCI (techtest3-178)>add replicat repinit,specialrun

GGSCI (techtest3-178)>edit params repinit

replicat repinit

targetdb ogg@10.200.3.178:3306 userid ogg password ggs

sourcedefs /u01/app/ggs/dirdef/table_def

discardfile /u01/app/ggs/dirrpt/repinit.dsc,purge

sqlexec "SET FOREIGN_KEY_CHECKS=0"

map WORKFLOW.act_ru_execution, target activiti.act_ru_execution;

4.在source源端添加Extract組
GGSCI (testtech4-193) > ADD EXTRACT extdel,TRANLOG, BEGIN NOW
GGSCI (testtech4-193) >edit params extdel
EXTRACT extdel
userid ggs, password ggs
GETTRUNCATES
REPORTCOUNT EVERY 1 MINUTES, RATE
numfiles 5000
DISCARDFILE ./dirrpt/extdel.dsc, APPEND, MEGABYTES 1000
WARNLONGTRANS 2h, CHECKINTERVAL 3m
EXTTRAIL ./dirdat/dl
TRANLOGOPTIONS CONVERTUCS2CLOBS
THREADOPTIONS MAXCOMMITPROPAGATIONDELAY 60000
DBOPTIONS ALLOWUNUSEDCOLUMN
table  WORKFLOW.act_ru_execution;
GGSCI (testtech4-193) >add exttrail ./dirdat/dl, extract extdel, megabytes 500

GGSCI (testtech4-193) >ADD EXTRACT dpedel, EXTTRAILSOURCE ./dirdat/dl

GGSCI (testtech4-193) >edit params dpedel

EXTRACT dpedel
RMTHOST 10.200.3.178, MGRPORT 8809, compress
PASSTHRU
numfiles 50000
RMTTRAIL ./dirdat/dl
DYNAMICRESOLUTION
table  WORKFLOW.act_ru_execution;

GGSCI (testtech4-193) >ADD RMTTRAIL ./dirdat/dl, EXTRACT dpedel, megabytes 500

以下是啟動抽取和投遞進程，由于oracle到mysql初始化無法做到像oracle一樣的利用scn等。
大致思路是，先進程抽取數據，然后進行初始化操作。等初始化完了，啟動應用進程會有沖突，處理沖突，解決問題。最后做數據對比。
GGSCI (testtech4-193) >start extdel
GGSCI (testtech4-193) >start dpedel

5.源端source啟動初始化加載數據
在source源端啟動之前配置的初始化加載提取任務init：
GGSCI (testtech4-193) >start init

在目標端target端看初始化數據加載復制進程的報告確認加載成功：

GGSCI (techtest3-178)> view report repinit

init初始化結束以后，進程會自動重啟

6.最后目標端target啟動repdel

確認初始化數據加載完成后關閉改變同步Replicat進程repdel的handlecollisions。

GGSCI (techtest3-178) 1> send repdel,nohandlecollisions

并將repdel參數文件中的handlecollisions參數去除，防止repdel下次啟動時handlecollisions被再次啟用。

最后驗證數據：初始化數據，變更數據等

對于以上使用ogg(goldengate)方案將Oracle遷移到MySQL相關內容，大家還有什么不明白的地方嗎？或者想要了解更多相關，可以繼續關注我們的行業資訊板塊。