GoldenGate

GoldenGate 12c Bi-Directional Replication with Integrated Capture

This article explains the bidirectional replication using Oracle GoldenGate, and adding basic conflict detection and resolution.

Environment:

Oracle Version:  12.1.0.2

GoldenGate Version: 12c

Source Server=alphahost    SID=aplhadb

Target Server=betahost       SID=betadb

1.   Initial configurations on both hosts  (alpha & beta):

n  Run the above command on both servers.

./ggsci

GGSCI >create subdirs

n  Configure PORT setting on both server.

GGSCI @alphahost > edit params mgr

port 7809

GGSCI @alphahost> start mgr

GGSCI @betahost > edit params mgr

port 7809

GGSCI @betahost > start mgr

n  Edit GLOBALS file on all servers. (Inside $GGH dir NOT params dir)

GGSCI@alphahost> Edit params ./GLOBALS

CHECKPOINTTABLE ggcheckpoint

GGSCI@betahost> Edit params ./GLOBALS

CHECKPOINTTABLE ggcheckpoint

è Ensure tnanames.ora on both servers.

n  Edit 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/beta/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 john; -> Optional to make debugging much easier.

n  Now create checkpoint table. Since it is bidirectional replication, we need to create in both machines (as both act as Targets)

GGSCI @alphahost > dblogin userid ggadmin

Password:

Successfully logged into database.

GGSCI@alphahost > add checkpointtable ggadmin.ggcheckpoint

GGSCI @betahost > dblogin userid ggadmin

Password:

Successfully logged into database.

GGSCI@betahost > add checkpointtable ggadmin.ggcheckpoint

n  Only In the source environment, add the transaction data:

GGSCI@alphahost > add trandata john.* cols *

n  Find the current scn from sqlplus:

SQLPLUS> select current_scn from v$database;

n  Export the data from Alpha and scp to Beta :

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

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

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

n  Import the dump into Beta:

sqlplus / as sysdba

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

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

2.   Configure Extract, Pump process Alphahost:

Extract:

GGSCI@alphahost > dblogin userid ggadmin

Password:

Successfully logged into database.

GGSCI@alphahost > register extract insrc database

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

GGSCI@alphahost > edit params insrc

extract insrc

 SETENV(ORACLE_SID="alphadb")

 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 john.*;

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

Note: LOGALLSUPCOLS causes Extract to do the following with these supplementally logged columns:

• Automatically includes in the trail record the before image for UPDATE operations.
• Automatically includes in the trail record the before image of all supplementally logged columns for both UPDATE and DELETE operations.

Pump:

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

GGSCI@alphahost > edit params pumpint

EXTRACT pumpint

 RMTHOST betahost, MGRPORT 7809

 RMTTRAIL ./dirdat/pn

 TABLE john.*;

GGSCI@alphahost > add rmttrail ./dirdat/pn, extract pumpint, megabytes 10

GGSCI@alphahost > start extract intsrc

GGSCI@alphahost > start extract pumpint

Note: 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.

3.   Configure Integrated Replicat on Beta

Connect to the destination database server and start ggsci:

./ggsci

GGSCI@betahost> edit params repintan

 replicat repintan

 SETENV(ORACLE_SID='betadb')

 DBOPTIONS INTEGRATEDPARAMS(parallelism 4)

 AssumeTargetDefs

 DiscardFile ./dirrpt/rpdw.dsc, purge

 USERID ggadmin, PASSWORD ggadmin

 MAP john.*, target john.*;

GGSCI@betahost> dblogin userid ggadmin

password:

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

GGSCI@betahost> start replicat repintan

Betahost:

GGSCI@betahost> info all

Program     Status     Group       Lag at Chkpt Time Since Chkpt

MANAGER     RUNNING

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

Alphahost: 

GGSCI@alphahost> 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 a dml statement to verify that replication is working.

SQLPLUJS@ALPHA> select count(1) from john.emp;

COUNT(1)

 ----------

 10

