golendgate(32)--defgen

defgen

源端：

1.表结构

SQL> desc gis_test.baobao14;

Name Type         Nullable Default Comments

---- ------------ -------- ------- --------

PXID NUMBER                                

FANG VARCHAR2(10) Y                        

TDD  VARCHAR2(20) Y                        

AWR  VARCHAR2(15) Y

 

alter table gis_test.baobao14 add constraint pk_pxid primary key (pxid);

 

目标端：

 

1. 表结构

SQL> desc gis_test.baobao14;

Name Type         Nullable Default Comments

---- ------------ -------- ------- --------

PXID NUMBER                                

FANG VARCHAR2(10) Y 

 

alter table gis_test.baobao14 add constraint pk_pxid primary key (pxid);

  

 

源端与目标端表gis_test.baobao14结构不相同  

 

如何同步数据！       

 

使用defgen生成源端定义文件来同步数据。  

 

源端操作：

 

GGSCI (oggmiddle) 19> edit params defgen

 

DEFSFILE /u01/app/oracle/ggs/dirdef/baobao14.def

USERID ggs@dbking, PASSWORD ggs

TABLE GIS_TEST.BAOBAO14;

保存.

 

GGSCI (oggmiddle) 20>exit

[oracle@oggmiddle ggs]$ pwd

/u01/app/oracle/ggs

 

[oracle@oggmiddle ggs]$ defgen paramfile /u01/app/oracle/ggs/dirprm/defgen.prm

 

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

        Oracle GoldenGate Table Definition Generator for Oracle

      Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230

   Linux, x86, 32bit (optimized), Oracle 11g on Apr 23 2012 05:00:20

 

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

 

 

                    Starting at 2013-07-22 21:47:45

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

 

Operating System Version:

Linux

Version #1 SMP Tue Aug 18 15:51:54 EDT 2009, Release 2.6.18-164.el5

Node: oggmiddle

Machine: i686

                         soft limit   hard limit

Address Space Size   :    unlimited    unlimited

Heap Size            :    unlimited    unlimited

File Size            :    unlimited    unlimited

CPU Time             :    unlimited    unlimited

 

Process id: 31907

 

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

**            Running with the following parameters                  **

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

DEFSFILE /u01/app/oracle/ggs/dirdef/baobao14.def

USERID ggs@dbking, PASSWORD ***

TABLE GIS_TEST.BAOBAO14;

Retrieving definition for GIS_TEST.BAOBAO14

 

Definitions generated for 1 table in /u01/app/oracle/ggs/dirdef/baobao14.def

 

生成def文件baobao14.def

oracle@oggmiddle dirdef]$ cat baobao14.def

*+- Defgen version 2.0, Encoding UTF-8

*

* Definitions created/modified  2013-07-22 21:47

*

*  Field descriptions for each column entry:

*

*     1    Name

*     2    Data Type

*     3    External Length

*     4    Fetch Offset

*     5    Scale

*     6    Level

*     7    Null

*     8    Bump if Odd

*     9    Internal Length

*    10    Binary Length

*    11    Table Length

*    12    Most Significant DT

*    13    Least Significant DT

*    14    High Precision

*    15    Low Precision

*    16    Elementary Item

*    17    Occurs

*    18    Key Column

*    19    Sub Data Type

*

Database type: ORACLE

Character set ID: windows-936

National character set ID: UTF-16

Locale: neutral

Case sensitivity: 14 14 14 14 14 14 14 14 14 14 14 14 11 14 14 14

*

Definition for table GIS_TEST.BAOBAO14

Record length: 118

Syskey: 0

Columns: 4

PXID   64     50        0  0  0 1 0     50     50     50 0 0 0 0 1    0 1 2

FANG   64     10       56  0  0 1 0     10     10      0 0 0 0 0 1    0 0 0

TDD    64     20       72  0  0 1 0     20     20      0 0 0 0 0 1    0 0 0

AWR    64     15       98  0  0 1 0     15     15      0 0 0 0 0 1    0 0 0

End of definition

 

上面的内容是biaobao14的源端定义文件的内容.

 

以ASCII格式ftp到目标端，在目标端replicat定义.

 

抽取进程配置：

 

GGSCI (oggmiddle) 6> view params s_ex_mid

 

EXTRACT s_ex_mid

-- SETENV (NLS_LANG = CHINESE_CHINA.ZHS16GBK)

SETENV (ORACLE_HOME = "/u01/app/oracle/product/11.2.0/dbhome_1")

SETENV (ORACLE_SID = "dbking")

USERID ggs@dbking, PASSWORD ggs

TRANLOGOPTIONS ARCHIVEDLOGONLY

-- TRANLOGOPTIONS ASMUSERsys@asm, ASMPASSWORD grid sysdba

TRANLOGOPTIONS DBLOGREADER

TRANLOGOPTIONS ALTARCHIVELOGDEST INSTANCE dbking /arch/archivelog

TRANLOGOPTIONS DBLOGREADERBUFSIZE 1048576

EXTTRAIL /u01/app/oracle/ggs/dirdat/trail/cc

TABLE gis_test.baobao14, COLSEXCEPT (tdd,awr);

 

or

TABLE gis_test.baobao14;

 

GGSCI (oggmiddle) 6>add trandata gis_test.baobao14

 

投递进程配置：

 

GGSCI (oggmiddle) 7> view params pump_un

 

EXTRACT pump_un

USERID ggs@dbking, PASSWORD ggs

RMTHOST 10.23.5.71, MGRPORT 7809

RMTTRAIL /u01/app/oracle/ggs/dirdat/rtrail/pp

PASSTHRU

TABLE gis_test.baobao14;

 

 

复制进程配置：

 

GGSCI (primary) 5> view params rep_li

 

REPLICAT rep_li

USERID ggs, PASSWORD ggs

ASSUMETARGETDEFS

DISCARDFILE /u01/app/oracle/ggs/dirrpt/discard/rep.dsc, APPEND

SOURCEDEFS /u01/app/oracle/ggs/dirdef/baobao14.def

MAP gis_test.baobao14, TARGET gis_test.baobao14;

 

or

MAP gis_test.baobao14, TARGET gis_test.baobao14,

colmap (

pxid=pxid, fang=fang)

;

 

重启OGG进程.

 

测试：

 

源端插入数据：

insert into gis_test.baobao14 values (1000,'a','b','c');

insert into gis_test.baobao14 values (2000,'b','c','e');

insert into gis_test.baobao14 values (3000,'c','d','f');

commit;

 

         PXID     FANG   TDD     AWR

            1000    a          b            c

            2000    b          c            e

            3000    c          d            f

 

目标端查看：

 

 

select * from gis_test.baobao14;

         PXID     FANG

            1000    a

            2000    b

            3000    c

 

成功复制PXID,FANG两列的数据，其他的列没有复制.

 

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

转载于:http://blog.itpub.net/21266384/viewspace-766881/