Oracle Golden Gate 系列十七 -- GG 一对多 real-time 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(满)
