I. New Replicat Modes Availalble in GolgenGate 12.1

Prior to the release of version 12.1, the replicat applied transactions to the target database serially. To achieve parallelism, tables could be split across multiple replicats to get data applied quicker and reduce lag. Drawbacks to this approach was the manual configuration of multiple replicats, trail files, and the limitation of not be able to split tables that had foreign key relationships across replicats.

The new Coordinated and Integrated Replicats can help you achieve transactions being applied in parallel on the target database without having to manually split out tables into multiple replicats.

Let’s take a look at the difference in the replicat modes.

A. Classic Replicat

Applies SQL serially.

Must be used if the database version is < 11.2.0.4

B. Coordinated Replicat

The configured replicat is called the coordinator. It starts additional replicats based on number of threads specified. The replicat name is limited to 5 characters and the threaded replicats will have this name appended with a 3 digit number ( RGGMA, RGGMA001, RGGMA002, … ). The coordinator sends SQL to the threads, which can be applied in parallel. The SQL is applied in committed order.

C. Integrated Replicat

Constructs logical change records (LCR) that represent source database DML transactions (in committed order). DDL is applied directly by Replicat. Transactions can be applied in parallel.
Attaches to a background process in the target database known as a database inbound server by means of a lightweight streaming interface
Transmits the LCRs to the inbound server, which applies the data to the target database

II. Configuration

Let’s setup one extract that will feed a heartbeat table into the three different replicat types. Here a diagram.

GG12 Replicat Modes

A. Parameter Files

Integrated Capture

EXTRACT cgga
SETENV (ORACLE_HOME="/u01/app/oracle/product/11.2.0.4")
SETENV (ORACLE_SID="ORA11204")
SETENV (NLS_LANG="AMERICAN_AMERICA.WE8MSWIN1252")
USERID c##ogg, PASSWORD ogg
TRANLOGOPTIONS MININGUSER c##ogg, MININGPASSWORD ogg
TRANLOGOPTIONS INTEGRATEDPARAMS (max_sga_size 200, parallelism 1)
LOGALLSUPCOLS
DISCARDFILE ./dirrpt/CGGA.dsc, APPEND Megabytes 100M
INCLUDE ./dirprm/gg_hb_capture.mac
EXTTRAIL ./dirdat/GA
#gg_hb_capture ();

Pump

EXTRACT pgga
SETENV (ORACLE_HOME="/u01/app/oracle/product/11.2.0.4")
SETENV (ORACLE_SID="ORA11204")
SETENV (NLS_LANG="AMERICAN_AMERICA.WE8MSWIN1252")
USERID c##ogg, PASSWORD ogg
DISCARDFILE ./dirrpt/PGGA.dsc, APPEND Megabytes 100M
RMTHOST db1.vm.com, MGRPORT 7812
INCLUDE ./dirprm/gg_hb_pump.mac
--
RMTTRAIL ./dirdat/a1
NOPASSTHRU
#gg_hb_pump ();
--
RMTTRAIL ./dirdat/a2
NOPASSTHRU
#gg_hb_pump ();
--
RMTTRAIL ./dirdat/a3
NOPASSTHRU
#gg_hb_pump ();

Classic Replicat

REPLICAT rgga1
SETENV (ORACLE_HOME="/u01/app/oracle/product/12.1.0.1")
SETENV (ORACLE_SID="ORA12101")
SETENV (NLS_LANG="AMERICAN_AMERICA.WE8MSWIN1252")
UserId c##ogg@pdbogg, Password ogg
ASSUMETARGETDEFS
DBOPTIONS NOSUPPRESSTRIGGERS
DBOPTIONS DEFERREFCONST
DISCARDFILE ./dirrpt/RGGA1.dsc, append, megabytes 100
INCLUDE ./dirprm/gg_hb_classic_replicat.mac
#gg_hb_classic_replicat ();

Coordinated Replicat

REPLICAT rgga2
SETENV (ORACLE_HOME="/u01/app/oracle/product/12.1.0.1")
SETENV (ORACLE_SID="ORA12101")
SETENV (NLS_LANG="AMERICAN_AMERICA.WE8MSWIN1252")
UserId c##ogg@pdbogg, Password ogg
ASSUMETARGETDEFS
DBOPTIONS NOSUPPRESSTRIGGERS
DBOPTIONS DEFERREFCONST
DISCARDFILE ./dirrpt/RGGA2.dsc, append, megabytes 100
INCLUDE ./dirprm/gg_hb_coordinated_replicat.mac
#gg_hb_coordinated_replicat (1);

Integrated Replicat

