Oracle GoldenGate : DB2 to Oracle

This article will demonstrate heterogeneous replication using OGG from DB2 to Oracle.

1. Pre-requisites

OGG is installed on source and target systems.

2. Environment

DB2 on Mainfram Z/OS
Oracle Database : 11.2.0.3

DB2 to Oracle

3. Configuration on Source Server ( DB2 )

A. Parameter Files

Place all parameter files in the $GG_HOME/dirprm directory.

 

Extract Capture

> cat cdb2.prm

extract cdb2
--trailcharsetascii
sourcedb db2t, userid ggcap, password ggcap
exttrail ./dirdat/lt
transmemory directory(./dirtmp, 450m, 450m),transallsources 450m
table user.table1;

 

Extract Data Pump

> cat pdb2.prm

extract pdb2
rmthost target_server, mgrport 7809
rmttrail ./dirdat/rt
passthru
table user.table1;

 

Extract for Initial Load

> cat ildb2.prm

extract ildb2
sourceistable
sourcedb db2t, userid ggcap, password ggcap
rmthost target_server, mgrport 7809, tcpbufsize 1048576, tcpflushbytes 1048576
rmtfile ./dirdat/la, megabytes 2048, maxfiles 999
TABLE user.table1;

 

B. Add Trandata

 

cd $GG_HOME

> ./ggsci

GGSCI> DBLogin sourcedb db2t, userid ggcap, password ggcap</strong>
GGSCI> add trandata user.table1
GGSCI> exit

 

C. Source Table Definitions

 

> cd $GG_HOME/dirdef

> cat defgen_db2t.prm
DEFSFILE dirdef/db2t_source_tabs.def, PURGE
sourcedb db2t, userid ggcap, password ggcap
TABLE user.table1;

> cd $GG_HOME

>./defgen paramfile </strong><strong>./dirdef/defgen_db2t.prm NOEXTATTR

 

When done, copy the output file (dirdef/db2t_source_tabs.def) over to the target database server and put it in the $GG_HOME/dirdef directory.

 

D. Create and Start the Extract Processes

 

> ./ggsci

GGSCI> DBLogin sourcedb db2t, userid ggcap, password ggcap
GGSCI> add extract cdb2 , tranlog DB2T.BSDS01 , begin now
GGSCI> add exttrail ./dirdat/lt, extract cdb2
GGSCI> add extract pdb2, exttrailsource ./dirdat/lt
GGSCI> add rmttrail ./dirdat/rt, extract pdb2
GGSCI> start extract cdb2
GGSCI> start extract pdb2

 

E. Create and Start the Initial Load Capture Process

 

GGSCI> add extract ildb2 , sourceistable
GGSCI> start extract ildb2

 

3. Configuration on Target Server ( Oracle)

A. Parameter Files

Place all parameter files in the $GG_HOME/dirprm directory.

 

Replicat

> cat rdb2.prm

REPLICAT RDB2
SETENV (ORACLE_HOME="/u01/app/oracle/product/11.2.0.3/dbhome_1")
SETENV (ORACLE_SID="ORA11G")
SETENV (NLS_LANG="AMERICAN_AMERICA.AL32UTF8") 
USERID ggrep, PASSWORD ggrep 
DiscardRollover at 23:00
ReportRollover at 23:00 
ReportCount Every 5 Minutes, Rate
StatOptions ReportFetch 
DiscardFile ./dirrpt/RDB2.dsc, Append, Megabytes 100M
HandleCollisions
SOURCEDEFS ./dirdef/db2t_source_tabs.def
DbOptions SuppressTriggers
DbOptions DeferRefConst
MAP user.table1, table1 user.table1;

 

Initial load Replicat

> cat ilrdb2.prm

replicat ilrdb2
END RUNTIME
SETENV(ORACLE_HOME="/u01/app/oracle/product/11.2.0.3/dbhome_1")
SETENV(ORACLE_SID="ORA11G")
SETENV(NLS_LANG="AMERICAN_AMERICA.AL32UTF8")
UserID ggrep, Password ggrep
SOURCEDEFS ./dirdef/db2t_source_tabs.def
DISCARDFILE ./dirrpt/ilrdb2.dsc, Purge
MAP user.table1, TARGET user.table1;

 

B. Initial load Steps – File to Replicat

After the initial load process from DB2 has completed, then continue with the configuration of the initial load replicat.

Configure and Start the Initial-Load Replicat

> cd $GG_HOME

>./ggsci

GGSCI> DBLogin UserID ggrep, Password ggrep
GGSCI> add replicat ilrdb2, ExtTrail ./dirdat/aa, CheckPointTable ggrep.ggs_checkpoint
GGSCI> start replicat ilrdb2

 

After the initial-load replicat is complete, continue on to the next step.
 

Configure and Start the Change Synchronization Replicat

> cd $GG_HOME

>./ggsci

GGSCI> add replicat rdb2, ExtTrail ./dirdat/rt, CheckPointTable ggrep.ggs_checkpoint
GGSCI> start extract rdb2

 

Monitor the replicat for any errors and the current time and position of the records it is applying.

Next, verify that the replicat has passed the point where the initial-load replicat stopped. You can compare both time and the RBA by looking in the rdb2.rpt report file and the current position of the change synchronization replication as shown by the “info rdb2” command.

Turn Off Handle Collisions

> cd $GG_HOME

>./ggsci

GGSCI> send replicat rdb2, nohandlecollisions

 

Also, edit the parameter file and comment out the HANDLECOLLISIONS line so that the next time the replicat is restarted, this parameter will not take effect.

GGSCI> edit params rdb2

REPLICAT RDB2
SETENV (ORACLE_HOME="/u01/app/oracle/product/11.2.0.3/dbhome_1")
SETENV (ORACLE_SID="ORA11G")
SETENV (NLS_LANG="AMERICAN_AMERICA.AL32UTF8") 
USERID ggrep, PASSWORD ggrep 
DiscardRollover at 23:00
ReportRollover at 23:00 
ReportCount Every 5 Minutes, Rate
StatOptions ReportFetch 
DiscardFile ./dirrpt/RDB2.dsc, Append, Megabytes 100M
--HandleCollisions
SOURCEDEFS ./dirdef/db2t_source_tabs.def
DbOptions SuppressTriggers
DbOptions DeferRefConst
MAP user.table1, table1 user.table1;

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s