Single Table Replication – Same Schema

In this article, we will be looking at single table replication using Oracle Golden Gate where the schema name is the same on both source and target database.

• Create Sample Table

• Test Replication

• Stop Replication

Create Sample Table

Create Fox user on both proddb and devdb

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

Test Replication

Note: always make sure extract, pump, and replicate are running before testing

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