REPLICAT rgga3
SETENV (ORACLE_HOME="/u01/app/oracle/product/12.1.0.1")
SETENV (ORACLE_SID="ORA12101")
SETENV (NLS_LANG="AMERICAN_AMERICA.WE8MSWIN1252")
UserId c##ogg@pdbogg, Password ogg
ASSUMETARGETDEFS
DBOPTIONS NOSUPPRESSTRIGGERS
DBOPTIONS DEFERREFCONST
DISCARDFILE ./dirrpt/RGGA3.dsc, append, megabytes 100
INCLUDE ./dirprm/gg_hb_integrated_replicat.mac
#gg_hb_integrated_replicat ();

Macros

$ cat gg_hb_classic_replicat.mac
MACRO #gg_hb_classic_replicat
BEGIN
MAP ggmon.ggmon_heartbeat, TARGET ggmon.classic_heartbeat, &amp;
 COLMAP (USEDEFAULTS &amp;
 , SOURCE_COMMIT = @GETENV ('GGHEADER', 'COMMITTIMESTAMP') &amp;
 , TARGET_COMMIT = @DATE ('YYYY-MM-DD HH:MI:SS.FFFFFF','JTS',@GETENV ('JULIANTIMESTAMP')) &amp;
 , CAP_SOURCE_COMMIT = @TOKEN('CAP_SOURCE_COMMIT') &amp;
 , CAPGROUP = @TOKEN ('CAPGROUP') &amp;
 , CAPTIME = @TOKEN ('CAPTIME') &amp;
 , PMPGROUP = @TOKEN ('PMPGROUP') &amp;
 , PMPTIME = @TOKEN ('PMPTIME') &amp;
 , REPGROUP = @GETENV ('GGENVIRONMENT', 'GROUPNAME') &amp;
 , REPTIME = @DATE ('YYYY-MM-DD HH:MI:SS.FFFFFF','JTS',@GETENV ('JULIANTIMESTAMP')) &amp;
 );
MAP ggmon.ggmon_heartbeat, TARGET ggmon.classic_heartbeat_history, &amp;
 INSERTALLRECORDS &amp;
 , COLMAP (USEDEFAULTS &amp;
 , SOURCE_COMMIT = @GETENV ('GGHEADER', 'COMMITTIMESTAMP') &amp;
 , TARGET_COMMIT = @DATE ('YYYY-MM-DD HH:MI:SS.FFFFFF','JTS',@GETENV ('JULIANTIMESTAMP')) &amp;
 , CAP_SOURCE_COMMIT = @TOKEN('CAP_SOURCE_COMMIT') &amp;
 , CAPGROUP = @TOKEN ('CAPGROUP') &amp;
 , CAPTIME = @TOKEN ('CAPTIME') &amp;
 , PMPGROUP = @TOKEN ('PMPGROUP') &amp;
 , PMPTIME = @TOKEN ('PMPTIME') &amp;
 , REPGROUP = @GETENV ('GGENVIRONMENT', 'GROUPNAME') &amp;
 , REPTIME = @DATE ('YYYY-MM-DD HH:MI:SS.FFFFFF','JTS',@GETENV ('JULIANTIMESTAMP')) &amp;
 );
END;

