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.
