1. Home
  2. Knowledge Base
  3. GoldenGate
  4. GoldenGate How to handle replication with extra columns on source and target tables

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
Updated on June 2, 2021

Was this article helpful?

Related Articles

Leave a Comment