How to run defgen ERROR OGG-01161 Bad column index (43)

So golden gate has two kinds of replication.

1. With DDL replication enabled  

2. Without DDL replication enabled

If without DDL replication enabled is configured then we have to run something

called defgen i.e. if there is a change in the structure of the source tables. It is

actually generating the definition of the source tables so the replication does not

freak out for a difference in what it knows about the table's past compared to

what was changed on the source table and is at present.  

A good example is let's say, there are 20 columns in a table and last time we ran defgen

it was 20 columns and a CR is executed or is going to be executed which will change the

source table columns to 21 from 20. That means the structure of the table has changed.

In such a case we have to run DEFGEN utility of Golden gate. Which generated a

file stating that the table has these many column in our case 21 now.

The defgen file is kept in replicat parameter file and lives at GG home location

./dirdef/excfblq2.def. It will make sure the replicat knows the structure of the source table so

it replicates accordingly.  Anytime DDL structure is change on source table defgen

needs to be run for replicat unless DDL replication is enabled. I will run an example here.

Below are my GG processes running.

GGSCI (server1) 1> info all

Program     Status Group       Lag at Chkpt Time Since Chkpt

MANAGER     RUNNING

EXTRACT     RUNNING EXCFBLQ2    00:00:05 00:00:04

REPLICAT    RUNNING RPMPCLQ2    00:00:00 00:00:04

Let's say there is a CR which will execute the below commands where source table
structure will be changed and column will be added:

spool alter_service_order.log ALTER SESSION SET CURRENT_SCHEMA = DLEUSER; alter table dleuser.service_order add WF_API_GUID VARCHAR2(100); SPOOL OFF;

In the golden gate replication we are using service_order table so since the structure will
change, the replicat above should have abended.

Let's check after the execution of script the replicat has abended:

GGSCI (server1) 2> info all

Program Status   Group    Lag at Chkpt  Time Since Chkpt

MANAGER RUNNING

EXTRACT RUNNING EXCFBLQ2 00:00:05   00:00:06

REPLICAT    ABENDED RPMPCLQ2 00:00:00   00:04:02

Now let's check the report file of the replicat on ggsci command line and go at the
end:

GGSCI (server1) 2>view report RPMPCLQ2

At the very end you will see below error: 018-04-05 15:14:57 ERROR   OGG-01161 Bad column index (43) specified for table DLEUSER.SERVICE_ORDER, max columns = 43. *********************************************************************** *                   ** Run Time Statistics **                         * *********************************************************************** Last record for the last committed transaction is the following:

This error is because of the change in the definition of the source table.

Solution:

1.     Check which table's definition will be generated when defgen will be run:

Catalog the file: make sure all table names exists in the file in my case it does. The
table because of which replicat abended does exist. .

[server1: 12c]$ cat ./dirprm/defgen.prm defsfile ./dirdef/excfblq2.def userid ggsuser@OGG password XXXX ==>> get in server to see password   TABLE dleuser.service_order; =====>> I see the one I am looking for TABLE dleuser.state ; That looks good. Service table is included in the parameter file. Let's run the command which generated the defgen. NOTE: when I run the defgen you will see the file created as listed in the param file. You will have to use the same name in replicat parameter file so keep an eye on the name. 2.     Rename the already existing file created from the past. Make sure to rename the already generated file from the past else you will get process abending the file already exists. [server1: 12c]$ cd dirdef [server1: dirdef]$ mv excfblq2.def excfblq2_old.def [server1: dirdef]$ ls -ltrh total 195K -rw-r----- 1 ggpusr2 dba  56K Aug 5 2015 excfblq2_old.def -rw-r----- 1 ggpusr2 dba 6.6K Aug 11  2015 excfblq1.def 3.     Let's run defgen for the extract table or tables ./defgen PARAMFILE ./dirprm/defgen.prm Definitions generated for 17 tables in ./dirdef/excfblq2.def. 4.     Okay so the new file with the listed table or tables has been generated. Now let's add it to the replicat parameter file. NOTE: If the replicat is on a different server winscp or scp it to the destination server and add it to the ./dirdef/ directory with the same name as its used in the replicate parameter file. In my case it's still the same name and GG home and same server where replicat run where i created the file, such as  ./dirdef/excfblq2.def and my replicat is running from same OGG home and server so i just need to start the replicat and let it read the new file i just created.   5.     Once I verified or performed step 4, its time to start the replicat. I only have to start the replicat since I kept the same name as it existed in replicat. Let's start and check. [server1-12c]$ ./ggsci Oracle GoldenGate Command Interpreter for Oracle Version 12.1.2.1.5 20635622 OGGCORE_12.1.2.1.0OGGBP_PLATFORMS_150320.0454_FBO Linux, x64, 64bit (optimized), Oracle 11g on Apr 22 2015 02:38:13 Operating system character set identified as UTF-8. Copyright (C) 1995, 2015, Oracle and/or its affiliates. All rights reserved. GGSCI (server1) 1> info all Program     Status Group       Lag at Chkpt Time Since Chkpt MANAGER     RUNNING EXTRACT     RUNNING EXCFBLQ2    00:00:03 00:00:05 REPLICAT    ABENDED RPMPCLQ2    00:00:00 00:25:44 I see there is a lag of 25 min lets start replicat and it should work because it knows about the 21st column added and will not complain now. GGSCI (server1) 2> start RPMPCLQ2 Sending START request to MANAGER ... REPLICAT RPMPCLQ2 starting GGSCI (server1) 3> info all Program     Status Group       Lag at Chkpt Time Since Chkpt MANAGER     RUNNING EXTRACT     RUNNING EXCFBLQ2    00:00:04 00:00:01 REPLICAT    RUNNING RPMPCLQ2    00:00:00 00:00:00 The lag should catch up and the process is running back again   That is it. Hope the document helps.