搭建一個Oracle到Oracle的Goldengate雙向復制環境

搭建一個Oracle到Oracle的Goldengate雙向復制環境

目標：搭建一個Oracle到Oracle的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;

（２）禁用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。

（３）創建存放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、 t o c o n f i g u r e t h e p r i m a r y e x t r a c t g o u p

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、 t o c o n f i g u r e t h e r e p l i c a t g r o u p

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 、t o c o n f i g u r e t h e p r i m a r y e x t r a c t g r o u p

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 、t o c o n f i g u r e t h e p u m p

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.t o c o n f i g u r e t h e r e p l i c a t g r o u p

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)。

實現雙向復制，一般用於雙業務中心環境下在實際應用中。雙向復制（ＤＤＬ＋ＤＭＬ）面臨著許多問題，主要有如下幾點：

1. 如果兩個庫同時更新同一條記錄如何處理？調整業務規則

2. 如果網絡出現失敗如何處理？支持斷點續傳

3. 如果數據不同步後如何修復？導出源端，導入目標端

總結：

雙向復制避免數據沖突是難點，解決這個難點根本還是調整業務：

a) 只在一段開展業務

b) 兩端開展不同業務，涉及不同數據集

c) 兩端開展相同業務，但依據地域或其他條件對數據予以區分，兩邊不操作同一條數據

補充：

在oracle中可以通過rowid來定位某條記錄，但是目標端的數據庫和源端數據庫的數據庫可能完全不一樣，所以無法通過rowid來確定源端數據庫的邏輯變化，這時附加日志supplemental log便登上了表演的舞台。數據庫在開啟附加日志功能後，對於源端的修改操作，oracle會同時追加能夠唯一標示記錄的列到redo log。這樣目標端數據庫就可以知道源端發生了哪些具體的變化。

在目標庫上創建checkpointtable ，此表中保存的檢查點指示 Replicat 進程的當前讀寫位置。這用於在進程需要重啟時或者在服務器出現任何故障或網絡停頓時防止數據丟失。如果沒有這一機制，將導致數據丟失。另一個好處是，通過使用檢查點，多個 Extract 或 Replicat 進程可通過相同的一組線索進行讀取。

對於以批處理模式運行的 Extract 和 Replicat 進程，檢查點不是必需的，因為這樣的進程總是可以重啟。然而，在 Extract 和 Replicat 進程連續工作的情況下，檢查點是必需的。檢查點通常保存在 dirchk 子目錄的文件中，但是對於 Replicat，可以選擇在數據庫的檢查點表中保存檢查點。