Andy Kerber (@dbakerber), Senior Consultant

In this four part series, I will provide an example of how to set up replication using Oracle GoldenGate. In part one, we will set up Multi-Master replication between two Oracle databases. In part two, we will add one way replication to MySQL. In part three, we will add one way replication to PostgreSQL. In part four, we will add a third master database, Oracle version 11.2.0.1, where we cannot use integrated replicat and extact.

On the Oracle side we will use integrated replicat and extract in the Oracle 12.1.0.2.

This blog post should be read closely. We move from one environment to the other frequently and have color coded the sections accordingly.

Note: In the Oracle Multi-Master setup, I include only basic conflict resolution steps. Conflict detection and resolution is entire area of discussion of its own, which we will not discuss in depth here.

In addition, I am not going to go into great detail for the Oracle GoldenGate installation, as you can run the installer and follow the steps. You will also need to run the GoldenGate shell software (ggsci) from the installation directory. The Oracle database LD_LIBRARY_PATH will need to be in your path statement to run Oracle GoldenGate.

In this discussion, the variable $GGH points to the GoldenGate Home directory on the Oracle database servers.

Oracle Environment:

Oracle Enterprise Edition x86_64 on Linux. 12.1.0.2, Sid ggdemo on server ggvm (initial target server)

Oracle Enterprise Edition x86_64 on Linux, 12.1.0.2 Sid ggtest on server pgorcl (initial source server)

Replicated source schema: andy

Replicated destination schema: andy

Oracle GoldenGate Version: 12c

Oracle GoldenGate schema: GGADMIN

Normally the ggsci prompt will contain the server name and database sid, and a line number in addition to ggsci and will look something like this:

GGSCI (pgorcl.localdomain as ggadmin@ggtest) 19>

If you are not logged into the database, it will just have the server name and line number:

GGSCI (pgorcl.localdomain) 2>

For the purposes of this document, we are just using GGSCI @ server>

Any time the command ‘edit params <name>’ is entered, GGSCI> will open a text editor. Add the configuration lines that follow the command, and then save and exit the file.

Replication Setup

Set up 1-Way Replication and Verify

1. Configure Integrated Extract on the Source DB

The first step after installing GoldenGate in each environment is to start ggsci and create the subdirectories.

./ggsci

GGSCI >create subdirs

 

Run the above command on all Oracle and MySQL servers.

Configure the port settings by editing the params file for the manager and adding the line to set the port number in ggsci. Do this on all servers.

GGSCI @pgorcl > edit params mgr

port 7809

GGSCI @pgorcl> start mgr

 

GGSCI @ggvm > edit params mgr

port 7809

GGSCI @ggvm > start mgr

 

Next, set up your globals file in both environments.

GGSCI@pgorcl> Edit params ./GLOBALS

CHECKPOINTTABLE ggcheckpoint

 

GGSCI@ggvm> Edit params ./GLOBALS

CHECKPOINTTABLE ggcheckpoint

 

After editing the global parameters, make sure that the lines are in a file called GLOBALS in the $GGH directory. I have run into issues where it was created as globals.prm in the params directory. But, the settings need to be in the GLOBALS file in the $GGH directory.

Configure your database environment. Be sure that each database is configured in tnsnames.ora on both servers. Run these commands in each Oracle database (in SQLPLUS). Run them from $GGH, the SQL files reside there:

 SQLPLUS>alter database add supplemental log data
 SQLPLUS>alter system set enable_goldengate_replication=true scope=both;
 SQLPLUS>create tablespace ggs_data datafile '/u01/app/oracle/oradata/ggdemo/ggs_data01.dbf' size 1024m autoextend on;
 SQLPLUS>create user ggadmin identified by ggadmin default tablespace ggs_data temporary tablespace temp;
 SQLPLUS>grant connect,resource,create session, alter session to ggadmin;
 SQLPLUS>grant select any dictionary, select any table,create table to ggadmin;
 SQLPLUS>grant alter any table to ggadmin;
 SQLPLUS>grant execute on utl_file to ggadmin;
 SQLPLUS>grant flashback any table to ggadmin;
 SQLPLUS>grant execute on dbms_flashback to ggadmin;
 SQLPLUS>grant insert,update,delete on target.tcustmer to ggadmin;
 SQLPLUS>grant insert,update,delete on target.tcustord to ggadmin;
 SQLPLUS>@marker_setup.sql
 SQLPLUS>@ddl_setup.sql
 SQLPLUS>@role_setup.sql
 SQLPLUS>@ddl_enable.sql
 SQLPLUS>@sequence.sql
 SQLPLUS>grant connect, resource, create session to andy;

 

