Monthly Archives: March 2014

GoldenGate 12 – Classic, Coordinated and Integrated Replicat

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

Classic Replicat

Applies SQL serially.

Must be used if the database version is < 11.2.0.4

B. Coordinated Replicat

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

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

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 .

GoldenGate 12 Users, Privileges and Security on Multitenant DBs

I. Users and Privileges

With the release of the latest versions of GG and database, there have been many modifications that require changes in how users are created, logins and the privileges granted.

A. Database Users

If the source database is a multitenant container database, the Extract user must be a common user and must log into the root container.

One extract capture process can capture from multiple pluggable databases to a single trail.

A replicat process can apply transactions to only one pluggable database.

B. Privileges

Click the following link to see a list of privileges required for a GoldenGate user on a 11.2.0.4 or greater database.

Users Privileges for GolgenGate Users for DB 11.2.0.4 or Higher

CREATE USER c##ogg IDENTIFIED BY ogg
DEFAULT TABLESPACE ogg_tbsp
TEMPORARY TABLESPACE temp
PROFILE DEFAULT ACCOUNT UNLOCK
CONTAINER=ALL;

ALTER USER c##ogg DEFAULT ROLE ALL CONTAINER=ALL;
ALTER USER c##ogg QUOTA UNLIMITED ON ogg_tbsp CONTAINER=ALL;

GRANT CREATE SESSION TO c##ogg CONTAINER=ALL;
GRANT CONNECT TO c##ogg CONTAINER=ALL;
GRANT RESOURCE TO c##ogg CONTAINER=ALL;
GRANT ALTER SYSTEM TO c##ogg CONTAINER=ALL;
GRANT DBA TO c##ogg CONTAINER=ALL;
GRANT SELECT ANY TRANSACTION TO c##ogg CONTAINER=ALL;
GRANT ALTER ANY TABLE TO c##ogg CONTAINER=ALL;

BEGIN
 dbms_goldengate_auth.grant_admin_privilege
 (
 grantee => 'C##OGG',
 privilege_type => '*',
 grant_select_privileges => TRUE,
 do_grants => TRUE,
 container => 'ALL'
 );
END;
/

C. Logins

1. Capture DB Login

DBLogin UserId c##ogg, Password ogg

2. Pluggable DB Login

DBLogin UserId c##ogg@pdbogg, Password ogg

II. Security

A. Credential Store

The credential store is used to maintain encrypted database passwords, associating an alias with the userid.

ADD CREDENTIALSTORE

ALTER CREDENTIALSTORE ADD USER c##ogg PASSWORD ogg ALIAS ogg_user

ALTER CREDENTIALSTORE ADD USER c##ogg@pdbogg PASSWORD ogg ALIAS pdbogg_user

B. Logins

1. Capture DB Login

DBLOGIN UserIdAlias ogg_user

2. Pluggable DB Login

DBLOGIN UserIdAlias pdbogg_user