不同表结构OGG数据同步

                               不同表结构OGG数据同步

如果目标表和源表的表结构不一致，则同步需要使用defgen:

源表表结构:

create table test_pri(id number primarykey, name varchar2(10),name1 varchar2(10),name2 varchar2(10),name3 varchar2(10));

目标表表结构:

create table test_pri(id number primarykey, name varchar2(10),name1 varchar2(10));

 

1 源端操作

--编辑defgen

GGSCI(oggs) 1> edit params defgen

defsfile /u01/ggs/test_pri.p

userid ggs,password ggs

table ggs.test_pri;

 

[oracle@oggs11.2.0]$ pwd

/u01/ggs/11.2.0

--生成defgen.prm

[oracle@oggs11.2.0]$ ./defgen paramfile/u01/ggs/11.2.0/dirprm/defgen.prm

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

       Oracle GoldenGate Table Definition Generator for Oracle

     Version 11.2.1.0.1OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230

  Linux, x64, 64bit (optimized), Oracle 11g on Apr 23 2012 05:08:19

 

Copyright (C) 1995, 2012, Oracle and/or itsaffiliates. All rights reserved.

 

 

                    Starting at 2016-02-2115:41:48

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

 

Operating System Version:

Linux

Version #1 SMP Wed Feb 22 17:37:40 EST2012, Release 2.6.32-300.10.1.el5uek

Node: oggs

Machine: x86_64

                         soft limit   hard limit

Address Space Size   :   unlimited    unlimited

Heap Size            :   unlimited    unlimited

File Size            :   unlimited    unlimited

CPU Time             :    unlimited   unlimited

 

Process id: 11019

 

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

**            Running with the followingparameters                  **

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

defsfile /u01/ggs/test_pri.p

userid ggs,password ***

table ggs.test_pri;

Retrieving definition for GGS.TEST_PRI

--将TEST_PRI发送到目标库

[oracle@oggs11.2.0]$ scp ../test_pri.p oracle@192.168.127.101:/u01/ggs

Nasty PTR record"192.168.127.101" is set up for 192.168.127.101, ignoring

oracle@192.168.127.101's password:

test_pri.p                                                                                       100% 1225     1.2KB/s   00:00

--编辑ext1,排除不需要同步的列

GGSCI(oggs) 66> edit params ext1

extract ext1

dynamicresolution

userid ggs,password ggs

setenv(ORACLE_SID=oggs)

exttrail /u01/ggs/11.2.0/dirdat/et

table ggs.test_pri, COLSEXCEPT (NAME2,NAME3);

 

GGSCI(oggs) 67> edit params pump1

extract pump1

dynamicresolution

userid ggs,password ggs

rmthost 192.168.127.101,mgrport7809,compress

rmttrail /u01/ggs/11.2.0/dirdat/pt

table ggs.test_pri;

 

 

2 目标端操作

GGSCI(oggt) 41> edit params repl

replicat repl

userid ggs,password ggs

assumetargetdefs

reperror default,discard

discardfile /u01/ggs/11.2.0/dirrpt/repl.dsc,append,megabytes50

dynamicresolution

sourcedefs /u01/ggs/test_pri.p

map ggs.test_pri,target ggs.test_pri;

 

3 源端和目标端分别操作

重新启动源端ext1,pump1(startext1   start  pump1)

重新启动目标端 repl(startrepl)

 

 

4 源端表结构变更注意问题

如果源端表结构发生变化，则会导致目标端接收进程repl中断，需要在表结构变化后重新在源端生成defgen，并且传送给目标端

[oracle@oggs11.2.0]$ pwd

/u01/ggs/11.2.0

[oracle@oggs11.2.0]$ ./defgen paramfile/u01/ggs/11.2.0/dirprm/defgen.prm

[oracle@oggs11.2.0]$ scp ../test_pri.poracle@192.168.127.101:/u01/ggs