GoldenGate将Oracle数据同步到GreenPlum

源端
Oracle 11.2.0.4 RAC 2-Nodes
Oracle Linux 5.8 x86_64

Oracle GoldenGate V11.1.1.0.0 for Oracle 11g on Linux x86-64.zip

Oracle GoldenGate V11.1.1.0.0 For FlatFile on Linux 64-bit for OGG v11.1.1.0.0.zip

目标端
Greenplum Database 4.2.6.1 4-Nodes

CentOS 5.7 x86_64

Oracle GoldenGate V11.1.1.0.0 for Oracle 11g on Linux x86-64.zip

Oracle GoldenGate V11.1.1.0.0 For FlatFile on Linux 64-bit for OGG v11.1.1.0.0.zip

======================================================
源端配置


--本例是将OGG安装在ACFS共享存储上，方便RAC节点故障OGG的切换
--ACFS信息
[root@vzwc1 ~]# acfsutil info fs
/ggs
    ACFS Version: 11.2.0.4.0
    flags:        MountPoint,Available
    mount time:   Sat Feb 15 14:33:22 2014
    volumes:      1
    total size:   8589934592
    total free:   8453541888
    primary volume: /dev/asm/ggs-11
        label:                 
        flags:                 Primary,Available,ADVM
        on-disk version:       39.0
        allocation unit:       4096
        major, minor:          252, 5633
        size:                  8589934592
        free:                  8453541888
        ADVM diskgroup         OGGDG
        ADVM resize increment: 33554432
        ADVM redundancy:       mirror
        ADVM stripe columns:   4
        ADVM stripe width:     131072
    number of snapshots:  0
    snapshot space usage: 0
    replication status: DISABLED
[root@vzwc1 ~]# 
[root@vzwc1 ~]# df -Th
Filesystem    Type    Size  Used Avail Use% Mounted on
/dev/mapper/VolGroup00-LogVol00
              ext3     44G   15G   28G  35% /
/dev/sda1     ext3     99M   24M   71M  25% /boot
tmpfs        tmpfs    1.5G  944M  564M  63% /dev/shm
/dev/asm/ggs-11
              acfs    8.0G  131M  7.9G   2% /ggs
 
 
 
--源端RAC信息
[root@vzwc1 ~]# crsctl query crs activeversion
Oracle Clusterware active version on the cluster is [11.2.0.4.0]
[root@vzwc1 ~]# crsctl query crs releaseversion
Oracle High Availability Services release version on the local node is [11.2.0.4.0]
[root@vzwc1 ~]# crsctl query crs softwareversion -all
Oracle Clusterware version on node [vzwc1] is [11.2.0.4.0]
Oracle Clusterware version on node [vzwc2] is [11.2.0.4.0]
[root@vzwc1 ~]# 
[root@vzwc1 ~]# crsctl stat res -t
--------------------------------------------------------------------------------
NAME           TARGET  STATE        SERVER                   STATE_DETAILS       
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.CRSDG.dg
               ONLINE  ONLINE       vzwc1                                        
               ONLINE  ONLINE       vzwc2                                        
ora.DATADG.dg
               ONLINE  ONLINE       vzwc1                                        
               ONLINE  ONLINE       vzwc2                                        
ora.FRADG.dg
               ONLINE  ONLINE       vzwc1                                        
               ONLINE  ONLINE       vzwc2                                        
ora.LISTENER.lsnr
               ONLINE  ONLINE       vzwc1                                        
               ONLINE  ONLINE       vzwc2                                        
ora.OGGDG.dg
               ONLINE  ONLINE       vzwc1                                        
               ONLINE  ONLINE       vzwc2                                        
ora.SYSDG.dg
               ONLINE  ONLINE       vzwc1                                        
               ONLINE  ONLINE       vzwc2                                        
ora.asm
               ONLINE  ONLINE       vzwc1                    Started             
               ONLINE  ONLINE       vzwc2                    Started             
ora.gsd
               OFFLINE OFFLINE      vzwc1                                        
               OFFLINE OFFLINE      vzwc2                                        
ora.net1.network
               ONLINE  ONLINE       vzwc1                                        
               ONLINE  ONLINE       vzwc2                                        
ora.ons
               ONLINE  ONLINE       vzwc1                                        
               ONLINE  ONLINE       vzwc2                                        
ora.registry.acfs
               ONLINE  ONLINE       vzwc1                                        
               ONLINE  ONLINE       vzwc2                                        
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.LISTENER_SCAN1.lsnr
      1        ONLINE  ONLINE       vzwc1                                        
ora.cvu
      1        ONLINE  ONLINE       vzwc1                                        
ora.oc4j
      1        ONLINE  ONLINE       vzwc1                                        
ora.scan1.vip
      1        ONLINE  ONLINE       vzwc1                                        
ora.vzwc1.vip
      1        ONLINE  ONLINE       vzwc1                                        
ora.vzwc2.vip
      1        ONLINE  ONLINE       vzwc2                                        
ora.zwc.db
      1        ONLINE  ONLINE       vzwc1                    Open                
      2        ONLINE  ONLINE       vzwc2                    Open                
 
 
 
--配置oracle用户环境变量，添加OGG配置
[oracle@vzwc1 ~]$ cat .bash_profile 
# .bash_profile


# Get the aliases and functions
if [ -f ~/.bashrc ]; then
        . ~/.bashrc
fi


# User specific environment and startup programs


PATH=$PATH:$HOME/bin


export PATH


alias ls="ls -FA"
GG_HOME=/ggs/ogg; export GG_HOME
ORACLE_SID=zwc1; export ORACLE_SID
ORACLE_UNQNAME=zwc; export ORACLE_UNQNAME
JAVA_HOME=/usr/local/java; export JAVA_HOME
ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/11.2.0/dbhome_1; export ORACLE_HOME
ORACLE_PATH=/u01/app/common/oracle/sql; export ORACLE_PATH
TNS_ADMIN=$ORACLE_HOME/network/admin; export TNS_ADMIN
ORA_NLS11=$ORACLE_HOME/nls/data; export ORA_NLS11
NLS_LANG=AMERICAN_AMERICA.ZHS16GBK; export NLS_LANG
PATH=.:${JAVA_HOME}/bin:${PATH}:$HOME/bin:$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:$GG_HOME
PATH=${PATH}:/usr/bin:/bin:/usr/bin/X11:/usr/local/bin
PATH=${PATH}:/u01/app/common/oracle/bin
export PATH
LD_LIBRARY_PATH=$ORACLE_HOME/lib:$GG_HOME
LD_LIBRARY_PATH=${LD_LIBRARY_PATH}:$ORACLE_HOME/oracm/lib
LD_LIBRARY_PATH=${LD_LIBRARY_PATH}:/lib:/usr/lib:/usr/local/lib
export LD_LIBRARY_PATH
CLASSPATH=$ORACLE_HOME/JRE
CLASSPATH=${CLASSPATH}:$ORACLE_HOME/jlib
CLASSPATH=${CLASSPATH}:$ORACLE_HOME/rdbms/jlib
CLASSPATH=${CLASSPATH}:$ORACLE_HOME/network/jlib
export CLASSPATH
THREADS_FLAG=native; export THREADS_FLAG
export TEMP=/tmp
export TMPDIR=/tmp
umask 022


alias sqlplus="rlwrap sqlplus"
alias rman="rlwrap rman"




--编辑添加RAC1和RAC2 oracle用户的tnsnames.ora文件，支持ASM访问
[oracle@vzwc1 ~]$ cat $ORACLE_HOME/network/admin/tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.


ZWC =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = vzwc-cluster-scan)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = zwc)
    )
  )


ASM =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = vzwc1-vip)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = +ASM)
      (INSTANCE_NAME = +ASM1)
      (UR=A)
    )
  )
[oracle@vzwc1 ~]$ ssh vzwc2 cat $ORACLE_HOME/network/admin/tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.


ZWC =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = vzwc-cluster-scan)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = zwc)
    )
  )


ASM =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = vzwc2-vip)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = +ASM)
      (INSTANCE_NAME = +ASM2)
      (UR=A)
    )
  )


  


--开启SUPPLEMENTAL LOG
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA; 