SQLPLUS@BETA> select count(1) from john.emp;

COUNT(1)

 ----------

 10

SQLPLUJS@ALPHA>

delete from john.emp where empno=7900;

1 row deleted.

SQLPLUJS@ALPHA> commit;

 Commit complete.

SQLPLUS@BETA> select count(1) from john.emp;

COUNT(1)

 ----------

 9

One way replication is now working.

4.   Set-up Bi-directional replication

On source database (Alpha):

For each table add last updated timestamp:

SQLPLUS> alter table john.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 john.updt_emp

before update on john.emp

for each row

begin

:new.last_updt_dt_tm:=systimestamp;

end;

/

Note :  GoldenGate will automatically suppress triggers for replicated objects.

On both source and target database:

Create the below Exception Handling Table:

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 (Beta):

Extract:

GGGSCI@betahost> edit params inextan2

extract inextan2

 USERID ggadmin, PASSWORD ggadmin

 SETENV(ORACLE_SID="betadb")

 SETENV(ORACLE_HOME="/u01/app/oracle/product/12.1.0/dbhome_1")

 TRANLOGOPTIONS IntegratedParams (max_sga_size 256)

 EXTTRAIL ./dirdat/in

 LOGALLSUPCOLS

 UPDATERECORDFORMAT COMPACT

 DDL INCLUDE MAPPED OBJNAME JOHN.* 

 TABLE JOHN.*;

Note: UPDATERECORDFORMAT parameter cause Extract to combine the before and after images of an UPDATE operation into a single record in the trail.

UPDATERECORDFORMAT [FULL | COMPACT]

FULL

Generates one trail record that contains the before and after images of an UPDATE, where the before image includes all of the columns that are available in the transaction record for both the before and after images. This is the default. When viewed in the Logdump utility, this record appears as GGSUnifiedUpdate.

COMPACT

Generates one trail record that contains the before and after images of an UPDATE, where the before image includes all of the columns that are available in the transaction record, but the after image is limited to the primary key columns and the columns that were modified in the UPDATE. UPDATERECORDFORMAT COMPACT is recommended for configurations that include an integrated Replicat.

GGSCI@betahost> dblogin userid ggadmin

 Passsword:

GGSCI@betahost> add trandata john.* cols *

GGSCI@betahost> register extract inextan2 database

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

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

Pump:

GGSCI@betahost> add extract pumpint2, exttrailsource ./dirdat/in

GGSCI@betahost> edit params pumpint2

EXTRACT pumpint2

 RMTHOST alphahost, MGRPORT 7809

 RMTTRAIL ./dirdat/pn

 TABLE JOHN.*;

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

GGSCI@betahost> start extract pumpint2

GGSCI@betahost> start extract inextan2

On source server (Alpha) add the replicat:

GGSCI@alphahost> edit params rpintan2

replicat rpintan2

 SETENV(ORACLE_SID='alphadb')

 DBOPTIONS INTEGRATEDPARAMS(parallelism 4)

 AssumeTargetDefs

 DiscardFile ./dirrpt/rpdw.dsc, purge

 USERID ggadmin, PASSWORD ggadmin

 DDLERROR DEFAULT DISCARD IGNOREMISSINGOBJECTS

 REPERROR (DEFAULT, EXCEPTION)

 MAP JOHN.*, target JOHN.*

 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@alphahost> dblogin userid ggadmin

 password

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

GGSCI@alphahost> start replicat rpintan2

On target server, add basic conflict detection and resolution:

edit params REPINTAN

replicat repintan

 SETENV(ORACLE_SID='betadb')

 DBOPTIONS INTEGRATEDPARAMS(parallelism 4)

 AssumeTargetDefs

 DiscardFile ./dirrpt/rpdw.dsc, purge

 USERID ggadmin, PASSWORD ggadmin

 DDLERROR DEFAULT DISCARD IGNOREMISSINGOBJECTS

 REPERROR (DEFAULT, EXCEPTION)

 MAP JOHN.*, target JOHN.*

 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@betahost> stop replicat repintan

