Hey guys !! Welcome back..
I have seen many of my friends facing issues with replicating huge volume tables using goldengate, Here we go, I want to discuss, how goldengate replicat split helps and improve the performance of replication.
In order to split replicat 1 into many, Table involved in replication should have primary key and unique key should be on same column, If a table have primary key and unique key On different column, REPLICAT WILL ABEND BY GENERATING A LOT OF DISCARDS, REPLICAT'S involved in splitting WILL COMPETE EACH OTHER IN APPLYING SAME RECORD ON THE TRAIL to the target table.
Resolution:
we need to add handlecollisions to replicat paramfile and restart the replicat from abend
Steps to split replicat 1 into many, we split replicat using FILTER and RANGE parameters. In this scenario, We are splitting replicat 1 into 4, each replicat logically work ( perform DML) on 1/4 th of base table;
steps to split replicat as follows
STEP1: STOP REPLICAT
GGSCI (eniya.varman.com) 4> STOP RTECVE
Sending STOP request to REPLICAT RTECVE ...
Request processed.
STEP2: collect RBA and sequence number of remote trail from the old replicat !!
GGSCI (eniya.varman.com) 19> INFO RTECVE
REPLICAT RTECVE Last Started 2015-02-01 13:51 Status STOPPED
Checkpoint Lag 00:00:00 (updated 00:00:23 ago)
Log Read Checkpoint File /u01/app/goldengate/remotetrail/zz000004
2015-02-01 19:02:16.537343 RBA 2292
STEP3: PREPARE THE NEW OGG REPLICAT PROCESSES
-- Handles all tables WITHOUT PK /UI
-- GGSCI> add replicat rtecve_1, exttrail /u01/app/goldengate/remotetrail/zz, checkpointtable ggsuser.ckpt
—- ALTER rtecve_1, extseqno 4 extrba 2292
replicat rtecve_1
assumetargetdefs
sourcedefs ./dirdef/teacher.def
------structure of source table and target table is same
userid ggsuser, password oracle
reportcount every 24 hours, rate
discardfile /u01/app/goldengate/discardtrail/rtecve_1.dsc, megabytes 10, append
discardrollover at 06:00
map nspdba.teacher, target nspdba.teacher, FILTER(@RANGE(1,4));
-- Handles all tables WITHOUT PK /UI
-- GGSCI> add replicat rtecve_2, exttrail /u01/app/goldengate/remotetrail/zz, checkpointtable ggsuser.ckpt
—- ALTER rtecve_2, extseqno 4 extrba 2292
replicat rtecve_2
assumetargetdefs
sourcedefs ./dirdef/teacher.def
------structure of source table and target table is same
userid ggsuser, password oracle
reportcount every 24 hours, rate
discardfile /u01/app/goldengate/discardtrail/rtecve_2.dsc, megabytes 10, append
discardrollover at 06:00
map nspdba.teacher, target nspdba.teacher, FILTER(@RANGE(2,4));
-- Handles all tables WITHOUT PK /UI
-- GGSCI> add replicat rtecve_3, exttrail /u01/app/goldengate/remotetrail/zz, checkpointtable ggsuser.ckpt
—- ALTER rtecve_3, extseqno 4 extrba 2292
replicat rtecve_3
assumetargetdefs
sourcedefs ./dirdef/teacher.def
------structure of source table and target table is same
userid ggsuser, password oracle
reportcount every 24 hours, rate
discardfile /u01/app/goldengate/discardtrail/rtecve_3.dsc, megabytes 10, append
discardrollover at 06:00
map nspdba.teacher, target nspdba.teacher, FILTER(@RANGE(4,4));
-- Handles all tables WITHOUT PK /UI
-- GGSCI> add replicat rtecve_4, exttrail /u01/app/goldengate/remotetrail/zz, checkpointtable ggsuser.ckpt
—- ALTER rtecve_4, extseqno 4 extrba 2292
replicat rtecve_4
assumetargetdefs
sourcedefs ./dirdef/teacher.def
------structure of source table and target table is same
userid ggsuser, password oracle
reportcount every 24 hours, rate
discardfile /u01/app/goldengate/discardtrail/rtecve_4.dsc, megabytes 10, append
discardrollover at 06:00
map nspdba.teacher, target nspdba.teacher, FILTER(@RANGE(3,4));
REPLICAT STOPPED RTECVE 00:00:00 00:12:35
REPLICAT STOPPED RTECVE_1 00:00:00 00:04:57
REPLICAT STOPPED RTECVE_2 00:00:00 00:03:39
REPLICAT STOPPED RTECVE_3 00:00:00 00:02:53
REPLICAT STOPPED RTECVE_4 00:00:00 00:00:25
STEP 4: START NEW REPLICAT’S
GGSCI (eniya.varman.com) 48> start RTECVE_1
Sending START request to MANAGER ...
REPLICAT RTECVE_1 starting
GGSCI (eniya.varman.com) 49> start RTECVE_2
Sending START request to MANAGER ...
REPLICAT RTECVE_2 starting
GGSCI (eniya.varman.com) 50> start RTECVE_3
Sending START request to MANAGER ...
REPLICAT RTECVE_3 starting
GGSCI (eniya.varman.com) 51> start RTECVE_4
Sending START request to MANAGER ...
REPLICAT RTECVE_4 starting
STEP 5: VALIDATE ALL PROCESSES AND PARAMETER’S USED IN REPLICATS !!
REPLICAT RUNNING RTECVE_1 00:00:00 00:00:04
REPLICAT RUNNING RTECVE_2 00:00:00 00:00:09
REPLICAT RUNNING RTECVE_3 00:00:00 00:00:07
REPLICAT RUNNING RTECVE_4 00:00:00 00:00:02
STEP 6: REMOVE THE OLD REPLICAT —
GGSCI (eniya.varman.com) 8> dblogin userid ggsuser@eniya, password oracle
Successfully logged into database.
GGSCI (eniya.varman.com) 9> delete RTECVE
Deleted REPLICAT RTECVE.
-------Thank you
I went through this pdf, this is really intresting, kindly go through
http://www.oracle11ggotchas.com/articles/Defining%20Multiple%20Replicats%20to%20Increase%20GoldenGate%20Performance.pdf
I have seen many of my friends facing issues with replicating huge volume tables using goldengate, Here we go, I want to discuss, how goldengate replicat split helps and improve the performance of replication.
In order to split replicat 1 into many, Table involved in replication should have primary key and unique key should be on same column, If a table have primary key and unique key On different column, REPLICAT WILL ABEND BY GENERATING A LOT OF DISCARDS, REPLICAT'S involved in splitting WILL COMPETE EACH OTHER IN APPLYING SAME RECORD ON THE TRAIL to the target table.
Resolution:
we need to add handlecollisions to replicat paramfile and restart the replicat from abend
Steps to split replicat 1 into many, we split replicat using FILTER and RANGE parameters. In this scenario, We are splitting replicat 1 into 4, each replicat logically work ( perform DML) on 1/4 th of base table;
steps to split replicat as follows
STEP1: STOP REPLICAT
GGSCI (eniya.varman.com) 4> STOP RTECVE
Sending STOP request to REPLICAT RTECVE ...
Request processed.
STEP2: collect RBA and sequence number of remote trail from the old replicat !!
GGSCI (eniya.varman.com) 19> INFO RTECVE
REPLICAT RTECVE Last Started 2015-02-01 13:51 Status STOPPED
Checkpoint Lag 00:00:00 (updated 00:00:23 ago)
Log Read Checkpoint File /u01/app/goldengate/remotetrail/zz000004
2015-02-01 19:02:16.537343 RBA 2292
STEP3: PREPARE THE NEW OGG REPLICAT PROCESSES
-- Handles all tables WITHOUT PK /UI
-- GGSCI> add replicat rtecve_1, exttrail /u01/app/goldengate/remotetrail/zz, checkpointtable ggsuser.ckpt
—- ALTER rtecve_1, extseqno 4 extrba 2292
replicat rtecve_1
assumetargetdefs
sourcedefs ./dirdef/teacher.def
------structure of source table and target table is same
userid ggsuser, password oracle
reportcount every 24 hours, rate
discardfile /u01/app/goldengate/discardtrail/rtecve_1.dsc, megabytes 10, append
discardrollover at 06:00
map nspdba.teacher, target nspdba.teacher, FILTER(@RANGE(1,4));
-- Handles all tables WITHOUT PK /UI
-- GGSCI> add replicat rtecve_2, exttrail /u01/app/goldengate/remotetrail/zz, checkpointtable ggsuser.ckpt
—- ALTER rtecve_2, extseqno 4 extrba 2292
replicat rtecve_2
assumetargetdefs
sourcedefs ./dirdef/teacher.def
------structure of source table and target table is same
userid ggsuser, password oracle
reportcount every 24 hours, rate
discardfile /u01/app/goldengate/discardtrail/rtecve_2.dsc, megabytes 10, append
discardrollover at 06:00
map nspdba.teacher, target nspdba.teacher, FILTER(@RANGE(2,4));
-- Handles all tables WITHOUT PK /UI
-- GGSCI> add replicat rtecve_3, exttrail /u01/app/goldengate/remotetrail/zz, checkpointtable ggsuser.ckpt
—- ALTER rtecve_3, extseqno 4 extrba 2292
replicat rtecve_3
assumetargetdefs
sourcedefs ./dirdef/teacher.def
------structure of source table and target table is same
userid ggsuser, password oracle
reportcount every 24 hours, rate
discardfile /u01/app/goldengate/discardtrail/rtecve_3.dsc, megabytes 10, append
discardrollover at 06:00
map nspdba.teacher, target nspdba.teacher, FILTER(@RANGE(4,4));
-- Handles all tables WITHOUT PK /UI
-- GGSCI> add replicat rtecve_4, exttrail /u01/app/goldengate/remotetrail/zz, checkpointtable ggsuser.ckpt
—- ALTER rtecve_4, extseqno 4 extrba 2292
replicat rtecve_4
assumetargetdefs
sourcedefs ./dirdef/teacher.def
------structure of source table and target table is same
userid ggsuser, password oracle
reportcount every 24 hours, rate
discardfile /u01/app/goldengate/discardtrail/rtecve_4.dsc, megabytes 10, append
discardrollover at 06:00
map nspdba.teacher, target nspdba.teacher, FILTER(@RANGE(3,4));
REPLICAT STOPPED RTECVE 00:00:00 00:12:35
REPLICAT STOPPED RTECVE_1 00:00:00 00:04:57
REPLICAT STOPPED RTECVE_2 00:00:00 00:03:39
REPLICAT STOPPED RTECVE_3 00:00:00 00:02:53
REPLICAT STOPPED RTECVE_4 00:00:00 00:00:25
STEP 4: START NEW REPLICAT’S
GGSCI (eniya.varman.com) 48> start RTECVE_1
Sending START request to MANAGER ...
REPLICAT RTECVE_1 starting
GGSCI (eniya.varman.com) 49> start RTECVE_2
Sending START request to MANAGER ...
REPLICAT RTECVE_2 starting
GGSCI (eniya.varman.com) 50> start RTECVE_3
Sending START request to MANAGER ...
REPLICAT RTECVE_3 starting
GGSCI (eniya.varman.com) 51> start RTECVE_4
Sending START request to MANAGER ...
REPLICAT RTECVE_4 starting
STEP 5: VALIDATE ALL PROCESSES AND PARAMETER’S USED IN REPLICATS !!
REPLICAT RUNNING RTECVE_1 00:00:00 00:00:04
REPLICAT RUNNING RTECVE_2 00:00:00 00:00:09
REPLICAT RUNNING RTECVE_3 00:00:00 00:00:07
REPLICAT RUNNING RTECVE_4 00:00:00 00:00:02
STEP 6: REMOVE THE OLD REPLICAT —
GGSCI (eniya.varman.com) 8> dblogin userid ggsuser@eniya, password oracle
Successfully logged into database.
GGSCI (eniya.varman.com) 9> delete RTECVE
Deleted REPLICAT RTECVE.
-------Thank you
I went through this pdf, this is really intresting, kindly go through
http://www.oracle11ggotchas.com/articles/Defining%20Multiple%20Replicats%20to%20Increase%20GoldenGate%20Performance.pdf