Database altered.


SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;  


Database altered.


SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (UNIQUE) COLUMNS; 


Database altered.


SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (FOREIGN KEY) COLUMNS;  


Database altered.


SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;


Database altered.
--也可以针对表级别开启补充日志
SQL> ALTER TABLE SCOTT.EMP ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;


Table altered.




--在/ggs/ogg下解压ggs_Linux_x64_ora11g_64bit_v11_1_1_0_0_078.tar和ggs_FileWriter_Linux_x64_v11_1_1_0_0_007.tar
--配置OGG
[oracle@vzwc1 ogg]$ ./ggsci 


Oracle GoldenGate Command Interpreter for Oracle
Version 11.1.1.0.0 Build 078
Linux, x64, 64bit (optimized), Oracle 11 on Jul 28 2010 13:13:42


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


GGSCI (vzwc1) 1> create subdirs     


Creating subdirectories under current directory /ggs/ogg


Parameter files                /ggs/ogg/dirprm: created
Report files                   /ggs/ogg/dirrpt: created
Checkpoint files               /ggs/ogg/dirchk: created
Process status files           /ggs/ogg/dirpcs: created
SQL script files               /ggs/ogg/dirsql: created
Database definitions files     /ggs/ogg/dirdef: created
Extract data files             /ggs/ogg/dirdat: created
Temporary files                /ggs/ogg/dirtmp: created
Veridata files                 /ggs/ogg/dirver: created
Veridata Lock files            /ggs/ogg/dirver/lock: created
Veridata Out-Of-Sync files     /ggs/ogg/dirver/oos: created
Veridata Out-Of-Sync XML files /ggs/ogg/dirver/oosxml: created
Veridata Parameter files       /ggs/ogg/dirver/params: created
Veridata Report files          /ggs/ogg/dirver/report: created
Veridata Status files          /ggs/ogg/dirver/status: created
Veridata Trace files           /ggs/ogg/dirver/trace: created
Stdout files                   /ggs/ogg/dirout: created



--配置mgr
GGSCI (vzwc1) 3> edit params mgr


GGSCI (vzwc1) 4> view params mgr