GGSCI@betahost> start replicat repintan

Note:

Collision Detection and Resolution (CDR):

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

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

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.

Gate Configuration and troubleshooting

Setup the goldengate:

1. Unzip the goldengate setup

2. Make a folder on the drive without space and copy the goldengate setup.

2. Go to CMD from location of goldengate binaries.

3. GGSCI program.

4. CREATE SUBDIRS

5. EXIT

To specify a custom Manager name: (default name is GGSMGR)

1. Run GGSCI.

2. EDIT PARAMS ./GLOBALS

3. add the following line

   MGRSERVNAME <name>

4. file is saved automatically with the name GLOBALS, without a file extension.

Process to setup extract and replicate process:

1. Setup MGMT process of both the database.

2. Start MGMT process of both database.

3. Setup first Extract process. ( Note: Do not start the extract process before Setup the replicate)

4. Setup replicate process

5. Start the extract process.

6. Start the replicate process.

Starting steps of the Golden Gate:

1. Start Source database MGMT process

2. Start Target database MGMT Process.

3. START EXT1 PROCESS

4. START REP1 PROCESS

Note: If you start the ext1  process without creating the rep1 process it will cause error in creating rep1 process.

Command to check process are running and check error in Golden Gate:

1. Info <process name>
   eg info mgr , info ext1 , info rep1
2. check all process : info all
3. view report <process name>
    e.g view report ext1, view report mgr

Issues in Golden Gate:

ISSUE:  2013-01-23 15:12:37  ERROR   OGG-01224  TCP/IP error 10061 (No connection could be made because the target machine actively refused it.); retries exceeded.

Solution :

1. check both database server golden gate manager process is start

2. check the firwal setting of both the databases.

3. If use different port in extract then specify the following on target server in Mgmt process parameter file:

     dynamicportlist 7810-7820

4. Check the ping command for both the source and target machine.

5. Check the entries in hosts file of both source and target name.

ISSUE

Target server on start of replicate process:

2013-01-23 15:38:45  ERROR   OGG-00446  Missing filename opening checkpoint file

Solution :

Added the following line in above of replication process parameter file.
REPLICAT REP1 

ISSUE : 2013-01-23 16:00:58  ERROR   OGG-00446  Checkpoint table ggs_owner.checkpoint do

es not exist.  Please create the table or recreate the REP1 group using the correct table.

Solution:

1.  dblogin userid OGG, password OGG

2. Delete  CHECKPOINTTABLE GGS_OWNER.CHKPTAB
3. ADD CHECKPOINTTABLE GGS_OWNER.CHKPTAB

ISSUE: 2013-01-22 20:24:37  ERROR   OGG-00403  There can be only one DDL filtering statement. If DDL filter is long, use ampersand (&) sign to continue it on another line.

Solution:

check the Extract process and replicate process , DDL line having causing some error.Please correct the syntax of DDL line in parameter file.

ISSUE:2013-01-23 15:05:46 ERROR OGG-00446 Could not find archived log for sequence 33 thread 1 under default destinations SQL <SELECT name FROM v$archived_log WHERE sequence# = :ora_seq_no AND thread# = :ora_thread AND resetlogs_id = :ora_resetlog_id AND archived = 'YES' AND deleted = 'NO' AND name not like '+%' AND standby_dest = 'NO' >, error retrieving redo file name for sequence 33, archived = 1, use_alternate = 0Not able to establish initial position for begin time 2013-01-21 15:49:55.

Solution:

alter extract <name> begin now

Issue:

ERROR OGG-01224 Address already in use.

Solution:

Port is already used by another process, check on linux 

ps -ef|grep mgr

Issue:

 OGG-01224 TCP/IP error 101

Solution:

checked firwall setting.

Reference:

http://dbaworks-sunny.blogspot.in/2013/01/golden-gate-in-oracle.html
oracle-base.com