X

Welcome to All Things Data Integration: Announcements, Insights, Best Practices, Tips & Tricks, and Trend Related...

GoldenGate 12c - Coordinated Delivery Example

(This paper was jointly created with my colleague Yugandhar Dama) 

In
the previous blog, I discussed the basic concept of coordinated
delivery (replicat). In this blog, let's look at an example for setting
up a coordinated delivery (replicat) using Oracle GoldenGate. This
example runs with Oracle GoldenGate for MySQL, and the code can be
downloaded from the following file: 
The replication parameter file is shown as follows (crrep.prm):
replicat crrep
targetdb dtetgt userid dteusr1t, password dteusr1t
assumetargetdefs
discardfile dirrpt/crrep.dsc, purge
map dtesrc.STUDENT11, target dtetgt.STUDENT11, THREADRANGE (1-3);
map dtesrc.STUDENT12, target dtetgt.STUDENT12, THREAD (5);
map dtesrc.EVENT_TBL, target dtetgt.EVENT_TBL, COORDINATED,THREADRANGE (6-7);
map dtesrc.multiRecTrans_MRK, target dtetgt.multiRecTrans_MRK;

In this replicat, there are four tables.
STUDENT1 table is replicated with thread 1 to 3. STUDENT 2 table is
replicated with thread 5. Without specifying the thread, the
multiRecTrans_MRK table is replicated with the lowest thread 1.

In
coordinated replicat, DDL, PK-UPDATE, records for maps with the
COORDINATED parameter, and EVENTACTIONS are all considered as barrier
transactions. Barrier transactions are applied with the barrier thread. The EVENT_TBL is
replicated with the barrier thread, which is the lowest thread (thread 1
in this example). 

You can add the replicat and start the replicat process and check it's status as follows: 

GGSCI> dblogin sourcedb dtetgt userid dteusr1t, password dteusr1t
GGSCI> add checkpointtable dtetgt.checkPoint_crrep
GGSCI> add replicat crrep, exttrail ./dirdat/cr_example/bg,
COORDINATED, MAXTHREADS 15, checkpointtable dtetgt.checkPoint_crrep
GGSCI> start replicat crrep
GGSCI> info replicat crrep
REPLICAT CRREP Last Started 2013-12-26 20:50 Status RUNNING
COORDINATED Coordinator MAXTHREADS 15
Checkpoint Lag 00:00:00 (updated 00:00:09 ago)
Process ID 1932
Log Read Checkpoint File ./dirdat/cr_example/bg000001
First Record RBA 1431

The
status shows that the replicat is a COORDINATED replicat with maximum 15
threads. Let's check how many threads are actually running for this
replicat:

GGSCI (JIWANG-LAP) 158> info replicat crrep*
REPLICAT CRREP Last Started 2013-12-26 20:50 Status RUNNING
COORDINATED Coordinator MAXTHREADS 15
Checkpoint Lag 00:00:00 (updated 00:00:01 ago)
Process ID 1932
Log Read Checkpoint File ./dirdat/cr_example/bg000001
First Record RBA 1431
REPLICAT CRREP001 Last Started 2013-12-26 20:50 Status RUNNING
COORDINATED Replicat Thread Thread 1
Checkpoint Lag 00:00:00 (updated 00:00:01 ago)
Process ID 7616
Log Read Checkpoint File ./dirdat/cr_example/bg000001
2013-12-26 20:50:23.178000 RBA 1431
REPLICAT CRREP002 Last Started 2013-12-26 20:50 Status RUNNING
COORDINATED Replicat Thread Thread 2
Checkpoint Lag 00:00:00 (updated 00:00:00 ago)
Process ID 12660
Log Read Checkpoint File ./dirdat/cr_example/bg000001
2013-12-26 20:50:23.178000 RBA 1431
REPLICAT CRREP003 Last Started 2013-12-26 20:50 Status RUNNING
COORDINATED Replicat Thread Thread 3
Checkpoint Lag 00:00:00 (updated 00:00:01 ago)
Process ID 12656
Log Read Checkpoint File ./dirdat/cr_example/bg000001
2013-12-26 20:50:23.178000 RBA 1431
REPLICAT CRREP005 Last Started 2013-12-26 20:50 Status RUNNING
COORDINATED Replicat Thread Thread 5
Checkpoint Lag 00:00:00 (updated 00:00:01 ago)
Process ID 12340
Log Read Checkpoint File ./dirdat/cr_example/bg000001
2013-12-26 20:50:23.178000 RBA 1431
REPLICAT CRREP006 Last Started 2013-12-26 20:50 Status RUNNING
COORDINATED Replicat Thread Thread 6
Checkpoint Lag 00:00:00 (updated 00:00:01 ago)
Process ID 12880
Log Read Checkpoint File ./dirdat/cr_example/bg000001
2013-12-26 20:50:23.178000 RBA 1431
REPLICAT CRREP007 Last Started 2013-12-26 20:50 Status RUNNING
COORDINATED Replicat Thread Thread 7
Checkpoint Lag 00:00:00 (updated 00:00:01 ago)
Process ID 10488
Log Read Checkpoint File ./dirdat/cr_example/bg000001
2013-12-26 20:50:23.178000 RBA 1431

The
status shows that there are 7 threads for the replicat with 1
coordinating thread (CRREP) and 6 processing threads (CRREP001...007). The example performs DML
operations using the following command: 

 GGSCI> sh mysql --force --user=dteusr1s --password=dteusr1s 
dtesrc < dirsql/cr_example/dml.sql

Oracle GoldenGate replicates the data to the target and you can then check the status with the following command:

GGSCI>  view report crrep
....
Report at 2013-12-26 23:22:12 (activity since 2013-12-26 23:14:15)
From Table DTESRC.STUDENT11 to DTETGT.STUDENT11:
# inserts: 103
# updates: 0
# deletes: 3
# discards: 0
From Table DTESRC.STUDENT12 to DTETGT.STUDENT12:
# inserts: 103
# updates: 0
# deletes: 3
# discards: 0
From Table DTESRC.EVENT_TBL to DTETGT.EVENT_TBL:
# inserts: 4
# updates: 0
# deletes: 0
# discards: 0
From Table DTESRC.MULTIRECTRANS_MRK to DTETGT.MULTIRECTRANS_MRK:
# inserts: 1
# updates: 0
# deletes: 0
# discards: 0
Coordinated Replicat Statistics:
Thread Lag Gap: 00:00:00 (updated 00:00
:02 ago)
Coordinated Total DDLs: 0
Coordinated Total PK-update transactions: 0
Coordinated Total EMI transactions: 0
Total transactions with user-requested coordination: 4
Average Coordination Time: 00:00:00

There are
4 DML operations on the EVENT_TBL1 table. Because it is setup as the
coordinated process, you can see 4 transactions shown in the
user-requested coordination in the "Coordinated Replicat Statistics" . You can also check each thread's report as follows:

 GGSCI> send crrep001 report

The report shows the detailed operation per thread with statistics. The following is an example report in thread 5:

The report shown that
only the STUDENT12 table is replicated in this tread with 103 inserts, 3
deletes and 1 discard operation.

From Table dtesrc.student12 to dtetgt.STUDENT12:
# inserts: 103
# updates: 0
# deletes: 3
# discards: 1

We have now completed this example. Feel free to send your questions and comments

Join the discussion

Comments ( 3 )
  • guest Tuesday, September 29, 2015

    Max value for maxthreads?


  • guest Wednesday, September 30, 2015

    The maximum number of threads is 500.


  • GG coordinator Friday, February 16, 2018
    One of my replicat is running slow after given Maxthread 15, I need to increase to Maxthread 100. How can I do it without unregistering the replicat?
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.Captcha
Oracle

Integrated Cloud Applications & Platform Services