Oracle 11.2.0.1 Replication Using GoldenGate

posted May 24, 2017, 10:45 AM by

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.

Share with your networkTweet about this on TwitterShare on LinkedInShare on FacebookDigg thisEmail this to someone

Leave a Reply

Your email address will not be published. Required fields are marked *

WANT TO LEARN MORE?

Share with your networkTweet about this on TwitterShare on LinkedInShare on FacebookDigg thisEmail this to someone