OGG for oracle to mysql

红枣店铺:https://item.taobao.com/item.htm?id=525210310056
qq:45446263

(1)配置数据库归档
startup mount;
alter database archivelog;
alter database open;


(2)添加数据库附加日志
alter database add supplemental log data;
alter system switch logfile;


--不修改路径的话，oracle使用db_recovery_file_dest保存归档日志，路径是动态日期，OGG获取不到
alter system set log_archive_dest_1='location=/u01/app/arch'


(3)创建系统用户
useradd -g oinstall ggs
mkdir /ggs
chown ggs:oinstall  /ggs -R
[root@ibc ~]# passwd ggs


(4)配置环境变量
su - ggs
vi .bash_profile
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1
export ORACLE_SID=testic
export GG_HOME=/ggs
export PATH=$PATH:$ORACLE_HOME/bin:$GG_HOME
export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/lib:$GG_HOME
alias ggsci='cd $GG_HOME ; rlwrap ggsci'


source .bash_profile


(5)创建初始化目录
[ggs@ibc ggs]$ rz
rz waiting to receive.
Starting zmodem transfer.  Press Ctrl+C to cancel.
Transferring 121200_fbo_ggs_Linux_x64_shiphome.zip...
  100%  333316 KB    5290 KB/sec    00:01:03       0 Errors 


[ggs@ibc ggs]$ unzip 121200_fbo_ggs_Linux_x64_shiphome.zip
[ggs@ibc ggs]$ cd fbo_ggs_Linux_x64_shiphome/
[ggs@ibc fbo_ggs_Linux_x64_shiphome]$ ls
Disk1
[ggs@ibc fbo_ggs_Linux_x64_shiphome]$ cd Disk1/
[ggs@ibc Disk1]$ ./runInstaller   --使用xmanager进行安装，选择安装版本11G和安装目录/ggs/ggsoracle
cd /ggs/ggsoracle/
[ggs@ibc ggsoracle]$ ls
bcpfmt.tpl             ddl_session1.sql                 dirjar          libggperf.so        prvtclkm.plb
bcrypt.txt             ddl_session.sql                  dirout          libggrepo.so        pw_agent_util.sh
cachefiledump          ddl_setup.sql                    dirprm          libicudata.so.48    remove_seq.sql
cfg                    ddl_status.sql                   dirwww          libicudata.so.48.1  replicat
cfgtoollogs            ddl_staymetadata_off.sql         emsclnt         libicui18n.so.48    retrace
chkpt_ora_create.sql   ddl_staymetadata_on.sql          extract         libicui18n.so.48.1  reverse
convchk                ddl_tracelevel.sql               freeBSD.txt     libicuuc.so.48      role_setup.sql
convprm                ddl_trace_off.sql                ggcmd           libicuuc.so.48.1    sequence.sql
db2cntl.tpl            ddl_trace_on.sql                 ggMessage.dat   libxerces-c.so.28   server
ddl_cleartrace.sql     defgen                           ggsci           libxml2.txt         sqlldr.tpl
ddl_create.sql         deinstall                        help.txt        logdump             srvm
ddl_ddl2file.sql       demo_more_ora_create.sql         install         marker_remove.sql   tcperrs
ddl_disable.sql        demo_more_ora_insert.sql         inventory       marker_setup.sql    ucharset.h
ddl_enable.sql         demo_ora_create.sql              jagent.sh       marker_status.sql   ulg.sql
ddl_filter.sql         demo_ora_insert.sql              jdk             mgr                 UserExitExamples
ddl_ora10.sql          demo_ora_lob_create.sql          keygen          notices.txt         usrdecs.h
ddl_ora10upCommon.sql  demo_ora_misc.sql                label.sql       oggerr              zlib.txt
ddl_ora11.sql          demo_ora_pk_befores_create.sql   libantlr3c.so   OPatch
ddl_ora9.sql           demo_ora_pk_befores_insert.sql   libdb-5.2.so    oraInst.loc
ddl_pin.sql            demo_ora_pk_befores_updates.sql  libgglog.so     oui
ddl_remove.sql         diagnostics                      libggnnzitp.so  params.sql
[ggs@ibc ggsoracle]$ ./ggsci
GGSCI (ibc) 2> create subdirs




