总步骤:(注意 rep进程要等初始化进程ok之后再指定seq和rba启动,mysql端大小写敏感,

exp pump标准的格式为数据库名小写,表名大写)

1、创建物化视图

2、搭建ogg

3、开启增量ext(提前记录seq rba)

4、初始化数据(ext端和rep端 只用ext端开启)

5、指定seq rba进行pump

6、增量rep 指定 seq rba进行恢复



1、将需要同步的表通过dblink和同义词+物化视图的方式同步到backup数据库

1.1 创建dblink(对于增量刷新的只能创建访问主库的dblink)


1.2 创建用户和 物化视图


--创建表空间和用户

create tablespace ogg datafile '/OEM12c_data/OEM12c/ogg01.dbf' size 20G autoextend on;

alter tablespace ogg add datafile '/OEM12c_data/OEM12c/ogg02.dbf' size 5G;


--创建物化视图日志


create tablespace viewlog datafile '+DATA/upmd/viewlog001.dbf' size 10G autoextend on;


--创建物化视图(切换到本端相应用户)


create materialized view table

refresh fast on DEMAND

start with sysdate next sysdate+1/288 WITH PRIMARY KEY

as select * from table@dblink;


2、在backup上搭建ogg

---源库

1)开启附加日志

[oracle@ubackup ogg]$ ./ggsci 

./ggsci: error while loading shared libraries: libnnz11.so: cannot open shared object file: No such file or directory

[oracle@ubackup ogg]$ export  LD_LIBRARY_PATH=$ORACLE_HOME/lib

SQL> select supplemental_log_data_min from v$database;

SUPPLEME

--------

NO

SQL> alter database add supplemental log data;

Database altered.

SQL> select supplemental_log_data_min from v$database;

SUPPLEME

--------

YES

---2)开归档(已经是归档模式,省略)

SQL> alter system set log_archive_dest_1='location=/u01/archivelog';

SQL> shutdown immediate

SQL> startup mount

SQL> alter database archivelog;

SQL> archive log list;

Database log mode             Archive Mode

Automatic archival            Enabled

Archive destination           /u01/arch

SQL> alter database open;

---3)开启强制写日志(可以不开启,最好开启)

SQL> select force_logging from v$database;

        FOR

        ------

        NO

SQL> alter database force logging;

SQL> select force_logging from v$database;

        FOR

        ------

        YES

---4)创建用户并授权

SQL> create tablespace ggtbs datafile '/u01/oradata/db1/ggtbs01.dbf' size 200m autoextend on;

SQL> create user ogg identified by ogg default tablespace ggtbs quota unlimited on ggtbs;

SQL> grant connect,resource,create session,alter session to ogg;

SQL> grant select any dictionary,select any table, alter any table, create any table,flashback any table to ogg;

SQL> grant execute on dbms_flashback to ogg;

---自己的

create tablespace ogg datafile '/OEM12c_data/OEM12c/ogg01.dbf' size 20G autoextend on;

alter tablespace ogg add datafile '/OEM12c_data/OEM12c/ogg02.dbf' size 5G;

create user ogg identified by "Order_OGG"  default tablespace ogg;

grant resource,create session,CREATE MATERIALIZED VIEW,ON COMMIT REFRESH,dba  to ogg;

--------11.2之后需要

ALTER SYSTEM SET ENABLE_GOLDENGATE_REPLICATION = TRUE SCOPE=BOTH;

exec dbms_goldengate_auth.grant_admin_privilege('ogg','*',TRUE);

[oracle@ubackup ogg]$ ./ggsci 

Oracle GoldenGate Command Interpreter for Oracle

Version 12.2.0.1.1 OGGCORE_12.2.0.1.0_PLATFORMS_151211.1401_FBO

Linux, x64, 64bit (optimized), Oracle 11g on Dec 12 2015 00:54:38

Operating system character set identified as UTF-8.

Copyright (C) 1995, 2015, Oracle and/or its affiliates. All rights reserved.

