Fixing OGG-01161 Bad Column Index (77) Specified for the Table

In this blog we will perform require steps to resolve OGG-01161 Bad column index (77) specified for the table in Oracle Goldengate, This error occurs when the target table doesn’t match the source table, there is a mismatch on columns between source and target.

OGG Setup – Source (Window Server)

GGSCI (test-machine01) 27> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
EXTRACT     RUNNING     EC232       00:00:00      00:00:05
EXTRACT     RUNNING     PJ173       00:00:00      00:00:07

OGG Setup – Target (Linux Server)

GGSCI (test-machine02) 8> info RC232

REPLICAT   RC232     Last Started 2021-04-29 10:28   Status ABENDED
Checkpoint Lag       00:00:00 (updated 41:26:43 ago)
Log Read Checkpoint  File /u01/ggate/p_file_C232/ra000142
                     2021-04-26 22:15:53.533609  RBA 935235065

Step 1. Replicat Process Abended in Target: As you will notice Replicat RC232 Status is ABENDED, Check the replicate process rpt file to know the cause of ABEND. Use command show evn to get the rpt file location.

GGSCI (test-machine02) 9>
GGSCI (test-machine02) 9> show env

Parameter settings:

SET SUBDIRS    ON
SET DEBUG      OFF

Current directory: /u01/ggate/ucas

Using subdirectories for all process files

Editor:  vi

Reports (.rpt)                 /u01/ggate/ucas/dirrpt
Parameters (.prm)              /u01/ggate/ucas/dirprm
Stdout (.out)                  /u01/ggate/ucas/dirout
Replicat Checkpoints (.cpr)    /u01/ggate/ucas/dirchk
Extract Checkpoints (.cpe)     /u01/ggate/ucas/dirchk
Process Status (.pcs)          /u01/ggate/ucas/dirpcs
SQL Scripts (.sql)             /u01/ggate/ucas/dirsql
Database Definitions (.def)    /u01/ggate/ucas/dirdef
GGSCI (oda-jed-prod01) 9>

[oracle@test-machine02 dirrpt]$ cd /u01/ggate/ucas/dirrpt
[oracle@test-machine02 dirrpt]$ vi RC232.rpt
2021-04-29 10:28:09  ERROR   OGG-01161  Bad column index (77) specified for table UCASII.OP_DBL_H, max columns = 77.

Step 2. Compare the number of Columns between Source & Target Table: This error occurs when the target table doesn’t match the source table, there is a mismatch on columns between source and target. If you compare the source and target Table you will notice column CATERING_TYPE is missing in Target Table.

SOURCE
=================================================
FLT_KEY                     VARCHAR2(24)   
SERVICE_REP                 VARCHAR2(100)  
DOCUMENT                    VARCHAR2(100)  
CUSTOMER_REMARKS            VARCHAR2(250)  
SPML_REMARKS                VARCHAR2(4000) 
CATERING_TYPE               VARCHAR2(10)  

TARGET
=================================================
FLT_KEY                     VARCHAR2(24)   
SERVICE_REP                 VARCHAR2(100)  
DOCUMENT                    VARCHAR2(100)  
CUSTOMER_REMARKS            VARCHAR2(250)  
SPML_REMARKS                VARCHAR2(4000)

Step 3. Add missing Column in Target Table: Add missing column CATERING_TYPE in Target Table.

SQL>
SQL> alter table ucascai.op_dbl_h  add CATERING_TYPE               VARCHAR2(10)   ;
Table altered

Step 4. Start Replicat Process in Target: Start Replicat process using start <replicat name> command.

GGSCI (test-machine02) 13> start RC232

Sending START request to MANAGER ...
REPLICAT RC232 starting



GGSCI (test-machine02) 16> info RC232

REPLICAT   RC232     Last Started 2021-04-29 12:05   Status RUNNING
Checkpoint Lag       37:50:23 (updated 00:00:03 ago)
Log Read Checkpoint  File /u01/ggate/p_file_C232/ra000143
                     2021-04-27 22:15:11.289293  RBA 170456785

Step 5. Monitor the Replication: You can monitor the replication sync process with status and info command, With the info command, you will notice RBA (relative byte address) keeps on incrementing as transactions are applied on the target side, and with the status command, you can get the number of records of the current transactions. With the status and lag command, you will notice replicate process processed all records and we have reached trail file EOF (End of File). This indicates Source and Target are in sync now.

GGSCI (test-machine02) 16> info RC232

REPLICAT   RC232     Last Started 2021-04-29 12:05   Status RUNNING
Checkpoint Lag       37:50:23 (updated 00:00:03 ago)
Log Read Checkpoint  File /u01/ggate/p_file_C232/ra000143
                     2021-04-27 22:15:11.289293  RBA 170456785


GGSCI (test-machine02) 17>


GGSCI (test-machine02) 19> info RC232

REPLICAT   RC232     Last Started 2021-04-29 12:05   Status RUNNING
Checkpoint Lag       00:00:00 (updated 00:00:07 ago)
Log Read Checkpoint  File /u01/ggate/p_file_C232/ra000143
                     2021-04-28 22:15:54.471394  RBA 774030715

GGSCI (test-machine02) 23> send RC232 status

Sending STATUS request to REPLICAT RC232 ...
  Current status: At EOF
  Sequence #: 143
  RBA: 774030715
  0 records in current transaction

GGSCI (test-machine02) 20> lag RC232

Sending GETLAG request to REPLICAT RC232 ...
Last record lag: 49804 seconds.
At EOF, no more records to process.

Reference : OGG Replicat Abends with OGG-01161 Bad Column Index (Doc ID 1263504.1)

This document is only for learning purpose and always validate in the LAB environment first before applying in the LIVE environment.

Hope so you like this article!
Please share your valuable feedback/comments/subscribe and follow us below and don’t forget to click on the bell icon to get the most recent update. Click here to understand more about our pursuit.