1      概述：

OGG即oracle goldengate是一款oracle公司的ETL工具，下文介绍一下ogg和GaussDB(DWS)的对接和配置方法。

前提：

1、已安装好oracle

2、GaussDB(DWS)已发布成功，并在ecs机器上安装完成客户端，可正常连接使用。

2      Ogg安装配置：

2.1      Oracle端：

解压ogg_for_pg_12.2.0.1.1.zip，提取ggs_Linux_x64_PostgreSQL_64bit.tar到 oracle服务器，目录/home/oracle/ogg

新建for_pg目录：mkdir for_pg

解压：tar –xvf  ggs_Linux_x64_PostgreSQL_64bit.tar  -C  /ogg

配置环境变量：

vim ~/.bashrc

添加

export LD_LIBRARY_PATH=/home/oracle/ogg/for_pg/lib:$LD_LIBRARY_PATH

export PATH=/home/oracle/ogg/for_pg:$PATH

刷新使之生效， source ~/.bashrc

配置Oracle数据库

#启用归档

sys@ORCL>alter database archivelog;

#Forcing logging

sys@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 oracle identified by oracle;

sys@ORCL>grant dba to oracle;

sys@ORCL>create user ogg identified by ogg;

sys@ORCL>grant dba to ogg;

#创建测试表

登录ogg 用户sqlplus ogg/ogg

 SQL>create table ggtest (col1 number, col2 varchar2(20));

 Table created.

 SQL >alter table ggtest add constraint pk_ggtest primary key(col1);

 Table altered.

#配置ogg参数文件

oracle@SIA1000092630:~/ogg/for_pg> ./ggsci

#配置mgr

GGSCI (SIA1000092630) 1> edit params mgr

 PORT 7809

AUTOSTART ER *

AUTORESTART EXTRACT *,RETRIES 100,WAITMINUTES 2

