Dear Readers,
In this article we will see how to replicate only particular columns to the target table
On Source database :
Take Dept table and add two more columns
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
SQL> select * from dept; DEPTNO DNAME LOC ---------- -------------- ------------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON SQL> alter table dept add Mobile number(10); Table altered. SQL> alter table dept add Pin number(6); Table altered. SQL> select * from dept; DEPTNO DNAME LOC MOBILE PIN ---------- -------------- ------------- ---------- ---------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON |
Now on source dept have 5 columns(DEPTNO,DNAME,LOC,MOBILE,PIN)
On Target database we should add same two columns if DDL is not enabled.
On Target database :
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
SQL> select * from dept; DEPTNO DNAME LOC ---------- -------------- ------------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON SQL> alter table dept add Mobile number(10); Table altered. SQL> alter table dept add Pin number(6); Table altered. SQL> select * from dept; DEPTNO DNAME LOC MOBILE PIN ---------- -------------- ------------- ---------- ---------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON |
Now We will replicate only DEPTNO,DNMAE,LOC column values to the target
That means we need exclude MOBILE and PIN Column values
On Source GGSCI:
Connect to ggsci
|
1 2 3 4 5 6 7 8 |
[oracle@gg ogg_src]$ ./ggsci Oracle GoldenGate Command Interpreter for Oracle Version 19.1.0.0.0 OGGCORE_19.1.0.0.0_PLATFORMS_190508.1447_FBO Linux, x64, 64bit (optimized), Oracle 12c on May 9 2019 06:21:59 Operating system character set identified as UTF-8. Copyright (C) 1995, 2019, Oracle and/or its affiliates. All rights reserved. |
Check process
|
1 2 3 4 5 |
GGSCI (gg.orcl.com) 1> info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING EXTRACT RUNNING DPINT 00:00:00 00:00:02 EXTRACT RUNNING EXTINT 00:00:05 00:00:02 |
Stop the extint process
|
1 2 3 |
GGSCI (gg.orcl.com) 2> stop extint Sending STOP request to EXTRACT EXTINT ... Request processed. |
Edit the param extint file
Add TABLE ajay.DEPT COLSEXCEPT(MOBILE,PIN);
|
1 2 3 4 5 6 7 8 9 10 |
GGSCI (gg.orcl.com) 3> edit param extint EXTRACT EXTINT SETENV(ORACLE_SID='ORCL') SETENV(ORACLE_HOME='/oraeng/app/oracle/product/12.1.0') useridalias ggadmin_src TRANLOGOPTIONS INTEGRATEDPARAMS(MAX_SGA_SIZE 100) EXTTRAIL /u01/app/oracle/product/ogg_src/dirdat/lt --DDL include all TABLE ajay.DEPT COLSEXCEPT(MOBILE,PIN); TABLE AJAY.*; |
Or
we can use COLS keyword to specify what columns to replicate.
|
1 2 3 4 5 6 7 8 9 10 |
GGSCI (gg.orcl.com) 3> edit param extint EXTRACT EXTINT SETENV(ORACLE_SID='ORCL') SETENV(ORACLE_HOME='/oraeng/app/oracle/product/12.1.0') useridalias ggadmin_src TRANLOGOPTIONS INTEGRATEDPARAMS(MAX_SGA_SIZE 100) EXTTRAIL /u01/app/oracle/product/ogg_src/dirdat/lt --DDL include all TABLE ajay.DEPT COLS(DEPTNO,DNAME,LOC); TABLE AJAY.*; |
Start the extint process
|
1 2 3 4 5 6 7 8 |
GGSCI (gg.orcl.com) 5> start extint Sending START request to MANAGER ... EXTRACT EXTINT starting GGSCI (gg.orcl.com) 6> info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING EXTRACT RUNNING DPINT 00:00:00 00:00:01 EXTRACT RUNNING EXTINT 00:05:01 00:00:01 |
Now we can insert records into table on Source database.
Insert records
|
1 2 3 4 |
SQL> insert into dept values(50,'SALES','FRANCE',4845845,652656); 1 row created. SQL> commit; Commit complete. |
|
1 2 3 4 5 6 7 8 |
SQL> select * from dept; DEPTNO DNAME LOC MOBILE PIN ---------- -------------- ------------- ---------- ---------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON 50 SALES FRANCE 4845845 652656 |
On Target :
Check records
|
1 2 3 4 5 6 7 8 |
SQL> select * from dept; DEPTNO DNAME LOC MOBILE PIN ---------- -------------- ------------- ---------- ---------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON 50 SALES FRANCE |
Yes it replicate only first three columns.
Thank you for giving your valuable time to read the above information. Please click here to subscribe for further updates
KTEXPERTS is always active on below social media platforms.
Facebook : https://www.facebook.com/ktexperts/
LinkedIn : https://www.linkedin.com/company/ktexperts/
Twitter : https://twitter.com/ktexpertsadmin
YouTube : https://www.youtube.com/c/ktexperts
Loading...
