?

一.官網(wǎng)說明

A datadistribution configuration is a one-to-many configuration. Oracle GoldenGatesupports synchronization of a source database to any number of target systems.Oracle GoldenGate supports like-to-like or heterogeneous transfer of data, withcapabilities for filtering and conversion on any system in the configuration(support varies by database platform).

--GG 支持一個Source 對任意個Target 的同步。

?

1.1 Considerations for a data-distribution configuration

1.1.1 Fault tolerance

For a datadistribution configuration, the use of data pumps ensures that if network connectivityto any of the targets fails, the captured data still can be sent to the other targets.Use a primary Extract group and a data-pump Extract group in the source configuration,one for each target.

--對于data distribution，需要使用data pump來預防網(wǎng)絡故障。如果有多個Target，則每個Target 對應一個Data pump。

?

1.1.2 Filtering and conversion

You can use anyprocess to perform filtering and conversion. However, using the data pumps toperform filtering operations removes that processing overhead from the primary Extractgroup, and it reduces the amount of data that is sent across the network.

?

1.1.2.1 To filter data,you can use:

(1)????A FILTER or WHERE clause in aTABLE statement (Extract) or in a MAP statement (Replicat).

(2)????A SQL query or procedure

(3)????User exits

?

1.1.2.2 To transformdata, you can use:

(1)????Native Oracle GoldenGateconversion functions

(2)????A user exit from the Extract orReplicat process that applies rules from an external transformation solution,then returns the manipulated data to Oracle GoldenGate.

(3)????Replicat to deliver datadirectly to an ETL solution or other transformation engine.

?

1.1.3 Data volume

The standard configuration is sufficientif:

(1)????The transaction load isconsistent and of moderate volume that is spread out more or less evenly amongall of the objects to be replicated.

(2)????There are none of thefollowing: tables that are subject to long-running transactions, tables thathave a very large number of columns that change, or tables that contain columnsfor which Oracle GoldenGate must fetch from the database (generally columnswith LOBs, columns that are affected by SQL procedures executed by Oracle GoldenGate,and columns that are not logged to the transaction log).

?

If your environment does not satisfy those conditions, consider adding one or more setsof parallel processes.

?

1.1.4 Additional information

(1)????For additional system anddatabase configuration requirements, see the Oracle GoldenGate Installation andSetup Guide for your database type.

(2)????For information aboutadditional requirements for Teradata Extract configurations, see the OracleGoldenGate Teradata Installation and Setup Guide.

(3)????For detailed instructions onconfiguring Oracle GoldenGate change capture and delivery groups, see“Configuring online change synchronization” on page 120.

(4)????For complete syntax anddescriptions of the Oracle GoldenGate commands and parameters, see the OracleGoldenGate Windows and UNIX Reference Guide.

?

1.2 Creating a data distribution configuration

Refer to Figure 12 for a visualrepresentation of the objects you will be creating.

?

Figure 12 Oracle GoldenGate configurationelements for data distribution

?

?

1.2.1 Source system

-- To configure theManager process

1. On the source, configure the Managerprocess。

2. In the Manager parameter file, use thePURGEOLDEXTRACTS parameter to control the purging of files from the localtrail.

?

--To configure theprimary Extract

3. On the source, use the ADD EXTRACT commandto create a primary Extract group. For documentation purposes, this group iscalled ext.

ADD EXTRACT <ext>, TRANLOG, BEGIN<time>, [, THREADS]

注意： Use TRANLOG as the data source option. For DB2 on Z/OS, specify thebootstrap data set (BSDS) name following TRANLOG.

?

4. On the source, use the ADD EXTTRAILcommand to create a local trail.

ADD EXTTRAIL<local_trail>, EXTRACT <ext>

注意： Use the EXTRACT argument to link this trail to the primary Extractgroup. The primary Extract group writes to this trail, and the data pump groupsread it.

?

5. On the source, use the EDIT PARAMScommand to create a parameter file for the primary Extract group. Include thefollowing parameters plus any others that apply to your database environment.

?

-- Identify the Extract group:

EXTRACT <ext>

-- Specify database login information asneeded for the database:

[SOURCEDB <dsn_1>,][USERID<user>[, PASSWORD <pw>]]

-- Specify the local trail that thisExtract writes to:

EXTTRAIL <local_trail>

-- Specify tables to be captured:

TABLE <owner>.<table>;

注意： Use EXTTRAIL to specify the local trail.

?

--To configure the datapump Extract groups

6. On the source, use the ADD EXTRACTcommand to create a data pump for each target system. For documentationpurposes, these groups are called pump_1 and pump_2.

ADD EXTRACT <pump_1>, EXTTRAILSOURCE<local_trail>, BEGIN <time>

ADD EXTRACT <pump_2>, EXTTRAILSOURCE<local_trail>, BEGIN <time>

注意： UseEXTTRAILSOURCE as the data source option, and supply the name of the localtrail.

?

7. On the source, use the ADD RMTTRAILcommand to specify a remote trail that will be created on each of the targetsystems.

ADD RMTTRAIL <remote_trail_1>,EXTRACT <pump_1>

ADD RMTTRAIL <remote_trail_2>,EXTRACT <pump_2>

注意： Use the EXTRACT argument to link each remote trail to a differentdata pump group. The linked data pump writes to this trail.

?

8. On the source, use the EDIT PARAMScommand to create a parameter file for each of the

data pumps. Include the followingparameters plus any others that apply to your

database environment.

Data pump_1

-- Identify the data pump group:

EXTRACT <pump_1>

-- Specify database login information asneeded for the database:

[SOURCEDB <dsn_1>,][USERID<user>[, PASSWORD <pw>]]

-- Specify the name or IP address of thefirst target system:

RMTHOST <target_1>, MGRPORT<portnumber>

-- Specify the remote trail on the firsttarget system:

RMTTRAIL <remote_trail_1>

-- Allow mapping, filtering, conversion orpass data through as-is:

[PASSTHRU | NOPASSTHRU]

-- Specify tables to be captured:

TABLE <owner>.<table>;

?

Data pump_2

-- Identify the data pump group:

EXTRACT <pump_2>

-- Specify database login information asneeded for the database:

[SOURCEDB <dsn_1>,][USERID<user>[, PASSWORD <pw>]]

-- Specify the name or IP address of thesecond target system:

RMTHOST <target_2>, MGRPORT<portnumber>

-- Specify the remote trail on the secondtarget system:

RMTTRAIL <remote_trail_2>

-- Allow mapping, filtering, conversion orpass data through as-is:

[PASSTHRU | NOPASSTHRU]

-- Specify tables to be captured:

TABLE <owner>.<table>;

?

NOTE ：

To use PASSTHRUmode, the names of the source and target objects must be identical. No columnmapping, filtering, SQLEXEC functions, transformation, or other functions thatrequire data manipulation can be specified in the parameter file. You cancombine normal processing with pass-through processing by pairing PASSTHRU andNOPASSTHRU with different TABLE statements.

?

1.2.2 Target systems

--To configure theManager process

9. On each target, configure the Managerprocess.

10. In each Manager parameter file, use thePURGEOLDEXTRACTS parameter to control the purging of files from the trail.

?

--To configure theReplicat groups

11. On each target, create a Replicatcheckpoint table.

12. On each target, use the ADD REPLICATcommand to create a Replicat group for the remote trail on that system. Fordocumentation purposes, these groups are called rep_1 and rep_2.

?

Target_1

ADD REPLICAT <rep_1>, EXTTRAIL<remote_trail_1>, BEGIN <time>

Target_2

ADD REPLICAT <rep_2>, EXTTRAIL <remote_trail_2>,BEGIN <time>

注意：Use the EXTTRAIL argument to link the Replicat group to the correcttrail.

?

13. On each target, use the EDIT PARAMScommand to create a parameter file for the Replicat group. Use the followingparameters plus any others that apply to your database environment.

?

Target_1

-- Identify the Replicat group:

REPLICAT <rep_1>

-- State whether or not source and targetdefinitions are identical:

SOURCEDEFS <full_pathname> |ASSUMETARGETDEFS

-- Specify database login information as neededfor the database:

[TARGETDB <dsn_2>,] [USERID <userid>[, PASSWORD <pw>]]

-- Specify error handling rules:

REPERROR (<error>, <response>)

-- Specify tables for delivery:

MAP <owner>.<table>, TARGET<owner>.<table>[, DEF <template name>];

Target_2

-- Identify the Replicat group:

REPLICAT <rep_2>

-- State whether or not source and targetdefinitions are identical:

SOURCEDEFS <full_pathname> |ASSUMETARGETDEFS

-- Specify database login information asneeded for the database:

[TARGETDB <dsn_3>,] [USERID <userid>[, PASSWORD <pw>]]

-- Specify error handling rules:

REPERROR (<error>, <response>)

-- Specify tables for delivery:

MAP <owner>.<table>, TARGET<owner>.<table>[, DEF <template name>];

?

注意： You can use anynumber of MAP statements for any given Replicat group. All MAP statements for agiven Replicat group must specify the same objects that are

contained in the trail that is linked tothe group.

?

二.示例