########################
####  配置抽取进程  ####
########################


(1) edit params mgr
PORT 7809
autorestart EXTRACT * , waitminutes 2 , resetminutes 5, retries 500000
autorestart REPLICAT * , waitminutes 2 , resetminutes 5, retries 500000 
autostart extract *
autostart replicat *


(2)对表添加传输日志
dblogin userid system , password testic


GGSCI (ibc) 2> add trandata gongjg.test_0827


2014-08-26 10:31:25  WARNING OGG-06439  No unique key is defined for table T_USERS. All viable columns will be used to represent the key, but may not guarantee uniqueness. KEYCOLS may be used to define the key.


Logging of supplemental redo data enabled for table GONGJG.T_USERS.
TRANDATA for scheduling columns has been added on table 'GONGJG.T_USERS'.
GGSCI (ibc) 3> add trandata gongjg.test_0826


2014-08-26 10:31:59  WARNING OGG-06439  No unique key is defined for table TEST_0826. All viable columns will be used to represent the key, but may not guarantee uniqueness. KEYCOLS may be used to define the key.


Logging of supplemental redo data enabled for table GONGJG.TEST_0826.
TRANDATA for scheduling columns has been added on table 'GONGJG.TEST_0826'.




(3)创建抽取进程


1、add extract extacc, tranlog, begin now, threads 1                          
2、edit params extacc    


extract extacc
setenv (NLS_LANG="AMERICAN_AMERICA.ZHS16GBK")
dynamicresolution
userid gongjg,password gjg#2012
exttrail /ggs/ggsoracle/dirdat/ac
table gongjg.test_0827;


--add exttrail ./dirdat/ac, extract extacc, megabytes 5
add exttrail  /ggs/ggsoracle/dirdat/ac, EXTRACT EXTACC


add extract pupacc,exttrailsource ./dirdat/ac
edit params pupacc
extract pupacc
setenv (NLS_LANG="AMERICAN_AMERICA.ZHS16GBK")
passthru
dynamicresolution
userid gongjg,password gjg
rmthost 192.168.6.151,mgrport 7809
rmttrail /ggs/dirdat/ac   --目标端的路径
table gongjg.*;


add rmttrail /ggs/dirdat/ac extract pupacc ,megabytes 5


start pupacc


add extract initacc,sourceistable
edit params initacc




extract initacc
setenv (NLS_LANG="AMERICAN_AMERICA.ZHS16GBK")
userid gongjg,password gjg
rmthost 192.168.6.151, MGRPORT 7809
rmttask replicat, group initacc
table gongjg.test_0827;




GGSCI (ibc) 1> edit params defacc


defsfile /ggs/ggsoracle/dirdef/defacc.prm
userid gongjg,password gjg
table gongjg.test_0827;


[ggs@ibc ggsoracle]$  /ggs/ggsoracle/defgen paramfile dirprm/defacc.prm
scp /ggs/ggsoracle/dirdef/defacc.prm  root@192.168.6.151:/ggs/dirdef/


https://item.taobao.com/item.htm?id=525210310056
qq:45446263

--源端
edit params ./dirprm/GLOBALS                                                                                                                            
CHECKPOINTTABLE gongjg.ggchkptable


--目标端
edit params ./dirprm/GLOBALS                                                                                                                            
CHECKPOINTTABLE gongjg.ggchkptable


dblogin sourcedb gongjg, userid gongjg, password gjg#2012
   add checkpointtable ggchkptable                                                                                                               


add replicat repacc, exttrail ./dirdat/ac CHECKPOINTTABLE ggchkptable




报 sys.props$ 不存在，给用户赋权




alter extract extacc,begin now




extract initacc
setenv (NLS_LANG="AMERICAN_AMERICA.ZHS16GBK")
userid gongjg,password gjg
rmthost 192.168.6.151, MGRPORT 7809
rmttask replicat, group initacc
table gongjg.test_0827;