---5创建目录

GGSCI (ubackup) 1> create subdirs

Creating subdirectories under current directory /u01/app/oracle/ogg

Parameter files                /u01/app/oracle/ogg/dirprm: already exists

Report files                   /u01/app/oracle/ogg/dirrpt: already exists

Checkpoint files               /u01/app/oracle/ogg/dirchk: already exists

Process status files           /u01/app/oracle/ogg/dirpcs: already exists

SQL script files               /u01/app/oracle/ogg/dirsql: already exists

Database definitions files     /u01/app/oracle/ogg/dirdef: already exists

Extract data files             /u01/app/oracle/ogg/dirdat: already exists

Temporary files                /u01/app/oracle/ogg/dirtmp: already exists

Credential store files         /u01/app/oracle/ogg/dircrd: already exists

Masterkey wallet files         /u01/app/oracle/ogg/dirwlt: already exists

Dump files                     /u01/app/oracle/ogg/dirdmp: already exists

GGSCI (ubackup) 2> 

---6)为表级添加附加日志

GGSCI (pos300-db01) 2> dblogin userid ogg

        Password:Order_OGG 

        Successfully logged into database.

GGSCI (pos300-db01) 3> 

add trandata ogg_voucher.* 

add trandata ogg_cmf.*

add trandata ogg_trade.*

add trandata ogg_payment.*

add trandata ogg_dpm.*

add trandata ogg_member.*

add trandata ogg_weihuiadm.*

---7)创建管理进程,并启动

./ggsci

GGSCI (pos300-db01) 1> edit params mgr

PORT 7809

autorestart er *, retries 5, waitminutes 3

