目標:搭建一個Oracle到Oracle的Goldengate環境(支持DDL+DML)。
Goldengate結構介紹:
環境:
OS:Red Hat Enterprise Linux Server release 5.5 (Tikanga)
DB:Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
gg1和gg2互為source和target,所處OS和所安DB均一樣。
一、安裝GG
在gg1和gg2都執行如下操作:
(1)添加環境變量,在/home/oracle/.bash_profile文件裡添加如下內容:
export PATH=/u01/ggate:$PATH
exportLD_LIBRARY_PATH=/u01/ggate:$LD_LIBRARY_PATH
export ggtest=/u01/ggate
這裡的GG 和Oracle 使用的是相同的用戶,所以把GG 的變量加上就可以了。加載剛剛設置的環境變量:
source/home/oracle/.bash_profile
(2)使用ggsci工具,創建必要的目錄
gg1:/u01/ggate> ggsci
--調用ggsci 工具
Oracle GoldenGate Command Interpreter forOracle
Version 11.1.1.1OGGCORE_11.1.1_PLATFORMS_110421.2040
Linux, x64, 64bit (optimized), Oracle 11gon Apr 21 2011 22:42:14
Copyright (C) 1995, 2011, Oracle and/or itsaffiliates. All rights reserved.
GGSCI (gg1) 1> create subdirs
--使用ggsci 工具創建目錄
Creating subdirectories under currentdirectory /u01/ggate
Parameter files /u01/ggate/dirprm: created
Report files /u01/ggate/dirrpt: created
Checkpoint files /u01/ggate/dirchk: created
Process status files /u01/ggate/dirpcs: created
SQL script files /u01/ggate/dirsql: created
Database definitions files /u01/ggate/dirdef: created
Extract data files /u01/ggate/dirdat: created
Temporary files /u01/ggate/dirtmp: created
Veridata files /u01/ggate/dirver: created
Veridata Lock files /u01/ggate/dirver/lock: created
Veridata Out-Of-Sync files /u01/ggate/dirver/oos: created
Veridata Out-Of-Sync XML files/u01/ggate/dirver/oosxml: created
Veridata Parameter files /u01/ggate/dirver/params: created
Veridata Report files /u01/ggate/dirver/report: created
Veridata Status files /u01/ggate/dirver/status: created
Veridata Trace files /u01/ggate/dirver/trace: created
Stdout files /u01/ggate/dirout: created
GGSCI (gg1) 2>
以上就是GG 的安裝,在source 和target database 都執行。
二、分別配置source和target
(1)GoldenGate通過抓取源端數據庫重做日志進行分析,將獲取的數據應用到目標端,實現數據同步。因此,源數據庫需要必須處於歸檔模式,並啟用附加日志和強制日志。
歸檔模式、附加日志、強制日志
--查看
SQL> select log_mode,supplemental_log_data_min,force_logging from v$database;
LOG_MODE SUPPLEME FOR
------------ -------- ---
ARCHIVELOG NO NO
--修改
(1)archivelog
SQL>shutdown immediate
SQL>startup mount
SQL>alter database archivelog;
SQL>alter database open;
(2) force logging
SQL>alterdatabase force logging;
(3)supplemental log data
SQL>alterdatabase add supplemental log data;
(2)禁用Recycle Bin
如果啟用DDL 支持,必須關閉recycle bin。官網的解釋如下:
If the recyclebin is enabled, the Oracle GoldenGate DDL trigger session receives implicitrecycle bin DDL operations that cause the trigger to fail.
Oracle 11g:
SQL> alter system set recyclebin=offscope=spfile;
System altered.
如果數據庫是10g,需要關閉recyclebin並重啟;或者手工purge recyclebin。
(3)創建存放DDL 信息的user並賦權
SQL> create user ggtest identified by oracle default tablespace users temporary tablespace temp;
User created.
SQL> grant connect,resource to ggtest;
Grant succeeded.
SQL> grant execute on utl_file to ggtest;
Grant succeeded.
退出所有使用Oracle 的session,然後使用SYSDBA權限的用戶執行如下腳本:
gg1:/u01/ggate> echo $ggate
/u01/ggate
--進入GG的目錄,然後調用腳本:
gg1:/home/oracle> cd $ggate
gg1:/u01/ggate> sqlplus / as sysdba;
SQL*Plus: Release 11.2.0.3.0 Production onTue Nov 8 19:41:58 2011
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise EditionRelease 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Miningand Real Application Testing options
--腳本1:
SQL> @marker_setup.sql;
Marker setup script
You will be prompted for the name of aschema for the GoldenGate database objects.
NOTE: The schema must be created prior torunning this script.
NOTE: Stop all DDL replication beforestarting this installation.
--輸入我們之前創建的用戶名:
Enter GoldenGate schema name:ggtest
Marker setup table script complete, runningverification script...
Please enter the name of a schema for theGoldenGate database objects:
Setting schema name to ggtest
MARKER TABLE
-------------------------------
OK
MARKER SEQUENCE
-------------------------------
OK
Script complete.
--腳本2:
SQL> @ddl_setup.sql;
GoldenGate DDL Replication setup script
Verifying that current user has privilegesto install DDL Replication...
You will be prompted for the name of aschema for the GoldenGate database objects.
NOTE: For an Oracle 10g source, the systemrecycle bin must be disabled. For Oracle 11g and later, it can be enabled.
--注意這裡提示我們在10g裡,必須關閉recycle bin,在11g以後的版本,可以不用關閉。
NOTE: The schema must be created prior torunning this script.
NOTE: Stop all DDL replication beforestarting this installation.
--提示輸入GG的用戶:
Enter GoldenGate schema name:ggtest
You will be prompted for the mode ofinstallation.
To install or reinstall DDL replication,enter INITIALSETUP
To upgrade DDL replication, enter NORMAL
--這裡讓我們選擇安裝模式: install 和 reinstall 選擇INITIALSETUP
Enter mode of installation:INITIALSETUP
Working, please wait ...
Spooling to file ddl_setup_spool.txt
Checking for sessions that are holdinglocks on Oracle Golden Gate metadata tables ...
Check complete.
Using ggtest as a GoldenGate schema name,INITIALSETUP as a mode of installation.
Working, please wait ...
DDL replication setup script complete,running verification script...
Please enter the name of a schema for theGoldenGate database objects:
Setting schema name to ggtest
DDLORA_GETTABLESPACESIZE STATUS:
……
STATUS OF DDL REPLICATION
-------------------------------------------------------------------------------------------------------
SUCCESSFUL installation of DDL Replicationsoftware components
Script complete.
--腳本3:
SQL> @role_setup.sql;
GGS Role setup script
This script will drop and recreate the roleGGS_GGSUSER_ROLE
To use a different role name, quit thisscript and then edit the params.sql script to change the gg_role parameter tothe preferred name. (Do not run the script.)
You will be prompted for the name of aschema for the GoldenGate database objects.
NOTE: The schema must be created prior torunning this script.
NOTE: Stop all DDL replication beforestarting this installation.
--同樣輸入GG用戶名:
Enter GoldenGate schema name:ggtest
Wrote file role_setup_set.txt
PL/SQL procedure successfully completed.
Role setup script complete
Grant this role to each user assigned tothe Extract, GGSCI, and Manager processes, by using the following SQL command:
--這裡提示我們賦權給相關的用戶:
GRANT GGS_GGSUSER_ROLE TO<loggedUser>
where <loggedUser> is the userassigned to the GoldenGate processes.
--腳本4:賦權
SQL> grant GGS_GGSUSER_ROLE to ggtest;
Grant succeeded.
--腳本5:
SQL> @ddl_enable.sql;
Trigger altered.
注意這裡腳本創建的table都是使用默認的名稱,當然也可以修改這些table的默認名。
三、測試
注意:
(1) 目標庫的用戶名和對象名稱可以與源端不同,關鍵在於配置文件中要能夠正確匹配。
(2) 配置源和目標兩端tnsnames,保持互聯互通。(記得啟動lsnrctl)
gg1:
SQL> create user ssgg identified by oracle default tablespace users temporary tablespace temp;
User created.
SQL> grant connect,resource,dba to ssgg;
Grant succeeded.
gg2:
SQL> create user ttgg identified by oracle default tablespace users temporary
tablespace temp;
User created.
SQL> grant connect,resource,dba to ttgg;
Grant succeeded.
參數設置
配置gg1,添加checkpoint表:
GGSCI (gg1) 1> dblogin userid ggtest,password oracle
Successfully logged into database.
GGSCI (gg1) 2> view params ./GLOBAL
ggschema ggtest
checkpointtable ggtest.checkpoint
GGSCI (gg1) 27> view params mgr
PORT 7809 --指定管理端口
dynamicportlist 7810-7900 --動態端口列表,當指定端口不可用時,管理進程會自動選擇一個可用端口。最大可指定256個端口
autostart er * --mgr開啟時自動開啟所有的extract and replicat process
autorestart er *,retries 5,waitminutes 2 --extract and replicat processes fail mgr automatically start參數每2分鐘重啟一次,重啟5次後放棄
1、to configure the primary extract group
GGSCI (gg1) 10> add extract ext01,tranlog,begin now
EXTRACT added.
GGSCI (gg1) 13> add exttrail /u01/ggate/dirdat/sd,extract ext01
EXTTRAIL added.
GGSCI (gg1) 28> view params ext01
extract ext01 --提取進程組的名字
userid ggtest, password oracle --同步用戶
exttrail /u01/ggate/dirdat/sd --trail 文件目錄和標識(類似於sd*這樣文件),命名只能是2個字符。
tranlogoptions excludeuser ggtest --避免循環復制的出現
DDL INCLUDE ALL
DDLOPTIONS ADDTRANDATA
table ssgg.*; --同步的表或用戶下所有的表
當你需要同步除Goldengate和Oracle數據庫自帶的用戶外所有用戶的DDL操作時,可在源端主抽取進程中加入下面的內容
ddl include all
然後再在目標端Replicat進程參數文件中加入DDL錯誤的處理語句即可。
2、 t o c o n f i g u r e t h e d a t a p u m p
GGSCI (gg1) 19> add extract p01,exttrailsource /u01/ggate/dirdat/sd,begin now
EXTRACT added.
GGSCI (gg1) 20> add rmttrail /u01/ggate/dirdat/pd,extract p01
RMTTRAIL added.
GGSCI (gg1) 29> view params p01
extract p01
userid ggtest,password oracle
rmthost 192.168.50.231,mgrport 7809 --指定遠端IP和MGR端口
rmttrail /u01/ggate/dirdat/pd
table ssgg.*;
3、 to configure the rreplicat group
GGSCI (gg1) 3> add replicat r02,exttrail /u01/ggate/dirdat/dp,begin now
ERROR: No checkpoint table specified for ADD REPLICAT.
GGSCI (gg1) 4> add replicat r02,exttrail /u01/ggate/dirdat/dp,checkpointtable ggtest.checkpoint
REPLICAT added.
GGSCI (gg1) 30> view params r02
replicat r02
assumetargetdefs --定義目標表和源表使用MAP語法有相同的列結構,當出現熱點時,不查看源結構而直接從源定義文件查
看
userid ggtest,password oracle
DISCARDFILE /u01/ggate/dirdat/sdisc.dsc,append,megabytes 100
DDL INCLUDE MAPPED
DDLERROR DEFAULT IGNORE RETRYOP
map ttgg.*, target ssgg.*;
啟動所有進程:
GGSCI (gg1) 3> start mgr
Manager started.
GGSCI (gg1) 4> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EXT01 00:00:00 00:00:07
EXTRACT RUNNING P01 00:00:00 00:00:01
REPLICAT RUNNING R02 00:00:00 00:00:08
配置gg2,添加checkpoint表:
GGSCI (gg2) 1> dblogin userid ggtest,password oracle
Successfully logged into database.
GGSCI (gg2) 25> view params ./GLOBAL
ggschema ggtest
checkpointtable ggtest.checkpoint
GGSCI (gg2) 29> view params mgr
PORT 7809
dynamicportlist 7810-7900
autostart er *
autorestart er *,retries 5,waitminutes 2
c o n f i g u r a t i o n f r o m s e c o u n d a r y s y s t e m t o p r i m a r y s y s t e m
1 、to configure the primary extract group
GGSCI (gg2) 12> add extract ext02,tranlog,begin now
EXTRACT added.
GGSCI (gg2) 13> add exttrail /u01/ggate/dirdat/td,extract ext02
EXTTRAIL added.
GGSCI (gg2) 26> view params ext02
extract ext02
userid ggtest, password oracle
exttrail /u01/ggate/dirdat/td
tranlogoptions excludeuser ggtest
DDL INCLUDE ALL
DDLOPTIONS ADDTRANDATA
table ttgg.*;
2 、to configure the pump
GGSCI (gg2) 17> add extract p02,exttrailsource /u01/ggate/dirdat/td,begin now
EXTRACT added.
GGSCI (gg2) 19> add rmttrail /u01/ggate/dirdat/dp,extract p02
RMTTRAIL added.
GGSCI (gg2) 27> view params p02
extract p02
userid ggtest,password oracle
rmthost 192.168.50.230,mgrport 7809
rmttrail /u01/ggate/dirdat/dp
table ttgg.*;
3.to configure the replicat group
GGSCI (gg2) 7> add replicat r01,exttrail /u01/ggate/dirdat/pd,begin now
ERROR: No checkpoint table specified for ADD REPLICAT.
GGSCI (gg2) 8> add replicat r01,exttrail /u01/ggate/dirdat/pd,checkpointtable ggtest.checkpoint;
REPLICAT added.
GGSCI (gg2) 28> view params r01
replicat r01
assumetargetdefs
userid ggtest,password oracle
DISCARDFILE /u01/ggate/dirdat/sdisc.dsc,append,megabytes 100
DISCARDFILE不能直接在安裝目錄/u01/ggate/,進程可能起不來。
DDL INCLUDE MAPPED
DDLERROR DEFAULT IGNORE RETRYOP
map ssgg.*, target ttgg.*;
啟動所有進程:
GGSCI (gg2) 3> start mgr
Manager started.
GGSCI (gg2) 4> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EXT02 00:00:00 00:00:03
EXTRACT RUNNING P02 00:00:00 00:00:07
REPLICAT RUNNING R01 00:00:14 00:00:04
數據同步測試:
Gg1:
SQL> conn ssgg/oracle
Connected.
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
SSGG TABLE
TTGG TABLE
SQL> create table test as select * from ssgg;
Table created.
SQL> select * from test;
ID
----------
1
5
SQL> commit;
Commit complete.
然後查看gg2,看數據是否同步
Gg2:
SQL> conn ttgg/oracle
Connected.
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
SSGG TABLE
TTGG TABLE
SQL> /
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
SSGG TABLE
TEST TABLE
TTGG TABLE
SQL> insert into test values(10);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from test;
ID
----------
1
5
10
再返回gg1,查看數據是否同步:
SQL> host hostname
gg1
SQL> select * from test;
ID
----------
1
5
10
至此,Oracle到Oracle的雙向數據同步成功(DML+DDL)。
實現雙向復制,一般用於雙業務中心環境下在實際應用中。雙向復制(DDL+DML)面臨著許多問題,主要有如下幾點:
1. 如果兩個庫同時更新同一條記錄如何處理?調整業務規則
2. 如果網絡出現失敗如何處理?支持斷點續傳
3. 如果數據不同步後如何修復?導出源端,導入目標端
總結:
雙向復制避免數據沖突是難點,解決這個難點根本還是調整業務:
a) 只在一段開展業務
b) 兩端開展不同業務,涉及不同數據集
c) 兩端開展相同業務,但依據地域或其他條件對數據予以區分,兩邊不操作同一條數據
補充:
在oracle中可以通過rowid來定位某條記錄,但是目標端的數據庫和源端數據庫的數據庫可能完全不一樣,所以無法通過rowid來確定源端數據庫的邏輯變化,這時附加日志supplemental log便登上了表演的舞台。數據庫在開啟附加日志功能後,對於源端的修改操作,oracle會同時追加能夠唯一標示記錄的列到redo log。這樣目標端數據庫就可以知道源端發生了哪些具體的變化。
在目標庫上創建checkpointtable ,此表中保存的檢查點指示 Replicat 進程的當前讀寫位置。這用於在進程需要重啟時或者在服務器出現任何故障或網絡停頓時防止數據丟失。如果沒有這一機制,將導致數據丟失。另一個好處是,通過使用檢查點,多個 Extract 或 Replicat 進程可通過相同的一組線索進行讀取。
對於以批處理模式運行的 Extract 和 Replicat 進程,檢查點不是必需的,因為這樣的進程總是可以重啟。然而,在 Extract 和 Replicat 進程連續工作的情況下,檢查點是必需的。檢查點通常保存在 dirchk 子目錄的文件中,但是對於 Replicat,可以選擇在數據庫的檢查點表中保存檢查點。