Note that the last line is not strictly necessary, but it does make debugging much easier.

Log into each database through GGSCI and create the checkpoint table. This is only required in the destination database, but since we are going to set up bi-directional replication, we will go ahead and create it in both environments.

GGSCI @pgorcl > dblogin userid ggadmin
 Password:
 Successfully logged into database.
 GGSCI@pgorcl > add checkpointtable ggadmin.ggcheckpoint

 

GGSCI @ggvm > dblogin userid ggadmin
 Password:
 Successfully logged into database.
 GGSCI@ggvm > add checkpointtable ggadmin.ggcheckpoint

 

In the source environment only, add the transaction data:

GGSCI@pgorcl > add trandata andy.* cols *

 

Find the current scn from sqlplus:

SQLPLUS> column current_scn format 99999999999

SQLPLUS> select current_scn from v$database;

 

Use datapump to export the data and import it into the destination (target) database:

SQLPLUS> create directory dumpdir as '/home/oracle/dump' ;

oracle@pgorcl$ expdp system/password directory=dumpdir full=y schemas=scott parallel=4 dumpfile=ora112_%u.dmp flashback_scn=&SCN_FROM_ABOVE

scp ora112*.dmp ggvm:/home/oracle/dump

 

(on ggvm)

sqlplus / as sysdba

SQLPLUS> create directory dumpdir as '/home/oracle/dump' ;

impdp@ggvm impdp system/password directory=dumpdir dumpfile=ora112_%u.dmp  parallel=4

 

In ggsci, on the source server, configure the combined extract process:

GGSCI@pgorcl > dblogin userid ggadmin
 Password:
 Successfully logged into database.

GGSCI@pgorcl > register extract insrc database

GGSCI@pgorcl > add extract insrc, integrated tranlog, begin now

GGSCI@pgorcl > edit params insrc

extract insrc
 SETENV(ORACLE_SID="ggtest")
 SETENV(ORACLE_HOME="/u01/app/oracle/product/12.1.0/dbhome_1")
 USERID ggadmin, PASSWORD ggadmin
 TRANLOGOPTIONS IntegratedParams (max_sga_size 256)
 EXTTRAIL ./dirdat/in
 LOGALLSUPCOLS
 UPDATERECORDFORMAT COMPACT
 TABLE andy.*;

GGSCI@pgorcl > add exttrail ./dirdat/in, extract insrc, megabytes 10

 

Create the pump extract process:

GGSCI@pgorcl > add extract pumpint, exttrailsource ./dirdat/in

GGSCI@pgorcl > edit params pumpint

EXTRACT pumpint
 RMTHOST ggvm, MGRPORT 7809
 RMTTRAIL ./dirdat/pn
 TABLE andy.*;

GGSCI@pgorcl > add rmttrail ./dirdat/pn, extract pumpint, megabutes 10

GGSCI@pgorcl > start extract intsrc

GGSCI@pgorcl > start extract pumpint

 

Tail the ggserr.log file on the source server in the GoldenGate home directory ($GGH). Watch for any errors, and resolve as required. If there are no errors, the extract process should be configured properly.

2. Configure Integrated Replicat on the Destination DB

Connect to the destination database server and start ggsci:

./ggsci

GGSCI@ggvm> edit params repintan
 replicat repintan
 SETENV(ORACLE_SID='ggdemo')
 DBOPTIONS INTEGRATEDPARAMS(parallelism 4)
 AssumeTargetDefs
 DiscardFile ./dirrpt/rpdw.dsc, purge
 USERID ggadmin, PASSWORD ggadmin
 MAP andy.*, target andy.*;

GGSCI@ggvm> dblogin userid ggadmin

password:

GGSCI@ggvm> add replicat repintan integrated exttrail ./dirdat/pn

GGSCI@ggvm> start replicat repintan

 

To show all processes are running, enter info all:

GGSCI@ggvm> info all

Program     Status     Group       Lag at Chkpt Time Since Chkpt

MANAGER     RUNNING

REPLICAT   RUNNING     REPINTAN   00:00:00     00:00:39

(on source server)

 

GGSCI@pgorcl> info all

Program     Status     Group       Lag at Chkpt Time Since Chkpt

MANAGER     RUNNING
 EXTRACT     RUNNING     INTEXTAN    00:00:07      00:00:07
 EXTRACT     RUNNING     PUMPINT     00:00:00      00:00:08

 

Now in SQLPLUS, run an dml statement to verify that replication is working.

SQLPLUJS@GGTEST> select count(1) from andy.emp;

COUNT(1)
 ----------
 13

 

SQLPLUS@GGDEMO> select count(1) from andy.emp;

COUNT(1)
 ----------
 13

 

SQLPLUJS@GGTEST>

 

delete from andy.emp where empno=7900;

1 row deleted.

SQLPLUJS@GGTEST> commit;
 Commit complete.

 

SQLPLUS@GGDEMO> select count(1) from andy.emp;

COUNT(1)
 ----------
 12

 

One way replication is now working.

3. Make the required changes for bi-directional replication

On source database, for each table:

SQLPLUS> alter table andy.emp add (last_updt_dt_tm timestamp);

 

Add last_updt_dt_tm to each table being replicated for purposes of collision avoidance. Because we are replicating DDL, the column is also added to the destination tables.

After adding the column, create triggers for each table like this:

create or replace trigger andy.updt_emp
before update on andy.emp
for each row
begin
:new.last_updt_dt_tm:=systimestamp;
end;
/

 

Note that GoldenGate will automatically suppress triggers for replicated objects.

On both source and target database:

SQLPLUS>    CREATE TABLE "GGADMIN"."EXCEPTIONS"
 (    "EXCP_DATE" TIMESTAMP (6) DEFAULT systimestamp,
 "REP_NAME" VARCHAR2(10),
 "TABLE_NAME" VARCHAR2(56),
 "ERRNO" NUMBER,
 "ERRTYPE" VARCHAR2(6),
 "OPTYPE" VARCHAR2(24),
 "TRANSIND" VARCHAR2(12),
 "TRANSIMGIND" VARCHAR2(8),
 "COMMITTIMESTAMP" VARCHAR2(26),
 "RECCSN" NUMBER,
 "RECSEQNO" NUMBER,
 "RECRBA" NUMBER,
 "RECTRANSPOS" NUMBER,
 "RECLENGTH" NUMBER,
 "LOGRBA" NUMBER,
 "LOGPOSITION" NUMBER,
 "GROUPTYPE" VARCHAR2(12),
 "FILENAME" VARCHAR2(50),
 "FILENO" NUMBER,
 "SRCROWID" VARCHAR2(40),
 "SRCDBCHARSET" VARCHAR2(40),
 "REPLAG" NUMBER,
 "CNT_CDR_CONFLICTS" NUMBER,
 "CNT_CDR_RESOLUTIONS" NUMBER,
 "CNT_CDR_FAILED" NUMBER
 ) SEGMENT CREATION IMMEDIATE
 PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
 TABLESPACE "GGS_DATA" ;

 

On target server:

GGGSCI@ggvm> edit params inextan2

extract inextan2
 USERID ggadmin, PASSWORD ggadmin
 TRANLOGOPTIONS IntegratedParams (max_sga_size 256)
 EXTTRAIL ./dirdat/in
 LOGALLSUPCOLS
 UPDATERECORDFORMAT COMPACT
 DDL INCLUDE MAPPED OBJNAME ANDY.*
 TABLE ANDY.*;

GGSCI@ggvm> dblogin userid ggadmin
 Passsword:

GGSCI@ggvm> add trandata andy.* cols *

GGSCI@ggvm> register extract inextan2 database

GGSCI@ggvm> add extract inextan2, integrated tranlog, begin now

