配置Goldengate从Oracle到PostgreSQL的同步复制

1、平台环境

Oracle：rhel6.7+Oracle11.2.0.4 ip:192.168.56.2

PostgreSQL：rhel7.2+Pg9.6.1 ip:192.168.56.25

Goldengate：Goldengate12.2.0.1 for oracle和Goldengate 12.2.0.1 for PostgreSQL

2、Ogg配置

Oracle端：

直接安装goldengate for oracle 11g

配置环境变量

[oracle@rhel6 ogg]$ vi ~/.bash_profile#添加export LD_LIBRARY_PATH=/ogg/lib:$LD_LIBRARY_PATHexport PATH=/ogg:$PATH

配置Oracle数据库

#启用归档sys@ORCL>alter database archivelog;#Forcing loggingsys@ORCL>alter database force logging;#添加最小附加日志sys@ORCL>alter database add supplemental log data;#查看结果sys@ORCL>select LOG_MODE,FORCE_LOGGING,SUPPLEMENTAL_LOG_DATA_MIN from v$database;LOG_MODE                             FORCE_LOG SUPPLEMENTAL_LOG_DATA_MI------------------------------------ --------- ------------------------ARCHIVELOG                           YES       YES#创建goldengate用户sys@ORCL>create user goldengate identified by goldengate;sys@ORCL>grant dba to goldengate;sys@ORCL>create user zhaoxu identified by zhaoxu;sys@ORCL>grant dba to zhaoxu;#创建测试表zhaoxu@ORCL>create table ggtest (col1 number, col2 varchar2(20));Table created.zhaoxu@ORCL>alter table ggtest add constraint pk_ggtest primary key(col1);Table altered.

配置ogg参数文件

#配置mgrGGSCI (rhel6) 2> edit params mgrPORT 7809AUTOSTART ER *AUTORESTART EXTRACT *,RETRIES 100,WAITMINUTES 2PURGEOLDEXTRACTS ./dirdat/*,USECHECKPOINTS,MINKEEPDAYS 3LAGREPORTHOURS 1LAGINFOMINUTES 30LAGCRITICALMINUTES 45SYSLOG ERROR,WARN#启动mgrGGSCI (rhel6) 3> start mgrGGSCI (rhel6) 4> info all  Program     Status      Group       Lag at Chkpt  Time Since ChkptMANAGER     RUNNING #配置抽取进程参数GGSCI (rhel6) 5> edit params ext_empEXTRACT EXT_EMPDYNAMICRESOLUTIONSETENV (NLS_LANG="AMERICAN_AMERICA.UTF8")SETENV (ORACLE_HOME="/u02/app/oracle/product/11.2.4/db1")SETENV (ORACLE_SID="orcl")USERID goldengate,PASSWORD goldengateDISCARDFILE ./dirrpt/ext_emp.dsc,APPEND,MEGABYTES 1024EXTTRAIL ./dirdat/zxtable zhaoxu.ggtest;#配置投递进程参数GGSCI (rhel6) 6> edit params dp_tabEXTRACT DP_TABPASSTHRURMTHOST 192.168.56.25 ,MGRPORT 7809 , COMPRESSRMTTRAIL ./dirdat/zxtable zhaoxu.ggtest;#配置生成定义文件参数GGSCI (rhel6) 7> edit params defgendefsfile ./dirdef/defgen.defuserid goldengate, password goldengatetable zhaoxu.ggtest;#增加抽取进程和传输进程GGSCI (rhel6) 8> add extract ext_emp,tranlog,begin nowGGSCI (rhel6) 9> add exttrail ./dirdat/zx, extract ext_emp, megabytes 200GGSCI (rhel6) 10> add extract dp_tab, exttrailsource ./dirdat/zxGGSCI (rhel6) 11> add rmttrail ./dirdat/zx, extract dp_tab, megabytes 200GGSCI (rhel6) 12> info allProgram     Status      Group       Lag at Chkpt  Time Since ChkptMANAGER     RUNNING                                           EXTRACT     STOPPED     DP_TAB      00:00:00      00:01:43    EXTRACT     STOPPED     EXT_EMP     00:00:00      00:01:01#添加表的附加日志GGSCI (rhel6) 13> dblogin userid goldengate password goldengateSuccessfully logged into database.GGSCI (rhel6 as goldengate@orcl) 14> add trandata zhaoxu.ggtestLogging of supplemental redo data enabled for table ZHAOXU.GGTEST.TRANDATA for scheduling columns has been added on table 'ZHAOXU.GGTEST'.TRANDATA for instantiation CSN has been added on table 'ZHAOXU.GGTEST'.#生成定义文件[oracle@rhel6 ogg]$ ./defgen paramfile ./dirprm/defgen.prm ***********************************************************************        Oracle GoldenGate Table Definition Generator for Oracle      Version 12.2.0.1.1 OGGCORE_12.2.0.1.0_PLATFORMS_151211.1401   Linux, x64, 64bit (optimized), Oracle 11g on Dec 11 2015 21:37:21 Copyright (C) 1995, 2015, Oracle and/or its affiliates. All rights reserved.                    Starting at 2016-12-08 13:45:00***********************************************************************Operating System Version:LinuxVersion #1 SMP Wed Jul 1 18:23:37 EDT 2015, Release 2.6.32-573.el6.x86_64Node: rhel6Machine: x86_64                         soft limit   hard limitAddress Space Size   :    unlimited    unlimitedHeap Size            :    unlimited    unlimitedFile Size            :    unlimited    unlimitedCPU Time             :    unlimited    unlimitedProcess id: 3669*************************************************************************            Running with the following parameters                  *************************************************************************defsfile ./dirdef/defgen.defuserid goldengate, password ***table zhaoxu.ggtest;Retrieving definition for ZHAOXU.GGTEST.Definitions generated for 1 table in ./dirdef/defgen.def.[oracle@rhel6 ogg]$ cat ./dirdef/defgen.def *+- Defgen version 5.0, Encoding UTF-8** Definitions created/modified  2016-12-08 13:45**  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*    20    Native Data Type*    21    Character Set*    22    Character Length*    23    LOB Type*    24    Partial Type*Database type: ORACLECharacter set ID: UTF-8National character set ID: UTF-16Locale: neutralCase sensitivity: 14 14 14 14 14 14 14 14 14 14 14 14 11 14 14 14TimeZone: GMT*Definition for table ZHAOXU.GGTESTRecord length: 82Syskey: 0Columns: 2COL1   64     50        0  0  0 1 0     50     50     50 0 0 0 0 1    0 1   2    2       -1      0 0 0COL2   64     20       56  0  0 1 0     20     20      0 0 0 0 0 1    0 0   0    1       -1      0 0 0End of definition

