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, &
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.classic_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;
$ cat gg_hb_coordinated_replicat.mac
MACRO #gg_hb_coordinated_replicat
PARAMS (#thread)
BEGIN
MAP ggmon.ggmon_heartbeat, TARGET ggmon.coordinated_heartbeat, THREAD (#thread) &
, &
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.coordinated_heartbeat_history, THREAD (#thread) &
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;
$ 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 .
