Goldengate Multi-Master Replication on Oracle 19

In this article we are going to configure Oracle Goldengate for mult-master replication in a RAC environment.  Note that this is a complex environment, and your environment will have different problems and issues.  Here is my environment:

We have two-2 node RAC Databases:

Cluster 1 has nodes ggclust11 and ggclust12.  Cluster 1 runs cluster database demogg1, with instances demogg11 and demogg12.

Cluster number 2, has nodes ggclust21 and ggclust22 and runs database ggclust2, with instances ggclust21 and ggclust22.

We are going to use the hr schema in the sample schemas provided by oracle for our replication. I have added a column to each table, last_upd_dt_tm of data type timestamp in order to allow us to effectively run multi-master replication. I also created a trigger for each table that fills out the last_updt_dt_tm column on update or insert.

See my previous blog (https://dbakerber.wordpress.com/2019/07/31/oracle-software-installation-bugs/) for a couple of fun bugs I ran into while building the cluster and creating the databases.

On both databases the following commands were run as part of the initial setup:

Alter database archivelog;
Alter database flashback on;
Alter database add supplemental log data;
Alter database force logging;

I have configured dnsmasq for names resolution so that the DNS works as it would in an enterprise level environment. Below is the hosts file used for the two clusters:

127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4 
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6
10.12.1.190 ggclust1-scan.localdomain ggclust1-scan 
10.12.1.191 ggclust1-scan.localdomain ggclust1-scan 
10.12.1.192 ggclust1-scan.localdomain ggclust1-scan 
10.12.1.193 ggclust11-vip.localdomain ggclust11-vip 
10.12.1.200 ggclust12-vip.localdomain ggclust12-vip 
10.12.1.195 ggclust2-scan.localdomain ggclust2-scan 
10.12.1.196 ggclust2-scan.localdomain ggclust2-scan 
10.12.1.197 ggclust2-scan.localdomain ggclust2-scan 
10.12.1.198 ggclust21-vip.localdomain ggclust21-vip 
10.12.1.199 ggclust22-vip.localdomain ggclust22-vip
10.12.1.238 ggclust11.localdomain ggclust11 
10.12.1.46 ggclust12.localdomain ggclust12 
10.12.1.37 ggclust21.localdomain ggclust21 
10.12.1.15 ggclust22.localdomain ggclust22
192.168.31.111 ggclust11-priv.localdomain ggclust11-priv 
192.168.31.112 ggclust12-priv.localdomain ggclust12-priv 
192.168.31.121 ggclust21-priv.localdomain ggclust21-priv 
192.168.31.122 ggclust22-priv.localdomain ggclust22-priv

After creating the cluster, the next step is to configure a service for Goldengate processes to connect to from the remote host. Since you typically only want Goldengate running on one node, this service is configured to be only available on one node at a time:
srvctl add service -db demogg1 -service oggserv -preferred demogg11 -available demogg12 
srvctl add service -db demogg2 -service oggserv -preferred demogg21 -available demogg22

Next, set up tnsnames entries for the Goldengate processes.  Samples are below, the local entry is the complex entry configured to optimize network performance.  The local entry was different for each server:

DEMOGG1 = 
(DESCRIPTION = 
(SDU=2097152) 
(ADDRESS = 
(PROTOCOL = BEQ) 
(PROGRAM = /u01/app/oracle/product/19.3.0/dbhome_1/bin/oracle) 
(ARGVO=demogg11) 
(ARGS='(DESCRIPTION=(SDU=2097152)(LOCAL=YES)(ADDRESS=(PROTOCOL=BEQ)))') 
(ENVS='ORACLE_HOME=/u01/app/oracle/product/19.3.0/dbhome_1,ORACLE_SID=demogg12') 
) 
(CONNECT_DATA=(SID=demogg12)) 
)
DEMOGG2 = 
(DESCRIPTION = 
(SDU=2097152) 
(ADDRESS = (PROTOCOL = TCP)(HOST = ggclust2-scan)(PORT = 1521)) 
(CONNECT_DATA = 
(SERVER = DEDICATED) 
(SERVICE_NAME = oggserv) 
) 
) 

Next, I configured an ACFS share for the important data directories for Oracle Goldengate.  This was configured in ASMCA, as shown below:

image

image

Next, Oracle Goldengate was installed on all 4 nodes.  Goldengate does not have a cluster installation option, and the home directory was set to /u02/gghome.  The ‘start manager’ option was chosen, then the following commands were run to put the key directories on the shared file system (/u02/ggdata):

cd /u02/gghome
rmdir dirchk 
rmdir dirprm 
rmdir dirdat 
rmdir dirtmp 
rmdir dircrd 
rmdir dirrpt 
cd /u02/ggdata
mkdir dirchk 
mkdir dirprm 
mkdir dirdat 
mkdir dirtmp 
mkdir dircrd 
mkdir dirrpt 
mkdir BR
cd /u02/gghome
ln -s /u02/ggdata/dirprm /u02/gghome/dirprm 
ln -s /u02/ggdata/dirchk /u02/gghome/dirchk 
ln -s /u02/ggdata/dirdat /u02/gghome/dirdat 
ln -s /u02/ggdata/dircrd /u02/gghome/dircrd 
ln -s /u02/ggdata/dirrpt /u02/gghome/dirrpt 

Note that you may want to have different parameter files based on the cluster node that is running the Goldengate process, in which case you would not put dirprm on shared storage.

At this point, the preparation is ready.  Now to configure the replication.  The schema we are using for this study is HR, password is tiger.  The goldengate user for the replication process is gguser.  We will also create a ggadmin user whose changes will not be replicated.

Next, create the individual users on the database:

1. Create the tablespace for goldengate, in our case golden:

create tablespace golden datafile ‘+DATA’ size 1024m autoextend on;

2. Create the users.

create user gguser identified by gguser;
create user ggadmin identified by ggadmin;

3. Grant required privileges.

exec dbms_goldengate_auth.grant_admin_privilege('GGUSER'’);
exec dbms_goldengate_auth.grant_admin_privilege('GGADMIN');
grant connect, resource, alter any table to gguser, ggadmin;
@marker_setup.sql
@ddl_setup.sql
@role_setup.sql
@ddl_enable.sql
@sequence

Next, we configured the credentials we will be using.  All credentials are configured the same so that there is never any question which db to connect to.  The ggulocal useridalias is to the local database, and the gguremote user is always to the remote database:

add credentialstore
alter credentialstore add user gguser@demogg1, password gguser alias ggulocal 
alter credentialstore add user ggadmin@demogg1, password ggadmin alias ggalocal 
alter credentialstore add user gguser@demogg2, password gguser alias gguremote 
alter credentialstore add user ggadmin@demogg2, password ggadmin alias ggaremote 

Next, add the transaction data and create the checkpointtable.  Do this on both databases since we are going to do multi-master replication.

GGSCI (ggclust11.localdomain) dblogin useridalias ggulocal
Successfully logged into database.
GGSCI (ggclust11.localdomain as gguser@demogg11) 2>add trandata hr.* cols *

GGSCI (ggclust11.localdomain as gguser@demogg11) 3> add checkpointtable gguser.checkpoint

Next, ensure that the manager and GLOBALS are configured properly> These files are the same on both clusters:
edit params mgr


PORT 7809
Autostart ER *

Edit the GLOBALS file and configure it as follows:

checkpointtable gguser.checkpoint 
GGSCHEMA gguser 
ALLOWOUTPUTDIR /u02/ggdata 
ALLOWOUTPUTDIR /u02/gghome 

Exit ggsci and re-enter, then restart the mgr process.
Now we are ready to begin the actual building of the replication. On ggclust11 we will build the initial extract and pump processes.

First the extract. Edit the parameter file for the extract and add these lines (edit params hrext1):

EXTRACT hrext1 
setenv (ORACLE_SID='demogg1') ç Not that this is actually the just the entry in the oratab. 
useridalias ggulocal 
tranlogOptions IntegratedParams (max_sga_size 256), excludeuser ggadmin, excludetag 00 
CACHEMGR CACHEDIRECTORY /u02/ggdata/dirtmp 
BR BRDIR /u02/ggdata/BR 
DDL include all 
Exttrail /u02/ggdata/dirdat/hr 
LOGALLSUPCOLS 
UPDATERECORDFORMAT COMPACT 
table hr.*;

As we noted earlier, we will be using the ggadmin user for maintenance, which is why we have the ‘excludeuser ggadmin’ entry. All dml and ddl done by the ggadmin user will be ignored by Goldengate. The excludetag will ensure that the we don’t run into problems with ping-pong updates. Note that the settings we are using for the excludetag are the default.

GGSCI (ggclust11.localdomain) Dblogin useridalias ggulocal 
GGSCI (ggclust11.localdomain) register extract hrext1 database # Note that this command will return an scn, eg 947747283. Take note of this scn for later use. 
GGSCI (ggclust11.localdomain) add extract hrext1 integrated tranlog, begin now 
GGSCI (ggclust11.localdomain) add exttrail /u02/ggdata/dirdat/hr, extract hrext1

Next we create the pump process to transmit the data.

Edit params hrpump:

extract hrpump 
useridalias gguremote 
CACHEMGR CACHEDIRECTORY /u02/ggdata/dirtmp 
ddl include all 
rmthost ggclust21-vip, mgrport 7809 
rmttrail /u02/ggdata/dirdat/ph 
table hr.*;
</pre>
GGSCI (ggclust11.localdomain) 1&gt; add extract hrpump, exttrailsource /u02/ggdata/dirdat/hr

GGSCI (ggclust11.localdomain) 1&gt; add rmttrail /u02/ggdata/dirdat/ph, extract hrpump
<pre>


At this point the extract and the pump process have been created. Now, run the datapump export using the scn from above:

ggclust11$ expdp system/password directory=dumpdir schemas=hr parallel=4 dumpfile=hrdump%u.dmp flashback_scn=947747283

Then, log into the target system, ggclust21 in our example, and run the import:

ggclust21$ impdp system/password directory=dumpdir parallel=4 dumpfile=hrdump%u.dmp

Now, go back to the source system, ggclust11, and start the extracts

GGSCI (ggclust11.localdomain) start extract hrext1
GGSCI (ggclust11.localdomain) start extract hrpump

Ensure that you monitor the file ggserr.log for errors. It is not uncommon to have simple typos that need to be fixed.

Next connect to ggclust21 and create the replicat process.  Replicat is the term Oracle Goldengate uses for the process that writes the data to the target system.  Note the resolveconflict section. Since we plan to set up multi-master replication, basic conflict resolution is required.  The setup below will handle the simple problems.  There will be conflicts that require manual resolution in any true bi-directional environment.

GGSCI (ggclust21.localdomain) edit params hrrep1
replicat hrrep1
SETENV(ORACLE_SID='demogg2')
DBOPTIONS INTEGRATEDPARAMS(parallelism 2), settag 00
assumetargetdefs
useridalias ggulocal
handlecollisions
discardfile /u02/ggdata/dirrpt/hrerr.dsc, purge
ddl include all
map hr.*, target hr.*
RESOLVECONFLICT (UPDATEROWEXISTS, (DEFAULT, USEMAX (last_updt_dt_tm))),
RESOLVECONFLICT (DELETEROWEXISTS, (DEFAULT, OVERWRITE)),
RESOLVECONFLICT (INSERTROWEXISTS, (DEFAULT, USEMAX (last_updt_dt_tm))),
RESOLVECONFLICT (UPDATEROWMISSING, (DEFAULT, OVERWRITE)),
RESOLVECONFLICT (DELETEROWMISSING, (DEFAULT, DISCARD)),
MAPEXCEPTION
(TARGET ggadmin.EXCEPTIONS, EXCEPTIONSONLY, INSERTALLRECORDS, COLMAP
(
excp_date = @DATENOW(),
rep_name = @GETENV ('GGENVIRONMENT', 'GROUPNAME'),
table_name = @GETENV ('GGHEADER', 'TABLENAME'),
errno = @GETENV ('LASTERR', 'DBERRNUM'),
errtype = @GETENV ('LASTERR', 'ERRTYPE'),
optype = @GETENV ('LASTERR', 'OPTYPE'),
transind = @GETENV ('GGHEADER', 'TRANSACTIONINDICATOR'),
transimgind = @GETENV ('GGHEADER', 'BEFOREAFTERINDICATOR'),
committimestamp = @GETENV ('GGHEADER', 'COMMITTIMESTAMP'),
reccsn = @GETENV ('TRANSACTION', 'CSN'),
recseqno = @GETENV ('RECORD', 'FILESEQNO'),
recrba = @GETENV ('RECORD', 'FILERBA'),
rectranspos = @GETENV ('RECORD', 'RSN'),
reclength = @GETENV ('GGHEADAER', 'RECORDLENGTH'),
logrba = @GETENV ('GGHEADER', 'LOGRBA'),
logposition = @GETENV ('GGHEADER', 'LOGPOSITION'),
grouptype = @GETENV ('GGENVIRONMENT', 'GROUPTYPE'),
filename = @GETENV ('GGFILEHEADER', 'FILENAME'),
fileno = @GETENV ('GGFILEHEADER', 'FILESEQNO'),
srcrowid = @GETENV ('TRANSACTION', 'CSN'),
srcdbcharset = @GETENV ('GGFILEHEADER', 'DBCHARSET'),
replag = @GETENV ('LAG', 'SEC'),
cnt_cdr_conflicts = @GETENV ('DELTASTATS','TABLE', @GETENV ('GGHEADER', 'TABLENAME'),'CDR_CONFLICTS'), cnt_cdr_resolutions = @GETENV ('DELTASTATS','TABLE', @GETENV ('GGHEADER', 'TABLENAME'),'CDR_RESOLUTIONS_SUCCEEDED'),
cnt_cdr_failed = @GETENV ('DELTASTATS','TABLE', @GETENV ('GGHEADER', 'TABLENAME'),'CDR_RESOLUTIONS_FAILED')
)
);

Now lets get the replicat going:

GGSCI (ggclust21.localdomain)dblogin useridalias ggulocal
GGSCI (ggclust21.localdomain ggrep21) add replicat hrrep1 integrated exttrail /u02/ggdata/dirdat/ph # note that this exttrail corresponds to the remotetrail on the pump extract
GGSCI (ggclust21.localdomain ggrep21) register replicat hrrep1 database
GGSCI (ggclust21.localdomain ggrep21) start replicat hrrep1

Once again, monitor the file ggserr.log and correct any problems that appear.

Once you have verified that the extracts and replicat are running properly, insert a row into the source schema and verify that it gets replicated to the target. Once the row replicates, you know that the processes are working properly.

Now, its time to set up the replication in the other direction. The setup is identical, with the exception that we do not need to run a datapump export/import since the initial source of the data was our source database.  Below are the commands to set up the extract and pump processes.  These are set up on the target database this time:

GGSCI (ggclust21.localdomain ggrep21)  register extract hr2ext1 database;
GGSCI (ggclust21.localdomain ggrep21) add extract hr2ext1, integrated tranlog, begin now
GGSCI (ggclust21.localdomain ggrep21) add exttrail /u02/ggdata/dirdat/xr, extract hr2ext1
EXTRACT hr2ext1
setenv (ORACLE_SID='demogg2')
useridalias ggulocal
tranlogOptions IntegratedParams (max_sga_size 256), excludeuser ggadmin, excludetag 00
CACHEMGR CACHEDIRECTORY /u02/ggdata/dirtmp
BR BRDIR /u02/ggdata/BR
DDL include all
Exttrail /u02/ggdata/dirdat/xr
LOGALLSUPCOLS
UPDATERECORDFORMAT COMPACT
table hr.*;

extract hrpump2
Dblogin useridalias gguremote
ddl include all
rmthost ggclust11-vip, mgrport 7809
rmttrail /u02/ggdata/dirdat/xh
table hr.*;

GGSCI (ggclust21.localdomain ggrep21) add extract hrpump2, exttrailsource /u02/ggdata/dirdat/xr
GGSCI (ggclust21.localdomain ggrep21) add rmttrail /u02/ggdata/dirdat/xh, extract hrpump2
GGSCI (ggclust21.localdomain ggrep21) start extract hr2ext1
GGSCI (ggclust21.localdomain ggrep21) start extract hrpump2

Now monitor the ggserr.log for errors on the extract process.  If there are no errors, return to the source system (ggclust11) and build the replicat process:

GGSCI (ggclust11.localdomain) ggrep1 register replicat hrrep2 database
GGSCI (ggclust11.localdomain) ggrep1 add replicat  hrrep2 integrated exttrail /u02/ggdata/dirdat/xh
replicat hrrep2
 SETENV(ORACLE_SID='demogg1')
 DBOPTIONS INTEGRATEDPARAMS(parallelism 2)
 AssumeTargetDefs
 DiscardFile /u02/ggdata/dirrpt/rpdw.dsc, purge
 USERIDALIAS ggulocal
 DDLERROR DEFAULT DISCARD IGNOREMISSINGOBJECTS
 REPERROR (DEFAULT, EXCEPTION)
 MAP HR.*, target HR.*
 RESOLVECONFLICT (UPDATEROWEXISTS, (DEFAULT, USEMAX (last_updt_dt_tm))),
 RESOLVECONFLICT (DELETEROWEXISTS, (DEFAULT, OVERWRITE)),
 RESOLVECONFLICT (INSERTROWEXISTS, (DEFAULT, USEMAX (last_updt_dt_tm))),
 RESOLVECONFLICT (UPDATEROWMISSING, (DEFAULT, OVERWRITE)),
 RESOLVECONFLICT (DELETEROWMISSING, (DEFAULT, DISCARD)),
 MAPEXCEPTION
 (TARGET ggadmin.EXCEPTIONS, EXCEPTIONSONLY, INSERTALLRECORDS, COLMAP
 (
 excp_date = @DATENOW(),
 rep_name = @GETENV ('GGENVIRONMENT', 'GROUPNAME'),
 table_name = @GETENV ('GGHEADER', 'TABLENAME'),
 errno = @GETENV ('LASTERR', 'DBERRNUM'),
 errtype = @GETENV ('LASTERR', 'ERRTYPE'),
 optype = @GETENV ('LASTERR', 'OPTYPE'),
 transind = @GETENV ('GGHEADER', 'TRANSACTIONINDICATOR'),
 transimgind = @GETENV ('GGHEADER', 'BEFOREAFTERINDICATOR'),
 committimestamp = @GETENV ('GGHEADER', 'COMMITTIMESTAMP'),
 reccsn = @GETENV ('TRANSACTION', 'CSN'),
 recseqno = @GETENV ('RECORD', 'FILESEQNO'),
 recrba = @GETENV ('RECORD', 'FILERBA'),
 rectranspos = @GETENV ('RECORD', 'RSN'),
 reclength = @GETENV ('GGHEADAER', 'RECORDLENGTH'),
 logrba = @GETENV ('GGHEADER', 'LOGRBA'),
 logposition = @GETENV ('GGHEADER', 'LOGPOSITION'),
 grouptype = @GETENV ('GGENVIRONMENT', 'GROUPTYPE'),
 filename = @GETENV ('GGFILEHEADER', 'FILENAME'),
 fileno = @GETENV ('GGFILEHEADER', 'FILESEQNO'),
 srcrowid = @GETENV ('TRANSACTION', 'CSN'),
 srcdbcharset = @GETENV ('GGFILEHEADER', 'DBCHARSET'),
 replag = @GETENV ('LAG', 'SEC'),
 cnt_cdr_conflicts = @GETENV ('DELTASTATS','TABLE', @GETENV ('GGHEADER', 'TABLENAME'),'CDR_CONFLICTS'), cnt_cdr_resolutions = @GETENV ('DELTASTATS','TABLE', @GETENV ('GGHEADER', 'TABLENAME'),'CDR_RESOLUTIONS_SUCCEEDED'),
 cnt_cdr_failed = @GETENV ('DELTASTATS','TABLE', @GETENV ('GGHEADER', 'TABLENAME'),'CDR_RESOLUTIONS_FAILED')
 )
 );
GGSCI (ggclust11.localdomain) ggrep1 start replicat hrrep2.

At this point, the bi-directional (multi-master) replication should be working properly.  Insert a row into each database and verify that it gets copied.  Then delete it and make sure it is deleted.

Some notes:

The exttrail for the replicat is always the same as the remotetrail for the pump process. This is /u02/ggdata/dirdat/ph on demogg1 (for hrpump/hrext1).

The exttrailsource for the pump process is the same as the exttrail for the extract process. This is /u02/ggdata/dirdat/hr on demogg1 (from hrext1).

The last step in this process is to install and configure the Grid Infrastructure standalone agent.  My experimentation with the agent shows a decent amount of potential, but substantial customization in the form of script writing is going to be required to make it really useful.  This will probably be the subject of another blog post, but for this post I will just cover the installation and configuration.

This agent is used to manage the Goldengate processes. The agent is downloaded from here: http://www.oracle.com/technetwork/database/database-technologies/clusterware/downloads/xag-agents-downloads-3636484.html

After downloading the agent, install it in a directory that is outside of both the database and GI home. I chose /u01/oracle/xag. Just unzip the file and run this command to install the agent on both nodes of each cluster. Run the command once per cluster, the setup installs the agent on both cluster nodes:

$./xagsetup.sh --install --directory /u01/oracle/xag --all_nodes

Now with the agent installed, we put the Goldengate under the control of the agent. The first step of this process it to put the standalone agent bin directory in your path statement ahead of all other oracle software. This is to ensure that the agent commands run against the correct agent. Then we put Goldengate under agent control (ggclust11):

./agctl add goldengate GG_SOURCE \
--gg_home /u02/gghome \
--oracle_home /u01/app/oracle/product/19.3.0/dbhome_1 \
--db_services ora.demogg2.oggserv.svc --use_local_services \
--monitor_extracts hrext1,hrpump1 --monitor_replicats hrrep2 \
--filesystems ora.acfs.ggvol.acfs \
--user oracle --group oinstall

The command for ggclust21:

./agctl add goldengate GG_TARGET \
 --gg_home /u02/gghome \
  --oracle_home /u01/app/oracle/product/19.3.0/dbhome_1 \
  --db_services ora.demogg2.oggserv.svc --use_local_services \
  --monitor_extracts hr2ext1,hrpump2 --monitor_replicats hrrep1 --filesystems ora.acfs.ggdata.acfs \
  --user oracle --group oinstall

As the last step in this process, add this line to the GLOBALS file for all 4 nodes, then stop and restart all the process:

XAGENABLE

With the golden gate processes managed by an agent you should see this:

GGSCI (ggclust21.localdomain) 2&amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;gt; info all

Program Status Group Lag at Chkpt Time Since Chkpt XAG

MANAGER 				RUNNING 				MANAGED		/xag.GG_TARGET.goldengate/ggclust21
EXTRACT 				RUNNING 				HREXT1 	00:00:00 00:00:02 NON-CRITICAL
EXTRACT 				RUNNING 				HRPUMP 	00:00:00 00:00:04 NON-CRITICAL
REPLICAT 				RUNNING 				HRREP2 	00:00:00 00:00:00 NON-CRITICAL

At this point, Multi-master goldengate is completely configured in a cluster. Remember that if you want to move the Goldengate process to run on the other node in the cluster, you will also need to move the service configured for Goldengate in this step:

srvctl add service -db demogg1 -service oggserv -preferred demogg11 -available demogg12 

Please note that there is manual intervention that will be required should you want to run Goldengate on the second node of the cluster. I would recommend writing scripts to use to move the Goldengate processes to the other cluster node. For example, you will need to change the destination of the pump process on the target node to point to ggclust12-vip from ggclust11-vip. For that reason, you may find it expedient to create a virtual IP on both clusters to be used by the Goldengate processes. The agent can be used for that purpose, but still remember that the agent only works on one cluster, it will not affect the remote cluster.  You will need to run agent commands on both clusters in order to move the Goldengate processes.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s


%d bloggers like this: