Oracle Golden Gate Incremental Refresh between source and target database / schema

by Snehashish Ghosh

Scenario


 After doing initial data loading into the source and target schema/database it is required to configure Oracle Golden Gate so that source schema/database is automatically remains in sync with target schema/database.

Solution


 Configure Extract Process on the Source System


 1.     Add Supplemental log data

Adding supplemental log data will ensure that the primary key columns are also captured by the extract process along with the changed columns for the rows.

d:\ogg>ggsci
GGSCI (WIN1) 1> dblogin userid ggate

Password:

Successfully logged into database.

GGSCI (win1) 2> add trandata sender.tcustmer

GGSCI(win1) 3> add trandata sender.tcustord

2.     Add Extract checkpoint in oracle redo log

GGSCI (WIN1) 5> add extract extsend, tranlog, begin now

EXTRACT added.

3.     Add Extract checkpoint for target system/remote extract trails

 GGSCI (WIN1) 7> ADD RMTTRAIL d:\ogg\dirdat\rt, EXTRACT EXTSEND, MEGABYTES  1

RMTTRAIL added.

4.     Configure Extract Parameter File

edit params extsend

— Extract parameter file to capture TCUSTORD

— and TCUSTMER changes

EXTRACT EXTSEND

USERID [email protected], PASSWORD password

RMTHOST localhost, MGRPORT 7809

RMTTRAIL d:\ogg\dirdat\rt

TABLE sender.TCUSTMER;

TABLE sender.TCUSTORD;

 Configure REPLICAT process in the Target Database


 1.     Configure GLOBALS parameter file to add checkpoint table name

ggsci> edit params GLOBALS

checkpointtable ggate.ggchkpt

2.     Add the checkpoint table

 ggsci> add checkpointtable

3.     Add the  Replicat Process

  ggsci> ADD REPLICAT REPREC, EXTTRAIL d:\ogg\dirdat\rt

4.     Configure Replicat parameter file

 ggsci> edit params reprec

 

— REPLICAT parameter file to replicate changes

— for TCUSTORD and TCUSTMER.

REPLICAT REPREC

PURGEOLDEXTRACTS

ASSUMETARGETDEFS

DISCARDFILE d:\ogg\dirrpt\reprec.dsc, PURGE

USERID [email protected], PASSWORD password

MAP sender.TCUSTORD, TARGET receiver.TCUSTORD;

MAP sender.TCUSTMER, TARGET receiver.TCUSTMER;

Test the data replication for incremental transactional data


 1.     Login to source schema using sqlplus and execute demo transaction script demo_ora_misc.sql script  from golden gate installation directory

d:\ogg>sqlplus sender/[email protected]

SQL> @demo_ora_misc.sql

2. Start manager if it is not already started

ggsci> start manager

3. Start the extract process

ggsci> start extract extsend

4. Start the replicat process

 ggsci> start replicat reprec

Conclusion

The incremental data extract from source database and replicat to destination or target database is done on a table by table basis using Oracle Golden Gate. Different parameter values can be used to transform the source to destination data in the desired format.

Leave a reply

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">

*