Wednesday 27 August 2014

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.