- Oracle GoldenGate for Informix: Support and Feature Overview
- Oracle GoldenGate for Informix Installation
- Making Informix IDS Database Ready for Replication
- Installing and Configuring ODBC
- Installing Oracle GoldenGate
- Initial loading and one-way replication from Informix IDS to Informix IDS database
- Initial loading and one-way replication from Informix IDS to Oracle database
- Dealing with special characters
- Oracle GoldenGate for Informix - Frequently Asked Questions
The following are the tutorials to help you get started with Oracle GoldenGate for Informix.
This example provides a step-by-step instruction on how to use Oracle GoldenGate Direct Load to perform initial loading from informix IDS to Oracle Database. Oracle GoldenGate direct load is one of Oracle GoldenGate initial loading methods, with which you run an Oracle GoldenGate initial-load Extract to extract the source records and send them directly to an initial-load Replicat task.
The example is test with the following setup:
The sample code is available for 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 (SOURCEDB), a table (TABLE1) and load the sample data (10 records).
[informix@ogg-1 demo_01]$ dbaccess <C:\Users\JIWANG\ggs_informix_example\informix_src\src_create_tbl.sql
[informix@ogg-1 demo_01]$ dbaccess <C:\Users\JIWANG\ggs_informix_example\informix_src\src_load_data.sql
2.On the Oracle DB target, create the Oracle Database table:
grant connect, resource to ggdemo identified by ggdemo;
grant dba to ggdemo;
create table table1(
col1 number not null,
PRIMARY KEY (col1));
Step 2: Creating Defgen File
Defgen is required for the current GoldenGate versions for replication in heterogeneous environment.
GGSCI> edit param defgen
GGSCI 69> sh defgen paramfile dirprm/defgen.prm
After the defgen file is created, you need to copy the defgenfile to the dirdef folder in Oracle GoldenGate for Oracle.
Step 3: Creating Initial Loading
1. Creating the Initial Loading Extract on Informix IDS source
GGSCI > edit param capini02
rmthost 220.127.116.11, mgrport 7020
rmttask replicat, group repini02
Note that here we connect to the Oracle GoldenGate for Oracle instance (on IP 18.104.22.168) with manager port to be 7020. The RMTTASK parameter specify the replicat group name on GoldenGate for Oracle side.
GGSCI> add ext capini02, vam, sourceistable
GGSCI> info capini02
EEXTRACT CAPINI02 Initialized 2015-02-25 15:10 Status STOPPED
Checkpoint Lag Not Available
Log Read Checkpoint Not Available
First Record Record 0
Note: The initial loading groups will note be shown in the "info all' command. You have to use "info <extract_name>" or "info <replicat_name>".
2. Creating the initial loading replicat on Oracle DB target
GGSCI> edit param replicat repini02
userid ggdemo, password ggdemo
discardfile ./dirrpt/rep010001.dsc, purge
map sourcedb.jiwang.table1, target ggdemo.table1;
GGSCI> add replicat repini02, specialrun
GGSCI> info repini02
REPLICAT REPINI02 Initialized 2015-02-25 18:14 Status STOPPED
Checkpoint Lag 00:00:00 (updated 00:00:07 ago)
Log Read Checkpoint Not Available
Step 4: Start the Initial Loading
On the Informix source run the following command:
GGSCI> start capini02
The specialrun replicat group will be launched automatically. After completing the process, the extract and replicat processes are shutdown automatically. You should see 10 records added to the Oracle database TABLE1 table.
----- End of File -----
Before implementing replication with Oracle GoldenGate for Informix, you need to check the Informix IDS database version and the Informix CSDK version. Oracle GoldenGate has the following requirements:
C:\Program Files\IBM Informix Software Bundle\bin>dbaccess -version
Program Name: dbaccess
Build Version: 12.10.FC4DE
Build Number: N111
Build Host: HODERI
Build OS: Windows_NT 5
Build Date: Tue Jun 10 22:06:59 CDT 2014
Build Timestamp: 10-Jun-2014.21:30:00
GLS Version: glslib-6.00.FC4
IBM Informix CSDK Version 4.10, IBM Informix-ESQL Version 4.10.FC4DE
Software Serial Number RDS#N000000
In this example, the OS version is Windows 2012 and Informix IDS 12.10, CSDK is 4.10 FC4DE. You need to choose the GoldenGate download based on the OS type. For each download, Oracle GoldenGate supports Informix 11.5, 11.7 and 12.10 databases.
If you are not familiar with Oracle GoldenGate's handling of character set encoding, please read Oracle GoldenGate Globalization first.
For Informix, Oracle GoldenGate extract and replicat both use the database characters (DB_LOCAL) as the session character set. There is no need to set the SESSIONCHARSET parameter. For database character set, you can check the Informix IDS database character set with the following SQL query:
Select site from systables where tabid=91;
The restrictions is the Informix IDS database charsets for source database and syscdc1 should be the same.
This blog provides answers to some frequently asked questions about Oracle GoldenGate for Informix.
Let's talk about how to setup a bi-directional replication for IBM Informix IDS databases with Oracle GoldenGate 22.214.171.124.0.
This tutorial provides a step-by-step example for setting up an one-way replication between Informix IDS databases.
Let's discuss the configuration steps needed for Informix IDS database to enable Oracle GoldenGate replications. Informix 11.70 is used as the example here.
Step 1: Installing Informix IDS Database
You can skip this step if an Informix IDS database is already installed.
(1) Log in the root user and create a user called informix
>useradd -g informix -d /home/informix informix
You can see the new group created in /etc/group:
The user information in /etc/passwd:
Set the user password as follows:
Changing password for user informix.
New UNIX password:
BAD PASSWORD: it is based on a dictionary word
Retype new UNIX password:
passwd: all authentication tokens updated successfully.
Log on as the informix user (or use su -l informix) to check the server has informix uid defined locally.
(2) Log in as the informix user and unzip/untar the informix ship home (iif.11.70.FC7DE.linux-x86_64.tar) locally to $INFORMIX_SHIPHOME (i.e./home/informix/software/11.70) folder.
tar -xvf iif.11.70.FC7DE.linux-x86_64.tar
Run the following command to perform the installation as root user:
In my setup, installation path is: /home/informix/ifx1170. For Informix 11.5 and 12.1, I use the following path:
Next, please check the available port numer in your system. An example Port# could be:
(3) Setup the Informix Database Environment Variable
Informix installation creates the script to help setup the environment including:
(3) Start the instance
Note, the -ivy i only use for the initial setup. For the later setup, just use oninit -v.
Check the database status:
IBM Informix Dynamic Server Version 11.70.FC7DE – On-Line – Up 04:28:19 – 173796 Kbytes
You should also check the configuration for the DB:
SHMVIRTSIZE 100000 # initial virtual shared memory segment size
SHMADD 8192 # Size of new shared memory segments (Kbytes)
SHMTOTAL 0 # Total shared memory (Kbytes). 0=>unlimited
Then, based on the article (http://kb.deister.net/index.php/Keeping_Your_Informix_rootdbs_Lean_and_Clean), you should also check the content in rootdbs with oncheck -pe >tempfile command (tempfile).
Check the version of the Informix IDS database.
IBM Informix Dynamic Server Version 11.70.FC7DE -- On-Line -- Up 11:28:09 -- 173796 Kbytes
Step 2: Create SYSCDC database
Run the following SQL command with the informix user:
dbaccess < $INFORMIXDIR/etc/syscdcv1.sql
You have to keep all the logs in the logical log before Oracle GoldenGate complete the extraction. Therefore, you might increase logical log size and the number of logical log files with the LOGSIZE and LOGFILES configuration parameters. An example is shown as follows:
onmode -wf LOGSIZE=1048576
11:21:14 Value of LOGSIZE has been changed to 1048576.
Step 3: Download and Install Informix CSDK for Oracle GoldenGate
Oracle GoldenGate for Informix requires CSDK 4.10FC4. You can install CSDK for Oracle GoldenGate in separate directory without changing the existing Informix database installation.
Installing Oracle GoldenGate is very straight forward. You just need to unzip the downloaded file and run the create subdirs command in GGSCI to create the directory structure for the Oracle GoldenGate installation.To start the Oracle GoldenGate manager, you can configure the port and parameters of the manager by setting up the parameter file. This tutorial provides an example.
Step 1: Unzip the Oracle GoldenGate software and start the GGSCI
Why I get the "The program can't start because MSVCP100.dll is missing from your computer error?
Answer: In searching for an answer to this, I was able to identify that this DLL is a part of the Microsoft Visual C++ 2010 Redistribution Package (x64) (download here). After installing the package, the GGSCI command will run successfully.
Step 2: Create the directory structure.
GGSCI> create subdirs
Creating subdirectories under current directory /home/informix/ogg
Parameter files /home/informix/ogg/dirprm: already exists
Report files /home/informix/ogg/dirrpt: created
Checkpoint files /home/informix/ogg/dirchk: created
Process status files /home/informix/ogg/dirpcs: created
SQL script files /home/informix/ogg/dirsql: created
Database definitions files /home/informix/ogg/dirdef: created
Extract data files /home/informix/ogg/dirdat: created
Temporary files /home/informix/ogg/dirtmp: created
Credential store files /home/informix/ogg/dircrd: created
Masterkey wallet files /home/informix/ogg/dirwlt: created
Dump files /home/informix/ogg/dirdmp: created
Step 3: Connect to Source Database
GGSCI (ogg-1.pssa.us.oracle.com) 1> dblogin sourcedb syscdcv1
2013-10-25 11:42:23 INFO OGG-03036 Database character set identified as ISO-8859-1. Locale: en_US.
2013-10-25 11:42:23 INFO OGG-03037 Session character set identified as UTF-8.
Successfully logged into database.
Step 4: Configure and Start the Oracle GoldenGate Manager
GGSCI> edit param mgr
PURGEOLDEXTRACTS ./dirdat/*, USECHECKPOINTS
AUTORESTART ER *
GGSCI> start mgr
GGSCI> info mgr
Manager is running (IP port brown.oracle.jp.15000, Process ID 18105).
Now, the Oracle GoldenGate is ready for use.
Oracle GoldenGate for Informix uses ODBC to talk to the source and target databases. Therefore, you need to setup the ODBC connections. This tutorial explains the steps needed for this setup.