利用ogg把oracle数据库迁移到mysql数据库，包括初始化，增量同步

背景： 源端： IP：192.168.31.190  oracle SID：wfmsit OGG同步用户：ogg 密码 oracle 同步的表xspay.trade_detail_his 目标端： IP：192.168.31.180  mysql 库：xspay OGG同步用户：ogg2 密码 oracle OGG版本：213000_fbo_ggs_Linux_x64_Oracle_shiphome.zip、213000_ggs_Linux_x64_MySQL_64bit.zip === 配置参数： 源端 GGSCI> edit params oracle_to_mysql ------------------------------------ defsfile dirdef/oracle_to_mysql.prm userid ogg@wfmsit,password oracle table xspay.trade_detail_his; [oracle@orasingle ogg]$ ./defgen paramfile dirprm/oracle_to_mysql.prm GGSCI > dblogin userid ogg@wfmsit, password oracle GGSCI >  edit params ./GLOBALS  checkpointtable xspay.checkpoint GGSCI> add checkpointtable xspay.checkpoint #在数据库中创建检查点记录表 GGSCI> info checkpointtable xspay.checkpoint GGSCI> add trandata   xspay.trade_detail_his -- ADD TRANDATA schema.tablename NOKEY 配置增量抽取进程： 源： GGSCI (dbtest4) 39> edit params mgr  port 7788 DYNAMICPORTLIST 7810-7909 autorestart extract *,waitminutes 2,retries 5 PURGEOLDEXTRACTS   ./dirdat/*,usecheckpoints, minkeepdays 2 LAGREPORTHOURS 1 LAGINFOMINUTES 30 LAGCRITICALMINUTES 45  GGSCI (dbtest4) 40> edit params ext1   extract ext1 setenv(ORACLE_SID="wfmsit") setenv(ORACLE_HOME="/u01/app/oracle/product/11.2.0.4/db_h") setenv(NLS_LANG=SIMPLIFIED CHINESE_CHINA.ZHS16GBK) GETTRUNCATES REPORTCOUNT EVERY 1 MINUTES, RATE DISCARDFILE ./dirrpt/ext1.dsc,APPEND,MEGABYTES 1000 WARNLONGTRANS 2h,CHECKINTERVAL 10m TRANLOGOPTIONS EXCLUDEUSER ogg #TRANLOGOPTIONS MINEFROMACTIVEDG DBOPTIONS ALLOWUNUSEDCOLUMN DYNAMICRESOLUTION FETCHOPTIONS FETCHPKUPDATECOLS,INCONSISTENTROW ALLOW #ddl include mapped objname xspay.* getupdatebefores nocompressdeletes nocompressupdates userid ogg@wfmsit,password oracle rmthost 192.168.31.190,mgrport 7788  exttrail ./dirdat/et table xspay.trade_detail_his; === GGSCI>  register extract ext1 database   GGSCI> add extract ext1, tranlog, begin now 添加抽取进程需要的文件 GGSCI> add exttrail ./dirdat/et,extract ext1,megabytes 100 投递进程配置文件 GGSCI (dbtest4) 41> edit params pump1 extract pump1 PASSTHRU DYNAMICRESOLUTION RMTHOST 192.168.31.180, MGRPORT 7788 RMTTRAIL ./dirdat/p1 table xspay.trade_detail_his; 添加投递进程 GGSCI> add extract pump1,exttrailsource ./dirdat/et, begin now 添加投递到远程的文件 GGSCI> add rmttrail ./dirdat/p1, extract pump1,megabytes 100 ===== 目标端： GGSCI (dbtest1-srv) 31> edit params mgr port 7788 DYNAMICPORTLIST 7810-7909 ACCESSRULE, PROG *, IPADDR 192.168.31.190,   ALLOW autorestart extract *,waitminutes 2,retries 5 purgeoldextracts ./dirdat/*,usecheckpoints,minkeephours 1,frequencyminutes 30 LAGREPORTHOURS 1 LAGINFOMINUTES 30 LAGCRITICALMINUTES 45 GGSCI> dblogin sourcedb  xspay,userid ogg2 password oracle GGSCI> dblogin sourcedb  xspay@192.168.31.180:3306,userid ogg2 password oracle GGSCI> add checkpointtable xspay.checkpoint_table 编辑复制参数文件 GGSCI (dbtest1-srv) 32> edit params rep1 replicat rep1  targetdb xspay,userid ogg2 password oracle #SQLEXEC "set tidb_constraint_check_in_place=1" handlecollisions MAXTRANSOPS 10000 sourcedefs /home/mysql/ogg/dirdef/oracle_to_mysql.prm discardfile /home/mysql/ogg/dirdat/rep1.dsc,append MAP xspay.trade_detail_his, TARGET xspay.trade_detail_his; GGSCI (dbtest1-srv) 9> add replicat rep1,exttrail /home/mysql/ogg/dirdat/p1,checkpointtable xspay.checkpoint_table  ============= 配置全量抽取 源端： GGSCI > dblogin userid ogg@wfmsit, password oracle GGSCI > edit param   init_ora extract init_ora userid ogg@wfmsit,password oracle setenv(ORACLE_SID="wfmsit") setenv(ORACLE_HOME="/u01/app/oracle/product/11.2.0.4/db_h") setenv(NLS_LANG=SIMPLIFIED CHINESE_CHINA.ZHS16GBK) rmthost 192.168.31.190,mgrport 7788  rmttask replicat,group rnit_ora table xspay.trade_detail_his; extract exta userid ggadmin, password Oracle1 rmthost oelmysql, mgrport 7809 RMTTASK REPLICAT, GROUP rinita reportcount every 60 seconds, rate table user.tb1, filter (id < 500000);   replicat rinita userid gguser, password Oracle1 discardfile ./dirrpt/lmysql.dsc, purge reportcount every 60 seconds, rate map user1.tb1, target scott.tb1;  GGSCI>  add extract init_ora,sourceistable 配置全量应用进程 GGSCI> dblogin sourcedb  xspay,userid ogg2 password oracle GGSCI> edit param   rnit_ora replicat rnit_ora targetdb  xspay,userid ogg2 password oracle discardfile /home/mysql/ogg/dirrpt/rnit_ora.dsc,purge MAP xspay.trade_detail_his, TARGET xspay.trade_detail_his; GGSCI>  add replicat rnit_ora,specialrun 启动源端和目标端的mgr star mgr 启动源端抽取和传输进程 start ext1 start pump1 启动目标端的应用进程 start rep1 启动完毕后可以在源端新增个数据测试，测试完后记得把这个数据删除，因为这个动作会记录到全量进程里 启动全量同步进 start init_ora   会自动启动全量应用进程