PostgreSQL端：

创建用于同步的数据库、用户和Schema，并创建测试表

postgres=# create database zhaoxu;postgres=# create user zhaoxu superuser password 'zhaoxu';postgres=# \c zhaoxu zhaoxuzhaoxu=# create schema zhaoxu;CREATE SCHEMAzhaoxu=# \dn List of schemas  Name  | Owner  --------+-------- public | pguser zhaoxu | zhaoxuzhaoxu=# CREATE TABLE ggtestzhaoxu-# (zhaoxu(#   col1 integer NOT NULL,zhaoxu(#   col2 varchar(20),zhaoxu(#   CONSTRAINT pk_ggtest PRIMARY KEY (col1)zhaoxu(# );CREATE TABLEzhaoxu=# \d        List of relations Schema |  Name  | Type  | Owner  --------+--------+-------+-------- zhaoxu | ggtest | table | zhaoxu

解压ogg

[pguser@rhel7 ogg]$ tar -xvf ggs_Linux_x64_PostgreSQL_64bit.tar

配置odbc数据源，goldengate 使用ODBC连接Postgres Database

[pguser@rhel7 ogg]$ pwd/ogg[pguser@rhel7 ogg]$ cat odbc.ini [ODBC Data Sources]  GG_Postgres=DataDirect 9.6 PostgreSQL Wire Protocol  [ODBC]  IANAAppCodePage=106InstallDir=/ogg[GG_Postgres] Driver=/ogg/lib/GGpsql25.soDescription=DataDirect 9.6 PostgreSQL Wire ProtocolDatabase=zhaoxuHostName=127.0.0.1PortNumber=5432  LogonID=zhaoxuPassword=zhaoxu

[ODBC Data Sources]里边配置该ODBC的别名，本文件中也就是GG_Postgres 后边的配置文件中的targetdb需要与这个对应

[ODBC]:

IANAAppCodePage指的是字符集的设置 这里的106值得是UTF8，如果是4则为ISO-8859-1，注意这个应该始终和postgres的字符集设置相同，不同字符集对应的值见附件。

InstallDir对应ogg的安装目录

[GG_Postgres]:这里的名称对应的是上边ODBC的别名

Driver这里指向的是ogg安装目录下的lib/GGpsql25.so

Description是描述

Database填写数据库名称

HostName填写本机的hostname，可以解析的即可。

PosrNumber是postgres的监听端口。

LogonID填写postgres的用户名

password填写postgres的密码

配置环境变量

export LD_LIBRARY_PATH=/ogg/lib:$LD_LIBRARY_PATHexport PATH=$PATH:/oggexport ODBCINI=/ogg/odbc.ini

配置Ogg

