配置DML方式复制,源数据库添加extract组并配置参数
GGSCI (localdg) 1> add extract eora,tranlog,begin now [,thread 1] --[,thread 1]可缺省
如果是RAC,比如双节点,则为threads 2
启用tranlog日志 (mssql和Oracle是用tranlog,mysql用的是vam),立即开始
GGSCI (localdg) 2> info extract *
EXTRACT EORA Initialized 2015-12-07 17:10 Status STOPPED
Checkpoint Lag 00:00:00 (updated 00:01:20 ago)
Log Read Checkpoint Oracle Redo Logs
2015-12-07 17:10:08 Seqno 0, RBA 0
SCN 0.0 (0)
GGSCI (localdg) 3> edit params eora
extract eora
userid system,password ORAcle_123
rmthost 192.168.40.96,mgrport 7809 --mssql用的是7815端口
rmttrail ./dirdat/rt
table wxuser.t_test;
由于没有配置PUMP抽取进程,此处直接指定远端trail路径,本地不配置trail路径
添加源库rmttrail并启动extract
GGSCI (localdg) 5> add rmttrail ./dirdat/rt,extract eora,megabytes 5
RMTTRAIL added.
此操作执行后,会在远端创建一个rt00000文件,如果已经有了,那么继续使用原来的rt000000文件,megabytes指定TRAIL文件大小的上限,此处是5M,默认是10M一个文件
GGSCI (localdg) 6> info rmttrail *
Extract Trail: ./dirdat/rt
Extract: EORA
Seqno: 0
RBA: 0
File Size: 5M
GGSCI (localdg) 8> start extract eora
Sending START request to MANAGER ...
EXTRACT EORA starting
GGSCI (localdg) 9> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EORA 00:00:00 00:05:45
配置目标库全局参数文件./GLOBALS,并添加checkpointtable
GGSCI (fk-db1) 1> edit params ./GLOBALS
checkpointtable ogg.chkpt
GGSCI (fk-db1) 2> dblogin userid system,password ORAcle_123
Successfully logged into database.
GGSCI (fk-db1) 3> add checkpointtable
ERROR: Missing checkpoint table specification.
GGSCI (fk-db1) 4> add checkpointtable ogg.chkpt
Successfully created checkpoint table ogg.chkpt.
GGSCI (fk-db1) 8> add replicat rora exttrail ./dirdat/rt checkpointtable ogg.chkpt --配置本地trail路径,该路径是在extract eora中配置rmttrail时指定的那个路径
REPLICAT added.
GGSCI (fk-db1) 9> edit params rora
replicat rora
userid system,password ORAcle_123
handlecollisions --冲突自动处理
assumetargetdefs --源和目标是同构的,如果异构,此处需指定sourcedefs ./dirdef\xxx.def
reperror default,discard --对错误的响应,可以不配置,默认为写入指定的discardfile
discardfile./dirrpt/rora.dsc,purge --也可以把purge改成append,megabytes 5
map wxuser.*, target fkuser.*;
GGSCI (fk-db1) 10> start replicat rora
Sending START request to MANAGER ...
REPLICAT RORA starting
GGSCI (fk-db1) 11> view report rora
查看是否有出错
GGSCI (fk-db1) 21> info rora
REPLICAT RORA Last Started 2015-12-07 17:30 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:00:05 ago)
Log Read Checkpoint File ./dirdat/rt000000
First Record RBA 1013
源库启动extract进程eora
GGSCI (localdg) 10> start extract eora
至此,数据同步配置已经,完成。可以进行数据增、删、改测试
注:以上的测试环境没有配置pump,而在实际生产环境中,一般都要再配置一个pump的extract进程,这样可以网络环境不是很稳定的情况下,保证OGG传输的可靠性,这个机制大致是:在先本地trail文件保存变更并提交的事务信息,然后可以断点续传到目标库,再由目标库的replicat进程完成同步复制,只要源库trail不被删除,网络通畅时就可以同步到目标库
GGSCI (localdg) 1> add extract eora,tranlog,begin now [,thread 1] --[,thread 1]可缺省
如果是RAC,比如双节点,则为threads 2
启用tranlog日志 (mssql和Oracle是用tranlog,mysql用的是vam),立即开始
GGSCI (localdg) 2> info extract *
EXTRACT EORA Initialized 2015-12-07 17:10 Status STOPPED
Checkpoint Lag 00:00:00 (updated 00:01:20 ago)
Log Read Checkpoint Oracle Redo Logs
2015-12-07 17:10:08 Seqno 0, RBA 0
SCN 0.0 (0)
GGSCI (localdg) 3> edit params eora
extract eora
userid system,password ORAcle_123
rmthost 192.168.40.96,mgrport 7809 --mssql用的是7815端口
rmttrail ./dirdat/rt
table wxuser.t_test;
由于没有配置PUMP抽取进程,此处直接指定远端trail路径,本地不配置trail路径
添加源库rmttrail并启动extract
GGSCI (localdg) 5> add rmttrail ./dirdat/rt,extract eora,megabytes 5
RMTTRAIL added.
此操作执行后,会在远端创建一个rt00000文件,如果已经有了,那么继续使用原来的rt000000文件,megabytes指定TRAIL文件大小的上限,此处是5M,默认是10M一个文件
GGSCI (localdg) 6> info rmttrail *
Extract Trail: ./dirdat/rt
Extract: EORA
Seqno: 0
RBA: 0
File Size: 5M
GGSCI (localdg) 8> start extract eora
Sending START request to MANAGER ...
EXTRACT EORA starting
GGSCI (localdg) 9> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EORA 00:00:00 00:05:45
配置目标库全局参数文件./GLOBALS,并添加checkpointtable
GGSCI (fk-db1) 1> edit params ./GLOBALS
checkpointtable ogg.chkpt
GGSCI (fk-db1) 2> dblogin userid system,password ORAcle_123
Successfully logged into database.
GGSCI (fk-db1) 3> add checkpointtable
ERROR: Missing checkpoint table specification.
GGSCI (fk-db1) 4> add checkpointtable ogg.chkpt
Successfully created checkpoint table ogg.chkpt.
GGSCI (fk-db1) 8> add replicat rora exttrail ./dirdat/rt checkpointtable ogg.chkpt --配置本地trail路径,该路径是在extract eora中配置rmttrail时指定的那个路径
REPLICAT added.
GGSCI (fk-db1) 9> edit params rora
replicat rora
userid system,password ORAcle_123
handlecollisions --冲突自动处理
assumetargetdefs --源和目标是同构的,如果异构,此处需指定sourcedefs ./dirdef\xxx.def
reperror default,discard --对错误的响应,可以不配置,默认为写入指定的discardfile
discardfile./dirrpt/rora.dsc,purge --也可以把purge改成append,megabytes 5
map wxuser.*, target fkuser.*;
GGSCI (fk-db1) 10> start replicat rora
Sending START request to MANAGER ...
REPLICAT RORA starting
GGSCI (fk-db1) 11> view report rora
查看是否有出错
GGSCI (fk-db1) 21> info rora
REPLICAT RORA Last Started 2015-12-07 17:30 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:00:05 ago)
Log Read Checkpoint File ./dirdat/rt000000
First Record RBA 1013
源库启动extract进程eora
GGSCI (localdg) 10> start extract eora
至此,数据同步配置已经,完成。可以进行数据增、删、改测试
注:以上的测试环境没有配置pump,而在实际生产环境中,一般都要再配置一个pump的extract进程,这样可以网络环境不是很稳定的情况下,保证OGG传输的可靠性,这个机制大致是:在先本地trail文件保存变更并提交的事务信息,然后可以断点续传到目标库,再由目标库的replicat进程完成同步复制,只要源库trail不被删除,网络通畅时就可以同步到目标库