Golden Gate 運作如下
Extract:負責撈來源端(Source)執行過的SQL然後打包成Trail檔案
Data pump:負責把Source的 Trail 內的資料,傳送到Target的Trail內
Trail:負責儲存SQL資料, 預設路徑是放在 $ogg_home/dirdat 下
Replicat:讀取來源端 Trail 的SQL資料 ,然後在Target端的資料庫執行SQL
1.環境說明:
Source: Red Hat Enterprise Linux Server release 5.8, Oracle 11.2.0.1.0 (single)
Target: Red Hat Enterprise Linux Server release 6.4 , Oracle 11.2.0.1.0 (single)
Golden gate 官方下載點:
http://www.oracle.com/technetwork/middleware/goldengate/downloads/index.html
source 端會安裝在 /u02/ogg_sou
target 端會安裝在 /u02/ogg_tar
[orcl11g@sou ~]$ mkdir -p /u02/ogg_sou
[oracle@tar ~]$ mkdir -p /u02/ogg_tar
再 .bash_profile 設置 ORACLE_SID、ORACLE_HOME和LD_LIBRARY_PATH；
export ORACLE_BASE=/ora/app/11;
export ORACLE_HOME=$ORACLE_BASE/db;
export ORACLE_SID=orcl;
export ORACLE_TERM=xterm;
export PATH=/usr/sbin:$PATH;
export PATH=$ORACLE_HOME/bin:$PATH;
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib;
export CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib;
2.在Source端 安裝 GoldenGate
將下載下來的檔案解壓縮
unzip fbo_ggs_Linux_x64_shiphome
安裝 ogg
cd fbo_ggs_Linux_x64_shiphome/Disk1
./runInstaller
在Target端也是按照上述步驟, 只是要稍微注意下目錄的設定就好
安裝完後進入解壓縮資料夾, 查看狀態
[orcl11g@sou ogg_sou]$ ./ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 12.2.0.1.1 OGGCORE_12.2.0.1.0_PLATFORMS_151211.1401_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Dec 12 2015 00:54:38
Operating system character set identified as UTF-8.
Copyright (C) 1995, 2015, Oracle and/or its affiliates. All rights reserved.
GGSCI (sou) 1> info mgr
Manager is running (IP port oracle9i.7809, Process ID 3592).
GGSCI (sou) 2>
這個版本在安裝的時候已經把相關資料夾給一併建立, 要是沒有可以手動下指令建立
2.Oracle user 、權限及相關設定
2.1 在Source端建立ogg_temp tablespace
CREATE TABLESPACE OGG_TBS01 DATAFILE
'/u01/11g/oradata/orcl11g/ogg_temp01.dbf' SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED
LOGGING
ONLINE
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;
2.2 在兩邊資料庫建立ogg user
create user ogg identified by ogg default tablespace ogg_tbs01 quota unlimited on ogg_tbs01
2.3 在兩邊資料庫建立ogg role
create role ogg_role;
2.4 在兩邊資料庫分配權限
grant
CREATE SESSION,
ALTER SESSION,
ALTER SYSTEM,
RESOURCE,
SELECT ANY DICTIONARY,
FLASHBACK ANY TABLE,
SELECT ANY TABLE,
SELECT ANY TRANSACTION,
insert any table,
update any table,
drop any table,
CREATE TABLE
to ogg_role;
grant SELECT on dba_clusters to ogg_role;
grant SELECT on V_$DATABASE to ogg_role;
grant select on sys.logmnr_buildlog to ogg_role;
grant EXECUTE on DBMS_FLASHBACK to ogg_role;
grant execute on DBMS_CAPTURE_ADM to ogg_role;
grant execute on DBMS_STREAMS to ogg_role;
grant EXECUTE_CATALOG_ROLE to ogg_role;
grant execute on DBMS_CAPTURE_ADM to ogg_role;
最後將role 權限給予ogg user , 並在兩端資料庫下授權指令
grant ogg_role to ogg;
3. 在來源端開啟 archive log 、supplemental log 及 FORCE LOGGING模式
alter database archivelog;
alter database add supplemental log data;
ALTER DATABASE FORCE LOGGING;
查詢狀態, 兩項皆須YES
SELECT supplemental_log_data_min, force_logging FROM v$database;
3.1 在資料庫裡執行ogg_home內的SQL
@marker_setup.sql
@ddl_setup.sql
@role_setup.sql
@ddl_enable.sql
@ddl_pin ogg_sou
4.設定 Golden Gate
進入 ogg_home, 執行 ./ggsci
4.1 啟動 Golden Gate 管理程式
GGSCI (oracle9i) 5> start mgr
Manager started.
GGSCI (oracle9i) 6> info mgr
Manager is running (IP port oracle9i.7809, Process ID 5739).
GGSCI (oracle9i) 7>
4.2在來源端建立Extract
在這邊會建立兩個 Extract , 一個是一般的extract 一個是data pump的extract
使用ogg帳號登入ggsci
GGSCI (oracle9i) 7> dblogin userid ogg,password ogg
Successfully logged into database.
4.2.1 建立第一個extract
add extract ext1, tranlog,begin now
edit params ext1
(使用vi編輯,也可以直接編輯$ogg_home/dirprm/mgr.prm, 相關設定檔都放在 $ogg_home/dirprm 底下)
加入以下內容
EXTRACT ext1
SETENV(ORACLE_SID="orcl11g")
SETENV (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)
userid ogg, password ogg
EXTTRAIL /u02/ogg_sou/dirdat/et
DDL INCLUDE ALL
TABLE vincentyuan.*;
userid:填入ogg的帳號密碼
setenv:可以在這邊設定環境變數
table:設定指定同步的table,指定多個table,可以使用*符號
設定完後, 啟動process
GGSCI (oracle9i as ogg_sou@orcl11g) 11> start ext1
Sending START request to MANAGER ...
EXTRACT EXT1 starting
GGSCI (oracle9i as ogg_sou@orcl11g) 12>
GGSCI (oracle9i as ogg_sou@orcl11g) 12> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EXT1 00:00:00 00:00:00
GGSCI (oracle9i as ogg_sou@orcl11g) 13>
要是status 不在 running ,可以下 view report EXT1 , 查看log
4.2.2 為第一個 extract 配置 trail
GGSCI (oracle9i as ogg_sou@orcl11g) 13>add exttrail /u02/ogg_sou/dirdat/et,extract ext1
exttrail added.
4.2.3 建立第二個 extract (data dump)
設定 pump config
edit params pump_so
填入以下設定
extract pump_so
dynamicresolution
passthru
rmthost 10.1.53.100,mgrport 7809,compress
rmttrail /u02/ogg_trg/dirdat/pt
DDL INCLUDE ALL
table vincentyuan.*;
*rmthost :為目的端的IP及Port
*rmttrail :為目的端的接收資料檔的位置
配置 data pump process
add extract pump_so,exttrailsource /u02/ogg_sou/dirdat/et
為data pump增加遠端trail文件
add rmttrail /u02/ogg_trg/dirdat/pt,extract pump_so
ADD EXTTRAIL /ORA/software/ogg_source/software/dirdat/pt, EXTRACT PUMP_SO
*. 他會產生開頭 pt 加上 9個數字的檔案 , 這個檔案會傳到target端 /u02/ogg_trg/dirdat 這邊設定的是要丟到Target 哪一個目錄
設定完後啟動Process
GGSCI (oracle9i as ogg_sou@orcl11g) 14> start pump_so
Sending START request to MANAGER ...
EXTRACT PUMP_SO starting
GGSCI (oracle9i as ogg_sou@orcl11g) 16> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EXT1 00:00:00 00:00:05
EXTRACT RUNNING PUMP_SO 00:00:00 00:17:52
GGSCI (oracle9i as ogg_sou@orcl11g) 17>
4.3 在目的端建立 Replicat
4.3.1 先在目的端配置 checkpoing table
[oracle@datagu ogg_trg]$ ./ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 12.2.0.1.1 OGGCORE_12.2.0.1.0_PLATFORMS_151211.1401_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Dec 12 2015 00:54:38
Operating system character set identified as UTF-8.
Copyright (C) 1995, 2015, Oracle and/or its affiliates. All rights reserved.
GGSCI (datagu) 1> dblogin userid ogg,password ogg
Successfully logged into database.
GGSCI (datagu as ogg_tar@orclogg) 2> add checkpointtable ogg_trg.ogg_chk
Successfully created checkpoint table ogg_trg.ogg_chk
將 checkpoint table 設定在 golden gate 裡頭
edit params ./GLOBALS
GGSCHEMA OGG_TRG
CHECKPOINTTABLE OGG_TRG.OGG_CHK
4.3.2 配置 Peplicat
新增Peplicat
add replicat rep01,exttrail /u02/ogg_trg/dirdat/pt,checkpointtable OGG_TAR.OGG_CHK
*.這邊設定要從哪裡讀取從target丟過來的檔案
設定參數
edit params rep01
將以下內容加入
replicat repl
setenv (ORACLE_SID=orclogg)
userid ogg,password ogg
assumetargetdefs
reperror default,discard
discardfile /u02/ogg_trg/dirdat/repl.dsc,append,megabytes 50
dynamicresolution
DDL INCLUDE ALL
DDLERROR DEFAULT IGNORE RETRYOP MAXRETRIES 3 RETRYDELAY 5
map vincentyuan.*, target vincentyuan.*;
*DDLERROR DEFAULT IGNORE RETRYOP MAXRETRIES 3 RETRYDELAY 5
是只說 DDL要是同步出現錯誤時, 會以每五秒Retry一次,會try 3次, 要是仍報錯,則被略過
啟動 Peplicat
GGSCI (datagu as ogg_tar@orclogg) 9> start rep01
Sending START request to MANAGER ...
REPLICAT REP01 starting
GGSCI (datagu as ogg_tar@orclogg) 10>
GGSCI (datagu as ogg_tar@orclogg) 10>
GGSCI (datagu as ogg_tar@orclogg) 10> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT RUNNING REP01 00:00:00 00:00:04
GGSCI (datagu as ogg_tar@orclogg) 11>
5.測試DDL
在來源端create table 後, 在去目的端就可以看到資料庫新增的table
要是同步失敗, 可以再次確認路徑是否正確
我在安裝過程中, 較常遇到的是路徑及名稱問題, 確定完後就可解決
備註: 在ggsci 指令 : info name detail 可以查看Process更詳細的內容
