Oracle Golden Gate Single Table Replication
Configure GoldenGate for specific table replication.
In this article, we will be looking at single table replication using Oracle Golden Gate. The replication will be setup for a table that belongs to same schema and different schemas.
Oracle Golden Gate Single Table Replication - Same Schema
Oracle Golden Gate Single Table Replication - Different Schema
Oracle Golden Gate Single Table Replication - Same Schema
Let's first look at single table replication using Oracle Golden Gate where the schema name is the same on both source and target database.
We assume that on both source and target there is FOX schema already existing
Create user fox identified by fox;
Grant connect, resource to fox;
Alter user fox quota unlimited on users;
Create TAB1 table on both proddb and devdb
Conn fox/fox
CREATE TABLE tab1
(
c1 NUMBER PRIMARY KEY,
c2 VARCHAR2(30),
c3 NUMBER,
c4 NUMBER
);
Connect to database via Golden Gate
On proddb:
==========
cd $GG_HOME
./ggsci
GGSCI> dblogin userid ogg, password ogg
Successfully logged into database.
Add table level supplemental logging via Golden Gate
On GGPROD:
==========
GGSCI> add trandata fox.tab1
Logging of supplemental redo data enabled for table FOX.TAB1.
TRANDATA for scheduling columns has been added on table 'FOX.TAB1'.
TRANDATA for instantiation CSN has been added on table 'FOX.TAB1'.
Create GG Extract Process
On GGPROD:
==========
GGSCI> ADD EXTRACT ext1, INTEGRATED TRANLOG, BEGIN NOW
EXTRACT (Integrated) added.
GGSCI> register extract ext1 database
--> execute above only in integrated capture mode
Create local trail file for extract process
GGSCI> add exttrail /u01/app/oracle/product/gg/dirdat/ex, extract ext1
Create parameter file for extract process
GGSCI> edit param ext1
EXTRACT ext1
USERID ogg, PASSWORD ogg
EXTTRAIL /u01/app/oracle/product/gg/dirdat/ex
TABLE fox.tab1;
Create GG DP Process
GGSCI> Add extract dp1, EXTTRAILSOURCE /u01/app/oracle/product/gg/dirdat/ex
Create remote trail file for extract process
GGSCI> Add rmttrail /u01/app/oracle/product/gg/dirdat/rt, extract dp1
Create parameter file for data pump process
GGSCI> edit param dp1
EXTRACT dp1
USERID ogg, PASSWORD ogg
RMTHOST ggdev, MGRPORT 7809
RMTTRAIL /u01/app/oracle/product/gg/dirdat/rt
TABLE fox.tab1;
Create GG Replicate on target
On GGDEV:
=========
GGSCI> dblogin userid ogg, password ogg
GGSCI> add replicat rep1, integrated exttrail /u01/app/oracle/product/gg/dirdat/rt
Create parameter file for replicat on target
GGSCI> edit param rep1
REPLICAT rep1
USERID ogg, PASSWORD ogg
ASSUMETARGETDEFS
MAP fox.tab1 TARGET fox.tab1;
Start manager, extract and data pump process on source
On GGPROD:
==========
./ggsci
GGSCI> start mgr
GGSCI> start ext1
GGSCI> start dp1
Start manager and replicat on target
On GGDEV:
=========
./ggsci
GGSCI> start mgr
GGSCI> start rep1
Check all the processes in case of any error
GGSCI> view report ext1
GGSCI> view report dp1
Note: always make sure extract, pump, and replicate are running before testing
Now it's time to test, insert rows in source table
On PRODDB:
==========
INSERT INTO tab1 VALUES (1,'Alpha',10,100);
INSERT INTO tab1 VALUES (2,'Beta',20,200);
commit;
Check on target if the table got rows inserted or not. Insert some more rows on source
On PRODDB:
==========
INSERT INTO tab1 VALUES (3,'Gamma',30,300);
INSERT INTO tab1 VALUES (4,'Tang',40,400);
Commit;
Stop Replication: Always stop extract and pump first
On GGPROD:
==========
GGSCI> stop ext1;
-- wait for some time before stopping pump process
GGSCI> stop dp1;
-- wait for some time before stopping replicat process
Proceed with stopping of replicate
On GGDEV:
=========
GGSCI> stop rep1
Oracle Golden Gate Single Table Replication – Different Schema
Let's look at single table replication using Oracle Golden Gate where the schema name is different on source and target but table structure remains the same.
Create Tom user on devdb
Create user tom identified by tom;
Grant connect, resource to tom;
Alter user tom quota unlimited on users;
Create TAB2 table on both proddb and devdb
On Proddb:
==========
Conn fox/fox
CREATE TABLE tab2(
c1 NUMBER PRIMARY KEY,
c2 VARCHAR2(30),
c3 NUMBER,
c4 NUMBER);
On Devdb:
=========
Conn tom/tom
CREATE TABLE tab2(
c1 NUMBER PRIMARY KEY,
c2 VARCHAR2(30),
c3 NUMBER,
c4 NUMBER);
Connect to database via Golden Gate
On proddb:
==========
cd $GG_HOME./ggsci
GGSCI> dblogin userid ogg, password ogg
Successfully logged into database.
Add table level supplemental logging via Golden Gate
On GGPROD:
==========
GGSCI> add trandata fox.tab2
Logging of supplemental redo data enabled for table FOX.TAB2.TRANDATA for scheduling columns has been added on table 'FOX.TAB2'.TRANDATA for instantiation CSN has been added on table 'FOX.TAB2'.
Create GG Extract Process
On GGPROD:
==========
GGSCI> ADD EXTRACT ext2, INTEGRATED TRANLOG, BEGIN NOWEXTRACT (Integrated) added.
GGSCI> register extract ext2 database
Create local trail file for extract process
GGSCI> add exttrail /u01/app/oracle/product/gg/dirdat/e2, extract ext2
Create parameter file for extract process
GGSCI> edit param ext2
EXTRACT ext2
USERID ogg, PASSWORD ogg
EXTTRAIL /u01/app/oracle/product/gg/dirdat/e2
TABLE fox.tab2;
Create GG DP Process
GGSCI> Add extract dp2, EXTTRAILSOURCE /u01/app/oracle/product/gg/dirdat/e2
Create remote trail file for extract process
GGSCI> Add rmttrail /u01/app/oracle/product/gg/dirdat/r2, extract dp2
Create parameter file for data pump process
GGSCI> edit param dp2
EXTRACT dp2
USERID ogg, PASSWORD ogg
RMTHOST ggdev, MGRPORT 7809RMTTRAIL /u01/app/oracle/product/gg/dirdat/r2
TABLE fox.tab2;
Create GG Replicate on target
On GGDEV:
=========
GGSCI> dblogin userid ogg, password ogg
GGSCI> add replicat rep2, integrated exttrail /u01/app/oracle/product/gg/dirdat/r2
Create parameter file for replicat on target
GGSCI> edit param rep2
REPLICAT rep2
USERID ogg, PASSWORD ogg
ASSUMETARGETDEFSMAP fox.tab2 TARGET tom.tab2;
Start manager, extract and data pump process on source
On GGPROD:
==========
./ggsci
GGSCI> start mgr
GGSCI> start ext2
GGSCI> start dp2
Start manager and replicat on target
On GGDEV:
=========
./ggsci
GGSCI> start mgr
GGSCI> start rep2
Check all the processes in case of any error
GGSCI> view report ext2
GGSCI> view report dp2
Note: always make sure extract, pump and replicate are running before testing.
Insert rows in source table
On PRODDB:
==========
INSERT INTO tab2 VALUES (1,'Alpha',10,100);INSERT INTO tab2 VALUES (2,'Beta',20,200);
commit;
Check on target if the table got rows inserted or not. Insert some more rows on source
On PRODDB:
==========
INSERT INTO tab2 VALUES (3,'Gamma',30,300);INSERT INTO tab2 VALUES (4,'Tang',40,400);
Commit;
Stop Replication: Always stop extract and pump first
On GGPROD:
==========
GGSCI> stop ext2;
-- wait for some time before stopping pump process
GGSCI> stop dp2;
-- wait for some time before stopping replicat process
Proceed with stopping of replicate
On GGDEV:
=========
GGSCI> stop rep2