利用OracleGoldenGate(ogg) 从Oracle同步数据到MySQL

案例A：增加增量同步

1.增加extract进程

dblogin userid GOLDENGATE@{{ Oracle server url }},password {{ Oracle Password }}
register extract E02 database
add extract E02,integrated tranlog, begin now
add EXTTRAIL /data/ogg19oracle/dirdat/eb, extract E02,MEGABYTES 100
add schematrandata {{ oracle.Schema }}

编辑ogg文件

• vim /data/ogg19oracle/dirprm/e02.prm

extract e02
setenv (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)
userid GOLDENGATE@{{ Oracle server url }},password {{ Oracle Password }}
exttrail /data/ogg19oracle/dirdat/eb
GETUPDATEAFTERS
GETUPDATEBEFORES
NOCOMPRESSDELETES
NOCOMPRESSUPDATES
table {{ SCOTT.AAAA }};
table {{ SCOTT.BBBB }};

2.增加pump进程

add extract p02 exttrailsource /data/ogg19oracle/dirdat/eb
add rmttrail /data/ogg19mysql/dirdat/eb, extract p02

3.增加replicat进程

dblogin sourcedb goldengate@{{ MySQL server url }}, userid ogg,password {{ MySQL server password }}
add checkpointtable goldengate.checkpoint
add replicat r02,exttrail /data/ogg19mysql/dirdat/eb,checkpointtable goldengate.checkpoint

编辑rep参数

• vim /data/ogg19mysql/dirprm/r02.prm

 #mysql rep 参数文件写入
replicat r02
targetdb goldengate@{{ MySQL server url }}, userid ogg,password {{ MySQL server password }}
sourcedefs /data/ogg19mysql/dirdef/ora_to_my_r02.def override
discardfile /data/ogg19mysql/dirrpt/r02.dsc,append,megabytes 1024
MAP {{ SCOTT.AAAA }} ,target {{ mytest.aaaa }};
MAP {{ SCOTT.BBBB }} ,target {{ mytest.bbbb }};

编辑ora_to_my参数

• vim /data/ogg19oracle/dirprm/ora_to_my_r02.prm

defsfile /data/ogg19mysql/dirdef/ora_to_my_r02.def
userid GOLDENGATE@{{ Oracle server url }},password {{ Oracle Password }}
table {{ SCOTT.AAAA }};
table {{ SCOTT.BBBB }};

案例B：增加全量同步

1.抽取进程

编辑param

• vim /data/ogg19oracle/dirprm/E02_in.prm

extract E02_in
userid GOLDENGATE@{{ Oracle server url }},password {{ Oracle Password }}
rmthost {{ ogg oracle serverip }},mgrport 7869,COMPRESS
RMTFILE /data/ogg19mysql/dirdat/sb
reportcount every 60 seconds, rate
table {{ SCOTT.AAAA }};
table {{ SCOTT.BBBB }};

增加进程

add extract E02_in,SourceisTable
start E02_in

2.订阅进程

编辑param

• vim /data/ogg19mysql/dirprm/r02_in.prm

replicat r02_in
targetdb goldengate@{{ MySQL server url }}, userid ogg,password {{ MySQL server password }}
sourcedefs /data/ogg19mysql/dirdef/ora_to_my_r02.def override
discardfile /data/ogg19mysql/dirrpt/r02_in.dsc,append,megabytes 1024
MAP {{ SCOTT.AAAA }} ,target {{ mytest.aaaa }};
MAP {{ SCOTT.BBBB }} ,target {{ mytest.bbbb }};

增加进程

dblogin sourcedb goldengate@{{ MySQL server url }}, userid ogg,password {{ MySQL server password }}
add replicat r02_in,exttrail /data/ogg19mysql/dirdat/sb,NODBCHECKPOINT
start r02_in

3.验证

select count(*) from  {{ mytest.aaaa }};
select count(*) from  {{ mytest.bbbb }};

常见错误：

SQL error 1062 mapping to [SQL error 1062]Duplicate entry for key

• 常见于初始化时，增量同步和全量同步的时间点冲突
• 解决方法是在订阅参数里加上：HANDLECOLLISIONS