rac ogg to mysql_GoldenGate从oracle迁移数据到mysql
1軟件簡介
安裝時(shí)應(yīng)該選擇最為穩(wěn)定的安裝版本，現(xiàn)在官方發(fā)布的版本主要為：
Oracle GoldenGate 11.2.1.0.1
對(duì)應(yīng)不同的數(shù)據(jù)庫和版本，有不同的安裝介質(zhì)。下面是根據(jù)現(xiàn)網(wǎng)情況使用的兩個(gè)安裝介質(zhì)：
ogg112101_fbo_ggs_Linux_x64_ora10g_64bit.zip
ggs_Linux_x64_MySQL_64bit.tar
Oracle Golden Gate軟件是一種基于日志的結(jié)構(gòu)化數(shù)據(jù)復(fù)制備份軟件，它通過解析源數(shù)據(jù)庫在線日志或歸檔日志獲得數(shù)據(jù)的增量變化，再將這些變化應(yīng)用到目標(biāo)數(shù)據(jù)庫，從而實(shí)現(xiàn)源數(shù)據(jù)庫與目標(biāo)數(shù)據(jù)庫同步。Oracle Golden Gate可以在異構(gòu)的IT基礎(chǔ)結(jié)構(gòu)(包括幾乎所有常用操作系統(tǒng)平臺(tái)和數(shù)據(jù)庫平臺(tái))之間實(shí)現(xiàn)大量數(shù)據(jù)亞秒一級(jí)的實(shí)時(shí)復(fù)制,從而在可以在應(yīng)急系統(tǒng)、在線報(bào)表、實(shí)時(shí)數(shù)據(jù)倉庫供應(yīng)、交易跟蹤、數(shù)據(jù)同步、集中/分發(fā)、容災(zāi)、數(shù)據(jù)庫升級(jí)和移植、雙業(yè)務(wù)中心等多個(gè)場景下應(yīng)用。同時(shí)，Oracle Golden Gate可以實(shí)現(xiàn)一對(duì)一、廣播(一對(duì)多)、聚合(多對(duì)一)、雙向、點(diǎn)對(duì)點(diǎn)、級(jí)聯(lián)等多種靈活的拓?fù)浣Y(jié)構(gòu)。
2整體架構(gòu)
和傳統(tǒng)的邏輯復(fù)制一樣，Oracle GoldenGate實(shí)現(xiàn)原理是通過抽取源端的redo log或者archive log，然后通過TCP/IP投遞到目標(biāo)端，最后解析還原應(yīng)用到目標(biāo)端，使目標(biāo)端實(shí)現(xiàn)同源端數(shù)據(jù)同步。以下是OracleGoldenGate的技術(shù)架構(gòu)
Manager進(jìn)程
Manager進(jìn)程是GoldenGate的控制進(jìn)程，運(yùn)行在源端和目標(biāo)端上。它主要作用有以下幾個(gè)方面：啟動(dòng)、監(jiān)控、重啟Goldengate的其他進(jìn)程，報(bào)告錯(cuò)誤及事件，分配數(shù)據(jù)存儲(chǔ)空間，發(fā)布閥值報(bào)告等。
Extract進(jìn)程
Extract運(yùn)行在數(shù)據(jù)庫源端，負(fù)責(zé)從源端數(shù)據(jù)表或者日志中捕獲數(shù)據(jù)。
Pump進(jìn)程
pump進(jìn)程運(yùn)行在數(shù)據(jù)庫源端。其作用是如果源端使用了本地的trail文件，那么pump進(jìn)程就會(huì)把trail以數(shù)據(jù)塊的形式通過TCP/IP協(xié)議發(fā)送到目標(biāo)端，這通常也是推薦的方式。pump進(jìn)程本質(zhì)是extract進(jìn)程的一種特殊形式，如果不使用trails文件，那么就是extract進(jìn)程在抽取完數(shù)據(jù)以后，直接投遞到目標(biāo)端。
Trail文件
為了更有效、更安全的把數(shù)據(jù)庫事務(wù)信息從源端投遞到目標(biāo)端。GoldenGate引進(jìn)trail文件的概念。前面提到extract抽取完數(shù)據(jù)以后Goldengate會(huì)將抽取的事務(wù)信息轉(zhuǎn)化為一種GoldenGate專有格式的文件。然后pump負(fù)責(zé)把源端的trail文件投遞到目標(biāo)端，所以源、目標(biāo)兩端都會(huì)存在這種文件。trail文件存在的目的旨在防止單點(diǎn)故障，將事務(wù)信息持久化，并且使用checkpoint機(jī)制來記錄其讀寫位置，如果故障發(fā)生，則數(shù)據(jù)可以根據(jù)checkpoint記錄的位置來重傳。
Replicat進(jìn)程
Replicat進(jìn)程，通常我們也把它叫做應(yīng)用進(jìn)程。運(yùn)行在目標(biāo)端，是數(shù)據(jù)傳遞的最后一站，負(fù)責(zé)讀取目標(biāo)端trail文件中的內(nèi)容，并將其解析為DML或DDL語句，然后應(yīng)用到目標(biāo)數(shù)據(jù)庫中。
GGSCI
GGSCI是GoldenGate Software Command Interface的縮寫，它提供了十分豐富的命令來對(duì)Goldengate進(jìn)行各種操作，如創(chuàng)建、修改、監(jiān)控GoldenGate進(jìn)程等等
靈活的拓?fù)浣Y(jié)構(gòu)：
2.1?? GoldenGate支持列表
Databases
OS and Platforms
Capture:
Windows 2000,2003,xp
Oracle
Linux
DB2 UDB
Sun Slolaris
Microsoft SQL Server
HP NonStop
Sybase ASE
HP Ux
Teradata
HP TRU64
Ingres
IBM AIX
Enscribe
IBM z/OS
SQL/MP
SQL/MX
Delivery:
All listed above
Mysql,hp neoview,Netezza
And ODBC compatible databases
3GoldenGate軟件安裝
3.1組和用戶創(chuàng)建
源端是oracle生產(chǎn)數(shù)據(jù)庫，已經(jīng)存在oracle和dba，復(fù)用該用戶和組。
目標(biāo)端是mysql，已經(jīng)存在mysql和dba，復(fù)用該用戶和組。
3.2GoldenGate安裝目錄檢查
安裝Oracle GoldenGate軟件前，需要事先確認(rèn)安裝目錄屬主權(quán)限正確，且所在文件系統(tǒng)有足夠的空間存放Oracle GoldenGate軟件并能支撐日常運(yùn)維使用。對(duì)于存放dirdat數(shù)據(jù)目錄，至少需要100GB以上的空間。對(duì)于源庫是oracle rac模式，必須設(shè)置ogghome的目錄的存儲(chǔ)能讓rac兩臺(tái)主機(jī)識(shí)別，只是mount在一個(gè)任務(wù)較輕的節(jié)點(diǎn)上，若是該節(jié)點(diǎn)出現(xiàn)故障，能及時(shí)掛載到另外一個(gè)節(jié)點(diǎn)上，增加高可用性。
檢查安裝目錄所在文件系統(tǒng)空間信息，通過下述命令實(shí)現(xiàn)：
df -g
/home/ogghome
確認(rèn)安裝目錄屬主權(quán)限信息，通過下述命令實(shí)現(xiàn)：
ls –ld /home/ogghome
3.3解壓安裝文件
Oracle GoldenGate軟件通過解壓縮方式實(shí)現(xiàn)安裝，
解壓縮GoldenGate安裝文件，通過下述命令實(shí)現(xiàn)：
unzip
ggs_*.zip
tar
-xvf? ggs_*.tar
3.4GoldenGate軟件啟動(dòng)測試
Oracle GoldenGate軟件通過ggsci命令啟動(dòng)交互界面，當(dāng)ggsci啟動(dòng)成功則表名安裝過程無誤。啟動(dòng)ggsci前務(wù)必進(jìn)入Oracle GoldenGate軟件的安裝目錄。
啟動(dòng)Oracle GoldenGate的ggsci，通過下述命令實(shí)現(xiàn)：
cd /home/oracle/ggs/goldengate
./ggsci
Oracle數(shù)據(jù)庫的賬號(hào)必須設(shè)置LD_LIBRARY_PATH環(huán)境變量，否則會(huì)報(bào)錯(cuò)，
LD_LIBRARY_PATH=/oracle/product/10.2/db/lib
3.5、創(chuàng)建GoldenGate軟件子目錄
進(jìn)入安裝路徑，創(chuàng)建相關(guān)目錄(源端和目標(biāo)端都執(zhí)行)，源端使用oracle用戶，目標(biāo)端使用mysql用戶：
cd /home/ogghome
./ggsci
GGSCI> create subdirs
下面是日志：
Creating
subdirectories under current directory /home/ogghome
Parameter
files? ??????????????/home/ogghome/dirprm: already
exists
Report
files
/home/ogghome/dirrpt: created
Checkpoint
files?????????????? /home/ogghome/dirchk:
created
Process
status files
/home/ogghome/dirpcs: created
SQL
script files????? ?????????/home/ogghome/dirsql: created
Database
definitions files
/home/ogghome/dirdef: created
Extract
data files
/home/ogghome/dirdat: created
Temporary
files
/home/ogghome/dirtmp: created
Stdout
files?????????????????? /home/ogghome/dirout:
created
4GoldenGate部署
4.1初始化源端數(shù)據(jù)庫
4.1.1開啟歸檔模式
GoldenGate是基于oracle日志變化的捕獲，所以為了完整的捕獲到oracle數(shù)據(jù)庫的變化，有必要將歸檔模式開啟。
SQL>????? alter system set
log_archive_dest_1='location=/arch' scope=both;
SQL>? shutdown
immediate
SQL>? startup
mount
SQL>? alter
database archivelog
SQL>? alter
database open
SQL>? archive
log list
注意，由于上面命令涉及停止數(shù)據(jù)庫應(yīng)用，應(yīng)結(jié)合業(yè)務(wù)實(shí)際情況進(jìn)行操作。
4.1.2開啟附加日志
在oracle中我們可以通過rowid來定位某條記錄，但是目標(biāo)端的數(shù)據(jù)庫和源端數(shù)據(jù)庫的數(shù)據(jù)庫可能完全不一樣，所以無法通過rowid來確定源端數(shù)據(jù)庫的邏輯變化，這時(shí)附件日志supplemental log便登上了表演的舞臺(tái)。數(shù)據(jù)庫在開啟附加日志功能后，對(duì)于源端的修改操作，oracle會(huì)同時(shí)追加能夠唯一標(biāo)示記錄的列到redo log。這樣目標(biāo)端數(shù)據(jù)庫就可以知道源端發(fā)生了哪些具體的變化。
SQL>?????? alter
database add supplemental log data;
SQL>?????? alter
system switch logfile;
SQL>?????? select
supplemental_log_data_min from v$database;
4.1.3開啟強(qiáng)制日志模式
SQL>alter
database force logging;
SQL>SELECT FORCE_LOGGING FROM V$DATABASE;
4.1.4goldengate創(chuàng)建用戶并授權(quán)
源端oracle數(shù)據(jù)庫創(chuàng)建賬號(hào)：
create
tablespace data_goldengate datafile '+DG_DATA1' size 10240m autoextend on;
create
user goldengate identified by password default tablespace data_goldengate
temporary tablespace temp;
賦予goldengate相關(guān)權(quán)限(想簡單的話，可以賦予DBA權(quán)限，若是涉及安全問題，dba權(quán)限不能隨便賦予)
grant
CONNECT,RESOURCE to goldengate;
grant
CREATE SESSION,ALTER SESSION to goldengate;
grant
SELECT ANY DICTIONARY,SELECT ANY TABLE to goldengate;
grant
CREATE TABLE,ALTER ANY TABLE to goldengate;
grant
unlimited tablespace to goldengate;
目標(biāo)端mysql創(chuàng)建賬號(hào)，賦予dba權(quán)限：
grant
ALL PRIVILEGES on *.* to goldengate@'%';
grant
ALL PRIVILEGES on *.* to goldengate@'localhost';
update
mysql.user set password=password('password') where user='goldengate';
4.1.5對(duì)需要同步的表確認(rèn)是否開啟表級(jí)附加日志
./ggsci
GGSCI
(localhost.localdomain) 1> dblogin userid goldengate password password
Successfully
logged into database.
GGSCI
(localhost.localdomain) 2> info trandata xianyezhao.CALENDAR_DETAIL
Logging
of supplemental redo log data is disabled for table XIANYEZHAO.CALENDAR_DETAIL.
GGSCI
(localhost.localdomain) 3> add trandata xianyezhao.CALENDAR_DETAIL
Logging
of supplemental redo data enabled for table XIANYEZHAO.CALENDAR_DETAIL.
GGSCI
(localhost.localdomain) 4> info trandata
XIANYEZHAO.CALENDAR_DETAIL
Logging
of supplemental redo log data is enabled for table XIANYEZHAO.CALENDAR_DETAIL.
Columns
supplementally logged for table XIANYEZHAO.CALENDAR_DETAIL: SEQNO.
4.2配置ASM訪問方式
由于oracle redo日志文件在ASM存儲(chǔ)上，goldengate軟件需要訪問ASM，
配置asm實(shí)例的tns連接字符串，由于監(jiān)聽狀態(tài)是BLOCKED，必須添加(UR=A)才能通過ogg遠(yuǎn)程登錄asm實(shí)例。下面是官方詳細(xì)說明：
ASM
instances appear with a blocked status from the 'lsnrctl services'command (see
below). To remotely access a 'blocked service' you need toadd (UR=A) to the tns
connect string (plus you will need a password filefor the asm instance).
Tnsnames.ora
entry:----
下面是監(jiān)聽狀態(tài)，使用lsnrctl status查看：
Service
"+ASM" has 1 instance(s).
Instance "+ASM2", status BLOCKED,
has 1 handler(s) for this service...
下面是具體的tns連接字符串：
ASM
=
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.219.6)(PORT
= 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = +ASM)
(INSTANCE_NAME = +ASM2)
(UR=A)
)
)
4.3配置NFS文件系統(tǒng)
由于歸檔是使用文件系統(tǒng)，需要使用nfs文件模式，把另外一個(gè)節(jié)點(diǎn)的歸檔掛載到本地。
a.開啟nfs服務(wù)：
service nfs start
查看nfs狀態(tài)：
service nfs status
b.開啟nfs權(quán)限：
編輯權(quán)限列表：
vi /etc/exports
/archivelog1??? *(rw)
更新權(quán)限：
exportfs –a
c.創(chuàng)建/archivelog1，更改權(quán)限為oracle和dba，
chown oracle:dba /archivelog1
使用root用戶mount上：
mount 192.168.219.3:/archivelog1
/archivelog1
4.4在源端和目標(biāo)端配置MGR
ggsci>???? EDIT
PARAMS? MGR
PORT?? 7809
PURGEOLDEXTRACTS? ./dirdat,USECHECKPOINTS
ggsci>???? START
MGR
ggsci>
INFO????? MGR
4.5創(chuàng)建Extract: ext_cal
4.5.1增加extract
add
extract ext_cal, tranlog, begin now, THREADS 2
EXTRACT
added.
4.5.2設(shè)定exttrail
add
exttrail ./dirdat/ca, extract ext_cal, megabytes 100
EXTTRAIL
added.
4.5.3配置extract
下面******替換成密碼：
edit
params ext_cal
/********************************************************************/
EXTRACT
ext_cal
SETENV
(NLS_LANG="AMERICAN_AMERICA.ZHS16GBK")
SETENV
(ORACLE_SID="g139bak2")
userid
goldengate, password password
TRANLOGOPTIONS
ASMUSER sys@asm,ASMPASSWORD ******
EXTTRAIL
./dirdat/ca
TRANLOGOPTIONS
LOGRETENTION DISABLED
TRANLOGOPTIONS
BUFSIZE 2048000
tranlogoptions
altarchivedlogformat threadid 1 g139bak_%S_%T_%R.log
tranlogoptions
altarchivedlogformat threadid 2 g139bak_%S_%T_%R.log
tranlogoptions
altarchivelogdest instance g139bak1 /archivelog1/g139bak_archivelog,
altarchivelogdest instance g139bak2 /archivelog2/g139bak_archivelog
--DDL
INCLUDE mapped
--DDLOPTIONS
ADDTRANDATA
DISCARDFILE
./dirrpt/ext_cal.dsc,APPEND,MEGABYTES 1000
DISCARDROLLOVER
AT 6:00
REPORTROLLOVER
AT 6:00
REPORTCOUNT
EVERY 1 HOURS,RATE
TRANLOGOPTIONS
ALTARCHIVELOGDEST /archivelog2
FETCHOPTIONS
MISSINGROW ABEND
STATOPTIONS
REPORTFETCH
WARNLONGTRANS
1H,CHECKINTERVAL 10m
DYNAMICRESOLUTION
TABLE
XIANYEZHAO.CALENDAR_DETAIL;
TABLE
XIANYEZHAO.CALENDAR_INFO;
/********************************************************************/
下面是查看內(nèi)容：
GGSCI
(gb139bak02) 5> view report ext_cal
4.6創(chuàng)建投送進(jìn)程: dp_cal
4.6.1增加extract
add
extract dp_cal, exttrailsource ./dirdat/ca
4.6.2設(shè)定extract投遞rmttrail
add
rmttrail ./dirdat/ca, extract dp_cal
4.6.3配置extract
edit
params dp_cal
/********************************************************************/
EXTRACT
dp_cal
passthru
DYNAMICRESOLUTION
RMTHOST
192.168.219.35, MGRPORT 7809, COMPRESS
RMTTRAIL
./dirdat/ca
TABLE
XIANYEZHAO.CALENDAR_DETAIL;
TABLE
XIANYEZHAO.CALENDAR_INFO;
/********************************************************************/
由于沒有配置目標(biāo)端的mgr進(jìn)程，7809沒有打開，網(wǎng)絡(luò)報(bào)錯(cuò)，導(dǎo)致進(jìn)程掛掉。
GGSCI
(localhost.localdomain) 48> view report dp_cal
GGSCI
(gb139bak02) 65> view report dp_cal
4.7創(chuàng)建定義文件
在源端編輯defgen文件
su -
oracle
cd
./dirprm
$ vi
cal.prm
DEFSFILE
/home/ogghome/dirdef/cal.def
USERID
goldengate PASSWORD password
TABLE
XIANYEZHAO.CALENDAR_DETAIL;
TABLE
XIANYEZHAO.CALENDAR_INFO;
通過執(zhí)行defgen命令生成定義文件
/home/ogghome/defgen
PARAMFILE /home/ogghome/dirprm/cal.prm
***********************************************************************
Oracle GoldenGate Table Definition
Generator for Oracle
Version 11.2.1.0.1
OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230
Linux, x64, 64bit (optimized), Oracle 10g on
Apr 23 2012 05:09:39
Copyright
(C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.
Starting at 2014-06-09 15:24:08
***********************************************************************
Operating
System Version:
Linux
Version
#1 SMP Thu Sep 5 21:21:44 EDT 2013, Release 2.6.18-371.el5
Node:
gb139bak02
Machine:
x86_64
soft limit?? hard limit
Address
Space Size?? :??? unlimited
unlimited
Heap
Size??????????? :??? unlimited
unlimited
File
Size??????????? :??? unlimited
unlimited
CPU
Time???????????? :??? unlimited
unlimited
Process
id: 2014
***********************************************************************
**??????????? Running with the following
parameters????????????????? **
***********************************************************************
DEFSFILE
/home/ogghome/dirdef/cal.def
USERID
goldengate PASSWORD ********
TABLE
XIANYEZHAO.CALENDAR_DETAIL;
Retrieving
definition for XIANYEZHAO.CALENDAR_DETAIL
TABLE
XIANYEZHAO.CALENDAR_INFO;
Retrieving
definition for XIANYEZHAO.CALENDAR_INFO
Definitions
generated for 2 tables in /home/ogghome/dirdef/cal.def
把生成的def文件傳輸?shù)絤ysql端
[oracle@localhost
dirdef]$ sftp mysql@192.168.61.131
Connecting
to 192.168.61.131...
mysql@192.168.61.131's
password:
sftp>
cd /home/ogghome/dirdef
sftp>
!ls
sftp>
put cal.def
Uploading
cal.def to /home/ogghome/dirdef/cal.def
cal.def?? ??????????????????????????????????????????????????????????????????????????????????????100%
1001???? 1.0KB/s?? 00:00
修改mysql端定義文件的權(quán)限
chown
mysql:dba /home/ogghome/dirdef/cal.def
chmod
755 /home/ogghome/dirdef/cal.def
4.8初始化數(shù)據(jù)
4.8.1Oracle到oracle導(dǎo)出方式
1、查看系統(tǒng)改變號(hào)：
col
current_scn for 999999999999999999999999999999
select
current_scn from v$database;
--1138506
2、導(dǎo)出導(dǎo)入數(shù)據(jù)：
set
linesize 133
col
directory_path format a50
select *
from dba_directories;
SYS??????????????????????????? DATA_PUMP_DIR????????????????? /opt/oracle/app/admin/test/dpdump/
expdp
\'/ as sysdba \' DIRECTORY=DATA_PUMP_DIR DUMPFILE=t3.dmp LOGFILE=t3.log
TABLES=test.t3 FLASHBACK_SCN=1138506
impdp
\'/ as sysdba \' DIRECTORY=DATA_PUMP_DIR DUMPFILE=t3.dmp LOGFILE=t3_imp.log
TABLES=test.t3 REMAP_TABLESPACE=DATA_TEST:DATA_TEST
--REMAP_SCHEMA=TEST:TEST
3、啟動(dòng)應(yīng)用進(jìn)程的時(shí)候，需要指定aftersn號(hào)，下面是具體例子：
用SCN啟動(dòng)Replicat
start
rep_cal, aftercsn 1138506
4.8.2Oracle到mysql方式
使用goldengatedirect load的方式:
源端：
ADD EXTRACT einixyz,
SOURCEISTABLE
EXTRACT einixyz
userid goldengate, password
password
RMTHOST 192.168.219.35, MGRPORT
7809, COMPRESS
RMTTASK REPLICAT, GROUP RINIXYZ
TABLE XIANYEZHAO.T_INI;
創(chuàng)建定義文件
在源端編輯defgen文件
su - oracle
cd ./dirprm
$ vi cal_xyz.prm
DEFSFILE
/home/ogghome/dirdef/cal_xyz.def
USERID goldengate PASSWORD
password
TABLE XIANYEZHAO.T_INI;
通過執(zhí)行defgen命令生成定義文件
/home/ogghome/defgen PARAMFILE
/home/ogghome/dirprm/cal_xyz.prm
sftp上傳到目標(biāo)端服務(wù)器/home/ogghome/dirdef/目錄下。目標(biāo)端：ADD?REPLICAT?rinixyz,?SPECIALRUN
replicat RINIXYZ
sourcedefs
/home/ogghome/dirdef/cal_xyz.def
SETENV
(MYSQL_UNIX_PORT=/mysql/dbdata/mysql.sock)
sourcedb cal@localhost userid
goldengate, password password
reperror default, discard
discardfile
./dirrpt/ext_in.dsc,APPEND,MEGABYTES 1000
MAP XIANYEZHAO.T_INI , TARGET
CAL.T_INI;
啟動(dòng)數(shù)據(jù)初始化，只是需要在源端啟動(dòng)抽取進(jìn)程即可：
start einixyz
4.9目標(biāo)端創(chuàng)建Replicat
4.9.1手工創(chuàng)建數(shù)據(jù)庫和表語句
創(chuàng)建數(shù)據(jù)庫：
create database cal;
創(chuàng)建表，需要把oracle格式轉(zhuǎn)化為mysql格式，下面是部分內(nèi)容：
1.number需要轉(zhuǎn)化成DECIMAL
2.varchar2需要轉(zhuǎn)化成varchar
3.date需要轉(zhuǎn)換成datetime
4.mysql不支持NUMBER(38,0)轉(zhuǎn)換成DECIMAL(38,0),因此，需要把*轉(zhuǎn)化成oracle最大的數(shù)值，38位長，DECIMAL(38,0)。
5.由于mysql分區(qū)字段必須是int類型，下面HASH (UIN)不能進(jìn)行hash分區(qū)。
6.mysql索引沒有并發(fā)概念，去除parallel選項(xiàng)。
CREATE
TABLE CALENDAR_DETAIL
( SEQNO
DECIMAL NOT NULL,
DETAILSEQNO DECIMAL,
LABELID DECIMAL,
LABELTYPE DECIMAL(2,0),
MESSTYPE DECIMAL(2,0),
UIN VARCHAR(32),
SERVICEID DECIMAL(8,0),
SPSID VARCHAR(32),
RECTARGET VARCHAR(1024),
STARTSENDTIME DATETIME,
SENDMSG VARCHAR(2000),
CREATETIME DATETIME,
MODIFYTIME DATETIME,
TYPE DECIMAL(38,0) DEFAULT 0
);
CREATE
INDEX CALENDAR_DETAIL_IDX3_NEW ON CALENDAR_DETAIL (DETAILSEQNO);
CREATE
UNIQUE INDEX PK_CALENDAR_DETAIL ON CALENDAR_DETAIL (SEQNO);
CREATE
INDEX CALENDAR_DETAIL_IDX1_NEW ON CALENDAR_DETAIL (STARTSENDTIME);
CREATE
INDEX CALENDAR_DETAIL_IDX2_NEW ON CALENDAR_DETAIL (UIN);
ALTER
TABLE CALENDAR_DETAIL ADD CONSTRAINT PK_CALENDAR_DETAIL PRIMARY KEY(SEQNO);
CREATE
TABLE CALENDAR_INFO
( SEQNO
DECIMAL NOT NULL,
UIN VARCHAR(32),
GID VARCHAR(50),
CREATORUIN VARCHAR(32),
SERVICEID DECIMAL(10,0),
SPSID VARCHAR(32),
TITLE VARCHAR(600),
SITE VARCHAR(200),
ISATTACH DECIMAL(2,0),
CONTENT VARCHAR(1500),
STARTDATE DATETIME,
ENDDATE DATETIME,
STARTTIME DECIMAL(5,0),
ENDTIME DECIMAL(5,0),
STATUS DECIMAL(2,0),
DATEDESCRIPT VARCHAR(128),
ENABLE DECIMAL(2,0),
AUTHSTATUS DECIMAL(2,0),
LABELID DECIMAL,
LABELTYPE DECIMAL(2,0),
SPECIALAPPID DECIMAL,
RECMYSMS DECIMAL(2,0),
RECMYEMAIL DECIMAL(2,0),
RECMOBILE VARCHAR(1024),
RECEMAIL VARCHAR(1024),
CREATETIME DATETIME,
MODIFYTIME DATETIME,
VERSION DECIMAL(8,0),
EXPEND VARCHAR(50),
COMEFROM DECIMAL(5,0),
SPECIALTYPE DECIMAL(2,0) DEFAULT 0
);
CREATE
INDEX CALENDAR_INFO_IDX6_NEW ON CALENDAR_INFO (SPECIALAPPID);
CREATE
INDEX CALENDAR_INFO_IDX1_NEW ON CALENDAR_INFO (UIN);
CREATE
INDEX CALENDAR_INFO_IDX7_NEW ON CALENDAR_INFO (LABELID);
CREATE
INDEX CALENDAR_INFO_IDX4_NEW ON CALENDAR_INFO (STARTTIME);
CREATE
INDEX CALENDAR_INFO_IDX8_NEW ON CALENDAR_INFO (GID);
CREATE
INDEX SPECIALTYPE_INDEX ON CALENDAR_INFO (SPECIALTYPE);
CREATE
UNIQUE INDEX PK_CALENDAR_INFO ON CALENDAR_INFO (SEQNO);
CREATE
INDEX CALENDAR_INFO_IDX5_NEW ON CALENDAR_INFO (ENDTIME);
CREATE
INDEX CALENDAR_INFO_IDX3_NEW ON CALENDAR_INFO (ENDDATE);
CREATE
INDEX CALENDAR_INFO_IDX2_NEW ON CALENDAR_INFO (STARTDATE);
ALTER
TABLE CALENDAR_INFO ADD CONSTRAINT PK_CALENDAR_INFO PRIMARY KEY (SEQNO);
4.9.2在目標(biāo)端配置replicat進(jìn)程
ggsci>???? EDIT
PARAMS ?./GLOBALS
CHECKPOINTTABLE???????? goldengate.ggschkpt
ggsci>?? exit
ggsci>?? dblogin sourcedb
cal@192.168.219.35:3306,userid goldengate, password password
ggsci>???? add checkpointtable cal.checkpt
Successfully
created checkpoint table cal.checkpt.
4.9.3創(chuàng)建Replicat
add replicat
rep_cal, exttrail ./dirdat/ca ,checkpointtable cal.checkpt
4.9.4配置replicat
edit
params rep_cal
/********************************************************************/
REPLICAT
rep_cal
SETENV
(MYSQL_UNIX_PORT=/mysql/dbdata/mysql.sock)
sourcedb
cal@localhost userid goldengate, password password
SQLEXEC
"select CURRENT_TIME();" EVERY 10 MINUTES
REPORT
AT? 6:00
gettruncates
STATOPTIONS
RESETREPORTSTATS
reperror
(default,abend)
discardrollover
at 6:00
numfiles
1000
MAXLONGLEN
8000
dynamicresolution
allownoopupdates
SOURCEDEFS
./dirdef/cal.def
GROUPTRANSOPS
100000
MAXTRANSOPS
100000
REPORTCOUNT
EVERY 1 MINUTES RATE
discardfile
./dirrpt/rep_cal.dsc, append, megabytes 1000
MAP
XIANYEZHAO.CALENDAR_DETAIL???????????? ,
TARGET CAL.CALENDAR_DETAIL??????????? ??,KEYCOLS( SEQNO );
MAP
XIANYEZHAO.CALENDAR_INFO?????????????? ,
TARGET CAL.CALENDAR_INFO
,KEYCOLS( SEQNO );
/********************************************************************/
若是oracle到oracle同步，則可以使用SCN啟動(dòng)Replica
start rep_cal, aftercsn 1138506
若是oracle到mysql，則直接啟動(dòng)：
start rep_cal
查看命令：
GGSCI
(mysqldb1) 20> info all
Program???? Status
Group?????? Lag at Chkpt? Time Since Chkpt
MANAGER???? RUNNING
REPLICAT??? RUNNING
REP_CAL???? 00:00:00????? 00:00:08
GGSCI
(mysqldb1) 21> view report rep_cal
5Goldengate同步測試
5.1開啟同步進(jìn)程
源端開啟相關(guān)進(jìn)程
GGSCI> start mgr
GGSCI> start ext_cal
GGSCI> start dp_cal
目標(biāo)端開啟相關(guān)進(jìn)程
GGSCI> start mgr
GGSCI> start rep_cal
5.2異構(gòu)不支持ddl同步
從oracle同步到mysql，屬于異構(gòu)架構(gòu)，不支持ddl同步，包括添加和刪除字段，添加和刪除索引，重命名表，表分析統(tǒng)計(jì)數(shù)據(jù)。
若是涉及到源端和目標(biāo)端ddl操作，需要進(jìn)行源端和目標(biāo)端同時(shí)手工操作。
5.3DML測試
從oracle同步到mysql，支持DML同步。
5.3.1沒有主鍵的表同步
CALENDAR_20131213_LOTTERY共有647581條記錄，沒有主鍵的表，每次進(jìn)行插入和刪除是按照100000條記錄進(jìn)行批處理，該值是由參數(shù)決定。
插入從15:18:43開始，到15:21:22結(jié)束，共使用159秒。
刪除從15:25:07開始，到15:28:42結(jié)束，共使用215秒。
在全同步期間的產(chǎn)生的DML，在開始增量后，數(shù)據(jù)同步是一致。批量同步會(huì)有部分延遲，每秒同步的數(shù)據(jù)插入是4072條記錄，刪除是3012條記錄。
5.3.2有主鍵的表同步
CALENDAR_INVITE_INFO表共有14358726條記錄，全量同步開始時(shí)間16:36:12，在16:56:40時(shí)刻，源端插入6645999，mysql目標(biāo)端插入3887999，有2758000條記錄延遲。源端全部完成插入14358726，mysql目標(biāo)端插入8545999。在17:19:19時(shí)刻，完成全部數(shù)據(jù)量插入，使用2587秒，每秒插入5550條左右。對(duì)比沒有主鍵的表來說，快了36%左右。因此，要求同步的表必須添加主鍵。
Update操作，calendar_detail表有12042條記錄，大概3秒左右完成同步目標(biāo)端，更新為1.
SQL> select count(1) ,type
from calendar_detail group by type;
COUNT(1)?????? TYPE
---------- ----------
1783????????? 3
10259????????? 0
update calendar_detail set
type=1;
mysql> select count(1) ,type
from calendar_detail group by type;
+----------+------+
| count(1) | type |
+----------+------+
|??? 12042 |
1 |
+----------+------+
CALENDAR_INVITE_INFO使用下面同步腳本(存儲(chǔ)過程P_01)：
declare
row_num number ;
cursor get_data is
select
SEQNO??????????? ,
CALSEQNO???????? ,
UIN????????????? ,
INVITERUIN?????? ,
INVITERALIAS???? ,
RECMYSMS???????? ,
RECMYEMAIL?????? ,
RECMOBILE??????? ,
RECEMAIL???????? ,
STATUS?????????? ,
REFUSERESION???? ,
INVITEAUTH?????? ,
INVITEREMARK???? ,
INVITETIME?????? ,
REMARKTIME?????? ,
ENABLE?????????? ,
ISDELETED??????? ,
BEFORETYPE?????? ,
BEFORETIME?????? ,
NOTIFYCONF?????? ,
NEXTSENDDATE
from calendar01.CALENDAR_INVITE_INFO;
begin
row_num := 1;
for rec in get_data loop
insert into CALENDAR_INVITE_INFO(
SEQNO??????????? ,
CALSEQNO???????? ,
UIN????????????? ,
INVITERUIN?????? ,
INVITERALIAS???? ,
RECMYSMS???????? ,
RECMYEMAIL?????? ,
RECMOBILE??????? ,
RECEMAIL???????? ,
STATUS?????????? ,
REFUSERESION???? ,
INVITEAUTH?????? ,
INVITEREMARK???? ,
INVITETIME?????? ,
REMARKTIME?????? ,
ENABLE?????????? ,
ISDELETED??????? ,
BEFORETYPE?????? ,
BEFORETIME?????? ,
NOTIFYCONF?????? ,
NEXTSENDDATE
)
values(
rec.SEQNO??????????? ,
rec.CALSEQNO???????? ,
rec.UIN????????????? ,
rec.INVITERUIN?????? ,
rec.INVITERALIAS???? ,
rec.RECMYSMS???????? ,
rec.RECMYEMAIL?????? ,
rec.RECMOBILE??????? ,
rec.RECEMAIL???????? ,
rec.STATUS?????????? ,
rec.REFUSERESION???? ,
rec.INVITEAUTH?????? ,
rec.INVITEREMARK???? ,
rec.INVITETIME?????? ,
rec.REMARKTIME?????? ,
rec.ENABLE????????? ?,
rec.ISDELETED??????? ,
rec.BEFORETYPE?????? ,
rec.BEFORETIME?????? ,
rec.NOTIFYCONF?????? ,
rec.NEXTSENDDATE
);
row_num := row_num + 1;
--每2000條提交一次
if mod(row_num, 2000) = 0 then
commit;
end if;
end loop;
commit;<
總結(jié)
以上是生活随笔為你收集整理的rac ogg to mysql_GoldenGate从oracle迁移数据到mysql的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。
