异构表的同步

 1 什么是defgen ？
   它是一个工具，用来获取远端表结构的


2、


GGSCI (rac1) 8> view params mgr
port 7809


GGSCI (rac1) 9> view params exta
extract exta
userid goldengate,password goldengate
rmthost 192.168.1.5,mgrport 7809
exttrail ./dirdat/aa
table scott.t1



add extract  exta, tranlog, begin now,threads 1
add exttrail ./dirdat/aa,extract  exta


GGSCI (rac1) 13> view params dpa


extract dpa
passthru
rmthost 192.168.1.5，mgrport 7809
rmttrail /ggs/dirdat/aa
table scott.*;


GGSCI (rac1) 11> add extract dpa exttrailsource /ggs/dirdat/aa
EXTRACT added.
GGSCI (rac1) 12> add rmttrail /ggs/dirdat/aa,extract dpa
RMTTRAIL added.



目标端：
GGSCI (test) 5> add replicat repa,exttrail /ggs/dirdat/aa,nodbcheckpoint
REPLICAT added.


到此源端和目标端已经是数据同步状态：


源端：
SQL> create table t2 as select * from emp where 0=1;
Table created.
SQL> ALTER TABLE t2 add constraints pk_t2 primary key(empno);
Table altered.


SQL> desc t2
 Name       Null? Type
 ----------------------------------------------------- -------- ------------------------------------
 EMPNO       NOT NULL NUMBER(4)
 ENAME VARCHAR2(10)
 JOB VARCHAR2(9)
 MGR NUMBER(4)
 HIREDATE DATE
 SAL NUMBER(7,2)
 COMM NUMBER(7,2)
 DEPTNO NUMBER(2)




目标端：
SQL> create table t2 as select empno,ename,deptno,sal from emp where 0=1;
Table created.
SQL> alter table t2 add constraints pk_t2 primary key (empno);
Table altered.


SQL> desc t2
 Name   Null?    Type
 ----------------------------------------- -------- ----------------------------
 EMPNO   NOT NULL NUMBER(4)
 ENAME    VARCHAR2(10)
 DEPTNO    NUMBER(2)
 SAL    NUMBER(7,2)




写参数文件：


GGSCI (rac1) 78> view params defgen


defsfile ./dirsql/t2.sql
userid goldengate password goldengate
table scott.t2






[oracle@rac1 ggs]$ ./defgen paramfile /ggs/dirprm/defgen.prm


***********************************************************************
        Oracle GoldenGate Table Definition Generator for Oracle
     Version 11.1.1.1.2 OGGCORE_11.1.1.1.2_PLATFORMS_111004.2100 
   Linux, x64, 64bit (optimized), Oracle 11g on Oct  5 2011 00:08:57
 
Copyright (C) 1995, 2011, Oracle and/or its affiliates. All rights reserved.




                    Starting at 2013-06-16 23:44:04
***********************************************************************


Operating System Version:
Linux
Version #1 SMP Tue Mar 16 21:52:39 EDT 2010, Release 2.6.18-194.el5
Node: rac1
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: 5157


***********************************************************************
**            Running with the following parameters                  **
***********************************************************************
defsfile ./dirsql/t2.sql
userid goldengate password **********
table scott.t2;
Retrieving definition for SCOTT.T2




Definitions generated for 1 tables in ./dirsql/t2.sql




[oracle@rac1 dirsql]$ cat t2.sql 
*
* Definitions created/modified  2013-06-16 23:44
*
*  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
*
*
Definition for table SCOTT.T2
Record length: 112
Syskey: 0
Columns: 8
EMPNO     134      8        0  0  0 1 0      8      8      8 0 0 0 0 1    0 1 3
ENAME      64     10       12  0  0 1 0     10     10      0 0 0 0 0 1    0 0 0
JOB        64      9       28  0  0 1 0      9      9      0 0 0 0 0 1    0 0 0
MGR       134      8       42  0  0 1 0      8      8      8 0 0 0 0 1    0 0 3
HIREDATE  192     19       54  0  0 1 0     19     19     19 0 5 0 0 1    0 0 0
SAL       134      9       76  2  0 1 0      8      8      8 0 0 0 0 1    0 0 3
COMM      134      9       88  2  0 1 0      8      8      8 0 0 0 0 1    0 0 3
DEPTNO    134      8      100  0  0 1 0      8      8      8 0 0 0 0 1    0 0 3
End of 
definition

把产生的文件ftp或者scp到目标端去：
[oracle@rac1 dirsql]$ scp t2.sql oracle@192.168.1.5:/ggs/dirsql/


源端：
GGSCI (rac1) 2> add extract extb,sourceistable
EXTRACT added.


GGSCI (rac1) 2> view params extb


extract extb
userid goldengate,password goldengate
rmthost 192.168.1.5,mgrport 7809
rmttask replicat,group repb
table scott.t2;


添加完进程后，info all是查看不到进程的，需要注意的是源端用 sourceistable


目标端：
GGSCI (test) 66> add replicat repb specialrun
REPLICAT added.


GGSCI (test) 74> view params repb


replicat repb
userid goldengate ,password goldengate
sourcedefs ./dirsql/t2.sql
map scott.t2, target scott.t2,
colmap (--注意此处要有一个空格，否则报错
empno=empno, ename=ename,deptno=deptno,sal=sal)
;



远端插入数据
insert  into t2 select * from emp；


启动进程 start extract  extb


view report extb
Output to repb:
From Table SCOTT.T2:
       #                   inserts:        14
       #                   updates:         0
       #                   deletes:         0
       #                  discards:         0
REDO Log Statistics
  Bytes parsed                    0
  Bytes output                 2546

http://gavinsoorma.com/2011/08/goldengate-replication-using-a-data-definition-file-and-defgen-utility/