ע ¼  
 ӹע
   ʾһ  |  ر
ܰʾ΢֤Ƶ΢ʺŰѹڣ°󶨣°΢  |  ر

OracleMySQLϼ

.

 
 
 
 
 

־

 
 

ʹGoldenGateͬMySQLݵOracle  

2017-01-09 18:41:29|  ࣺ Ⱥ |  ǩ |ٱ |ֺС 

  LOFTER ҵƬ  |

 

 

 

ʹGoldenGateͬMySQLݵOracle

 

 

 

Ŀ¼

һ... 2

زװogg. 2

1. ogg. 2

2. װogg. 2

Դ... 4

1. MySQLGoldenGateʺ... 4

2. MySQL... 4

3. ༭manageṛ... 4

4. extract... 4

5. pump... 5

6. defgen. 5

Ŀ... 6

1. ΪGoldenGateռ... 6

2. GoldenGateʺţΪʺȨ... 6

3. checkpoint... 6

4. ༭manageṛ... 6

5. replicat... 6

ġʼ... 7

塢ʼͬ... 7

1. ... 7

2. 鿴״̬... 7

Ҫ... 7

 

 

 

 

 

 

 

 

 

 

 

 

һ

 

OS

IP

⼰汾

OggװĿ¼

ͬı

Դ

linux 5.8

10.1.5.112

MySQL 5.6.26

/home/mysql/ogg/

testdb.t1  testdb.t2

Ŀ

linux 5.9

10.1.5.117

Oracle 11.2.0.3

/home/oracle/ogg/

scott.t1   scott.t2

Ogg12.1.2.1.0

 

 

 

 

 

زװogg

  1. ogg

ֱOracleMySQLoggϢͼ

ʹGoldenGateͬMySQLݵOracle - è - OracleMySQLϼ

 

2. װogg

Դˣ

su - mysql

unzip ggs_Linux_x64_MySQL_64bit.zip

tar xvf ggs_Linux_x64_MySQL_64bit.tar -C /home/mysql/ogg/

chmod u+x /home/mysql/ogg/ -R

cd /home/mysql/ogg

./ggsci

create subdirs

 

 

Ŀˣ

su - oracle

ѹoggװ

ͼν氲װGoldenGate

$ ./runInstaller

ʹGoldenGateͬMySQLݵOracle - è - OracleMySQLϼ 

ʹGoldenGateͬMySQLݵOracle - è - OracleMySQLϼ

 

װ󣬼ã

cd /home/oracle/ogg

./ggsci

create subdirs

 

 

 

 

 

Դ

1. MySQLGoldenGateʺ

grant select on *.* to ogg identified by 'ogg';

 

 

2. MySQL

log-bin

binlog-formatΪrow

oggֻ/tmp/mysql.sockMySQLmysql.sock/tmp£Ҫӣ£

ln -s /data/mysqldata/3306/mysql.sock /tmp/mysql.sock

 

 

3. manageṛ

cd /home/mysql/ogg

./ggsci

edit param mgr

ݣעⲻҪע͡--дУؽ̿ܲ

port 7809  --oggĬ϶˿

dynamicportlist 7840-7939  --Ĭ϶˿ڱռʱӶ̬˿бѡһ˿ڣ˿Ҫextract-replicat̶ԣָ256˿

--autostart ext1  --ָmgrʱЩ

