GoldenGate Active-Active replication using CONFLICTRESOLUTION

	Source	Target
Database Version	11.2.0.4	11.2.0.4
OS Version	OEL 6 – 64 Bit	OEL 6 – 64 Bit
OGG HOME	/u01/GG/training/source	/u01/GG/training
GoldenGate user	ggs_admin	ggs_admin
OGG Core	11.2.1.0.0OGGBP_PLATFORMS_140304.2209_FBO
OGG Version	11.2.1.0.20

Database Prerequisites (On Both Source and Target)

Enable GoldenGate Replicaiton  at database level (Applicable for 11.2.0.4 and above Database Versions)

SQL> show parameter enable_goldengate_replication NAME                                 TYPE        VALUE   ------------------------------------ ----------- -------- enable_goldengate_replication        boolean     TRUE

Keep your database in Archivelog Mode

SQL> select LOG_MODE from v$database; LOG_MODE ------------ ARCHIVELOG

Enable Supplemental Logging for Primary Key, Unique Index, Foreign Keys and All Data.

Note: supplemental_log_data_min can be IMPLICIT or YES

SQL> SELECT supplemental_log_data_min,         supplemental_log_data_pk,         supplemental_log_data_ui,         supplemental_log_data_fk,         supplemental_log_data_all FROM v$database;  SUPPLEME SUP SUP SUP SUP -------- --- --- --- --- NO       NO  NO  NO  NO SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS; SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (UNIQUE) COLUMNS; SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (FOREIGN KEY) COLUMNS; SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS; SQL> SELECT supplemental_log_data_min,       supplemental_log_data_pk,       supplemental_log_data_ui,           supplemental_log_data_fk,           supplemental_log_data_all FROM v$database; SUPPLEME SUP SUP SUP SUP -------- --- --- --- --- YES      YES YES YES YES

Source:

