ogg mysql to oracle_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/，如需转载，请注明出处，否则将追究法律责任。