PORT 7809
DYNAMICPORTLIST 7840-7914
USERID goldengate, PASSWORD goldengate
AUTORESTART EXTRACT *,RETRIES 3,WAITMINUTES 5,RESETMINUTES 10
PURGEOLDEXTRACTS ./dirdat/*,usecheckpoints, minkeepdays 1
PURGEDDLHISTORY MINKEEPDAYS 3, MAXKEEPDAYS 5
PURGEMARKERHISTORY MINKEEPDAYS 3, MAXKEEPDAYS 5
LAGREPORTHOURS 1
LAGINFOMINUTES 30
LAGCRITICALMINUTES 45


GGSCI (vzwc1) 5> start mgr


Manager started.


GGSCI (vzwc1) 6> info mgr


Manager is running (IP port vzwc1.7809).


GGSCI (vzwc1) 40> dblogin userid ggadmin, password ggadmin
Successfully logged into database.


GGSCI (vzwc1) 41> add trandata scott.*


2014-02-15 23:14:50  WARNING OGG-00869  No unique key is defined for table BONUS. 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 SCOTT.BONUS.


Logging of supplemental redo data enabled for table SCOTT.DEPT.


Logging of supplemental redo data enabled for table SCOTT.EMP.


2014-02-15 23:14:52  WARNING OGG-00869  No unique key is defined for table SALGRADE. 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 SCOTT.SALGRADE.


GGSCI (vzwc1) 43> list tables scott.*
SCOTT.BONUS
SCOTT.DEPT
SCOTT.EMP
SCOTT.SALGRADE


Found 4 tables matching list criteria.




--配置启动extract、pump、rmttrail
GGSCI (vzwc1) 1> dblogin userid ggadmin, password ggadmin
Successfully logged into database.


GGSCI (vzwc1) 7> add extract sora, tranlog, threads 2, begin now
EXTRACT added.


GGSCI (vzwc1) 8> ADD EXTTRAIL ./dirdat/ea, EXTRACT SORA
EXTTRAIL added.


GGSCI (vzwc1) 9> edit params sora


GGSCI (vzwc1) 10> view params SORA


EXTRACT SORA
USERID GGADMIN, PASSWORD ggadmin
EXTTRAIL ./dirdat/ea
TRANLOGOPTIONS RAWDEVICEOFFSET 0
REPORTCOUNT EVERY 1 MINUTES, RATE
DISCARDFILE ./dirrpt/exta.dsc,APPEND,MEGABYTES 1024
FETCHOPTIONS NOUSESNAPSHOT    
ThreadOptions MaxCommitPropagationDelay 10000 IOLatency 3000   
TRANLOGOPTIONS _INFINITYTOZERO
TRANLOGOPTIONS ASMUSER sys@asm , ASMPASSWORD oracle
DYNAMICRESOLUTION
TABLE SCOTT.*;


GGSCI (vzwc1) 102> add extract pora, exttrailsource ./dirdat/ea
EXTRACT added.


GGSCI (vzwc1) 103> add rmttrail ./dirdat/eb, extract pora, megabytes 20
RMTTRAIL added.


GGSCI (vzwc1) 105> view params pora


extract pora
userid ggadmin, password ggadmin
rmthost 192.168.1.23, mgrport 7809
rmttrail ./dirdat/eb
PASSTHRU 
table SCOTT.*;



--查看进程状态
GGSCI (vzwc1) 137> info all


Program     Status      Group       Lag           Time Since Chkpt


MANAGER     RUNNING                                           
EXTRACT     RUNNING     PORA        00:00:00      00:00:09    
EXTRACT     RUNNING     SORA        00:00:01      00:00:02    


GGSCI (vzwc1) 139> info sora


EXTRACT    SORA      Last Started 2014-02-15 23:36   Status RUNNING
Checkpoint Lag       00:00:01 (updated 00:00:05 ago)
Log Read Checkpoint  Oracle Redo Logs
                     2014-02-15 23:37:46  Thread 1, Seqno 67, RBA 640016
Log Read Checkpoint  Oracle Redo Logs
                     2014-02-15 23:37:47  Thread 2, Seqno 64, RBA 30771728




GGSCI (vzwc1) 140> info pora


EXTRACT    PORA      Last Started 2014-02-15 23:33   Status RUNNING
Checkpoint Lag       00:00:00 (updated 00:00:05 ago)
Log Read Checkpoint  File ./dirdat/ea000000
                     First Record  RBA 923




GGSCI (vzwc1) 141> view report sora




***********************************************************************
                 Oracle GoldenGate Capture for Oracle
                     Version 11.1.1.0.0 Build 078
   Linux, x64, 64bit (optimized), Oracle 11 on Jul 28 2010 14:58:37
 
Copyright (C) 1995, 2010, Oracle and/or its affiliates. All rights reserved.




                    Starting at 2014-02-15 23:36:06
***********************************************************************


Operating System Version:
Linux
Version #1 SMP Wed Feb 22 17:37:40 EST 2012, Release 2.6.32-300.10.1.el5uek
Node: vzwc1
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: 20667


Description: 


***********************************************************************
**            Running with the following parameters                  **
***********************************************************************
EXTRACT SORA
USERID GGADMIN, PASSWORD *******
EXTTRAIL ./dirdat/ea
TRANLOGOPTIONS RAWDEVICEOFFSET 0
REPORTCOUNT EVERY 1 MINUTES, RATE
DISCARDFILE ./dirrpt/exta.dsc,APPEND,MEGABYTES 1024
FETCHOPTIONS NOUSESNAPSHOT
ThreadOptions MaxCommitPropagationDelay 10000 IOLatency 3000
TRANLOGOPTIONS _INFINITYTOZERO
TRANLOGOPTIONS ASMUSER sys@asm , ASMPASSWORD ******
DYNAMICRESOLUTION
TABLE SCOTT.*;


2014-02-15 23:36:06  INFO    OGG-01635  BOUNDED RECOVERY: reset to initial or altered checkpoint.


Bounded Recovery Parameter:
Options    = BRRESET
BRINTERVAL = 4HOURS
BRDIR      = /ggs/ogg


CACHEMGR virtual memory values (may have been adjusted)
CACHEBUFFERSIZE:                         64K
CACHESIZE:                                8G
CACHEBUFFERSIZE (soft max):               4M
CACHEPAGEOUTSIZE (normal):                4M
PROCESS VM AVAIL FROM OS (min):          16G
CACHESIZEMAX (strict force to disk):  13.99G


Database Version:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE    11.2.0.4.0      Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production


Database Language and Character Set:
NLS_LANG = "AMERICAN_AMERICA.ZHS16GBK" 
NLS_LANGUAGE     = "AMERICAN" 
NLS_TERRITORY    = "AMERICA" 
NLS_CHARACTERSET = "ZHS16GBK" 


Maximum supported ASM read buffer size is 28 KB


Maximum supported ASM read buffer size is 28 KB


2014-02-15 23:36:07  INFO    OGG-00546  Default thread stack size: 8388608.


2014-02-15 23:36:07  INFO    OGG-01513  Positioning to (Thread 1) Sequence 67, RBA 183312, SCN 0.0.


2014-02-15 23:36:08  INFO    OGG-01516  Positioned to (Thread 1) Sequence 67, RBA 183312, SCN 0.0, Feb 15, 2014 11:24:46 PM.


2014-02-15 23:36:08  INFO    OGG-01513  Positioning to (Thread 2) Sequence 64, RBA 30332944, SCN 0.0.


2014-02-15 23:36:08  INFO    OGG-01516  Positioned to (Thread 2) Sequence 64, RBA 30332944, SCN 0.0, Feb 15, 2014 11:24:46 PM.


2014-02-15 23:36:08  INFO    OGG-01052  No recovery is required for target file ./dirdat/ea000000, at RBA 0 (file not opened).


2014-02-15 23:36:08  INFO    OGG-01478  Output file ./dirdat/ea is using format RELEASE 10.4/11.1.


2014-02-15 23:36:08  INFO    OGG-01517  Position of first record processed for Thread 1, Sequence 67, RBA 183312, SCN 0.2360215, Feb 15, 2014 11:24:46 PM.


***********************************************************************
**                     Run Time Messages                             **
***********************************************************************


2014-02-15 23:36:08  INFO    OGG-01517  Position of first record processed for Thread 2, Sequence 64, RBA 30332944, SCN 0.2360215, Feb 15, 2014 11:24:46 PM.




--生成defgen文件,并且拷贝到目标段/ggs/ogg/dirdef下
GGSCI (vzwc1) 144> edit param defgen


GGSCI (vzwc1) 145> view param defgen


DEFSFILE ./dirdef/scott.def
USERID GGADMIN, PASSWORD ggadmin
table SCOTT.*;


[oracle@vzwc1 ogg]$ ./defgen paramfile ./dirprm/defgen.prm


***********************************************************************
        Oracle GoldenGate Table Definition Generator for Oracle
                     Version 11.1.1.0.0 Build 078
   Linux, x64, 64bit (optimized), Oracle 11 on Jul 28 2010 13:15:49
 
Copyright (C) 1995, 2010, Oracle and/or its affiliates. All rights reserved.




                    Starting at 2014-02-15 23:45:43
***********************************************************************


Operating System Version:
Linux
Version #1 SMP Wed Feb 22 17:37:40 EST 2012, Release 2.6.32-300.10.1.el5uek
Node: vzwc1
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: 21351


***********************************************************************
**            Running with the following parameters                  **
***********************************************************************
DEFSFILE ./dirdef/scott.def
USERID GGADMIN, PASSWORD *******
table SCOTT.*;
Expanding wildcard SCOTT.*:


Retrieving definition for SCOTT.BONUS


2014-02-15 23:45:44  WARNING OGG-00869  No unique key is defined for table BONUS. All viable columns will be used to represent the key, but may not guarantee uniqueness.  KEYCOLS may be used to define the key.
Retrieving definition for SCOTT.DEPT
Retrieving definition for SCOTT.EMP
Retrieving definition for SCOTT.SALGRADE


2014-02-15 23:45:44  WARNING OGG-00869  No unique key is defined for table SALGRADE. All viable columns will be used to represent the key, but may not guarantee uniqueness.  KEYCOLS may be used to define the key.


Definitions generated for 4 tables in ./dirdef/scott.def


[oracle@vzwc1 ogg]$ scp dirdef/scott.def gpadmin@192.168.1.23:/ggs/ogg/dirdef  
The authenticity of host '192.168.1.23 (192.168.1.23)' can't be established.
RSA key fingerprint is c6:be:66:22:c8:96:ee:28:5c:9c:fe:f3:89:4e:07:e0.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '192.168.1.23' (RSA) to the list of known hosts.
gpadmin@192.168.1.23's password: 
scott.def                                                                                                                                                                                         100% 2329     2.3KB/s   00:00    
[oracle@vzwc1 ogg]$ 





==================================================================
目标段配置FaltFileWriter extract


--目标段的OGG需要安装在GreenPlum的Master节点上，这里软件版本、路径和源端一致，你可能需要拷贝源端oracle的lib库来运行ggsci等程序
--创建File
GGSCI (mdw) 13> add extract ffwriter, exttrailsource ./dirdat/eb
EXTRACT added.


GGSCI (mdw) 7> view param ffwriter
extract ffwriter
CUSEREXIT ./flatfilewriter.so CUSEREXIT PASSTHRU INCLUDEUPDATEBEFORES, PARAMS "ffwriter.properties"
sourcedefs ./dirdef/scott.def
table SCOTT.*;



--配置ffwriter.properties文件，模版可以从sample-dirprm中找到
[gpadmin@mdw ogg]$ pwd
/ggs/ogg
[gpadmin@mdw ogg]$ cat ffwriter.properties 
#------------------------
#LOGGING OPTIONS
#------------------------
goldengate.log.logname=ffwriter
goldengate.log.level=DEBUG
goldengate.log.modules=LOGMALLOC
goldengate.log.level.LOGMALLOC=ERROR
goldengate.log.tostdout=true
goldengate.log.tofile=true
#------------------------
#FLAT FILE WRITER OPTIONS
#------------------------
goldengate.flatfilewriter.writers=dsvwriter
goldengate.userexit.chkptprefix=ffwriter_
#------------------------
# dsvwriter options
#------------------------
dsvwriter.mode=DSV
dsvwriter.rawchars=false
dsvwriter.includebefores=false
dsvwriter.includecolnames=false
dsvwriter.omitvalues=false
dsvwriter.diffsonly=false
dsvwriter.omitplaceholders=false
dsvwriter.files.onepertable=true
#dsvwriter.files.prefix=csv
dsvwriter.files.data.rootdir=./dirout
dsvwriter.files.data.ext=_data.dsv
dsvwriter.files.data.tmpext=_data.dsv.temp
dsvwriter.files.data.rollover.time=10
#dsvwriter.files.data.rollover.size=
dsvwriter.files.data.norecords.timeout=10
#dsvwriter.files.control.use=true
#dsvwriter.files.control.ext=_data.control
#dsvwriter.files.control.rootdir=./dirout
dsvwriter.dsv.nullindicator.chars=
dsvwriter.dsv.fielddelim.chars=|
#dsvwriter.dsv.linedelim.chars=\n
dsvwriter.dsv.quotes.chars="
dsvwriter.dsv.quotes.escaped.chars=
dsvwriter.metacols=opcode,timestamp
dsvwriter.metacols.opcode.insert.chars=I
dsvwriter.metacols.opcode.update.chars=U
#dsvwriter.metacols.txind.fixedlen=1
#dsvwriter.metacols.txind.begin.chars=B
#dsvwriter.metacols.txind.middle.chars=M
#dsvwriter.metacols.txind.end.chars=E
dsvwriter.files.formatstring=pump_%s_%t_%d_%05n

--以下是ffwriter.properties模板文件

#------------------------
#LOGGING OPTIONS
#------------------------
goldengate.log.logname=ffwriter
goldengate.log.level=INFO
goldengate.log.modules=LOGMALLOC
goldengate.log.level.LOGMALLOC=ERROR
goldengate.log.tostdout=false
goldengate.log.tofile=true
#------------------------
#FLAT FILE WRITER OPTIONS
#------------------------
goldengate.flatfilewriter.writers=dsvwriter
goldengate.userexit.chkptprefix=ffwriter_
#------------------------
# dsvwriter options
#------------------------
dsvwriter.mode=DSV
dsvwriter.rawchars=false
dsvwriter.includebefores=false
dsvwriter.includecolnames=false
dsvwriter.omitvalues=false
dsvwriter.diffsonly=false
dsvwriter.omitplaceholders=false
#dsvwriter.files.onepertable=false
dsvwriter.files.prefix=csv
dsvwriter.files.data.rootdir=./dirout
dsvwriter.files.data.ext=_data.dsv
dsvwriter.files.data.tmpext=_data.dsv.temp
dsvwriter.files.data.rollover.time=10
#dsvwriter.files.data.rollover.size=
dsvwriter.files.data.norecords.timeout=10
dsvwriter.files.control.use=true
dsvwriter.files.control.ext=_data.control
dsvwriter.files.control.rootdir=./dirout
dsvwriter.dsv.nullindicator.chars=<NULL>
dsvwriter.dsv.fielddelim.chars=|
dsvwriter.dsv.linedelim.chars=\n
dsvwriter.dsv.quotes.chars="
dsvwriter.dsv.quotes.escaped.chars=""
dsvwriter.metacols=position,txind,opcode,timestamp,schema,table
dsvwriter.metacols.txind.fixedlen=1
dsvwriter.metacols.txind.begin.chars=B
dsvwriter.metacols.txind.middle.chars=M
dsvwriter.metacols.txind.end.chars=E
dsvwriter.files.formatstring=pump_%s_%t_%d_%05n
#------------------------
# ldvwriter options
#------------------------
ldvwriter.mode=LDV
ldvwriter.rawchars=true
ldvwriter.includebefores=false
ldvwriter.includecolnames=false
ldvwriter.files.onepertable=false
ldvwriter.files.data.rootdir=./dirout
ldvwriter.files.data.ext=.data
ldvwriter.files.data.tmpext=.temp
ldvwriter.files.data.rollover.time=10
ldvwriter.files.data.norecords.timeout=10
ldvwriter.files.control.use=true
ldvwriter.files.control.ext=.ctrl
ldvwriter.files.control.rootdir=./dirout
ldvwriter.metacols=position,timestamp,@TOKEN-RBA,@TOKEN-POS,opcode,txind,schema,table
ldvwriter.metacols.TOKEN-RBA.fixedlen=10
ldvwriter.metacols.TOKEN-POS.fixedlen=10
ldvwriter.metacols.timestamp.fixedlen=26
ldvwriter.metacols.schema.fixedjustify=right
ldvwriter.metacols.schema.fixedpadchar.chars=Y
ldvwriter.metacols.opcode.fixedlen=1
ldvwriter.metacols.opcode.insert.chars=I
ldvwriter.metacols.opcode.update.chars=U
ldvwriter.metacols.opcode.delete.chars=D
ldvwriter.metacols.txind.fixedlen=1
ldvwriter.metacols.txind.begin.chars=B
ldvwriter.metacols.txind.middle.chars=M
ldvwriter.metacols.txind.end.chars=E
ldvwriter.metacols.txind.whole.chars=W
ldvwriter.ldv.vals.missing.chars=M
ldvwriter.ldv.vals.present.chars=P
ldvwriter.ldv.vals.null.chars=N
ldvwriter.ldv.lengths.record.mode=binary
ldvwriter.ldv.lengths.record.length=4
ldvwriter.ldv.lengths.field.mode=binary
ldvwriter.ldv.lengths.field.length=2
ldvwriter.files.rolloveronshutdown=false
ldvwriter.statistics.toreportfile=false
ldvwriter.statistics.period=onrollover
ldvwriter.statistics.tosummaryfile=true
ldvwriter.statistics.overall=true
ldvwriter.statistics.summary.fileformat=schema,table,schemaandtable,total,gctimestamp,ctimestamp
ldvwriter.statistics.summary.delimiter.chars=|
ldvwriter.statistics.summary.eol.chars=\n
ldvwriter.metacols.position.format=dec
ldvwriter.writebuffer.size=36863

--启动ffwriter
GGSCI (mdw) 8> start ffwriter


Sending START request to MANAGER ...
EXTRACT FFWRITER starting




GGSCI (mdw) 9> info all


Program     Status      Group       Lag           Time Since Chkpt


MANAGER     RUNNING                                           
EXTRACT     RUNNING     FFWRITER    00:00:00      00:00:00    


GGSCI (mdw) 20> view report ffwriter




***********************************************************************
                 Oracle GoldenGate Capture for Oracle
                     Version 11.1.1.0.0 Build 078
   Linux, x64, 64bit (optimized), Oracle 11 on Jul 28 2010 14:58:37
 
Copyright (C) 1995, 2010, Oracle and/or its affiliates. All rights reserved.




                    Starting at 2014-02-16 00:03:46
***********************************************************************


Operating System Version:
Linux
Version #1 SMP Fri Jul 22 04:43:29 EDT 2011, Release 2.6.18-274.el5
Node: mdw
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: 16158


Description: 


***********************************************************************
**            Running with the following parameters                  **
***********************************************************************
extract ffwriter
CUSEREXIT ./flatfilewriter.so CUSEREXIT PASSTHRU INCLUDEUPDATEBEFORES, PARAMS "ffwriter.properties"
/ggs/ogg/extract running with user exit library ./flatfilewriter.so, compatiblity level (2) is current.
sourcedefs ./dirdef/scott.def
table SCOTT.*;


CACHEMGR virtual memory values (may have been adjusted)
CACHEBUFFERSIZE:                         64K
CACHESIZE:                                4G
CACHEBUFFERSIZE (soft max):               4M
CACHEPAGEOUTSIZE (normal):                4M
PROCESS VM AVAIL FROM OS (min):        4.15G
CACHESIZEMAX (strict force to disk):   4.10G




***********************************************************************
Started Oracle GoldenGate for Flat File
Version 11.1.1.0.0
***********************************************************************


***********************************************************************
**                     Run Time Messages                             **
***********************************************************************


Opened trail file ./dirdat/eb000000 at 2014-02-16 00:03:47






======================================================
源端测试


[oracle@vzwc1 ~]$ sqlplus scott


SQL*Plus: Release 11.2.0.4.0 Production on Sun Feb 16 00:06:30 2014


Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Enter password: 


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options


SQL> delete from emp where rownum=1;


1 row deleted.


SQL> commit;


Commit complete.


SQL> update emp set ename='ZhongWC' where empno='7839';


1 row updated.


SQL> commit;


Commit complete.




======================================================
查看目标端/ggs/ogg/dirout生成的FlatFile


[gpadmin@mdw dirout]$ pwd
/ggs/ogg/dirout
[gpadmin@mdw dirout]$ ll
total 12
-rw-rw-rw- 1 gpadmin gpadmin  45 Feb 16 00:06 pump_SCOTT_EMP_2014-02-16_00-06-44_00000_data.dsv
-rw-rw-rw- 1 gpadmin gpadmin  95 Feb 16 00:09 pump_SCOTT_EMP_2014-02-16_00-09-42_00001_data.dsv
-rw-rw-rw- 1 gpadmin gpadmin 118 Feb 16 00:09 SCOTT.EMPcontrol
[gpadmin@mdw dirout]$ cat pump_SCOTT_EMP_2014-02-16_00-09-42_00001_data.dsv 
"U"|"2014-02-16 00:09:36.962844"|7839|"ZhongWC"|"PRESIDENT"||"1981-11-17:00:00:00"|5000.00||10
[gpadmin@mdw dirout]$ cat pump_SCOTT_EMP_2014-02-16_00-06-44_00000_data.dsv 

"D"|"2014-02-16 00:06:38.982882"|7369|||||||

GoldenGate没有直接针对GreenPlum数据库进行数据加载的接口，可以使用GoldenGate for FlatFile插件从oracle中抽取变化数据形成GreenPlum可以加载的文本，使用psql的copy命令或者gpload工具将变化数据加载到GreenPlum里。