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

Comments:

Post a Comment:
  • HTML Syntax: NOT allowed
About

Learn the latest trends, use cases, product updates, and customer success examples for Oracle's data integration products-- including Oracle Data Integrator, Oracle GoldenGate and Oracle Enterprise Data Quality

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
2
3
5
6
7
8
9
10
12
13
14
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today