purgeoldextracts /home/mysql/ogg/dirdat/*, usecheckpoints, minkeepdays 3  --ڳ3trailļɾ

lagreporthours 1   --mgrÿ1Сʱextractӳ

laginfominutes 30  --30ӾͰӳΪϢ¼־

lagcriticalminutes 60  --ӳٳ60ӣΪд־

upreporthours 1  --

 

mgr

start mgr

info mgr

 

 

4. extract

add extract ext1, tranlog, begin now--˴vamOracleΪtranlog

edit param ext1

extract ext1

sourcedb testdb@localhost:3306,userid ogg,password ogg   --˴Oracleͬ

tranlogoptions altlogdest /data/mysqldata/3306/binlog/mysql-bin.index--ָMySQL־λ

exttrail /home/mysql/ogg/dirdat/lg--ȡеλ

--dynamicresolution  --Ż̬ṹòѹʱɾ

--gettruncates     --ץȡtruncate

table testdb.t1;

 

extract trail

add exttrail /home/mysql/ogg/dirdat/lg, extract ext1

 

 

5. pump

add extract pump1, exttrailsource /home/mysql/ogg/dirdat/lg

 

pump

edit params pump1

extract pump1

passthru   --ֹextractݿ⽻Data Pump

rmthost 10.1.5.117, mgrport 7809, compress   --Ŀ˵ip˿

rmttrail /home/oracle/ogg/dirdat/lg--Ŀ˽־λ

--dynamicresolution  --Ż̬ṹɾ

table testdb.t1;   --ﻹԴ˵Ŀ

˳

 

Ŀpump

add rmttrail /home/oraclel/ogg/dirdat/lg, extract pump1

 

 

6. defgen

defgen

edit params t1  --˴defgenΪ

defsfile /home/mysql/ogg/dirdef/t1.prm

sourcedb testdb@localhost:3306,userid ogg,password ogg

table testdb.t1;

 

defgenļ

cd /home/mysql/ogg

./defgen paramfile/home/mysql/ogg/dirprm/t1.prm

ɵļ/home/mysql/ogg/dirdef/t1.prm

ɵļĿscp /home/mysql/ogg/dirdef/t1.prmoracle@10.1.5.117:/home/oracle/ogg/dirdef/t1.prm

 

 

 

 

Ŀ

1. ΪGoldenGateռ

create tablespace ogg datafile '/home/oracle/oradata/orcl/ogg01.dbf' size 50m autoextend on;

 

 

2. GoldenGateʺţΪʺȨ

create user ogg identified by ogg default tablespace ogg quota unlimited on ogg;

--grant connect,resource,alter session,alter any table,select any table,select any dictionary,flashback any table to ogg;

grant dba to ogg;

--grant EXECUTE ON utl_file TO ogg; ֧DDLͬʱҪڸȨ

 

 

3. checkpoint

./ggsci

dblogin userid ogg password ogg

add checkpointtable ogg.checkpoint

 

--ָcheckpoint

edit param globals

ggschema ogg

checkpointtable ogg.checkpoint

 

 

4. manageṛ

edit param mgr

port 7809  --oggĬ϶˿

dynamicportlist 7840-7939  --Ĭ϶˿ڱռʱӶ̬˿бѡһ˿ڣ˿Ҫextract-replicat̶ԣָ256˿

purgeoldextracts /home/oracle/ogg/dirdat/*, usecheckpoints, minkeepdays 3   --ڳ3trailļɾ

lagreporthours 1

laginfominutes 30

lagcriticalminutes 60

upreporthours 1  --

 

start mgr

info mgr

 

 

5. replicat

add replicat rep1, exttrail /home/oracle/ogg/dirdat/lg, checkpointtable ogg.checkpoint

edit param rep1

replicat rep1

sourcedefs /home/oracle/ogg/dirdef/t1.prm

discardfile /home/oracle/ogg/dirrpt/discard_rep1.log, append, megabytes 100

handlecollisions   

userid ogg, password ogg

map testdb.t1, target scott.t1;

 

 

 

 

 

ġʼ

MySQL

OracleϽձscott.t1

MySQLݵscott.t1

 

 

 

 

 

塢ʼͬ

1.

Դˣstart ext1

      start pump1

 

Ŀˣstart rep1

 

 

2. 鿴״̬

info all  --鿴н״̬

view report ext1 鿴Ϣ

 

 

 

 

 

Ҫ

1. ERROR   OGG-00146  Call to VAMRead returned with error status 600: VAM Client Report <CAUSE OF FAILURE : Failed

 to Query Metadata for Table : testdb.t1 AND Failed Queries Are: QUERY_FAILED_CHARSET_RETRIEVAL,QUERY_FAILED_SIGNED_INFO_RETRIEVAL,

WHEN FAILED : While Sending Insert and Delete Record

WHERE FAILED : MySQLBinLog Reader Module

CONTEXT OF FAILURE : No Information Available!>.

 

alter ext1 begin now

start ext1





 
 
Ķ(130)| (0)
Ƽ ת

ʷϵĽ

LOFTERĸ

<#--־Ⱥ־--> <#--Ƽ־--> <#--ü¼--> <#--Ƽ--> <#--Ķ--> <#--ҳƼ--> <#--ʷϵĽ--> <#--Ƽ־--> <#--һƪһƪ--> <#-- ȶ --> <#-- Ź --> <#--ұģṹ--> <#--ģṹ--> <#--ģṹ--> <#--ͶƱ-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

ҳ

ҵƬ - ͷ - ֻ - LOFTER APP - Ĵ˲

׹˾Ȩ ©1997-2017