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.
