Informix | Oracle |
BOOL | RAW,BLOB |
CLOB | CLOB |
BLOB | BLOB |
CHAR with length <=2000 | CHAR |
CHAR with length > 2000 | CLOB |
VARCHAR, max length 255 | VARCHAR2 |
DATE | DATE |
DATETIME | TIMESTAMP, DATE |
INTERVAL | No Support |
DECIMAL | NUMBER |
FLOAT | NUMBER |
SMALLFLOAT | NUMBER |
SMALLINT | NUMBER |
INT8 (INTEGER) | NUMBER |
BIGINT | NUMBER |
LVARCHAR | CLOB |
MONEY | NUMBER |
SERIAL | NUMBER |
SERIAL8 | NUMBER |
BIGSERIAL | NUMBER |
NCHAR | NCHAR, max length allowed is 2000 |
NVARCHAR, max length 255 | NVARCHAR2 |
BYTE | LONG RAW,BLOB |
TEXT | CLOB |
HTML | CLOB |
What is the datatype mapping used in Oracle Golden Gate between Informix and Oracle Database?2/25/2015
0 Comments
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 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 ... Database selected. Table created. Database closed. [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; connect ggdemo/ggdemo create table table1( col1 number not null, col3 varchar(10), 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 DEFSFILE ./dirdef/cap01.def,PURGE sourcedb ol_informix1210 TABLE sourcedb.jiwang.mytbl1; 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 extract capini02 sourcedb ol_informix1210 rmthost 144.25.0.109, mgrport 7020 rmttask replicat, group repini02 TABLE sourcedb.jiwang.table1; Note that here we connect to the Oracle GoldenGate for Oracle instance (on IP 144.25.0.109) 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 EXTRACT added. 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 Task SOURCEISTABLE 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 replicat repini02 userid ggdemo, password ggdemo sourcedefs ./dirdef/cap01.def GETTRUNCATES 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 Task SPECIALRUN 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 C:\PROGRA~1\IBMINF~1\bin>esql -v 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.
When defining GoldenGate parameter files, it's a best practice to define macros, build a macro library and share the library among multiple GoldenGate components. What is GoldenGate Macro? GoldenGate Macros are series of commands, parameters or data conversion functions that may be shared among multiple GoldenGate components. Let's start the discussion with an example. The following is an example macro library: dirmac/report.mac: MACRO #report BEGIN REPORT ON SUNDAY AT 1:00 END; dirmac/dbconnect.mac: MACRO #source_001_connect BEGIN SOURCEDB source1db useridalias oggadmin END; To load the macro library, in the parameter file, you can use the following command: NOLIST include ./dirmac/dbconnect.mac include ./dirmac/report.mac LIST NOLIST means not logging the content followed to the report file. LIST means the opposite. In the previous example, I decide not to show macro content in the report file. To call those macros, you can use #macroname () i.e.: extract ext01 #source_001_connect () #report () exttrail ./dirdat/e1 TABLE scott.t1; The benefit of this is obvious, if there is any change to apply to all GoldenGate components, you can only change in one place. I find macros is especially useful for setting up standards for:
References
When using GoldenGate (service) in mission-critical applications, you will have to agree on a Service-Level Agreement (SLA). Typical SLAs include:
How to Keep the Trail Files? Use PURGEOLDEXTRACTS in the Manager parameter file to manage trail file. Make sure use USECHECKPOINTS parameter to ensure the trail file are only purged after all the processes have finished with that file. Example: PURGEOLDEXTRACTS ./dirdat/aa* USECHECKPOINTS MIMKEEPDAYS 7
|
Oracle GoldenGateThis blog has updates, tips and tricks for Oracle GoldenGate. Categories
All
Archives
April 2018
|