Dear Readers,
In this article we will see how to add new table to existing GoldenGate Oracle to Oracle replication.
Please check below link for GG installation for Oracle to Oracle
GoldenGate: Oracle to Oracle GoldenGate Unidirectional Replication
Now we will add new table to existing setup
On Source database :
Create table on Scott
|
1 2 3 4 5 6 7 8 9 |
SCOTT> create table test1 (tno number,tname varchar2(10)); Table created. ########Add primary key############ SCOTT> alter table test1 add primary key(tno); Table altered. |
On Target database :
Create table target where you want to replicate
Note : Incase DDL replication already enabled no need to create table
|
1 2 |
SQL> create table test1 (tno number,tname varchar2(10)); Table created. |
On Source GGSCI :
Check process running on GGSCI
|
1 2 3 4 5 |
GGSCI (gg.orcl.com) 28> info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING EXTRACT RUNNING DPINT 00:00:00 00:00:06 EXTRACT RUNNING EXTINT 00:00:09 00:00:06 |
Stop the Extract process(EXTINT)
|
1 2 3 4 |
GGSCI (gg.orcl.com) 29> stop extint Sending STOP request to EXTRACT EXTINT ... Request processed. |
Stop datapump extract process(DPINT)
|
1 2 3 4 |
GGSCI (gg.orcl.com) 30> stop dpint Sending STOP request to EXTRACT DPINT ... Request processed. |
Now edit the extint process
Add TABLE SCOTT.TEST1 line (if you want replicate entire schema the keep TABLE SCOTT.*)
|
1 2 3 4 5 6 7 8 9 10 |
GGSCI (gg.orcl.com) 31> eidt param extint EXTRACT EXTINT SETENV(ORACLE_HOME='/oraeng/app/oracle/product/12.1.0') SETENV(ORACLE_SID='ORCL') useridalias ggadmin_src TRANLOGOPTIONS INTEGRATEDPARAMS(MAX_SGA_SIZE 200) EXTTRAIL /oraeng/app/oracle/product/ogg_src/dirdat/lt TABLE SCOTT.dept; TABLE SCOTT.EMP; TABLE SCOTT.TEST1; |
Now edit dpint process
Add TABLE SCOTT.TEST1 line (if you want replicate entire schema the keep TABLE SCOTT.*)
|
1 2 3 4 5 6 7 8 |
GGSCI (gg.orcl.com) 32> edit param dpint EXTRACT DPINT RMTHOST 192.168.0.180,MGRPORT 7810 RMTTRAIL /oraeng/app/oracle/product/ogg_trg/dirdat/rt PASSTHRU TABLE SCOTT.EMP; TABLE SCOTT.dept; TABLE SCOTT.TEST1; |
On Target GGSCI:
Stop the replicate process(REPINT)
|
1 2 3 |
GGSCI (gg2.orcl.com) 1>stop repint Sending STOP request to EXTRACT MANAGER ... Request processed. |
Now edit the replicat process(REPINT)
Add the param MAP scott.test1,target scott.test1
|
1 2 3 4 5 6 7 8 9 10 11 |
GGSCI (gg2.orcl.com) 3> edit param repint REPLICAT REPINT SETENV(ORACLE_HOME='/oraeng/app/oracle/product/12.1.0') SETENV(ORACLE_SID='ÓRCL') useridalias ggadmin_trg DBOPTIONS INTEGRATEDPARAMS(parallelism 4) ASSUMETARGETDEFS MAP scott.dept,target scott.dept; MAP scott.emp,target scott.emp; MAP scott.test1,target scott.test1; |
On Source GGSCI :
Login with ggamdin credentials
|
1 2 |
GGSCI (gg.orcl.com) 33> dblogin useridalias ggadmin_src Successfully logged into database. |
Add Trandata on scott.test1
|
1 2 3 4 |
GGSCI (gg.orcl.com as ggadmin@orcl) 34> add trandata scott.test1 2019-06-20 19:55:53 INFO OGG-15132 Logging of supplemental redo data enabled for table SCOTT.TEST1. 2019-06-20 19:55:53 INFO OGG-15133 TRANDATA for scheduling columns has been added on table SCOTT.TEST1. 2019-06-20 19:55:53 INFO OGG-15135 TRANDATA for instantiation CSN has been added on table SCOTT.TEST1. |
Start extract process(EXTINT)
|
1 2 3 |
GGSCI (gg.orcl.com as ggadmin@orcl) 37> start extint Sending START request to MANAGER ... EXTRACT EXTINT starting |
Start datapump process(DPINT)
|
1 2 3 |
GGSCI (gg.orcl.com as ggadmin@orcl) 38> start dpint Sending START request to MANAGER ... EXTRACT DPINT starting |
Check process running or not
|
1 2 3 4 5 |
GGSCI (gg.orcl.com) 28> info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING EXTRACT RUNNING DPINT 00:00:00 00:00:06 EXTRACT RUNNING EXTINT 00:00:12 00:00:06 |
On Target GGSCI
Check processes
|
1 2 3 4 |
GGSCI (gg2.orcl.com) 4> info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING REPLICAT STOPPED REPINT 00:00:00 00:00:18 |
Start replicate process(REPINT)
|
1 2 3 |
GGSCI (gg2.orcl.com) 4> start repint Sending START request to MANAGER ... REPLICAT REPINT starting |
Check processes
|
1 2 3 4 |
GGSCI (gg2.orcl.com) 5> info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING REPLICAT RUNNING REPINT 00:00:00 00:00:00 |
On Source SCOTT user
Perform the transactions on test1 table
|
1 2 |
SCOTT> insert into test1 values(10,'AJAY'); 1 row created. |
|
1 2 |
SCOTT> insert into test1 values(20,'VINOD'); 1 row created. |
|
1 2 |
SCOTT> insert into test1 values(30,'ASHISH'); 1 row created. |
|
1 2 |
SCOTT> commit; Commit complete |
On Source GGSCI
Check stats of EXTINT and DPINT
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 |
GGSCI (gg.orcl.com as ggadmin@orcl) 41> stats extint Sending STATS request to EXTRACT EXTINT ... Start of Statistics at 2019-06-20 20:01:00. Output to /oraeng/app/oracle/product/ogg_src/dirdat/lt: Extracting from SCOTT.TEST1 to SCOTT.TEST1: *** Total statistics since 2019-06-20 20:00:56 *** Total inserts 3.00 Total updates 0.00 Total deletes 0.00 Total upserts 0.00 Total discards 0.00 Total operations 3.00 *** Daily statistics since 2019-06-20 20:00:56 *** Total inserts 3.00 Total updates 0.00 Total deletes 0.00 Total upserts 0.00 Total discards 0.00 Total operations 3.00 *** Hourly statistics since 2019-06-20 20:00:56 *** Total inserts 3.00 Total updates 0.00 Total deletes 0.00 Total upserts 0.00 Total discards 0.00 Total operations 3.00 *** Latest statistics since 2019-06-20 20:00:56 *** Total inserts 3.00 Total updates 0.00 Total deletes 0.00 Total upserts 0.00 Total discards 0.00 Total operations 3.00 End of Statistics. GGSCI (gg.orcl.com as ggadmin@orcl) 42> stats dpint Sending STATS request to EXTRACT DPINT ... Start of Statistics at 2019-06-20 20:01:09. Output to /oraeng/app/oracle/product/ogg_trg/dirdat/rt: Extracting from SCOTT.TEST1 to SCOTT.TEST1: *** Total statistics since 2019-06-20 20:00:58 *** Total inserts 3.00 Total updates 0.00 Total deletes 0.00 Total upserts 0.00 Total discards 0.00 Total operations 3.00 *** Daily statistics since 2019-06-20 20:00:58 *** Total inserts 3.00 Total updates 0.00 Total deletes 0.00 Total upserts 0.00 Total discards 0.00 Total operations 3.00 *** Hourly statistics since 2019-06-20 20:00:58 *** Total inserts 3.00 Total updates 0.00 Total deletes 0.00 Total upserts 0.00 Total discards 0.00 Total operations 3.00 *** Latest statistics since 2019-06-20 20:00:58 *** Total inserts 3.00 Total updates 0.00 Total deletes 0.00 Total upserts 0.00 Total discards 0.00 Total operations 3.00 End of Statistics. |
On Target scott user:
Check the records are replicated or not
|
1 2 3 4 5 6 7 |
SQL> select * from test1; TNO TNAME ----- ---------- 10 AJAY 20 VINOD 30 ASHISH |
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...
Ajay Kumar
Hi All
If anyone needs GG Training and also needs to configure GG for heterogeneous Databases then Please contact Ashish..
Details :Ashish Agarwal
Email id ashish agarwalag@gmail.com
YouTube Channel:
https://www.youtube.com/c/ashishagarwal_ggLinkedin:
https://www.linkedin.com/in/ashish-agarwal-a1399663/