[ggs@ibc dirprm]$ more mgr.prm
PORT 7809
autorestart EXTRACT * , waitminutes 2 , resetminutes 5, retries 500000
autorestart REPLICAT * , waitminutes 2 , resetminutes 5, retries 500000 
autostart extract *
autostart replicat *


[ggs@ibc dirprm]$ more pm_acc.prm
extract pm_acc
setenv (NLS_LANG="AMERICAN_AMERICA.ZHS16GBK")
passthru
dynamicresolution
userid gongjg,password gjg
rmthost 192.168.6.151,mgrport 7809
rmttrail /ggs/ggsoracle/dirdat/ac
table gongjg.*;




-----------------------------------------------------------
---目标端--------------------------------------------------
GGSCI (mysql-master) 4> create subdirs


Creating subdirectories under current directory /ggs


Parameter files                /ggs/dirprm: already exists
Report files                   /ggs/dirrpt: created
Checkpoint files               /ggs/dirchk: created
Process status files           /ggs/dirpcs: created
SQL script files               /ggs/dirsql: created
Database definitions files     /ggs/dirdef: created
Extract data files             /ggs/dirdat: created
Temporary files                /ggs/dirtmp: created
Credential store files         /ggs/dircrd: created
Masterkey wallet files         /ggs/dirwlt: created
Dump files                     /ggs/dirdmp: created


GGSCI (mysql-master) 7> edit params mgr
PORT 7809
autorestart EXTRACT * , waitminutes 2 , resetminutes 5, retries 500000
autorestart REPLICAT * , waitminutes 2 , resetminutes 5, retries 500000 
autostart extract *
autostart replicat *


GGSCI (mysql-master) 11> start mgr
Manager started.


GGSCI (mysql-master) 13> edit params initacc
replicat initacc
setenv (NLS_LANG="AMERICAN_AMERICA.ZHS16GBK")
sourcedefs /ggs/dirdef/defgen.prm
sourcedb gongjg@192.168.6.151:3306, USERID gongjg, password gjg#2012
HANDLECOLLISIONS
reperror default,discard
discardfile /ggs/dirrpt/repinit.dsc, append, megabytes 500000
map gongjg.test_0827, target gongjg.test_0827;
GGSCI (mysql-master) 3> edit params ./dirprm/GLOBALS
CHECKPOINTTABLE gongjg.ggchkptable


GGSCI (mysql-master) 7> dblogin sourcedb gongjg, userid gongjg, password gjg#2012
Successfully logged into database.


GGSCI (mysql-master) 8> add checkpointtable ggchkptable


Successfully created checkpoint table ggchkptable.


GGSCI (mysql-master) 9> add replicat repacc, exttrail ./dirdat/ac CHECKPOINTTABLE ggchkptable
REPLICAT added.


GGSCI (mysql-master) 11> edit params repacc
replicat repacc
setenv (NLS_LANG="AMERICAN_AMERICA.ZHS16GBK")
sourcedefs /ggs/dirdef/defacc.prm
sourcedb gongjg, userid gongjg, password gjg
HANDLECOLLISIONS
reperror default,discard
discardfile /ggs/dirrpt/repacc.dsc, append, megabytes 50
map gongjg.test_0827, target gongjg.test_0827;






GGSCI (mysql-master) 3> info all


Program     Status      Group       Lag at Chkpt  Time Since Chkpt


MANAGER     RUNNING                                          
REPLICAT    STOPPED     REPACC      00:00:00      22:58:21   




GGSCI (mysql-master) 4> start repacc


Sending START request to MANAGER ...
REPLICAT REPACC starting




GGSCI (mysql-master) 5> info all


Program     Status      Group       Lag at Chkpt  Time Since Chkpt


MANAGER     RUNNING                                          
REPLICAT    RUNNING     REPACC      00:00:00      00:00:09   

红枣店铺:https://item.taobao.com/item.htm?id=525210310056
qq:45446263

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/24172475/viewspace-1259292/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/24172475/viewspace-1259292/