GGSCI@ggvm> add exttrail ./dirdat/in, extract inextan2, megabytes 10

GGSCI@ggvm> edit params pumpint2

EXTRACT pumpint2
 RMTHOST pgorcl, MGRPORT 7809
 RMTTRAIL ./dirdat/pn
 TABLE ANDY.*;

GGSCI@ggvm> add rmttrail ./dirdata/pn, extract pumpint2, megabytes 10

GGSCI@ggvm> start extract pumpint2

GGSCI@ggvm> start extract inextan2

 

On source server add the replicat:

GGSCI@pgorcl> edit params rpintan2

replicat rpintan2
 SETENV(ORACLE_SID='ggtest')
 DBOPTIONS INTEGRATEDPARAMS(parallelism 4)
 AssumeTargetDefs
 DiscardFile ./dirrpt/rpdw.dsc, purge
 USERID ggadmin, PASSWORD ggadmin
 DDLERROR DEFAULT DISCARD IGNOREMISSINGOBJECTS
 REPERROR (DEFAULT, EXCEPTION)
 MAP ANDY.*, target ANDY.*
 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@pgorcl> dblogin userid ggadmin
 password

GGSCI@pgorcl> add replicat rpintan2 integrated exttrail ./dirdat/pn

GGSCI@pgorcl> start replicat rpintan2

 

On target server, add basic conflict detection and resolution:

edit params REPINTAN

replicat repintan
 SETENV(ORACLE_SID='ggdemo')
 DBOPTIONS INTEGRATEDPARAMS(parallelism 4)
 AssumeTargetDefs
 DiscardFile ./dirrpt/rpdw.dsc, purge
 USERID ggadmin, PASSWORD ggadmin
 DDLERROR DEFAULT DISCARD IGNOREMISSINGOBJECTS
 REPERROR (DEFAULT, EXCEPTION)
 MAP ANDY.*, target ANDY.*
 RESOLVECONFLICT (UPDATEROWEXISTS, (DEFAULT, USEMAX (last_updt_dt_tm))),
 RESOLVECONFLICT (INSERTROWEXISTS, (DEFAULT, USEMAX (last_updt_dt_tm))),
 RESOLVECONFLICT (DELETEROWEXISTS, (DEFAULT, OVERWRITE)),
 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')
 )
 );

 

Stop and start the replicat:

GGSCI@ggvm> stop replicat repintan

GGSCI@ggvm> start replicat repintan

 

Note that what we have done in the replicats is add very basic collision avoidance. This is an explanation of the options used and the results.

RESOLVECONFLICT (INSERTROWEXISTS, (DEFAULT, USEMAX (last_updt_dt_tm))),

RESOLVECONFLICT (UPDATEROWEXISTS, (DEFAULT, USEMAX (last_updt_dt_tm))),

 

From Oracle GoldenGate documentation:

Per DEFAULT, use the USEMAX resolution logic for all other columns in the table (the default column group), using the last_mod_time column as the resolution column. This column is updated with the current time whenever the row is modified; the value of this column in the trail is compared to the value in the target. If the value of last_mod_time in the trail record is greater than the current value of last_mod_time in the target database, the changes to name, phone, address, balance, comment and last_mod_time are applied to the target.

RESOLVECONFLICT (DELETEROWEXISTS, (DEFAULT, IGNORE)),
The DELETEROWEXISTS keyword means that if the row exists during a delete operation, apply the delete.

RESOLVECONFLICT (UPDATEROWMISSING, (DEFAULT, OVERWRITE)),
If the row does not exist during an update, change the update to an insert and apply it.

RESOLVECONFLICT (DELETEROWMISSING, (DEFAULT, DISCARD)),
Means if the deleted row is missing, discard and continue.

In order to prevent the replication from failing, all other errors are logged to the exceptions table.

Now run the same validation tests with two way replication running, and verify that everything is working properly.

In this article, we have configured bidirectional replication using Oracle GoldenGate, and added basic conflict detection and resolution. In my next post, I will cover adding one way replication to MySQL.

Please note: this blog contains code examples provided for your reference. All sample code is provided for illustrative purposes only. Use of information appearing in this blog is solely at your own risk. Please read our full disclaimer for details.