Oracle Golden Gate 系列十七 GG 一对多 realtime data distribution 说明与示例

一.官网说明

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来预防网络故障。如果有多个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个节点做测试，节点1做Source DB，节点2和3做Target DB。从GG1 发送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

因为我们要发送到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，就需要根据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;

这里面有一个数据初始化的问题，具体参考：

Oracle Golden Gate 系列九 --GG 数据初始化装载说明与示例

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

Oracle GoldenGate 系列十二 --GG 数据初始化装载二基于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表空间和数据文件的关系，否则拒绝申请----

DBA1 群：62697716(满); DBA2 群：62697977(满) DBA3 群：62697850(满)

DBA 超级群：63306533(满); DBA4 群：83829929(满) DBA5群： 142216823(满)

DBA6 群：158654907(满) DBA7 群：69087192(满) DBA8 群：172855474

DBA 超级群2：151508914 DBA9群：102954821 聊天群：40132017(满)