Andy Kerber (@dbakerber), Senior Consultant
This is the fourth post in our four part series on Oracle GoldenGate replication. In this entry, we will add a third master instance into the replication. The third master instance will be Oracle 11.2.0.1, Oracle sid ggdem11.
In our configuration, ggdemo and ggdem11 will have bi-directional replication between both themselves and pgorcl. There will be no direct communication between ggdem11 and ggdemo, so changes will be replicated from ggdem11 to pgorcl, to ggdem, and vice versa.
Please read this blog post very closely as we move from one environment to the other frequently, and have colored coded the sections accordingly.
Note: In the Oracle multi-master setup, I include only basic conflict resolution steps. Conflict detection and resolution is an entire area of discussion of its own, and we will not discuss it in depth here.
In addition, I will not go into great detail about 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 in order to run Oracle GoldenGate.
In this discussion, the variable $GGH points to the GoldenGate Home directory on each database server.
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)
Oracle Enterprise Edition x86_64 on Linux, 11.2.0.1, Sid ggdem11 on server nggvm.
Replicated source schema: andy
Replicated destination schema: andy
Oracle GoldenGate Version: 12c
Oracle GoldenGate schema: GGADMIN
Normally the ggsci prompt contains the server name and database sid, and a line number in addition to ggsci, and would look something like this:
GGSCI (pgorcl.localdomain as ggadmin@ggtest) 19>
However, 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.
Database Setup
The first step is to make the database changes necessary for replication with the three master instances.
We make the assumption that the reader knows how to create an Oracle 11.2.0.1 database, enable archive logging, and create the schema. Below are the next steps. Note that we do not set the ‘enable_goldengate_replication’ init parameter because it is not available in 11.2.0.1. Run the below steps in the ggdem11 instance only, as they have already been run in the other instances:
SQLPLUS>alter database add supplemental log data 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;
Next, in all instances, we create a new user, ggoper. While we do not use ggoper in this blog, should you need to do maintenance in an Oracle instance without that change being replicated, you will do so with the ggoper user. Keep in mind that with three master instances, you cannot use the same user (ggadmin) for both replication and maintenance.
SQLPLUS> create user ggoper identified by ggoper;
SQLPLUS> grant dba, select any dictionary, unlimited tablespace, create session to ggoper;
Note that in Oracle 11.2.0.1, integrated extract and replicat are not available. So we cannot use them for either the source extract or target replicat data on the 11.2.0.1 instance. However we can, and do, use integrated extract for the extract from Oracle 12.1.0.2 (ggtest).
For each extract process you have created on the oracle instances, edit the parameter file and change the line that reads:
TRANLOGOPTIONS excludeuser ggadmin
to read:
TRANLOGOPTIONS excludeuser ggoper
The reason for this modification is to allow the changes to cascade to the additional instance. In order to properly manage conflicts, a user who can make changes that are not replicated to other environments is required. With bi-directional replication, the ggadmin user can be used for both maintenance and replication. But with three or more master instances, changes will need to cascade from the first instance to the second, then to the third, so activities by ggadmin need to be replicated. Thus we created the ggoper user to make changes that are not replicated to other environments.
Replication Setup
Set up 1-Way Replication and Verify
1 – Configure Extract on the New Instance (ggdem11)
The first step, after installing GoldenGate in each environment, is to start ggsci and create the subdirectories.
./ggsci GGSCI >create subdirs
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@nggvm > edit params mgr port 7809 GGSCI@nggvm> start mgr
Next, set up your globals file.
GGSCI@nggvm> 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.
Note that while the last line is not strictly necessary, it makes debugging much easier.
Log into the database through GGSCI and create the checkpoint table.
GGSCI@nggvm > dblogin userid ggadmin Password: Successfully logged into database. GGSCI@nggvm > add checkpointtable ggadmin.ggcheckpoint
Note: Normally at this point we would add the trandata to the source schema. However, it was added to the instance on pgorcl in part one of this blog, so we do not need to complete this step. The command would be add trandata andy.* after logging into the database through ggsci.
Find the current scn from sqlplus in ggdemo pgorcl server:
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 nggvm)
sqlplus / as sysdba SQLPLUS> create directory dumpdir as '/home/oracle/dump' ; impdp@nggvm impdp system/password directory=dumpdir dumpfile=ora112_%u.dmp parallel=4
In the new environment only, add the transaction data. This has already been added to the other environments:
GGSCI@nggvm > add trandata andy.* cols *
In ggsci, on the source server, configure the combined extract process:
GGSCI@pgorcl > dblogin userid ggadmin Password: Successfully logged into database. GGSCI@pgorcl > add extract andy1, integrated tranlog, begin now Edit params andy1 extract andy1 USERID ggadmin, PASSWORD ggadmin TRANLOGOPTIONS IntegratedParams (max_sga_size 256), excludeuser ggoper EXTTRAIL ./dirdat/an LOGALLSUPCOLS UPDATERECORDFORMAT COMPACT DDL INCLUDE MAPPED OBJNAME ANDY.* TABLE ANDY.*;
GGSCI@pgorcl> add exttrail ./dirdat/an, extract andy1, megabytes 10 GGSCI@pgorcl> dblogin userid ggadmin, password ggadmin Successfully logged into database. GGSCI@pgorcl> register extract andy1 database GGSCI@pgorcl> add extract andypump, exttrailsource ./dirdat/an begin now GGSCI@pgorcl> add rmttrail ./dirdat/ra extract andypump
Next, create the pump process:
GGSCI@pgorcl > edit params andypump extract andypump userid ggadmin, password ggadmin rmthost nggvm, mgrport 7809 rmttrail ./dirdat/an table andy.*;
Start the processes and tail the ggserr.log file on the source server in the GoldenGate home directory ($GGH). Watch for any errors, and resolve as required. If no errors occur, the extract process should be configured properly.
GGSCI@pgorcl > start extract andypump GGSCI@pgorcl > start extract andy1
2 – Configure Replicat on the Destination DB
Connect to the destination database server and start ggsci:
./ggsci GGSCI@nggvm> add replicat andy1 exttrail ./dirdat/an GGSCI@nggvm> edit params andy1 # note the extended conflict resolution. replicat andy1 assumetargetdefs userid ggadmin, password ggadmin handlecollisions discardfile ./dirrpt/andy.dsc, purge map andy.*, target andy.* RESOLVECONFLICT (UPDATEROWEXISTS, (DEFAULT, USEMAX (last_updt_dt_tm))), RESOLVECONFLICT (DELETEROWEXISTS, (DEFAULT, IGNORE)), 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@nggvm> dblogin userid ggadmin password: GGSCI@nggvm> add trandata andy.* GGSCI@nggvm> start replicat andy1
At this point, replication should be working to nggvm. Log into the ggdem11 instance and make a change at pgorcl (ggtest) then verify that the change is transmitted to ggdem11.
SQLPLUJS@GGTEST> select count(1) from andy.emp; COUNT(1) ---------- 13 SQLPLUS@GGDEM11> select count(1) from andy.emp; COUNT(1) ---------- 13 SQLPLUS@GGTEST> delete from andy.emp where empno=7900; 1 row deleted. SQLPLUJS@GGTEST> commit; Commit complete.
SQLPLUS@GGDE11> select count(1) from andy.emp; COUNT(1) ---------- 12
One way replication is now working.
3 – Make the required changes for multi-master replication
Note that GoldenGate will automatically suppress triggers for replicated objects.
On the ggdem11 instance, create the exception table:
SQLPLUS@ggdem11> 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" ;
In ggsci on nggvm, create the extract process:
GGSCI#nggvm> add extract andy2 tranlog begin now EXTRACT added. GGSCI@nggvm> add exttrail ./dirdat/ea extract andy2 EXTTRAIL added. GGSCI@nggvm> edit params andy2 extract andy2 USERID ggadmin, PASSWORD ggadmin TRANLOGOPTIONS excludeuser ggoper EXTTRAIL ./dirdat/ea LOGALLSUPCOLS DDL INCLUDE MAPPED OBJNAME ANDY.* TABLE ANDY.*; GGSCI#nggvm> add extract andypump exttrailsource ./dirdat/ea GGSCI@nggvm> add rmttrail ./dirdat/ra extract andypump, megabytes 10 GGSCI@nggvm> edit params andypump EXTRACT andypump USERID ggadmin, PASSWORD ggadmin RMTHOST pgorcl, MGRPORT 7809 RMTTRAIL ./dirdat/ra PASSTHRU TABLE andy.*; GGSCI@nggvm> start extract andypump GGSCI@nggvm> start extract andy2
On the source server, add the replicat:
GGSCI@pgorcl> add replicat andy2 exttrail ./dirdat/at GGSCI@pgorcl> edit params andy2 replicat andy2 userid ggadmin, password ggadmin assumetargetdefs handlecollisions discardfile ./dirrpt/andy.dsc, purge map andy.*, target andy.* RESOLVECONFLICT (UPDATEROWEXISTS, (DEFAULT, USEMAX (last_updt_dt_tm))), RESOLVECONFLICT (DELETEROWEXISTS, (DEFAULT, IGNORE)), 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> Start replicat andy2
Now run an update on each of the three instances, as described above, and verify that all changes cascade to all the database instances.
If you have been following along from the start, here is what we have running on the various database servers:
Server pgorcl: Oracle 12.1.0.2, Golden Gate Version 12c, oracle sid ggtest
Running in ggsci:
GGSCI (pgorcl.localdomain) 37> info all
Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING EXTRACT RUNNING ANDY1 00:00:11 00:00:08 # Extract to ggdem11 (nggvm) EXTRACT RUNNING ANDYPUMP 00:00:00 00:00:00 # Pump process to ggdem11 (nggvm) EXTRACT RUNNING EXTORAPG 00:00:00 00:00:03 # Extract for postgres (pgsql) EXTRACT RUNNING INTEXTAN 00:00:12 00:00:07 # Integrated extract for ggdemo (ggvm) EXTRACT RUNNING MYSQLORA 00:00:00 00:00:02 # Extract for mysql (mysql1) EXTRACT RUNNING PUMPINT 00:00:00 00:00:05 # Integrated pump extract for ggdemo (ggvm) REPLICAT RUNNING ANDY2 00:00:00 00:07:33 # Replicat from ggdem11 (nggvm) REPLICAT RUNNING RPINTAN2 00:00:12 00:30:21 # Integrated Replicat from ggdemo (ggvm)
Server ggvm: Oracle 12.1.0.2, Golden Gate version 12c
Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING EXTRACT RUNNING INEXTAN2 00:00:04 00:00:02 #integrated extract for ggtest (pgorcl) EXTRACT RUNNING PUMPINT2 00:00:00 00:35:30 #Integrated pump process for ggtest (pgorcl) REPLICAT RUNNING REPINTAN 02:17:47 00:00:08 #Replication from ggtest (pgorcl)
Server nggvm: Oracle 11.2.0.1, Golden Gate version 12c
GGSCI (nggvm) 4> info all
Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING EXTRACT RUNNING ANDY2 00:00:16 00:00:15 # Extract to ggtest (pgorcl) EXTRACT RUNNING ANDYPUMP 00:22:40 00:16:27 # pump process to ggtest (pgorcl) REPLICAT RUNNING ANDY1 01:44:22 00:00:15 # Replication from ggtest (pgorcl)
Server pgsql, postgres Version 9.6, Golden Gate version 12c
GGSCI (pgsql.localdomain) 1> info all
Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING REPLICAT RUNNING REPORAPG 00:00:00 00:00:02 # replicat process from ggtest (pgorcl)
Server mysql, MySql Version 5.7, Golden Gate version 12c
GGSCI (mysql1.localdomain) 1> info all
Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING REPLICAT RUNNING ORAMYSQL 00:00:00 00:00:02 # Replicat from ggtest (pgorcl)
In this series of four blogs, we have covered the steps for setting up Oracle single master and multi-master replication, including multi-master replication among three master instances. We also discussed and configured one-way replication from Oracle to MySQL, and Oracle to PostgreSQL.
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.