我這里用3個節(jié)點做測試，節(jié)點1做Source DB，節(jié)點2和3做Target DB。 從GG1 發(fā)送data 到GG2和GG3。

?

DB:Oracle 11.2.0.3

OS: redhat 5.4

?

2.1 設置Manager

GGSCI (gg1) 3> edit params mgr

PORT 7809

?

2.2 設置Extract進程

GGSCI (gg1) 11>?add extractext1,tranlog, begin now

GGSCI (gg1) 12>?add exttrail/u01/ggate/dirdat/lt, extract ext1

GGSCI (gg1) 13> edit params ext1

?

GGSCI (gg1) 4> view params ext1

?

extract ext1

ENCRYPTTRAIL

userid ggate@gg1,passwordAACAAAAAAAAAAAFAPHODADQGAJVDSHPG,encryptkey default

--rmthost gg2,mgrport 7809

--rmttrail /u01/ggate/dirdat/lt

exttrail /u01/ggate/dirdat/lt

ddl include all objname dave.pdba;

table dave.pdba;

?

這里對密碼進行了加密，有關加密這塊，參考：

Oracle Golden Gate 系列十六 --配置 GG 安全 說明 與 示例

http://blog.csdn.net/tianlesoftware/article/details/6991263

?

2.3 在Source 端配置 DataPump

因為我們要發(fā)送到2個Target，所以要配置2個Data Pump 進程。

--data pump1：

GGSCI (gg1) 49> add extractdpump,exttrailsource /u01/ggate/dirdat/lt

GGSCI (gg1) 51> add rmttrail/u01/ggate/dirdat/lt, extract dpump

GGSCI (gg1) 5> view params dpump

extract dpump

userid ggate@gg1, password ggate

rmthost gg2, mgrport7809,compress,compressthreshold 0

rmttrail /u01/ggate/dirdat/lt

passthru

table dave.pdba;

?

--data pump2：

GGSCI (gg1) 49> add extract dpump2,exttrailsource/u01/ggate/dirdat/lt

GGSCI (gg1) 51> add rmttrail/u01/ggate/dirdat/lt, extract dpump2

GGSCI (gg1) 5> view params dpump2

extract dpump2

userid ggate@gg1, password ggate

rmthost gg3, mgrport7809,compress,compressthreshold 0

rmttrail /u01/ggate/dirdat/lt

passthru

table dave.pdba;

?

這里注意Data Pump 默認是從lt000000 開始讀取trail 文件，如果是新搭建的GG 同步，那么都是從lt000000開始處理，所以沒有問題，如果是后來修改稱data pump，就需要根據(jù)extract 進程的錯誤提示信息，使用如下命令：

?????? GGSCI(gg1) 82> alter extract dpump,extseqno 2,extrba1965317

?

具體參考：

Oracle GoldenGate 系列十 -- 配置 DataPump process 說明 與 示例

http://blog.csdn.net/tianlesoftware/article/details/6978501

?

2.4 分別在2個Target 配置Replicat

--配置checkpoint

GGSCI (gg2) 6> EDIT PARAMS ./GLOBALS

GGSCHEMA ggate

CHECKPOINTTABLE ggate.checkpoint

GGSCI (gg2) 12>?dblogin useridggate@gg2,password ggate

GGSCI (gg2) 13>?add checkpointtableggate.checkpoint

?

這部分，參考：

Oracle Golden Gate 系列十三 --配置GG進程檢查點（checkpoint） 說明

http://blog.csdn.net/tianlesoftware/article/details/6983928

?

--配置 Replicat：

GGSCI (gg2) 14>?add replicatrep1,exttrail /u01/ggate/dirdat/lt, checkpointtable ggate.checkpoint

GGSCI (gg2) 2> view params rep1

replicat rep1

DECRYPTTRAIL

ASSUMETARGETDEFS

userid ggate@gg2,password ggate

discardfile/u01/ggate/dirdat/rep1_discard.txt, append, megabytes 10

--HANDLECOLLISIONS

ddl include all

ddlerror default ignore retryop

map dave.pdba, target dave.pdba;

?

這里面有一個數(shù)據(jù)初始化的問題，具體參考：

Oracle Golden Gate 系列九 --GG 數(shù)據(jù)初始化裝載 說明 與 示例

http://blog.csdn.net/tianlesoftware/article/details/6976551

?

Oracle GoldenGate 系列十二 --GG 數(shù)據(jù)初始化裝載二 基于SCN 的初始化 說明 與 示例

http://blog.csdn.net/tianlesoftware/article/details/6982908

?

2.5 啟動相關的進程進行

?

--Source DB

GGSCI (gg1) 17> start mgr

GGSCI (gg1) 19> start ext1

?

GGSCI (gg1) 26> info dpump2

