This step is included if you start the setup from scratch. If you already have tables on the source database, you can skip this step.
1. On the Source, use src_create_tbl.sql:
database sourcedb;
create table sourcedb:table1(
col1 integer not null,
col3 char(10),
PRIMARY KEY (col1));
close database;
Run the SQL script to create the table in the Informix IDS database:
[informix@ogg-1 demo_01]$ dbaccess <src_create_tbl.sql
Database selected.
Table created.
Database closed.
Check the table definition:
[informix@ogg-1 demo_01]$ isql -v sourcedb
SQL> select * from table1;
+------------+-----------+
| col1 | col3 |
+------------+-----------+
+------------+-----------+
SQLRowCount returns -1
2. On the Target, use tgt_create_tbl.sql:
database sourcedb;
create table table1(
col1 integer not null,
col3 char(10),
PRIMARY KEY (col1));
close database;
Run the SQL script to create the table in the Informix IDS database:
[informix@ogg-1 demo_01]$ dbaccess <tgt_create_tbl.sql
Database selected.
Table created.
Database closed.
Check the table definition:
[informix@ogg-1 demo_01]$ isql -v targetdb
SQL> select * from table1;
+------------+-----------+
| col1 | col3 |
+------------+-----------+
+------------+-----------+
SQLRowCount returns -1
Step 2: Add Supplemental Logging
Log into Oracle GoldenGate GGSCI command-line utility:
GGSCI> dblogin sourcedb syscdcv1
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.
Check the trandata info for the replication table :
GGSCI (ogg-1.pssa.us.oracle.com) 10> info trandata sourcedb.*.*
Logging of supplemental log data (full row logging) is disabled for table sourcedb.informix.table1.
Add the trandata to the replication table:
GGSCI (ogg-1.pssa.us.oracle.com) 11> add trandata sourcedb.informix.table1
Logging of supplemental log data (full row logging) is enabled for table sourcedb.informix.table1.
Step 3: Create Extract
Create the extract parameter file:
GGSCI > edit param cap01
extract cap01
sourcedb syscdcv1
TRANLOGOPTIONS GETMETADATAFROMVAM
GETTRUNCATES
EXTTRAIL dirdat/c1
TABLE sourcedb.*.*;
Create the extract group:
GGSCI> add ext cap01,vam,begin now
EXTRACT added.
Specify the trail file for the extract:
GGSCI> add exttrail dirdat/c1,extract cap01
EXTTRAIL added.
Start the extract process:
GGSCI> start ext cap01
Sending START request to MANAGER ...
EXTRACT CAP01 starting
Check the status of the extract process:
GGSCI> info cap01
EXTRACT CAP01 Last Started 2013-10-25 12:08 Status RUNNING
Checkpoint Lag 00:03:04 (updated 00:00:02 ago)
Process ID 18129
VAM Read Checkpoint 2013-10-25 12:05:47.738995
GGSCI> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING CAP01 00:00:00 00:00:02
You can see that the process is in RUNNING status.
Step 4: Test Extract
1. Insert Data
insert into table1 values (12345, 'integer');
2. Update Data
update table1 set col1=54321;
3. Delete Data
delete from table1 where col1=54321;
4. Truncate Data
insert into table1 values (1, 'record 1');
insert into table1 values (2, 'record 2');
truncate table table1;
5. Check the Extraction Status
GGSCI (ogg-1.pssa.us.oracle.com) 12> info cap01
EXTRACT CAP01 Last Started 2013-10-25 14:11 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:00:07 ago)
Process ID 18512
VAM Read Checkpoint 2013-10-25 14:12:52.000000
LSN: 1:0X5372078
Step 5: Create Defgen File
Create the defgen parameter file (dirprm/defgen.prm):
DEFSFILE ./dirdef/cap01.def,PURGE
sourcedb sourcedb
TABLE sourcedb.informix.table1;
Run the defgen utility:
GGSCI (ogg-1.pssa.us.oracle.com) 3> sh ./defgen paramfile ./dirprm/defgen.prm
The following is an example defgen file:
***********************************************************************
Oracle GoldenGate Table Definition Generator for Informix
Version 12.1.2.1.0 OGGCORE_MAIN_PLATFORMS_131015.1405
Linux, x64, 64bit (optimized), Informix1170 on Oct 15 2013 16:56:43
Copyright (C) 1995, 2013, Oracle and/or its affiliates. All rights reserved.
Starting at 2013-10-25 15:00:14
***********************************************************************
Operating System Version:
Linux
Version #1 SMP Thu Jan 3 18:31:38 PST 2013, Release 2.6.39-300.26.1.el5uek
Node: ogg-1.pssa.us.oracle.com
Machine: x86_64
soft limit hard limit
Address Space Size : unlimited unlimited
Heap Size : unlimited unlimited
File Size : unlimited unlimited
CPU Time : unlimited unlimited
Process id: 18616
***********************************************************************
** Running with the following parameters **
***********************************************************************
DEFSFILE ./dirdef/cap01.def,PURGE
sourcedb sourcedb
2013-10-25 15:00:14 INFO OGG-03036 Database character set identified as ISO-8859-1.
Locale: en_US.
2013-10-25 15:00:14 INFO OGG-03037 Session character set identified as UTF-8.
TABLE sourcedb.informix.table1;
Retrieving definition for sourcedb.informix.table1.
Definitions generated for 1 table in ./dirdef/cap01.def.
Step 6: Create Replicat
Create the replicat parameter file (rep01.prm):
replicat rep01
targetdb targetdb
sourcedefs ./dirdef/cap01.def
GETTRUNCATES
discardfile ./dirrpt/rep010001.dsc, purge
map sourcedb.informix.table1, target targetdb.informix.table1;
Create the replicat:
add replicat rep01, exttrail /home/informix/ogg/dirdat/c1,nodbcheckpoint
GGSCI>start rep01
In this example, both extract and replicat are on the same machine. Therefore, the replicat directly reads the extract trail file. If the target database in on the remote server, you can add a pump group to delivery the trail file via TCP/IP to the target.
Test the Replicat
You can use stats cap01 and stats rep01 to check the operations captures and replicated.
(Last Updated: 10/28/2014)