Loading....

In one of the the earlier posts we had discussed the GoldenGate 12c Integrated Apply or Integrated Replicat feature. It enables high volume transactions to be applied in parallel.

But it was only supported for Oracle databases and also required to be version 11.2.0.4 and higher.

The Coordinated Replicat feature is new in GoldenGate 12c where the Replicat is multi-threaded and a single replicat instance, multiple threads read the trail independently and apply transactions in parallel. One coordinator thread spawns and coordinates one or more threads that execute replicated SQL operations in parallel.

The main difference between the Integrated Replicat and the Coordinated Replicat is that while in case of the Integrated Replicat, GoldenGate will add (or remove) additional apply server processes depending on the workload, in case of Coordinated Replicat it is user defined partitioning of the workload so as to apply high volume transactions concurrently and in parallel. This is done via the parameter THREADS and MAXTHREADS which we will discuss in this post using an example.

In earlier versions, scalability was enabled by fanning out the work to multiple replicats when the work could not be handled by a single replicat – but this required us to have multiple extracts, data pumps and replicat groups (and parameter files as well).

For example we had to create three separate replicat groups and use the RANGE parameter:

REP1.PRM
MAP sales.acct, TARGET sales.acct, FILTER (@RANGE (1, 3, ID));

REP2.PRM
MAP sales.acct, TARGET sales.acct, FILTER (@RANGE (2, 3, ID));

REP3.PRM
MAP sales.acct, TARGET sales.acct, FILTER (@RANGE (3, 3, ID))

Now in Goldengate 12c Coordinated replicat or delivery, there is a single replicat parameter file and additional replicat groups are created automatically and a single coordinator process or thread spawns additional threads and assigns individual workloads to each thread. Partitioning of workload is done via the THREADRANGE parameter used in the MAP statement.

For example now we require just one single replicat parameter file:

REP.PRM
MAP sales.acct, TARGET sales.acct, THREADRANGE(1-3, ID));

So if the target database is an Oracle version that does not support integrated Replicat, or if it is a non-Oracle database, we can use a coordinated Replicat feature to more or less achieve the same benefits of Integrated Replicat which is to provide higher throughput of transaction application on the target database by processing workload in parallel.

Let us now look at an example of using a Coordinated Replicat.

As in the case of the previous example using Integrated Replicat, the source database is an Oracle 12c Pluggable Database called SALES and we are replicating to another Oracle 12c Pluggable Database called SALES_DR.

We have created the table MYOBJECTS in both the source and target databases and have already enabled supplemental logging at the schema level.

SQL> create table myobjects as select * from all_objects where 1=2;

Table created.

SQL> alter table myobjects add constraint pk_myobjects primary key (object_id);

Table altered.

SQL> grant all on myobjects to C##GGADMIN;

Grant succeeded.

 

On the source we have created the Extract and Data Pump groups – we are using Integrated Extract in this case.


 
Register the integrated extract
 
GGSCI (orasql-001-dev.mydomain) 6> DBLOGIN USERIDALIAS gg_root

Successfully logged into database CDB$ROOT.

GGSCI (orasql-001-dev.mydomain) 7> REGISTER EXTRACT myext1 DATABASE  CONTAINER (sales)

Extract MYEXT1 successfully registered with database at SCN 3669081.

 
Add the Integrated Extract and Data Pump 
 
GGSCI (orasql-001-dev.mydomain) 8> ADD EXTRACT myext1 INTEGRATED TRANLOG, BEGIN NOW

EXTRACT added.

GGSCI (orasql-001-dev.mydomain) 9> ADD EXTTRAIL ./dirdat/lt EXTRACT myext1

EXTTRAIL added.

GGSCI (orasql-001-dev.mydomain) 10> ADD EXTRACT mydp1 EXTTRAILSOURCE ./dirdat/lt BEGIN NOW

EXTRACT added.

GGSCI (orasql-001-dev.mydomain) 11> ADD RMTTRAIL ./dirdat/rt EXTRACT mydp1

RMTTRAIL added.

 
Edit the Integrated Extract Parameter File
 
GGSCI (orasql-001-dev.mydomain) 11> edit params myext1

EXTRACT myext1

SETENV (ORACLE_SID='condb2')
USERIDALIAS gg_root
LOGALLSUPCOLS
UPDATERECORDFORMAT COMPACT
EXTTRAIL ./dirdat/lt
SOURCECATALOG sales
TABLE sh.myobjects;

 
Edit the Data Pump Parameter File 
 
GGSCI (orasql-001-dev.mydomain) 12> edit params mydp1

EXTRACT mydp1
SETENV (ORACLE_SID='condb2')
USERIDALIAS gg_owner
RMTHOST orasql-001-test, MGRPORT 7809
RMTTRAIL ./dirdat/rt
SOURCECATALOG sales
TABLE sh.myobjects;

 


On the target, add the Coordinated Replicat
 

GGSCI (orasql-001-test.mydomain) 1> DBLOGIN USERIDALIAS gg_sales
Successfully logged into database SALES_DR.

GGSCI (orasql-001-dev.mydomain) 4> add replicat rep1, coordinated, EXTTRAIL ./dirdat/rt, maxthreads 5
REPLICAT (Coordinated) added.

GGSCI (orasql-001-dev.mydomain) 1> view params rep1

REPLICAT rep1
SETENV (ORACLE_SID='condb2')
USERIDALIAS gg_sales
ASSUMETARGETDEFS
MAP sales.sh.myobjects, TARGET sales_dr.sh.myobjects,
THREADRANGE(1-5, OBJECT_ID));

GGSCI (kens-orasql-001-dev.corporateict.domain) 5> start replicat rep1