$ cat gg_hb_coordinated_replicat.mac
MACRO #gg_hb_coordinated_replicat
PARAMS (#thread)
BEGIN
MAP ggmon.ggmon_heartbeat, TARGET ggmon.coordinated_heartbeat, THREAD (#thread) &amp;
 , &amp;
 COLMAP (USEDEFAULTS &amp;
 , SOURCE_COMMIT = @GETENV ('GGHEADER', 'COMMITTIMESTAMP') &amp;
 , TARGET_COMMIT = @DATE ('YYYY-MM-DD HH:MI:SS.FFFFFF','JTS',@GETENV ('JULIANTIMESTAMP')) &amp;
 , CAP_SOURCE_COMMIT = @TOKEN('CAP_SOURCE_COMMIT') &amp;
 , CAPGROUP = @TOKEN ('CAPGROUP') &amp;
 , CAPTIME = @TOKEN ('CAPTIME') &amp;
 , PMPGROUP = @TOKEN ('PMPGROUP') &amp;
 , PMPTIME = @TOKEN ('PMPTIME') &amp;
 , REPGROUP = @GETENV ('GGENVIRONMENT', 'GROUPNAME') &amp;
 , REPTIME = @DATE ('YYYY-MM-DD HH:MI:SS.FFFFFF','JTS',@GETENV ('JULIANTIMESTAMP')) &amp;
 );
MAP ggmon.ggmon_heartbeat, TARGET ggmon.coordinated_heartbeat_history, THREAD (#thread) &amp;
 INSERTALLRECORDS &amp;
 , COLMAP (USEDEFAULTS &amp;
 , SOURCE_COMMIT = @GETENV ('GGHEADER', 'COMMITTIMESTAMP') &amp;
 , TARGET_COMMIT = @DATE ('YYYY-MM-DD HH:MI:SS.FFFFFF','JTS',@GETENV ('JULIANTIMESTAMP')) &amp;
 , CAP_SOURCE_COMMIT = @TOKEN('CAP_SOURCE_COMMIT') &amp;
 , CAPGROUP = @TOKEN ('CAPGROUP') &amp;
 , CAPTIME = @TOKEN ('CAPTIME') &amp;
 , PMPGROUP = @TOKEN ('PMPGROUP') &amp;
 , PMPTIME = @TOKEN ('PMPTIME') &amp;
 , REPGROUP = @GETENV ('GGENVIRONMENT', 'GROUPNAME') &amp;
 , REPTIME = @DATE ('YYYY-MM-DD HH:MI:SS.FFFFFF','JTS',@GETENV ('JULIANTIMESTAMP')) &amp;
 );
END;

$ cat gg_hb_integrated_replicat.mac
MACRO #gg_hb_integrated_replicat
BEGIN
MAP ggmon.ggmon_heartbeat, TARGET ggmon.integrated_heartbeat, &
  COLMAP (USEDEFAULTS &
  , SOURCE_COMMIT = @GETENV ('GGHEADER', 'COMMITTIMESTAMP') &
  , TARGET_COMMIT = @DATE ('YYYY-MM-DD HH:MI:SS.FFFFFF','JTS',@GETENV ('JULIANTIMESTAMP')) &
  , CAP_SOURCE_COMMIT = @TOKEN('CAP_SOURCE_COMMIT') &
  , CAPGROUP = @TOKEN ('CAPGROUP') &
  , CAPTIME = @TOKEN ('CAPTIME') &
  , PMPGROUP = @TOKEN ('PMPGROUP') &
  , PMPTIME = @TOKEN ('PMPTIME') &
  , REPGROUP = @GETENV ('GGENVIRONMENT', 'GROUPNAME') &
  , REPTIME =  @DATE ('YYYY-MM-DD HH:MI:SS.FFFFFF','JTS',@GETENV ('JULIANTIMESTAMP')) &
  );
MAP ggmon.ggmon_heartbeat, TARGET ggmon.integrated_heartbeat_history, &
  INSERTALLRECORDS &
  , COLMAP (USEDEFAULTS &
  , SOURCE_COMMIT = @GETENV ('GGHEADER', 'COMMITTIMESTAMP') &
  , TARGET_COMMIT = @DATE ('YYYY-MM-DD HH:MI:SS.FFFFFF','JTS',@GETENV ('JULIANTIMESTAMP')) &
  , CAP_SOURCE_COMMIT = @TOKEN('CAP_SOURCE_COMMIT') &
  , CAPGROUP = @TOKEN ('CAPGROUP') &
  , CAPTIME = @TOKEN ('CAPTIME') &
  , PMPGROUP = @TOKEN ('PMPGROUP') &
  , PMPTIME = @TOKEN ('PMPTIME') &
  , REPGROUP = @GETENV ('GGENVIRONMENT', 'GROUPNAME') &
  , REPTIME =  @DATE ('YYYY-MM-DD HH:MI:SS.FFFFFF','JTS',@GETENV ('JULIANTIMESTAMP')) &
  );
END;

B. Creating Extract and Replicats

Oracle 11.2.0.4 Database

DBLogin UserId c##ogg, Password ogg
MiningDBLogin UserId c##ogg, Password ogg
REGISTER EXTRACT cgga DATABASE
ADD    SchemaTranData ggmon

ADD EXTRACT cgga, INTEGRATED TRANLOG, BEGIN NOW, DESC "***** Integrated Capture *****"
ADD EXTTRAIL ./dirdat/GA, EXTRACT cgga

ADD EXTRACT pgga, EXTTRAILSOURCE ./dirdat/GA, desc "***** Pump *****"
ADD RMTTRAIL ./dirdat/a1, EXTRACT pgga
ADD RMTTRAIL ./dirdat/a2, EXTRACT pgga
ADD RMTTRAIL ./dirdat/a3, EXTRACT pgga


GGSCI (db1.vm.com) 9> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
EXTRACT     RUNNING     CGGA        00:00:10      00:00:02
Description "***** Integrated Capture *****"
EXTRACT     RUNNING     PGGA        00:00:00      00:00:08
Description "***** Pump *****"

Oracle 12.1.0.1 Database

DBLogin UserId c##ogg, Password ogg
MiningDBLogin UserId c##ogg, Password ogg
DBLogin UserId c##ogg@pdbogg, Password ogg
-- Classic Replicat
ADD REPLICAT rgga1, ExtTrail ./dirdat/a1, checkpointtable pdbogg.c##ogg.checkpoint, desc "***** Classic Replicat *****"
-- Coordinated Replicat
ADD REPLICAT rgga2, Coordinated MaxThreads 2, ExtTrail ./dirdat/a2, checkpointtable pdbogg.c##ogg.checkpoint, desc "***** Coordinated Replicat *****"
-- Integrated Replicat
ADD REPLICAT rgga3, Integrated, ExtTrail ./dirdat/a3, desc "***** Integrated Replicat *****"


GGSCI (db1.vm.com) 8> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
REPLICAT    RUNNING     RGGA1       00:00:00      00:00:07
Description "***** Classic Replicat *****"
REPLICAT    RUNNING     RGGA2       00:00:00      00:00:07
Description "***** Coordinated Replicat *****"
REPLICAT    RUNNING     RGGA3       00:00:00      00:00:05
Description "***** Integrated Replicat *****"

C. Push Data Though via Heartbeat Table

After starting up the heartbeat table, we can query to see the data being replicated across each replicat type. The extract and replicat names can be seen in the output. Note that name of the coordinated replicat group has the three digit number appended to the original name.

Please keep in mind that this configuration was done on a small virtual machine, so the lag times are a bit high, but this blog was only about the functionality of the different replicat types.

GGMON:pdbogg> @all_rep_lag.sql
***********************************************************
Heartbeat
***********************************************************

REPLICAT    CAP                                 LAST              CAP    PMP    REP  TOTAL
TYPE        DB NAME  CAPGROUP PMPGROUP REPGROUP UPDATE            LAG    LAG    LAG    LAG
----------- -------- -------- -------- -------- -------------- ------ ------ ------ ------
Classic     ORA11204 CGGA     PGGA     RGGA1    03/06 02:42:31    5.9    5.3    2.3   13.5
Coordinated ORA11204 CGGA     PGGA     RGGA2001 03/06 02:42:31    5.9    5.3    3.0   14.2
Integrated  ORA11204 CGGA     PGGA     RGGA3    03/06 02:42:31    5.9    5.3   12.6   23.8

***********************************************************
Heartbeat History
***********************************************************

                                                   AVG    AVG    AVG    AVG
REPLICAT    CAP                                    CAP    PMP    REP  TOTAL
TYPE        DB NAME  CAPGROUP PMPGROUP REPGROUP    LAG    LAG    LAG    LAG
----------- -------- -------- -------- -------- ------ ------ ------ ------
Classic     ORA11204 CGGA     PGGA     RGGA1       3.6    5.4    4.1   13.2
Coordinated ORA11204 CGGA     PGGA     RGGA2001    3.6    5.4    2.5   11.6
Integrated  ORA11204 CGGA     PGGA     RGGA3       3.6    5.4   10.0   19.1

A copy of the all_rep_lag.sql script can be found here .

3 thoughts on “GoldenGate 12 – Classic, Coordinated and Integrated Replicat”

vishaldesai May 28, 2014 at 2:44 pm

Can you post all_rep_lag.sql?

Reply ↓
Sashi June 24, 2014 at 1:27 pm

This is really helpful post, can you please share the ‘all_rep_lag.sql’ script used.

Reply ↓
1. Rick Miners Post authorJuly 30, 2014 at 1:41 pm
  
  Thanks. A link to the script has been added to the bottom of the post.
  
  Reply ↓

An Oracle Database and Cloud Blog Site

Rick Miners

GoldenGate 12 – Classic, Coordinated and Integrated Replicat

I. New Replicat Modes Availalble in GolgenGate 12.1

A. Classic Replicat

B. Coordinated Replicat

C. Integrated Replicat

II. Configuration

A. Parameter Files

B. Creating Extract and Replicats

C. Push Data Though via Heartbeat Table

3 thoughts on “GoldenGate 12 – Classic, Coordinated and Integrated Replicat”

Leave a Reply Cancel reply

I. New Replicat Modes Availalble in GolgenGate 12.1

A. Classic Replicat

B. Coordinated Replicat

C. Integrated Replicat

II. Configuration

A. Parameter Files

B. Creating Extract and Replicats

C. Push Data Though via Heartbeat Table

Share this:

Like this:

Related

3 thoughts on “GoldenGate 12 – Classic, Coordinated and Integrated Replicat”

Leave a Reply Cancel reply