gg oracle tjs 同步_配置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_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?oracle@192.168.56.2:/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)
參考文檔：
官方文檔：
總結
以上是生活随笔為你收集整理的gg oracle tjs 同步_配置Goldengate从Oracle到PostgreSQL的同步复制的全部內容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： eclipse没有日志_技术进展 | 加
- 下一篇： 2修改字段名_DevExpress AS