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_PATH
export PATH=/ogg:$PATH 配置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 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參數檔案
#配置mgr
GGSCI (rhel6) 2> 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 (rhel6) 3> start mgr
GGSCI (rhel6) 4> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
#配置抽取程序參數
GGSCI (rhel6) 5> edit params ext_emp
EXTRACT EXT_EMP
DYNAMICRESOLUTION
SETENV (NLS_LANG="AMERICAN_AMERICA.UTF8")
SETENV (ORACLE_HOME="/u02/app/oracle/product/11.2.4/db1")
SETENV (ORACLE_SID="orcl")
USERID goldengate,PASSWORD goldengate
DISCARDFILE ./dirrpt/ext_emp.dsc,APPEND,MEGABYTES 1024
EXTTRAIL ./dirdat/zx
table zhaoxu.ggtest;
#配置投遞程序參數
GGSCI (rhel6) 6> edit params dp_tab
EXTRACT DP_TAB
PASSTHRU
RMTHOST 192.168.56.25 ,MGRPORT 7809 , COMPRESS
RMTTRAIL ./dirdat/zx
table zhaoxu.ggtest;
#配置生成定義檔案參數
GGSCI (rhel6) 7> edit params defgen
defsfile ./dirdef/defgen.def
userid goldengate, password goldengate
table zhaoxu.ggtest;
#增加抽取程序和傳輸程序
GGSCI (rhel6) 8> add extract ext_emp,tranlog,begin now
GGSCI (rhel6) 9> add exttrail ./dirdat/zx, extract ext_emp, megabytes 200
GGSCI (rhel6) 10> add extract dp_tab, exttrailsource ./dirdat/zx
GGSCI (rhel6) 11> add rmttrail ./dirdat/zx, extract dp_tab, megabytes 200
GGSCI (rhel6) 12> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER 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 goldengate
Successfully logged into database.
GGSCI (rhel6 as goldengate@orcl) 14> add trandata zhaoxu.ggtest
Logging 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:
Linux
Version #1 SMP Wed Jul 1 18:23:37 EDT 2015, Release 2.6.32-573.el6.x86_64
Node: rhel6
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: 3669
***********************************************************************
** Running with the following parameters **
***********************************************************************
defsfile ./dirdef/defgen.def
userid 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: ORACLE
Character set ID: UTF-8
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
TimeZone: GMT
*
Definition for table ZHAOXU.GGTEST
Record length: 82
Syskey: 0
Columns: 2
COL1 64 50 0 0 0 1 0 50 50 50 0 0 0 0 1 0 1 2 2 -1 0 0 0
COL2 64 20 56 0 0 1 0 20 20 0 0 0 0 0 1 0 0 0 1 -1 0 0 0
End of definition PostgreSQL端：
建立用于同步的資料庫、使用者和Schema，并建立測試表
postgres=# create database zhaoxu;
postgres=# create user zhaoxu superuser password 'zhaoxu';
postgres=# \c zhaoxu zhaoxu
zhaoxu=# create schema zhaoxu;
CREATE SCHEMA
zhaoxu=# \dn
List of schemas
Name | Owner
--------+--------
public | pguser
zhaoxu | zhaoxu
zhaoxu=# CREATE TABLE ggtest
zhaoxu-# (
zhaoxu(# col1 integer NOT NULL,
zhaoxu(# col2 varchar(20),
zhaoxu(# CONSTRAINT pk_ggtest PRIMARY KEY (col1)
zhaoxu(# );
CREATE TABLE
zhaoxu=# \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=106
InstallDir=/ogg
[GG_Postgres]
Driver=/ogg/lib/GGpsql25.so
Description=DataDirect 9.6 PostgreSQL Wire Protocol
Database=zhaoxu
HostName=127.0.0.1
PortNumber=5432
LogonID=zhaoxu
Password=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_PATH
export PATH=$PATH:/ogg
export ODBCINI=/ogg/odbc.ini 配置Ogg
[pguser@rhel7 ogg]$ ./ggsci
Oracle GoldenGate Command Interpreter
Version 12.2.0.1.1 OGGCORE_12.2.0.1.0_PLATFORMS_151211.1401
Linux, x64, 64bit (optimized), PostgreSQL on Dec 11 2015 16:22:42
Operating system character set identified as UTF-8.
Copyright (C) 1995, 2015, Oracle and/or its affiliates. All rights reserved.
#建立目錄
GGSCI (rhel7) 1>create subdirs
Creating subdirectories under current directory /ogg
Parameter files /ogg/dirprm: already exists
Report files /ogg/dirrpt: created
Checkpoint files /ogg/dirchk: created
Process status files /ogg/dirpcs: created
SQL script files /ogg/dirsql: created
Database definitions files /ogg/dirdef: created
Extract data files /ogg/dirdat: created
Temporary files /ogg/dirtmp: created
Stdout files /ogg/dirout: created
#配置mgr程序
PORT 7809
#啟動mgr程序
GGSCI (rhel7) 3> start mgr
Manager started.
GGSCI (rhel7) 4> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
#把源端生成的定義檔案取到目标端
[pguser@rhel7 ogg]$ scp [email protected]:/ogg/dirdef/defgen.def /ogg/dirdef
#配置複制程序參數
GGSCI (rhel7) 5> edit params rep1
REPLICAT rep1
SOURCEDEFS ./dirdef/defgen.def
SETENV(PGCLIENTENCODING = "UTF8" )
SETENV(ODBCINI="/ogg/odbc.ini" )
SETENV(NLS_LANG="AMERICAN_AMERICA.AL32UTF8")
TARGETDB GG_Postgres,userid zhaoxu ,password zhaoxu
DISCARDFILE ./dirrpt/rep1.dsc
map zhaoxu.ggtest ,target zhaoxu.ggtest;
#添加複制程序
GGSCI (rhel7) 6> add replicat rep1, exttrail ./dirdat/zx,nodbcheckpoint
GGSCI (rhel7) 7> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT STOPPED REP1 00:00:00 00:02:29
#測試連接配接PostgreSQL資料庫
GGSCI (rhel7) 8> dblogin sourcedb gg_postgres userid zhaoxu
Password:
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 starting
Sending START request to MANAGER ...
EXTRACT EXT_EMP starting
GGSCI (rhel6) 18> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER 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 starting
GGSCI (rhel7) 9> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER 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
![老美對EDA領域的知識外流，把控的真嚴。編譯器還有本“龍書”可以參考呢，OS也有Linux的源碼+很多核心的書，FPGA[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)