?

EXTRACT???DPUMP2??? Initialized?? 2011-11-19 22:26?? Status STOPPED

Checkpoint Lag?????? 00:00:00 (updated 00:12:34 ago)

Log Read Checkpoint? File /u01/ggate/dirdat/lt000000

???????????????????? First Record? RBA 0

?

GGSCI (gg1) 28> info dpump

?

EXTRACT???DPUMP???? Last Started 2011-11-1915:22?? Status ABENDED

Checkpoint Lag?????? 00:00:00 (updated 06:02:32 ago)

Log Read Checkpoint? File /u01/ggate/dirdat/lt000014

???????????????????? 2011-11-1915:44:00.966309? RBA 1009

?

GGSCI (gg1) 29> alterextract dpump2,extseqno 14,extrba 0

EXTRACT altered.

?

GGSCI (gg1) 30> info dpump2

EXTRACT???DPUMP2??? Initialized?? 2011-11-19 22:40?? Status STOPPED

Checkpoint Lag?????? 00:00:00 (updated 00:00:03 ago)

Log Read Checkpoint? File /u01/ggate/dirdat/lt000014

???????????????????? First Record? RBA 0

?

GGSCI (gg1) 31> start dpump

GGSCI (gg1) 32> start dpump2

?

GGSCI (gg1) 39> info all

Program????Status????? Group?????? Lag?????????? Time Since Chkpt

?

MANAGER????RUNNING????????????????????? ?????????????????????

EXTRACT????RUNNING???? DPUMP?????? 00:00:00????? 00:00:01???

EXTRACT????RUNNING???? DPUMP2????? 00:00:00????? 00:01:37???

EXTRACT????RUNNING???? EXT1??????? 00:00:00????? 00:00:09??

?

?

--2個TargetDB

GGSCI (gg3) 8> start rep1

GGSCI (gg3) 10> info all

Program????Status????? Group?????? Lag?????????? Time Since Chkpt

MANAGER????RUNNING??????????????????????????????????????????

REPLICAT???RUNNING???? REP1??????? 00:00:00????? 00:00:04???

?

GGSCI (gg2) 15> start rep1

GGSCI (gg2) 17> info all

Program????Status????? Group?????? Lag?????????? Time Since Chkpt

MANAGER????RUNNING??????????????????????????????????????????

REPLICAT???RUNNING???? REP1??????? 00:00:00????? 00:00:04???

?

2.6 在Source 進行DML操作，驗證同步

--Source DB

SQL> conn dave/dave;

Connected.

SQL> select count(*) from pdba;

?

?COUNT(*)

----------

??2678533

?

--Targt 1：

SQL> conn dave/dave;

Connected.

SQL> select count(*) from pdba;

?

?COUNT(*)

----------

?? 2678533

?

--Target 2：

SQL> conn dave/dave;

Connected.

SQL> select count(*) from pdba;

?COUNT(*)

----------

??2678533

?

--在Source 進行DML操作：

SQL> delete from pdba whererownum<1000;

999 rows deleted.

?

SQL> commit;

Commit complete.

?

SQL> select count(*) from pdba;

?COUNT(*)

----------

??2677534

?

--Target 1:

SQL> select count(*) from pdba;

?

?COUNT(*)

----------

??2677534

?

--Target 2:

SQL> select count(*) from pdba;

?

?COUNT(*)

----------

??2677534

?

同步成功。 至此GG 一對多的測試結束。

?

?

?

?

?

?

-------------------------------------------------------------------------------------------------------

版權所有，文章允許轉載，但必須以鏈接方式注明源地址，否則追究法律責任!

Blog:???? http://blog.csdn.net/tianlesoftware

Weibo: http://weibo.com/tianlesoftware

Email: ? tianlesoftware@gmail.com

Skype: tianlesoftware

?

-------加群需要在備注說明Oracle表空間和數(shù)據(jù)文件的關系，否則拒絕申請----

DBA1 群：62697716(滿);?? DBA2 群：62697977(滿)??DBA3 群：62697850(滿)??

DBA 超級群：63306533(滿);? DBA4 群：83829929(滿) DBA5群： 142216823(滿)?

DBA6 群：158654907(滿)?? DBA7 群：69087192(滿)??DBA8 群：172855474

DBA 超級群2：151508914? DBA9群：102954821???? 聊天 群：40132017(滿)

轉載于:https://www.cnblogs.com/spring3mvc/archive/2011/11/19/2414502.html

總結

以上是生活随笔為你收集整理的Oracle Golden Gate 系列十七 -- GG 一对多 real-time data distribution 说明 与 示例的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內容還不錯，歡迎將生活随笔推薦給好友。