Sending START request to MANAGER ...
REPLICAT REP1 starting

GGSCI (kens-orasql-001-dev.corporateict.domain) 6> info replicat rep1

REPLICAT   REP1      Last Started 2014-01-23 10:38   Status RUNNING
COORDINATED          Coordinator                      MAXTHREADS 5
Checkpoint Lag       00:00:00 (updated 00:00:04 ago)
Process ID           25811
Log Read Checkpoint  File ./dirdat/rt000000
                     First Record  RBA 0

GGSCI (kens-orasql-001-dev.corporateict.domain) 8>  info replicat rep1 detail

REPLICAT   REP1      Last Started 2014-01-23 10:38   Status RUNNING
COORDINATED          Coordinator                      MAXTHREADS 5
Checkpoint Lag       00:00:00 (updated 00:00:00 ago)
Process ID           25811
Log Read Checkpoint  File ./dirdat/rt000000
                     First Record  RBA 1642

 
We now populate the source table with some data and check if the extract has captured the change data
 

SQL> insert into myobjects
  2  select * from all_objects;

77694 rows created.

SQL> commit;

Commit complete.

GGSCI (kens-orasql-001-test.corporateict.domain) 1> stats extract ext1 latest

Sending STATS request to EXTRACT EXT1 ...

Start of Statistics at 2014-01-23 10:41:54.

Output to ./dirdat/lt:

Extracting from SALES.SH.MYOBJECTS to SALES_DR.SH.MYOBJECTS:

*** Latest statistics since 2014-01-23 10:41:33 ***
        Total inserts                                  77694.00
        Total updates                                      0.00
        Total deletes                                      0.00
        Total discards                                     0.00
        Total operations                               77694.00

End of Statistics.

 

We can now see that the Replicat has spawned 5 additional threads (because we had specified MAXTHREADS 5) and we see the addtional replicat groups have been created (REP1001 to REP1005).
 

GGSCI (orasql-001-dev.mydomain) 9> info replicat rep1 detail

REPLICAT   REP1      Last Started 2014-01-23 10:48   Status RUNNING
COORDINATED          Coordinator                      MAXTHREADS 5
Checkpoint Lag       00:03:11 (updated 00:00:00 ago)
Process ID           26831
Log Read Checkpoint  File ./dirdat/rt000000
                     2014-01-23 10:46:29.747584  RBA 28181513

Lowest Log BSN value: 

Active Threads:
ID  Group Name PID   Status   Lag at Chkpt  Time Since Chkpt
1   REP1001    26838 RUNNING  00:00:00      00:00:20
2   REP1002    26839 RUNNING  00:00:00      00:00:20
3   REP1003    26840 RUNNING  00:00:00      00:00:20
4   REP1004    26841 RUNNING  00:00:00      00:00:20
5   REP1005    26842 RUNNING  00:00:00      00:00:20

GGSCI (orasql-001-dev.mydomain) 2> info replicat rep1001

REPLICAT   REP1001   Last Started 2014-01-23 10:48   Status RUNNING
COORDINATED          Replicat Thread                  Thread 1
Checkpoint Lag       00:00:00 (updated 00:00:05 ago)
Process ID           26838
Log Read Checkpoint  File ./dirdat/rt000000
                     2014-01-23 10:49:24.008242  RBA 56361384

 

About 77000 rows were inserted in the target table and we can see that the workload has been distributed by the replicat coordinator process among the 5 threads – so each thread has processed about 15000 rows each.
 

GGSCI (orasql-001-dev.mydomain) 2> stats replicat rep1001

Sending STATS request to REPLICAT REP1001 ...

Start of Statistics at 2014-01-23 10:51:44.

Replicating from SALES.SH.MYOBJECTS to SALES_DR.SH.MYOBJECTS:

*** Total statistics since 2014-01-23 10:49:31 ***
        Total inserts                                  15748.00
        Total updates                                      0.00
        Total deletes                                      0.00
        Total discards                                     0.00
        Total operations                               15748.00

GGSCI (orasql-001-dev.mydomain) 4>

GGSCI (orasql-001-dev.mydomain) 3> stats replicat rep1005

Sending STATS request to REPLICAT REP1005 ...

Start of Statistics at 2014-01-23 10:52:09.

Replicating from SALES.SH.MYOBJECTS to SALES_DR.SH.MYOBJECTS:

*** Total statistics since 2014-01-23 10:49:31 ***
        Total inserts                                  15640.00
        Total updates                                      0.00
        Total deletes                                      0.00
        Total discards                                     0.00
        Total operations                               15640.00
Please follow and like us:

Last Update: June 3, 2020  

January 24, 2014 306 Gavin Soorma
Total 0 Votes:
0

Tell us how can we improve this post?

+ = Verify Human or Spambot ?

Add A Knowledge Base Question !

You will receive an email when your question will be answered.

+ = Verify Human or Spambot ?

, , , , , , , ,

2 thoughts on “12c GoldenGate New Feature – Coordinated Replicat

  1. ALI NAVEED ERSHAD

    Hi Gavin,

    I think we need to create checkpoint table @Target . Getting below error while adding replicat

    GGSCI (test.naveed.com as c##ggadmin@cdb2/PDB_TEST) 2> add replicat myrep1, coordinated, EXTTRAIL ./dirdat/rx, maxthreads 5
    ERROR: No checkpoint table specified for ADD REPLICAT.

Leave a Comment

Your email address will not be published. Required fields are marked *

*
*

Social media & sharing icons powered by UltimatelySocial
Back To Top

Add A Knowledge Base Question !

You will receive an email when your question will be answered.

+ = Verify Human or Spambot ?