purgeoldextracts /u01/app/oracle/ogg/dirdat/*,usecheckpoints, minkeepdays 2

GGSCI (pos300-db01) 2> start mgr

        Manager started.

GGSCI (pos300-db01) 3> info mgr

        Manager is running (IP port pos300-db01.7809, Process ID 2897).

---8)extract进程,并启动

GGSCI (pos300-db01) 2> edit params dce1

extract dce1

setenv (NLS_LANG="AMERICAN_AMERICA.UTF8") 

setenv (ORACLE_HOME = "/u01/app/oracle/product/11.2/db_1")

setenv (ORACLE_SID = "OEM12c")

userid ogg, password Order_OGG

exttrail /u01/app/oracle/ogg/dirdat/e1

discardfile  /u01/app/oracle/ogg/dirrpt/dce1.dsc,append

numfiles 3000

dynamicresolution

TABLE ogg_voucher.T_TRADE_VOUCHER;


GGSCI (pos300-db01) 3> add extract dce1,tranlog,begin now

GGSCI (pos300-db01) 4> add exttrail /u01/app/oracle/ogg/dirdat/e1,extract dce1,megabytes 5 

GGSCI (pos300-db01) 5> start dce1

GGSCI (pos300-db01) 2> edit params DCE2

extract dce2

setenv (NLS_LANG="AMERICAN_AMERICA.UTF8") 

setenv (ORACLE_HOME = "/u01/app/oracle/product/11.2/db_1")

setenv (ORACLE_SID = "OEM12c")

userid ogg, password Order_OGG

exttrail /u01/app/oracle/ogg/dirdat/e2

discardfile  /u01/app/oracle/ogg/dirrpt/dce2.dsc,append

numfiles 3000

dynamicresolution

TABLE ogg_payment.TB_PAYMENT_ORDER;

GGSCI (pos300-db01) 3> add extract dce2,tranlog,begin now

GGSCI (pos300-db01) 4> add exttrail /u01/app/oracle/ogg/dirdat/e2,extract dce2,megabytes 5 

GGSCI (pos300-db01) 5> start DCE2

GGSCI (pos300-db01) 2> edit params DCE3

extract dce3

setenv (NLS_LANG="AMERICAN_AMERICA.UTF8") 

setenv (ORACLE_HOME = "/u01/app/oracle/product/11.2/db_1")

setenv (ORACLE_SID = "OEM12c")

userid ogg, password Order_OGG

exttrail /u01/app/oracle/ogg/dirdat/e3

discardfile  /u01/app/oracle/ogg/dirrpt/dce3.dsc,append

numfiles 3000

dynamicresolution

TABLE ogg_cmf.TT_CMF_ORDER;

TABLE ogg_cmf.TT_INST_ORDER;

TABLE ogg_cmf.TM_FUND_CHANNEL;

GGSCI (pos300-db01) 3> add extract dce3,tranlog,begin now

GGSCI (pos300-db01) 4> add exttrail /u01/app/oracle/ogg/dirdat/e3,extract dce3,megabytes 5 

GGSCI (pos300-db01) 5> start DCE3

GGSCI (pos300-db01) 2> edit params DCE4

extract dce4

setenv (NLS_LANG="AMERICAN_AMERICA.UTF8") 

setenv (ORACLE_HOME = "/u01/app/oracle/product/11.2/db_1")

setenv (ORACLE_SID = "OEM12c")

userid ogg, password Order_OGG

exttrail /u01/app/oracle/ogg/dirdat/e4

discardfile  /u01/app/oracle/ogg/dirrpt/dce4.dsc,append

numfiles 3000

dynamicresolution

TABLE ogg_trade.T_PAYMENT_ORDER;

TABLE ogg_trade.T_TRADE_ORDER;

GGSCI (pos300-db01) 3> add extract dce4,tranlog,begin now

GGSCI (pos300-db01) 4> add exttrail /u01/app/oracle/ogg/dirdat/e4,extract dce4,megabytes 5 

GGSCI (pos300-db01) 5> start DCE4

GGSCI (pos300-db01) 2> edit params DCE5_dpm

extract DCE5_dpm

setenv (NLS_LANG="AMERICAN_AMERICA.UTF8") 

setenv (ORACLE_HOME = "/u01/app/oracle/product/11.2/db_1")

setenv (ORACLE_SID = "OEM12c")

userid ogg, password Order_OGG

exttrail /u01/app/oracle/ogg/dirdat/e5

discardfile  /u01/app/oracle/ogg/dirrpt/dce5.dsc,append

numfiles 3000

dynamicresolution

TABLE ogg_dpm.T_DPM_OUTER_ACCOUNT_DETAIL;

GGSCI (pos300-db01) 3> add extract DCE5_dpm,tranlog,begin now

GGSCI (pos300-db01) 4> add exttrail /u01/app/oracle/ogg/dirdat/e5,extract DCE5_dpm,megabytes 5 

GGSCI (pos300-db01) 5> start DCE5_dpm

GGSCI (pos300-db01) 2> edit params DCE6_mem

extract DCE6_mem

setenv (NLS_LANG="AMERICAN_AMERICA.UTF8") 

setenv (ORACLE_HOME = "/u01/app/oracle/product/11.2/db_1")

setenv (ORACLE_SID = "OEM12c")

userid ogg, password Order_OGG

exttrail /u01/app/oracle/ogg/dirdat/e6

discardfile  /u01/app/oracle/ogg/dirrpt/dce6.dsc,append

numfiles 3000

dynamicresolution

TABLE ogg_member.TM_MEMBER;

GGSCI (pos300-db01) 3> add extract DCE6_mem,tranlog,begin now

GGSCI (pos300-db01) 4> add exttrail /u01/app/oracle/ogg/dirdat/e6,extract DCE6_mem,megabytes 5 

GGSCI (pos300-db01) 5> start DCE6_mem

GGSCI (pos300-db01) 2> edit params DCE7_wei

extract DCE7_wei

setenv (NLS_LANG="AMERICAN_AMERICA.UTF8") 

setenv (ORACLE_HOME = "/u01/app/oracle/product/11.2/db_1")

setenv (ORACLE_SID = "OEM12c")

userid ogg, password Order_OGG

exttrail /u01/app/oracle/ogg/dirdat/e7

discardfile  /u01/app/oracle/ogg/dirrpt/dce7.dsc,append

numfiles 3000

dynamicresolution

TABLE ogg_weihuiadm.TB_FEE_CONFIG;

GGSCI (pos300-db01) 3> add extract DCE7_wei,tranlog,begin now

GGSCI (pos300-db01) 4> add exttrail /u01/app/oracle/ogg/dirdat/e7,extract DCE7_wei,megabytes 5 

GGSCI (pos300-db01) 5> start DCE7_wei

9)pump进程,并启动

>GGSCI (pos300-db01) 6> edit params DCP1

extract dcp1

passthru

SETENV (NLS_LANG = "AMERICAN_AMERICA.UTF8") 

userid ogg, password Order_OGG

rmthost  ip,mgrport 7809,compress

rmttrail  /opt/ggmysql/dirdat/p1

dynamicresolution

numfiles 3000

TABLE ogg_voucher.T_TRADE_VOUCHER;


GGSCI (pos300-db01) 7> add extract dcp1,exttrailsource /u01/app/oracle/ogg/dirdat/e1

GGSCI (pos300-db01) 8> add rmttrail /opt/ggmysql/dirdat/p1,extract dcp1,megabytes 5 

GGSCI (pos300-db01) 9> start DCP1

>GGSCI (pos300-db01) 6> edit params DCP2

extract dcp2

passthru

SETENV (NLS_LANG = "AMERICAN_AMERICA.UTF8") 

userid ogg, password Order_OGG

rmthost ip,mgrport 7809,compress

rmttrail  /opt/ggmysql/dirdat/p2

dynamicresolution

numfiles 3000

TABLE ogg_payment.TB_PAYMENT_ORDER;

GGSCI (pos300-db01) 7> add extract dcp2,exttrailsource /u01/app/oracle/ogg/dirdat/e2

GGSCI (pos300-db01) 8> add rmttrail /opt/ggmysql/dirdat/p2,extract dcp2,megabytes 5

GGSCI (pos300-db01) 9> start DCP2

>GGSCI (pos300-db01) 6> edit params DCP3

extract dcp3

passthru

SETENV (NLS_LANG = "AMERICAN_AMERICA.UTF8") 

userid ogg, password Order_OGG

rmthost  ip,mgrport 7809,compress

rmttrail  /opt/ggmysql/dirdat/p3

dynamicresolution

numfiles 3000

TABLE ogg_cmf.TT_CMF_ORDER;

TABLE ogg_cmf.TT_INST_ORDER;

TABLE ogg_cmf.TM_FUND_CHANNEL;

GGSCI (pos300-db01) 7> add extract dcp3,exttrailsource /u01/app/oracle/ogg/dirdat/e3

GGSCI (pos300-db01) 8> add rmttrail /opt/ggmysql/dirdat/p3,extract dcp3,megabytes 5

GGSCI (pos300-db01) 9> start DCP3

>GGSCI (pos300-db01) 6> edit params DCP4

extract dcp4

passthru

SETENV (NLS_LANG = "AMERICAN_AMERICA.UTF8") 

userid ogg, password Order_OGG

rmthost  ip,mgrport 7809,compress

rmttrail  /opt/ggmysql/dirdat/p4

dynamicresolution

numfiles 3000

TABLE ogg_trade.T_PAYMENT_ORDER;

TABLE ogg_trade.T_TRADE_ORDER;

GGSCI (pos300-db01) 7> add extract dcp4,exttrailsource /u01/app/oracle/ogg/dirdat/e4

GGSCI (pos300-db01) 8> add rmttrail /opt/ggmysql/dirdat/p4,extract dcp4,megabytes 5

GGSCI (pos300-db01) 9> start DCP4

>GGSCI (pos300-db01) 6> edit params DCP5_dpm

extract DCP5_dpm

passthru

SETENV (NLS_LANG = "AMERICAN_AMERICA.UTF8") 

userid ogg, password Order_OGG

rmthost  ip,mgrport 7809,compress

rmttrail  /opt/ggmysql/dirdat/p5

dynamicresolution

numfiles 3000

TABLE ogg_dpm.T_DPM_OUTER_ACCOUNT_DETAIL;

GGSCI (pos300-db01) 7> add extract DCP5_dpm,exttrailsource /u01/app/oracle/ogg/dirdat/e5

GGSCI (pos300-db01) 8> add rmttrail /opt/ggmysql/dirdat/p5,extract DCP5_dpm,megabytes 5

GGSCI (pos300-db01) 9> start DCP5_dpm

>GGSCI (pos300-db01) 6> edit params DCP6_mem

extract DCP6_mem

passthru

SETENV (NLS_LANG = "AMERICAN_AMERICA.UTF8") 

userid ogg, password Order_OGG

rmthost  ip,mgrport 7809,compress

rmttrail  /opt/ggmysql/dirdat/p6

dynamicresolution

numfiles 3000

TABLE ogg_member.TM_MEMBER;

GGSCI (pos300-db01) 7> add extract DCP6_mem,exttrailsource /u01/app/oracle/ogg/dirdat/e6

GGSCI (pos300-db01) 8> add rmttrail /opt/ggmysql/dirdat/p6,extract DCP6_mem,megabytes 5

GGSCI (pos300-db01) 9> start DCP6_mem

>GGSCI (pos300-db01) 6> edit params DCP7_wei

extract DCP7_wei

passthru

SETENV (NLS_LANG = "AMERICAN_AMERICA.UTF8") 

userid ogg, password Order_OGG

rmthost  ip,mgrport 7809,compress

rmttrail  /opt/ggmysql/dirdat/p7

dynamicresolution

numfiles 3000

TABLE ogg_weihuiadm.TB_FEE_CONFIG;

GGSCI (pos300-db01) 7> add extract DCP7_wei,exttrailsource /u01/app/oracle/ogg/dirdat/e7

GGSCI (pos300-db01) 8> add rmttrail /opt/ggmysql/dirdat/p7,extract DCP7_wei,megabytes 5

GGSCI (pos300-db01) 9> start DCP7_wei

---10)创建要同步的表的定义

#创建参数文件

GGSCI (pos300-db01) 10>edit param zhifu01

defsfile ./dirdef/zhifu01.def

userid ogg, password Order_OGG

TABLE ogg_voucher.T_TRADE_VOUCHER;

TABLE ogg_voucher.T_TRADE_VOUCHER_01;

TABLE ogg_voucher.T_TRADE_VOUCHER_02;

TABLE ogg_voucher.T_TRADE_VOUCHER_03;

TABLE ogg_voucher.T_TRADE_VOUCHER_04;

TABLE ogg_voucher.T_TRADE_VOUCHER_05;

TABLE ogg_voucher.T_TRADE_VOUCHER_06;

TABLE ogg_voucher.T_TRADE_VOUCHER_07;

TABLE ogg_voucher.T_TRADE_VOUCHER_08;

TABLE ogg_voucher.T_TRADE_VOUCHER_09;

#生成表定义文件

 ./defgen paramfile ./dirprm/zhifu01.prm

#传至目标库目录

GGSCI (pos300-db01) 10>edit param zhifu02

defsfile ./dirdef/zhifu02.def

userid ogg, password Order_OGG

TABLE ogg_payment.TB_PAYMENT_ORDER;

#生成表定义文件

 ./defgen paramfile ./dirprm/zhifu02.prm

GGSCI (pos300-db01) 10>edit param zhifu03

defsfile ./dirdef/zhifu03.def

userid ogg, password Order_OGG

TABLE ogg_cmf.TT_CMF_ORDER;

TABLE ogg_cmf.TT_INST_ORDER;

TABLE ogg_cmf.TM_FUND_CHANNEL;

#生成表定义文件

 ./defgen paramfile ./dirprm/zhifu03.prm

GGSCI (pos300-db01) 10>edit param zhifu04

defsfile ./dirdef/zhifu04.def

userid ogg, password Order_OGG

TABLE ogg_trade.T_PAYMENT_ORDER;

TABLE ogg_trade.T_TRADE_ORDER;

#生成表定义文件

 ./defgen paramfile ./dirprm/zhifu04.prm

GGSCI (pos300-db01) 10>edit param 05dpm

defsfile ./dirdef/05dpm.def

userid ogg, password Order_OGG

TABLE ogg_dpm.T_DPM_OUTER_ACCOUNT_DETAIL;

#生成表定义文件

 ./defgen paramfile ./dirprm/05dpm.prm

GGSCI (pos300-db01) 10>edit param 06member

defsfile ./dirdef/06member.def

userid ogg, password Order_OGG

TABLE ogg_member.TM_MEMBER;

#生成表定义文件

 ./defgen paramfile ./dirprm/06member.prm

GGSCI (pos300-db01) 10>edit param 07weihuiadm

defsfile ./dirdef/07weihuiadm.def

userid ogg, password Order_OGG

TABLE ogg_weihuiadm.TB_FEE_CONFIG;

#生成表定义文件

 ./defgen paramfile ./dirprm/07weihuiadm.prm

---mysql端需要创建相应的表

通过powerdesigner16.5 

***************************************************************************************

初始化数据

***************************************************************************************

---1)extract进程,并启动

GGSCI (oracle3) 10>edit param ext_1

extract ext_1

SETENV (NLS_LANG = "AMERICAN_AMERICA.UTF8")

userid ogg, password Order_OGG

rmthost ip,mgrport 7809

rmttask replicat,group tvoucher

TABLE ogg_voucher.T_TRADE_VOUCHER;

TABLE ogg_voucher.T_TRADE_VOUCHER_01;

TABLE ogg_voucher.T_TRADE_VOUCHER_02;

TABLE ogg_voucher.T_TRADE_VOUCHER_03;

TABLE ogg_voucher.T_TRADE_VOUCHER_04;

TABLE ogg_voucher.T_TRADE_VOUCHER_05;

TABLE ogg_voucher.T_TRADE_VOUCHER_06;

TABLE ogg_voucher.T_TRADE_VOUCHER_07;

TABLE ogg_voucher.T_TRADE_VOUCHER_08;

TABLE ogg_voucher.T_TRADE_VOUCHER_09;

GGSCI (oracle3) 10>add extract  ext_1,sourceistable

GGSCI (oracle3) 10>edit param ext_2

extract ext_2

SETENV (NLS_LANG = "AMERICAN_AMERICA.UTF8")

userid ogg, password Order_OGG

rmthost ip,mgrport 7809

rmttask replicat,group tpayment

TABLE ogg_payment.TB_PAYMENT_ORDER;

GGSCI (oracle3) 10>add extract  ext_2,sourceistable

GGSCI (oracle3) 10>edit param ext_3

extract ext_3

SETENV (NLS_LANG = "AMERICAN_AMERICA.UTF8")

userid ogg, password Order_OGG

rmthost ip,mgrport 7809

rmttask replicat,group tcmf

TABLE ogg_cmf.TT_CMF_ORDER;

TABLE ogg_cmf.TT_INST_ORDER;

TABLE ogg_cmf.TM_FUND_CHANNEL;

GGSCI (oracle3) 10>add extract  ext_3,sourceistable

GGSCI (oracle3) 10>edit param ext_4

extract ext_4

SETENV (NLS_LANG = "AMERICAN_AMERICA.UTF8")

userid ogg, password Order_OGG

rmthost ip,mgrport 7809

rmttask replicat,group ttrade

TABLE ogg_trade.T_PAYMENT_ORDER;

TABLE ogg_trade.T_TRADE_ORDER;

GGSCI (oracle3) 10>add extract  ext_4,sourceistable

GGSCI (oracle3) 10>edit param ext_5dpm

extract ext_5dpm

SETENV (NLS_LANG = "AMERICAN_AMERICA.UTF8")

userid ogg, password Order_OGG

rmthost ip,mgrport 7809

rmttask replicat,group tdpm

TABLE ogg_dpm.T_DPM_OUTER_ACCOUNT_DETAIL;

GGSCI (oracle3) 10>add extract  ext_5dpm,sourceistable

GGSCI (oracle3) 10>start  ext_5dpm

GGSCI (oracle3) 10>view report ext_5dpm

GGSCI (oracle3) 10>edit param ext_6mem

extract ext_6mem

SETENV (NLS_LANG = "AMERICAN_AMERICA.UTF8")

userid ogg, password Order_OGG

rmthost ip,mgrport 7809

rmttask replicat,group tmem

TABLE ogg_member.TM_MEMBER;

GGSCI (oracle3) 10>add extract  ext_6mem,sourceistable

GGSCI (oracle3) 10>start  ext_6mem

GGSCI (oracle3) 10>edit param ext_7wei

extract ext_7wei

SETENV (NLS_LANG = "AMERICAN_AMERICA.UTF8")

userid ogg, password Order_OGG

rmthost ip,mgrport 7809

rmttask replicat,group twei

TABLE ogg_weihuiadm.TB_FEE_CONFIG;

GGSCI (oracle3) 10>add extract  ext_7wei,sourceistable

GGSCI (oracle3) 10>start  ext_7wei

--------复制端(以tpayment为例)------------------------

replicat tpayment

targetdb payment@ip:3311 userid ogg password zufangbao

handlecollisions

sourcedefs /opt/ggmysql/dirprm/ogg_payment.def

map "OGG_PAYMENT"."TB_PAYMENT_ORDER",target payment.tb_payment_order;

ADD REPLICAT initial-load_Replicat, SPECIALRUN

*************************************

3、目标库

************************************

目标库为Mysql, 同oracle与oracle之间的同步略有区别

1)创建用户

mysql> GRANT ALL PRIVILEGES ON `gmdc`.* TO 'ogg'@'%'  IDENTIFIED BY ‘ogg'

2)创建目录cd /home/mysql

mkdir goldengate/

chown mysql:mysql ./goldengate

unzip ggs_121210_Linux_x64_MySQL_64bit.zip

tar xvf ggs_Linux_x64_MySQL_64bit.tar

./ggsci

GGSCI (zoop300) 1> create subdirs

Creating subdirectories under current directory /home/mysql

Parameter files                /goldengate/dirprm: created

Report files                   /goldengate/dirrpt: created

Checkpoint files               /goldengate/dirchk: created

Process status files           /goldengate/dirpcs: created

SQL script files               /goldengate/dirsql: created

Database definitions files     /goldengate/dirdef: created

Extract data files             /goldengate/dirdat: created

Temporary files                /goldengate/dirtmp: created

Stdout files                   /goldengate/dirout: created

3)配置管理进程

GGSCI (zoop300) 3>  edit params mgr

port 7809

dynamicportlist 7840-7939

autorestart er *, retries 5, waitminutes 3

purgeoldextracts /home/goldengate/dirdat/*,usecheckpoints, minkeepdays 2

GGSCI (zoop300) 4> start mgr

        Manager started.

GGSCI (zoop300) 3> info mgr

        Manager is running (IP port zoop300.7809, Process ID 2897).

4)配置检查点及全局

GGSCI (zoop300) 4>  dblogin sourcedb gmdc@192.168.7.221:3306 userid ogg password ogg

GGSCI (zoop300) 5>  add checkpointtable gmdc.checkpoint_table

GGSCI (zoop300) 6>  edit params ./GLOBALS

checkpointtable gmdc.checkpoint_table

5)配置replicat进程<pre name="code">GGSCI (zoop300) 7>  edit params dcr1

replicat dcr1

targetdb gmdc@192.168.7.221:3306 userid ogg password ogg

handlecollisions

sourcedefs /home/mysql/goldengate/dirprm/gmdc.def

discardfile /home/mysql/goldengate/dirrpt/dcr1.dsc,purge

map gmdc.ck,target gmdc.CK;

map gmdc.cksp,target gmdc.CKSP;

map gmdc.dcclz,target gmdc.DCCLZ;

map gmdc.dcckcl,target gmdc.DCCKCL;

map gmdc.dgdcsp,target gmdc.DGDCSP;

GGSCI (zoop300) 8>  add replicat dcr1,exttrail /home/mysql/goldengate/dirdat/p1,checkpointtable gmdc.checkpoint_table

GGSCI (zoop300) 9> start dcr1

4、测试数据同步

说明:注意mysql中表名区分大小写 修改

---------------同步sequnce extraba----------

alter extract dumpname, extseqno xx, extrba 0

alter replicat trade,extseqno 0,extrba 0

1、修改rba,checkpoint

     alter EXT_ALO,extseqno 17,extrba 9194144,thread 1

     alter EXT_ALO,extseqno 10,extrba 5740032,thread 2

2、修改recovery checkpoint

alter EXT_ALO,ioextseqno 17,ioextrba 9193488,thread 1

     alter  EXT_ALO,ioextseqno 10,ioextrba 5739536,thread 2

3、info extract cust_ext, detail   --抽取进程的详细信息

4、info extract ext*, showch  --抽取进程的检查点信息,注意checkpoint和recovery checkpoint

5、INFO TRANDATA user_name.table_names  --表的补充日志

6、常用添加extract命令

ggsci> add extract ext1, tranlog, begin now

ggsci> add extract ext1, tranlog, begin now, threads 4

ggsci> add extract ext1, tranlog, begin now, passive

ggsci> add extract ext1, extseqno 111, begin now

ggsci> add extract ext1, extrba 567890, begin 2012-02-02 12:00:00

ggsci> add extract ext1, sourceistable

ggsci> add extract ext1, exttrailsource /oracle/gg11/dirdat/hr   --data pump

ggsci> add extract ext1, vam                        -- VAM - Vendor Access Module

ggsci> add extract ext1, vamtrailsource /ogg/dirdat/vt

ggsci> add extract ext1, rmthost host123, mgrport 7810, rmtname fin

6、status

ggsci> STATUS MANAGER    -- To determine whether or not the Manager process is running

ggsci> STATUS EXTRACT group_name [, TASKS | ALLPROCESSES]   -- To determine whether or not Extract is running

ggsci> status extract extr_hr

ggsci> status extract ext*, tasks

ggsci> status extract *ext*, allprocesses

ggsci> STATUS REPLICAT group_name [, TASKS | ALLPROCESSES]  -- To determine whether or not Replicat is running

ggsci> status replicat emp_rep

ggsci> status replicat cust_rep, allprocesses

ggsci> STATUS ER group_wildcard_specification   -- To check the status of multiple Extract and Replicat groups as a unit

ggsci> status er *EX*

--------------——————————————--12.2会报的错--------------

CAUSE

Trying to read trail file which uses 6 digit checkpoint with version 12.2.

Version 12.2 uses a 9 digit checkpoint

?

ERROR OGG-06601 Mismatch between the length of seqno from checkpoint (9) and recovery (6) for extract trail T:\GoldenGate\dirdat\waats\1R.

?

SOLUTION

PART I

(1) Stop PUMP

(2) Stop Manager

(3) Add the following to your GLOBALS file

TRAIL_SEQLEN_6D

REASON: Tell GG to use 6 digit checkpoint

(4) Start Manager

(5) Alter Pump with ETROLLOVER

(6) Start Pump

(7) Allow PUMP to read local trail file and write them to a remote trail file

(8) Allow replicat to process all transactions.

Replicat should show 0 lags to indicate all transactions , from the source, have been processed on the target database.