• Arun Kumar

Single Table Replication – Different Schema

In this article we will be looking at single table replication using Oracle Golden Gate where the schema name is different on source and target but table structure remains same.


Create Sample Table


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 NOW
EXTRACT (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 7809
RMTTRAIL /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
ASSUMETARGETDEFS
MAP 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


Test Replication


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


Recent Posts

See All

Install Oracle 12c Golden Gate on Oracle Linux

In this article we will look at the steps to install oracle 12c Golden Gate on Oracle Enterprise Linux 6.5. The steps involved are: Virtual Machine Setup Install Oracle 12c Database Install Oracle 12c

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 same on both source and target database. Create Sample Table Create Fox user on both pr

Configure Golden Gate Initial Load and Change Sync

In this article we will configure initial load along with change sync inside Oracle Golden Gate. What is initial load? Initial load is a process of extracting data records from the source database and

  • Facebook
  • Twitter
  • YouTube
  • Instagram

© 2020 by DBA Genesis