How to perform real-time replication from Informix to Oracle?

This example provides a step-by-step instruction on how to setup a real-time one-way replication from Informix IDS database to Oracle Database using Oracle GoldenGate. The example is available to download at ggs_informix_example.zip. 

Step 1: Creating the Tables
1. On the Informix IDS source, run the src_create_tbl.sql and src_load_data.sql to create example database, a table and load the sample data. [informix@ogg-1 demo_01]$ dbaccess <C:\Users\JIWANG\ggs_informix_example\informix_src\src_create_tbl.sql
...
Database selected.
Table created.
Database closed.
 
[informix@ogg-1 demo_01]$ dbaccess <C:\Users\JIWANG\ggs_informix_example\informix_src\src_load_data.sqlRun the scripts to create tables and load sample data (10 records):

2.On the Target, create the Oracle Database table
grant connect, resource to ggdemo identified by ggdemo;
grant dba to ggdemo;
  
connect ggdemo/ggdemo
  
create table table1(
col1            number not null,
col3            varchar(10),
PRIMARY KEY (col1));

Step 2: Creating the Defgen File

GGSCI>  edit param defgen
DEFSFILE ./dirdef/cap01.def,PURGE
sourcedb ol_informix1210
TABLE sourcedb.jiwang.mytbl1;
 
GGSCI (JIWANG-VBOX7) 69> sh defgen paramfile dirprm/defgen.prm
***********************************************************************
       Oracle GoldenGate Table Definition Generator for Informix
      Version 12.1.2.1.0 OGGCORE_12.1.2.1.0_PLATFORMS_140818.0929
   Windows x64 (optimized), Informix_SDK410 on Aug 18 2014 13:41:32
Copyright (C) 1995, 2014, Oracle and/or its affiliates. All rights reserved.
 
                    Starting at 2015-02-23 20:53:54
***********************************************************************
Operating System Version:
Microsoft Windows Server 2012 , on x64
Version 6.2 (Build 9200)
Process id: 11412
***********************************************************************
**            Running with the following parameters                  **
***********************************************************************
DEFSFILE ./dirdef/cap01.def,PURGE
sourcedb ol_informix1210
2015-02-23 20:53:54  INFO    OGG-03036  Database character set identified as ISO
-8859-1. Locale: en_US.
2015-02-23 20:53:54  INFO    OGG-03037  Session character set identified as ISO-
8859-1.
TABLE sourcedb.jiwang.mytbl1;
Retrieving definition for sourcedb.jiwang.mytbl1.
 
Definitions generated for 1 table in ./dirdef/cap01.def.

Copy the defgen file to the dirdef folder in Oracle GoldenGate for Oracle Directory.

Step 3: Adding the Supplemental Logging on the Source

GGSCI> dblogin sourcedb ol_informix1210
2013-10-25 13:27:06  INFO    OGG-03036  Database character set identified as ISO-8859-1. Locale: en_US.
2013-10-25 13:27:06  INFO    OGG-03037  Session character set identified as UTF-8.
Successfully logged into database.
 
GGSCI (ogg-1.pssa.us.oracle.com) 10> info trandata *.*.*
Logging of supplemental log data (full row logging) is disabled for table sourcedb.jiwang.table1.
 
GGSCI (JIWANG-VBOX7) 22> add trandata sourcedb.jiwang.table1
Logging of supplemental log data (full row logging) is enabled for table sourcedb.jiwang.table1

Step 4: Creating the Extract and Data Pump

GGSCI > edit param  CAP01
extract CAP01
sourcedb ol_informix1210
TRANLOGOPTIONS GETMETADATAFROMVAM
GETTRUNCATES
EXTTRAIL dirdat/c1
TABLE sourcedb.jiwang.mytbl1;
 
GGSCI> add ext cap01,vam,begin now
EXTRACT added.
 
GGSCI> add exttrail dirdat/c1, extract cap01
EXTTRAIL added.
 
GGSCI> start ext cap01
Sending START request to MANAGER ...
EXTRACT CAP01 starting
 
GGSCI> info cap01
EXTRACT    CAP01     Last Started 2015-02-23 20:01   Status RUNNING
Checkpoint Lag       00:00:00 (updated 00:00:01 ago)
Process ID           6708
VAM Read Checkpoint  2015-02-23 19:59:45.379000
 
GGSCI> edit pmp02
extract pmp02
rmthost 144.25.0.109, mgrport 7020, compress, timeout 30
rmttrail ./dirdat/if
passthru
TABLE sourcedb.jiwang.table1;
  
GGSCI> add extract pmp02, exttrailsource dirdat/c1
 
GGSCI> add rmttrail ./dirdat/if, extract pmp02, megabytes 1

Step 5: Creating the Replicat

Then, add the replicat group:

GGSCI> edit param rep02
replicat rep02
userid ggdemo, password ggdemo
sourcedefs ./dirdef/cap01.def
GETTRUNCATES
discardfile ./dirrpt/rep010001.dsc, purge
HANDLECOLLISIONS
map sourcedb.jiwang.table1, target ggdemo.table1;
 
GGSCI> dblogin userid ggdemo, password ggdemo
Successfully logged into database.
 
GGSCI> add checkpointtable ggdemo.checkpointtbl
Successfully created checkpoint table ggdemo.checkpointtbl.
 
GGSCI> add replicat rep02, exttrail ./dirdat/if,checkpointtable ggdemo.checkpointtbl
REPLICAT added.

Step 6: Run the Replication

Start the Extract on Informix Side: 

GGSCI> start cap01
GGSCI> start pmp02

Start the replicat on Oracle Side 

GGSCI> start rep02