GGSCI (ogg1.sukumar.com 1> edit param ./GLOBALS GGSCHEMA GGS_ADMIN CHECKPOINTTABLE GGS_ADMIN.CHKPTAB

Target:

GGSCI (ogg2.sukumar.com 1> edit param ./GLOBALS GGSCHEMA GGS_ADMIN  CHECKPOINTTABLE GGS_ADMIN.CHKPTAB

Create checkpoint table on both Source and Target

Source:

GGSCI (ogg1.sukumar.com 1> add checkpointtable ggs_admin.CHKPTAB Successfully created checkpoint table ggs_admin.chkptab.

Target:

GGSCI (ogg2.sukumar.com 1> add checkpointtable ggs_admin.CHKPTAB Successfully created checkpoint table ggs_admin.chkptab.

Create Parameter Files on Source:

Extract

GGSCI (ogg01.sukumar.com) 5> edit param ext1 EXTRACT ext1 SETENV (NLS_LANG=AMERICAN_AMERICA.UTF8) SETENV ORACLE_SID=SUKUMAR1 USERID ggs_admin, PASSWORD ggs_admin EXTTRAIL /u01/GG/training/source/dirdat/e1 TRANLOGOPTIONS DBLOGREADER TRANLOGOPTIONS EXCLUDEUSER GGS_ADMIN TABLE ABC.RESERVATION, GETBEFORECOLS( ON UPDATE KEYINCLUDING(TIME), ON DELETE KEYINCLUDING(TIME));

Pump

GGSCI (ogg01.sukumar.com) 6> edit param dpump EXTRACT dpump USERID ggs_admin, PASSWORD ggs_admin RMTHOST ogg02.sukumar.com, MGRPORT 4444 RMTTRAIL /u01/GG/training/dirdat/p1 TABLE ABC.*;

Replicat

GGSCI (ogg01.sukumar.com) 8> edit param rep2 REPLICAT rep2 SETENV (NLS_LANG=AMERICAN_AMERICA.UTF8) SETENV ORACLE_SID=SUKUMAR1 USERID ggs_admin PASSWORD ggs_admin DISCARDFILE /u01/GG/training/source/dirrpt/reptr.dsc, APPEND, MEGABYTES 512 ALLOWNOOPUPDATES ASSUMETARGETDEFS MAP ABC.RESERVATION, TARGET ABC.RESERVATION, GETBEFORECOLS         (          ON UPDATE KEYINCLUDING (TIME),          ON DELETE KEYINCLUDING (TIME)         ), & RESOLVECONFLICT (UPDATEROWEXISTS, (DEFAULT, USEMIN (TIME))), & RESOLVECONFLICT (INSERTROWEXISTS, (DEFAULT, USEMIN (TIME))), & RESOLVECONFLICT (DELETEROWEXISTS, (DEFAULT, OVERWRITE)), & RESOLVECONFLICT (UPDATEROWMISSING, (DEFAULT, OVERWRITE)), & RESOLVECONFLICT (DELETEROWMISSING, (DEFAULT, DISCARD));

Add extract, pump and Replicat Process to GoldenGate

GGSCI (ogg01.sukumar.com) 9> add extract ext1  tranlog, begin now GGSCI (ogg01.sukumar.com) 10> add exttrail /u01/GG/training/source/dirdat/e1, extract ext1 GGSCI (ogg01.sukumar.com) 11> add extract dpump, exttrailsource /u01/GG/training/source/dirdat/e1 GGSCI (ogg01.sukumar.com) 12>add rmttrail /u01/GG/training/dirdat/p1, extract dpump GGSCI (ogg01.sukumar.com) 13> add replicat rep2 exttrail /u01/GG/training/source/dirdat/p2, CHECKPOINTTABLE GGS_ADMIN.CHKPTAB

 Check the Process status

GGSCI (ogg01.sukumar.com) 14>  info all Program     Status      Group       Lag at Chkpt  Time Since Chkpt MANAGER     RUNNING EXTRACT     STOPPED     DPUMP       00:00:00      00:00:20 EXTRACT     STOPPED     EXT1        00:00:00      00:00:15 REPLICAT    STOPPED     REP2        00:00:00      00:00:11

Start all the process and check the status (All should be in running state)

GGSCI (ogg01.sukumar.com) 14> start * GGSCI (ogg01.sukumar.com) 15> info all Program     Status      Group       Lag at Chkpt  Time Since Chkpt MANAGER     RUNNING EXTRACT     RUNNING      DPUMP       00:00:00      00:00:03 EXTRACT     RUNNING      EXT1        00:00:00      00:00:00 REPLICAT    RUNNING      REP2        00:00:00      00:00:02

Add TRANDATA on to the table

GGSCI (ogg01.sukumar.com) 5> dblogin userid ggs_admin, password ggs_admin Successfully logged into database. GGSCI (ogg01.sukumar.com) 6> add schematrandata abc 2014-08-27 08:15:31  INFO    OGG-01788  SCHEMATRANDATA has been added on schema abc.

Create Parameter Files on Target:

Extract

GGSCI (ogg02.sukumar.com) 6> edit param ext2 EXTRACT ext2 SETENV (NLS_LANG=AMERICAN_AMERICA.UTF8) SETENV ORACLE_SID=SUKUMAR2 USERID ggs_admin, PASSWORD ggs_admin EXTTRAIL /u01/GG/training/source/dirdat/e2 TRANLOGOPTIONS DBLOGREADER TABLE ABC.RESERVATION, GETBEFORECOLS( ON UPDATE KEYINCLUDING(TIME), ON DELETE KEYINCLUDING(TIME));

 Pump

GGSCI (ogg02.sukumar.com) 7> edit param dpump EXTRACT dpump USERID ggs_admin, PASSWORD ggs_admin RMTHOST ogg01.sukumar.com, MGRPORT 8877 RMTTRAIL /u01/GG/training/source/dirdat/p2 TABLE ABC.*;

 Replicat

GGSCI (ogg02.sukumar.com) 5> edit param rep1 REPLICAT rep1 SETENV (NLS_LANG=AMERICAN_AMERICA.UTF8) SETENV ORACLE_SID=SUKUMAR2 USERID ggs_admin PASSWORD ggs_admin DISCARDFILE /u01/GG/training/dirrpt/reptr.dsc, APPEND, MEGABYTES 512 ALLOWNOOPUPDATES ASSUMETARGETDEFS MAP ABC.RESERVATION, TARGET ABC.RESERVATION, GETBEFORECOLS         (          ON UPDATE KEYINCLUDING (TIME),          ON DELETE KEYINCLUDING (TIME)         ), & RESOLVECONFLICT (UPDATEROWEXISTS, (DEFAULT, USEMIN (TIME))), & RESOLVECONFLICT (INSERTROWEXISTS, (DEFAULT, USEMIN (TIME))), & RESOLVECONFLICT (DELETEROWEXISTS, (DEFAULT, OVERWRITE)), & RESOLVECONFLICT (UPDATEROWMISSING, (DEFAULT, OVERWRITE)), & RESOLVECONFLICT (DELETEROWMISSING, (DEFAULT, DISCARD));

Add extract, pump and Replicat Process to GoldenGate

GGSCI (ogg02.sukumar.com) 6> add extract ext2  tranlog, begin now GGSCI (ogg02.sukumar.com) 7> add exttrail /u01/GG/training/dirdat/e2  extract ext2 GGSCI (ogg02.sukumar.com) 8> add extract dpump,  exttrailsource /u01/GG/training/source/dirdat/e2 GGSCI (ogg02.sukumar.com) 9> add rmttrail /u01/GG/training/source/dirdat/p2  extract dpump GGSCI (ogg02.sukumar.com) 10> add replicat rep1, exttrail /u01/GG/training/dirdat/p1, CHECKPOINTTABLE GGS_ADMIN.CHKPTAB

Check the Process status

GGSCI (ogg02.sukumar.com) 11> info all Program     Status      Group       Lag at Chkpt  Time Since Chkpt MANAGER     RUNNING EXTRACT     STOPPED     DPUMP       00:00:00      00:00:11 EXTRACT     STOPPED     EXT2        00:00:00      00:00:09 REPLICAT    STOPPED     REP1        00:00:00      00:00:06

Start all the process and check the status(All should be in running state)

GGSCI (ogg02.sukumar.com) 11> start * GGSCI (ogg02.sukumar.com) 11> info all Program     Status      Group       Lag at Chkpt  Time Since Chkpt MANAGER     RUNNING EXTRACT     RUNNING      DPUMP       00:00:00      00:00:04 EXTRACT     RUNNING      EXT2        00:00:00      00:00:02 REPLICAT    RUNNING      REP1        00:00:00      00:00:00

Add TRANDATA to the table

GGSCI (ogg02.sukumar.com) 5> dblogin userid ggs_admin, password ggs_admin Successfully logged into database. GGSCI (ogg02.sukumar.com) 6> add schematrandata abc 2014-08-27 08:15:31  INFO    OGG-01788  SCHEMATRANDATA has been added on schema abc.

 Soruce Table

SQL> desc ABC.RESERVATION  Name              Null?    Type  ----------------- -------- ------------  ID                NOT NULL NUMBER  NAME                       VARCHAR2(20)  TIME                       TIMESTAMP(6)

Target Table

SQL> desc ABC.RESERVATION  Name              Null?    Type  ----------------- -------- ------------  ID                NOT NULL NUMBER  NAME                       VARCHAR2(20)  TIME                       TIMESTAMP(6)

Let’s test with Inserts from both the Nodes.

SQL> insert into reservation values (1,'ogg1',sysdate); 1 row created. SQL> commit; Commit complete. SQL> insert into reservation values (2,'ogg2', sysdate); 1 row created. SQL> commit; Commit complete. SQL> select * from reservation;         ID NAME                 TIME ---------- -------------------- ------------------------------          2 ogg2                 27-AUG-14 09.38.09.000000 AM          1 ogg1                 27-AUG-14 09.34.02.000000 AM SQL> select * from reservation;         ID NAME                 TIME ---------- -------------------- ------------------------------          1 ogg1                 27-AUG-14 09.34.02.000000 AM          2 ogg2                 27-AUG-14 09.38.09.000000 AM

Now test with the Update statements,

As we have used the Conflict Resolution as USEMIN reference to TIME column, which ever record have the minimum time will be updated to the table.

As simple as , First come First Serve of COMMIT.

SQL> update reservation set NAME='UPDATED ON OGG1',TIME=sysdate where id=1; 1 row updated. SQL> select * from reservation where id=1;         ID NAME                 TIME ---------- -------------------- ------------------------------          1 UPDATED ON OGG1      27-AUG-14 09.38.14.000000 AM SQL> update reservation set NAME='UPDATED ON OGG2', TIME=sysdate where id=1; 1 row updated. SQL> select * from reservation where id=1;         ID NAME                 TIME ---------- -------------------- ------------------------------          1 UPDATED ON OGG2      27-AUG-14 09.42.21.000000 AM

We dint commit the Transaction yet on any of the database, hence it is showing the record with two different times.

but I am commiting the record on ogg2 first then ogg1

SQL> update reservation set NAME='UPDATED ON OGG2', TIME=sysdate where id=1; 1 row updated. SQL> Commit; Commit complete. SQL> update reservation set NAME='UPDATED ON OGG1',TIME=sysdate where id=1; 1 row updated. SQL> Commit; Commit complete.

In General Latest update should be effective in the database, As we are using USEMIN to the Time column, First Commit will always be updated when the transaction at the other end is open/ not committed.

SQL> select * from reservation;         ID NAME                 TIME ---------- -------------------- ------------------------------          2 ogg2                 27-AUG-14 09.38.09.000000 AM          1 UPDATED ON OGG2      27-AUG-14 09.42.21.000000 AM SQL> select * from reservation;         ID NAME                 TIME ---------- -------------------- ------------------------------          1 UPDATED ON OGG2      27-AUG-14 09.42.21.000000 AM          2 ogg2                 27-AUG-14 09.38.09.000000 AM

Now Let’s work with Delete Statements.

Deleting the record from ogg1, keep the transaction open. Then, delete the record from ogg2.

Commit the transaction from ogg1, deletes the record on ogg2 also. If you commit on ogg2 now, in general it should throw no data found error. As we have used DELETEROWMISSING default ignore, transaction will commit successfully even though there is no record in the database.

SQL> delete from reservation where id=1; 1 row deleted. SQL> delete from reservation where id=1; 1 row deleted. SQL> commit; Commit complete. SQL> commit; Commit complete.