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.
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 .
Can you post all_rep_lag.sql?
This is really helpful post, can you please share the ‘all_rep_lag.sql’ script used.
Thanks. A link to the script has been added to the bottom of the post.