GoldenGate How to handle replication with extra columns on source and target tables

Case A – Additional Column on Target Table

In this example the JOBS table on the target database oradb2 has an additional IDENTITY COLUMN called ID

SQL> conn demo/oracle@oradb1
Connected.


SQL> desc jobs
Name Null? Type
----------------------------------------- -------- ----------------------------
JOB_ID NOT NULL VARCHAR2(10)
JOB_TITLE NOT NULL VARCHAR2(35)
MIN_SALARY NUMBER(6)
MAX_SALARY NUMBER(6)

SQL> conn demo/oracle@oradb2
Connected.


SQL> alter table jobs 
add (ID NUMBER GENERATED ALWAYS AS IDENTITY);

Table altered.

SQL> desc jobs
Name Null? Type
----------------------------------------- -------- ----------------------------
JOB_ID NOT NULL VARCHAR2(10)
JOB_TITLE NOT NULL VARCHAR2(35)
MIN_SALARY NUMBER(6)
MAX_SALARY NUMBER(6)
ID NOT NULL NUMBER

Note the clause used in the Replicat

COLMAP(USEDEFAULTS, ID=@COLSTAT(MISSING));

GGSCI (rac01.localdomain as oggadmin@oradb2) 13> view params rep1
 REPLICAT rep1
 USERIDALIAS oggadmin_oradb2
 MAP DEMO.JOBS, TARGET DEMO.JOBS, COLMAP(USEDEFAULTS, ID=@COLSTAT(MISSING));
 MAP DEMO.COUNTRIES, TARGET DEMO.COUNTRIES;
 MAP DEMO.LOCATIONS, TARGET DEMO.LOCATIONS;
 MAP DEMO.EMPLOYEES, TARGET DEMO.EMPLOYEES;
 MAP DEMO.JOB_HISTORY, TARGET DEMO.JOB_HISTORY;

Test GoldenGate replication – note ID column on the target table is populated automatically

SQL> insert into jobs 
    values
    ('DBA','Oracle DBA',5000,7500);

 1 row created.

 SQL> commit;
 Commit complete.

 SQL> conn demo/oracle@oradb2
 Connected.

 SQL> select * from jobs where job_id='DBA';

 JOB_ID       JOB_TITLE                   MIN_SALARY MAX_SALARY         ID
 
 DBA       Oracle DBA                    5000   7500         21

Case B – Additional Column on Source Table

We now drop the ID column on the target database table and add the additional IDENTITY COLUMN on the source table instead.

[oracle@rac01 ~]$ sqlplus demo/oracle@oradb2

 SQL> alter table jobs drop column id;

 Table altered.

 [oracle@rac01 ~]$ sqlplus demo/oracle@oradb1

 SQL> alter table jobs 
  add (ID NUMBER GENERATED BY DEFAULT AS IDENTITY);

 Table altered.

Note the clause in the Extract to handle the additional column on the source table – COLSEXCEPT (ID)

GGSCI (rac01.localdomain as oggadmin@oradb1) 46> view params ext1
 EXTRACT ext1
 USERIDALIAS oggadmin_oradb1
 EXTTRAIL ./dirdat/xx
 TABLE DEMO.JOBS, COLSEXCEPT(ID);
 TABLE DEMO.*;

Test GoldenGate replication – note that the source JOBS table ID column is now populated automatically and this column is not present in the target JOBS table.

SQL> insert into jobs 
    (job_id,job_title,min_salary,max_salary)
     values
   ('RSH','Market Research',10000,11000);

 1 row created.

 SQL> commit;
 Commit complete.

 SQL> select * from jobs where job_id='RSH';

 JOB_ID       JOB_TITLE                   MIN_SALARY MAX_SALARY         ID
 
 RSH       Market Research              10000      11000         41

 SQL> conn demo/oracle@oradb2
 Connected.

 SQL> select * from jobs where job_id='RSH';

 JOB_ID       JOB_TITLE                   MIN_SALARY MAX_SALARY
 
 RSH       Market Research              10000      11000