[pguser@rhel7 ogg]$ ./ggsciOracle GoldenGate Command InterpreterVersion 12.2.0.1.1 OGGCORE_12.2.0.1.0_PLATFORMS_151211.1401Linux, x64, 64bit (optimized), PostgreSQL on Dec 11 2015 16:22:42Operating system character set identified as UTF-8.Copyright (C) 1995, 2015, Oracle and/or its affiliates. All rights reserved.#创建目录GGSCI (rhel7) 1>create subdirsCreating subdirectories under current directory /oggParameter files                /ogg/dirprm: already existsReport files                   /ogg/dirrpt: createdCheckpoint files               /ogg/dirchk: createdProcess status files           /ogg/dirpcs: createdSQL script files               /ogg/dirsql: createdDatabase definitions files     /ogg/dirdef: createdExtract data files             /ogg/dirdat: createdTemporary files                /ogg/dirtmp: createdStdout files                   /ogg/dirout: created#配置mgr进程PORT 7809#启动mgr进程GGSCI (rhel7) 3> start mgrManager started.GGSCI (rhel7) 4> info allProgram     Status      Group       Lag at Chkpt  Time Since ChkptMANAGER     RUNNING      #把源端生成的定义文件取到目标端[pguser@rhel7 ogg]$ scp oracle@192.168.56.2:/ogg/dirdef/defgen.def /ogg/dirdef#配置复制进程参数GGSCI (rhel7) 5> edit params rep1REPLICAT rep1SOURCEDEFS ./dirdef/defgen.defSETENV(PGCLIENTENCODING = "UTF8" )SETENV(ODBCINI="/ogg/odbc.ini" )SETENV(NLS_LANG="AMERICAN_AMERICA.AL32UTF8")TARGETDB GG_Postgres,userid zhaoxu ,password zhaoxuDISCARDFILE ./dirrpt/rep1.dscmap zhaoxu.ggtest ,target zhaoxu.ggtest;#添加复制进程GGSCI (rhel7) 6> add replicat rep1, exttrail ./dirdat/zx,nodbcheckpointGGSCI (rhel7) 7> info allProgram     Status      Group       Lag at Chkpt  Time Since ChkptMANAGER     RUNNING                                           REPLICAT    STOPPED     REP1        00:00:00      00:02:29 #测试连接PostgreSQL数据库GGSCI (rhel7) 8> dblogin sourcedb gg_postgres userid zhaoxuPassword: 2016-12-08 13:27:34  INFO    OGG-03036  Database character set identified as UTF-8. Locale: en_US.2016-12-08 13:27:34  INFO    OGG-03037  Session character set identified as UTF-8.Successfully logged into database.GGSCI (rhel7 as zhaoxu@gg_postgres) 9>#如果连接不成功，检查pg_hba.conf配置文件

3、启动源端和目标端的进程

#Oracle端GGSCI (rhel6) 16> start *Sending START request to MANAGER ...EXTRACT DP_TAB startingSending START request to MANAGER ...EXTRACT EXT_EMP startingGGSCI (rhel6) 18> info allProgram     Status      Group       Lag at Chkpt  Time Since ChkptMANAGER     RUNNING                                           EXTRACT     RUNNING     DP_TAB      00:00:00      00:00:12    EXTRACT     RUNNING     EXT_EMP     00:00:00      00:00:01 #PostgreSQL端GGSCI (rhel7) 8> start *Sending START request to MANAGER ...REPLICAT REP1 startingGGSCI (rhel7) 9> info allProgram     Status      Group       Lag at Chkpt  Time Since ChkptMANAGER     RUNNING                                           REPLICAT    RUNNING     REP1        00:00:00      00:00:00

4、测试数据同步

测试insert

#Oracle端zhaoxu@ORCL>insert into ggtest values(1,'zhaoxu');1 row created.zhaoxu@ORCL>insert into ggtest values(2,'luoxi');1 row created.zhaoxu@ORCL>insert into ggtest values(3,'sanqi');1 row created.zhaoxu@ORCL>commit;Commit complete.#PostgreSQL端zhaoxu=# select * from ggtest; col1 |  col2  ------+--------    1 | zhaoxu    2 | luoxi    3 | sanqi(3 rows)

测试delete

#Oracle端zhaoxu@ORCL>delete from ggtest where col1=3;1 row deleted.zhaoxu@ORCL>commit;Commit complete.zhaoxu@ORCL>select * from ggtest;      COL1 COL2---------- ------------------------------------------------------------         1 zhaoxu         2 luoxi#PostgreSQL端zhaoxu=# select * from ggtest; col1 |  col2  ------+--------    1 | zhaoxu    2 | luoxi(2 rows)

测试update

#Oracle端zhaoxu@ORCL>update ggtest set col2 = 'sanqi' where col1=1;1 row updated.zhaoxu@ORCL>commit;Commit complete.zhaoxu@ORCL>select * from ggtest;      COL1 COL2---------- ------------------------------------------------------------         1 sanqi         2 luoxi#PostgreSQL端zhaoxu=# select * from ggtest; col1 | col2  ------+-------    2 | luoxi    1 | sanqi(2 rows)

参考文档：

http://blog.csdn.net/badly9/article/details/50372003

http://blog.csdn.net/staricqxyz/article/details/11096203

官方文档：

http://docs.oracle.com/goldengate/c1221/gg-winux/GIPSQ/sysreq.htm#GIPSQ107