PURGEOLDEXTRACTS ./dirdat/*,USECHECKPOINTS,MINKEEPDAYS 3

LAGREPORTHOURS 1

LAGINFOMINUTES 30

LAGCRITICALMINUTES 45

SYSLOG ERROR,WARN

#启动mgr

GGSCI (SIA1000092630) 2> start mgr

MGR is already running.

GGSCI (SIA1000092630) 3> info all

 Program     Status      Group       Lag           Time Since Chkpt

 MANAGER     RUNNING

#配置抽取进程参数

GGSCI (SIA1000092630) 4> edit params ext_emp

 EXTRACT EXT_EMP

DYNAMICRESOLUTION

SETENV (NLS_LANG="AMERICAN_AMERICA.WE8MSWIN1252")

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

SETENV (ORACLE_SID="oracle")

USERID oracle,PASSWORD oracle

DISCARDFILE ./dirrpt/ext_emp.dsc,APPEND,MEGABYTES 1024

EXTTRAIL ./dirdat/zx

table ogg.ggtest;

#配置投递进程参数

GGSCI (SIA1000092630) 5> edit params dp_tab

 EXTRACT DP_TAB

PASSTHRU

RMTHOST 10.119.29.232 ,MGRPORT 7809 , COMPRESS

RMTTRAIL ./dirdat/zx

table ogg.ggtest;

#配置投递进程参数

GGSCI (SIA1000092630) 6> edit params defgen

 defsfile ./dirdef/defgen.def

userid oracle, password oracle

table ogg.ggtest;

#增加抽取进程和传输进程

#增加抽取进程和传输进程

GGSCI (SIA1000092630) 7> add extract ext_emp,tranlog,begin now

GGSCI (SIA1000092630) 8> add exttrail ./dirdat/zx, extract ext_emp, megabytes

GGSCI (SIA1000092630) 9> add extract dp_tab, exttrailsource ./dirdat/zx  

GGSCI (SIA1000092630) 10> add rmttrail ./dirdat/zx, extract dp_tab, megabytes 200

GGSCI (SIA1000092630) 11> info all

 Program     Status      Group       Lag           Time Since Chkpt

 MANAGER     RUNNING                                          

EXTRACT     STOPPED     DP_TAB      00:00:00      00:00:04   

EXTRACT     STOPPED     EXT_EMP     00:00:00      00:00:03   

 #添加表的附加日志

GGSCI (SIA1000092630) 12> dblogin userid oracle password oracle

Successfully logged into database.

GGSCI (SIA1000092630) 12> add trandata ogg.ggtest

#生成定义文件

./defgen paramfile ./dirprm/defgen.prm

2.2      PostgreSQL端：

通过ecs机器登录DWS数据库，创建用于同步的数据库、用户和Schema，并创建测试表

postgres=# create database fs;

postgres=# create user fs superuser password 'XXXXXXXX';

postgres=# \c fs

Non-SSL connection (SSL connection is recommended when requiring high-security)

You are now connected to database "fs" as user "luole".

fs=#

fs=# create schema fs;

fs=# create table ggtest(col1 integer not null,col2 varchar(20),constraint pk_ggtest primary key (col1));

安装ogg

tar -xvf ggs_Linux_x64_PostgreSQL_64bit.tar -C for_pg/

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

touch odbc.ini

vim odbc.ini

[ODBC Data Sources]

GG_Postgres=DataDirect 6.1 PostgreSQL Wire Protocol

[ODBC]

IANAAppCodePage=106

InstallDir=/home/oracle/ogg/for_pg

[GG_Postgres]

Driver=/home/oracle/ogg/for_pg/lib/GGpsql25.so

Description=DataDirect 6.1 PostgreSQL Wire Protocol

Database=fs

HostName=127.0.0.1

Servername=127.0.0.1

PortNumber=61052

Port=61052

LogonID=fs

User=fs

Password=Gauss@123

配置环境变量

vim ~/.bashrc

添加

export LD_LIBRARY_PATH=/home/oracle/ogg/for_pg/lib:$LD_LIBRARY_PATH

export PATH=/home/oracle/ogg/for_pg:$PATH

export ODBCINI=/home/oracle/ogg/for_pg/odbc.ini

刷新生效，source ~./bashrc

配置Ogg

./ggsci

GGSCI (SIA1000091994) 1> create subdirs

#配置mgr进程

GGSCI (SIA1000091994) 1> edit params mgr

 PORT 7809

#启动mgr进程

GGSCI (SIA1000091994) 2> start mgr

#把源端生成的定义文件取到目标端

scp oraclehost:$PWD/defgen.def dirdef

此处oraclehost指代oracle服务器ip

#配置复制进程参数

GGSCI (SIA1000091994) 1> edit params rep1

 REPLICAT rep1

SOURCEDEFS ./dirdef/defgen.def

SOURCECHARSET utf-8

SETENV(PGCLIENTENCODING = "UTF8" )

SETENV(ODBCINI="/home/oracle/ogg/for_pg/odbc.ini" )

SETENV(NLS_LANG="AMERICAN_AMERICA.AL32UTF8")

TARGETDB GG_Postgres,userid fs ,password Gauss@123

DISCARDFILE ./dirrpt/rep1.dsc

map ogg.ggtest ,target fs.ggtest;

#添加复制进程

GGSCI (SIA1000091994) 2> add replicat rep1, exttrail ./dirdat/zx,nodbcheckpoint

#测试连接PostgreSQL数据库

GGSCI (SIA1000091994) 3> dblogin sourcedb gg_postgres userid fs

Password:

 2017-07-23 01:13:27  INFO    OGG-03036  Database character set identified as US-ASCII. Locale: en_US.

 2017-07-23 01:13:27  INFO    OGG-03037  Session character set identified as UTF-8.

Successfully logged into database.

#如果连接不成功，检查pg_hba.conf配置文件

启动源端和目标端的进程

#Oracle端

GGSCI (SIA1000092630) 1> start *

#PostgreSQL端

GGSCI (SIA1000091994 as fs@gg_postgres) 4> start *  

最后可以进行实际测试了，在oracle执行DML语句，在GaussDB(DWS)端查看数据变化