有关OGG 异构做数据初化 File to Replicat时报错ERROR OGG-01163

voctor

因本人测试OGG 异构做数据初化 File to Replicat时报错，请帮我分析原因，详细信息如：

1、        源端：IP地址：10.100.5.18
OS：centos 5.5 64位
       DB：oracle 11.2.0.3  +  11.2.1.0.1

       IP地址：10.100.5.17
       OS：centos 5.5 64位
       DB：Oracle 10.2.0.4  +  11.1.1.0.0
     
2、        目标端：10.100.5.19
         OS：centos 5.5 64位
         DB：mysql 5.1.68  +  11.2.1.0.1

3、        源初始化进程配置：
GGSCI> EDIT PARAMS eil_f2r         
SOURCEISTABLE
userid ggsuser,password ******
rmthost 10.100.5.19,mgrport 7809
RMTFILE /u01/goldengate/dirdat/ilSRC.DAT, PURGE
table MTSBW.AGENT;
table MTSBW.COLOR;
table MTSBW.FUC;
         目标端初始化进程配置：
          GGSCI> EDIT PARAMS ril_f2r
SPECIALRUN
END RUNTIME
sourcedb mtsbw@127.0.0.1,userid root,password *****
ASSUMETARGETDEFS
EXTFILE /u01/goldengate/dirdat/ilSRC.DAT
DISCARDFILE /u01/goldengate/dirrpt/ril_f2r.dsc, PURGE
map mtsbw.agent, target mtsbw.agent;
map mtsbw.color, target mtsbw.color;
map mtsbw.fuc, target mtsbw.fuc;

4、        在目标端, 开始初始化装载复制数据时
# replicat paramfile ./dirprm/ril_f2r.prm reportfile ./dirrpt/ril_f2r.rpt
        
         检查复制日志信息如：
# cat ./dirrpt/ril_f2r.rpt
……
MAP resolved (entry mtsbw.fuc):
  map "MTSBW"."FUC", target mtsbw.fuc;
Using following columns in default map by name:
  VENDER_ID, FUC_NUM, DOC_DATE, VENDEE_ID, ORD_MT_NUM, EARNEST,
  EARNEST_RATE, PUC_PROP, OPR_ID, BUYER_ID, SELLER_ID, INVSG_AT,
  INVSG_ID, TAX_RATE, CURRENCY, RLN_VAL, CNTT_QTY, CNTT_VAL,
  CNTT_EXEC_QTY, CNTT_EXEC_VAL, CNTT_DELIV_QTY, CNTT_DELIV_VAL,
  RCV_QTY, RCV_VAL, CR_QTY, CR_VAL, BREACH_QTY, BREACH_VAL,
  BREACH_QTY2, BREACH_VAL2, UPPER_LIMIT, LOWER_LIMIT, SIGN_DATE,
  FST_DELIV_DATE, LAST_DELIV_DATE, CNT_FILE, PROGRESS, CANCEL_ID,
  CANCEL_AT, CANCELLED, PRODUCT_COUNT, REMARK, REMARK2, REMARK3,
  REMARK4, REMARK5, DISP_WAREH_ID, SRC_DOC_TYPE, SRC_UNIT_ID,
  SRC_DOC_NUM, REMARK6, REMARK7, PROVIDE_TYPE, NEED_SEND, PROD_YEAR,
  PROD_SEASON, WIF_NUM, REMARK8, REMARK9, REMARK10, BRAND_ID,
  IS_PRECOMMIT, PRECOMMIT_QTY, PRECOMMIT_VAL, CNTT_FIRST_AT,
  CNTT_FIRST_INVID, CNTT_FIRST_QTY, CNTT_FIRST_VAL, CNTT_FIRST_BRE_QTY,
  CNTT_FIRST_BRE_VAL, CNTT_CURR_BRE_QTY, CNTT_CURR_BRE_VAL,
  BRE_ORD_NUMS, BRE_INV_NUMS, BRE_QTY, BRE_VAL, CANCELLED_REASON,
  IS_LOWER_ORDER, LOWER_SHOP_ID, DPS_SEND, IS_PRECOMMIT_ID,
  LOWER_FUC_NUM, IS_UPPER_FUC, IS_AUTO, UPPER_VENDER_ID,
  UPPER_WAREH_ID, AUTO_APPROVE_FLAG, BEGIN_DIST_TIME, DIST_REMARK,
  PROD_PROPERTY, NEW_FUC_NUM, APPROVED, DATA_SOURCE, LEFT_LOAN,
  DIST_STATUS, ORDER_DIST_NUMBER, FUC_TYPE, RESERV_TYPE, DIST_MSG
Using the following key columns for target table mtsbw.fuc: VENDER_ID, FUC_NUM.



Source Context :
  SourceModule            : [ggstd.conv.endian]
  SourceID                : [/scratch/aime1/adestore/views/aime1_adc4150256/oggcore/OpenSys/src/gglib/ggstd/lecnv.c]
  SourceFunction          : [convCompSQL]
  SourceLine              : [564]
  ThreadBacktrace         : [9] elements
                          : [/u01/goldengate/libgglog.so(CMessageContext::AddThreadContext()+0x26) [0x2b0b95275bc6]]
                          : [/u01/goldengate/libgglog.so(CMessageFactory::CreateMessage(CSourceContext*, unsigned int, ...)+0x366) [
0x2b0b9526f8b6]]
                          : [/u01/goldengate/libgglog.so(_MSG_ERR_MAP_COL_LENGTH_INVALID(CSourceContext*, ggs::gglib::ggapp::CQualDB
ObjName<(DBObjType)1> const&, ggs::gglib::ggapp::CDBObjName<(DBObjType)13> const&, unsigned int, unsigned int, CMessageFactory::Mess
ageDisposition)+0x83) [0x2b0b952527c3]]
                          : [replicat [0x66d5c1]]
                          : [replicat(ggConvRecLE(char*, file_def*, int, char, char)+0x93) [0x66de53]]
                          : [replicat(process_extract_loop()+0x403a) [0x56264a]]
                          : [replicat(main+0xbc4) [0x57ed64]]
                          : [/lib64/libc.so.6(__libc_start_main+0xf4) [0x3e6141d9c4]]
                          : [replicat(__gxx_personality_v0+0x282) [0x4b678a]]

2014-08-18 07:34:49  ERROR   OGG-01163  Bad column length (8) specified for column ORDER_DIST_NUMBER in table MTSBW.FUC, maximum all
owable length is 3.

***********************************************************************
*                   ** Run Time Statistics **                         *
***********************************************************************

Last record for the last committed transaction is the following:
___________________________________________________________________
Trail name :  /u01/goldengate/dirdat/ilSRC.DAT
Hdr-Ind    :     E  (x45)     Partition  :     .  (x04)
UndoFlag   :     .  (x00)     BeforeAfter:     A  (x41)
RecLength  :   242 (x00f2)    IO Time    : 2014-08-17 16:55:54.532161  
IOType     :     5  (x05)     OrigNode   :   255  (xff)
TransInd   :     .  (x03)     FormatType :     R  (x52)
SyskeyLen  :     0  (x00)     Incomplete :     .  (x00)
AuditRBA   :          0       AuditPos   : 0
Continued  :     N  (x00)     RecCount   :     1  (x01)

2014-08-17 16:55:54.532161 Insert             Len   242 RBA 1227974
Name: MTSBW.AGENT
___________________________________________________________________

Reading /u01/goldengate/dirdat/ilSRC.DAT, current RBA 1400983, 3631 records

Report at 2014-08-18 07:34:49 (activity since 2014-08-18 07:34:48)

From Table MTSBW.AGENT to mtsbw.agent:
       #                   inserts:      3531
       #                   updates:         0
       #                   deletes:         0
       #                  discards:         0
From Table MTSBW.COLOR to mtsbw.color:
       #                   inserts:       100
       #                   updates:         0
       #                   deletes:         0
       #                  discards:         0


Last log location read:
     FILE:      /u01/goldengate/dirdat/ilSRC.DAT
     RBA:       1400983
     TIMESTAMP: 2014-08-17 16:55:55.066464
     EOF:       NO
     READERR:   0


2014-08-18 07:34:49  ERROR   OGG-01668  PROCESS ABENDING.

CACHE OBJECT MANAGER statistics

CACHE MANAGER VM USAGE
vm current     =    448K   vm anon queues =    448K
vm anon in use =      0    vm file        =      0
vm used max    =    448K   ==> CACHE BALANCED

CACHE CONFIGURATION
cache size       =   2G   cache force paging = 3.41G
buffer min       =  64K   buffer highwater   =   8M
pageout eligible size =   8M


5、        对针源和目标端的表结构脚本，如：

voctor

补充一下源和目标端的表结构如：
--源端表结构：
-- Create table
create table FUC
(
  VENDER_ID          VARCHAR2(32) not null,
  FUC_NUM            VARCHAR2(15) not null,
  DOC_DATE           DATE,
  VENDEE_ID          VARCHAR2(32),
  ORD_MT_NUM         VARCHAR2(15),
  EARNEST            NUMBER(20,6),
  EARNEST_RATE       NUMBER(7,4),
  PUC_PROP           CHAR(2),
  OPR_ID             VARCHAR2(32),
  BUYER_ID           VARCHAR2(32),
  SELLER_ID          VARCHAR2(32),
  INVSG_AT           DATE,
  INVSG_ID           VARCHAR2(32),
  TAX_RATE           NUMBER(7,4),
  CURRENCY           VARCHAR2(5),
  RLN_VAL            NUMBER(20,6),
  CNTT_QTY           NUMBER(15,6),
  CNTT_VAL           NUMBER(20,6),
  CNTT_EXEC_QTY      NUMBER(15,6),
  CNTT_EXEC_VAL      NUMBER(20,6),
  CNTT_DELIV_QTY     NUMBER(15,6),
  CNTT_DELIV_VAL     NUMBER(20,6),
  RCV_QTY            NUMBER(15,6),
  RCV_VAL            NUMBER(20,6),
  CR_QTY             NUMBER(15,6),
  CR_VAL             NUMBER(20,6),
  BREACH_QTY         NUMBER(15,6),
  BREACH_VAL         NUMBER(20,6),
  BREACH_QTY2        NUMBER(20,6),
  BREACH_VAL2        NUMBER(15,6),
  UPPER_LIMIT        NUMBER(7,4),
  LOWER_LIMIT        NUMBER(7,4),
  SIGN_DATE          DATE,
  FST_DELIV_DATE     DATE,
  LAST_DELIV_DATE    DATE,
  CNT_FILE           BLOB,
  PROGRESS           CHAR(2),
  CANCEL_ID          VARCHAR2(32),
  CANCEL_AT          DATE,
  CANCELLED          CHAR(1),
  PRODUCT_COUNT      NUMBER(15,6),
  REMARK             VARCHAR2(250),
  REMARK2            VARCHAR2(250),
  REMARK3            VARCHAR2(250),
  REMARK4            NUMBER(15,6),
  REMARK5            NUMBER(15,6),
  DISP_WAREH_ID      VARCHAR2(32),
  SRC_DOC_TYPE       VARCHAR2(5),
  SRC_UNIT_ID        VARCHAR2(32),
  SRC_DOC_NUM        VARCHAR2(15),
  REMARK6            VARCHAR2(250),
  REMARK7            VARCHAR2(250),
  PROVIDE_TYPE       VARCHAR2(32),
  NEED_SEND          VARCHAR2(2) default '10',
  PROD_YEAR          VARCHAR2(8),
  PROD_SEASON        VARCHAR2(15),
  WIF_NUM            VARCHAR2(32),
  REMARK8            VARCHAR2(100),
  REMARK9            VARCHAR2(100),
  REMARK10           NUMBER(15,6),
  BRAND_ID           VARCHAR2(10),
  IS_PRECOMMIT       CHAR(1),
  PRECOMMIT_QTY      NUMBER(20,6),
  PRECOMMIT_VAL      NUMBER(15,6),
  CNTT_FIRST_AT      DATE,
  CNTT_FIRST_INVID   VARCHAR2(32),
  CNTT_FIRST_QTY     NUMBER(20,6),
  CNTT_FIRST_VAL     NUMBER(15,6),
  CNTT_FIRST_BRE_QTY NUMBER(20,6),
  CNTT_FIRST_BRE_VAL NUMBER(15,6),
  CNTT_CURR_BRE_QTY  NUMBER(20,6),
  CNTT_CURR_BRE_VAL  NUMBER(15,6),
  BRE_ORD_NUMS       NUMBER(20,6),
  BRE_INV_NUMS       NUMBER(20,6),
  BRE_QTY            NUMBER(20,6),
  BRE_VAL            NUMBER(15,6),
  CANCELLED_REASON   VARCHAR2(2),
  IS_LOWER_ORDER     CHAR(1),
  LOWER_SHOP_ID      VARCHAR2(32),
  DPS_SEND           VARCHAR2(2),
  IS_PRECOMMIT_ID    VARCHAR2(32),
  LOWER_FUC_NUM      VARCHAR2(15),
  IS_UPPER_FUC       CHAR(1),
  IS_AUTO            CHAR(1),
  UPPER_VENDER_ID    VARCHAR2(32),
  UPPER_WAREH_ID     VARCHAR2(32),
  AUTO_APPROVE_FLAG  CHAR(2),
  BEGIN_DIST_TIME    TIMESTAMP(6),
  DIST_REMARK        VARCHAR2(225),
  PROD_PROPERTY      VARCHAR2(2),
  NEW_FUC_NUM        VARCHAR2(16),
  APPROVED           VARCHAR2(2),
  DATA_SOURCE        VARCHAR2(2),
  LEFT_LOAN          NUMBER(25,6),
  DIST_STATUS        VARCHAR2(4) default 'IN',
  ORDER_DIST_NUMBER  NUMBER default 0,
  FUC_TYPE           VARCHAR2(3),
  RESERV_TYPE        VARCHAR2(3),
  DIST_MSG           VARCHAR2(255)
)
tablespace USERS
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 16
    minextents 1
    maxextents unlimited
  );
-- Create/Recreate primary, unique and foreign key constraints
alter table FUC
  add constraint PK_FUC8 primary key (VENDER_ID, FUC_NUM)
  using index
  tablespace INDX
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 1M
    minextents 1
    maxextents unlimited
  );
-- Create/Recreate check constraints
alter table FUC
  add constraint CKC_CANCELLED_FUC8
  check (CANCELLED is null or ( CANCELLED in ('T','F') ));
-- Grant/Revoke object privileges






--目标端表结构：

/*Table structure for table `fuc` */

CREATE TABLE `fuc` (
  `VENDER_ID` varchar(32) NOT NULL,
  `FUC_NUM` varchar(15) NOT NULL,
  `DOC_DATE` date DEFAULT NULL,
  `VENDEE_ID` varchar(32) DEFAULT NULL,
  `ORD_MT_NUM` varchar(15) DEFAULT NULL,
  `EARNEST` decimal(20,6) DEFAULT NULL,
  `EARNEST_RATE` decimal(7,4) DEFAULT NULL,
  `PUC_PROP` char(2) DEFAULT NULL,
  `OPR_ID` varchar(32) DEFAULT NULL,
  `BUYER_ID` varchar(32) DEFAULT NULL,
  `SELLER_ID` varchar(32) DEFAULT NULL,
  `INVSG_AT` datetime DEFAULT NULL,
  `INVSG_ID` varchar(32) DEFAULT NULL,
  `TAX_RATE` decimal(7,4) DEFAULT NULL,
  `CURRENCY` varchar(5) DEFAULT NULL,
  `RLN_VAL` decimal(20,6) DEFAULT NULL,
  `CNTT_QTY` decimal(15,6) DEFAULT NULL,
  `CNTT_VAL` decimal(20,6) DEFAULT NULL,
  `CNTT_EXEC_QTY` decimal(15,6) DEFAULT NULL,
  `CNTT_EXEC_VAL` decimal(20,6) DEFAULT NULL,
  `CNTT_DELIV_QTY` decimal(15,6) DEFAULT NULL,
  `CNTT_DELIV_VAL` decimal(20,6) DEFAULT NULL,
  `RCV_QTY` decimal(15,6) DEFAULT NULL,
  `RCV_VAL` decimal(20,6) DEFAULT NULL,
  `CR_QTY` decimal(15,6) DEFAULT NULL,
  `CR_VAL` decimal(20,6) DEFAULT NULL,
  `BREACH_QTY` decimal(15,6) DEFAULT NULL,
  `BREACH_VAL` decimal(20,6) DEFAULT NULL,
  `BREACH_QTY2` decimal(20,6) DEFAULT NULL,
  `BREACH_VAL2` decimal(15,6) DEFAULT NULL,
  `UPPER_LIMIT` decimal(7,4) DEFAULT NULL,
  `LOWER_LIMIT` decimal(7,4) DEFAULT NULL,
  `SIGN_DATE` date DEFAULT NULL,
  `FST_DELIV_DATE` datetime DEFAULT NULL,
  `LAST_DELIV_DATE` datetime DEFAULT NULL,
  `CNT_FILE` longblob,
  `PROGRESS` char(2) DEFAULT NULL,
  `CANCEL_ID` varchar(32) DEFAULT NULL,
  `CANCEL_AT` datetime DEFAULT NULL,
  `CANCELLED` char(1) DEFAULT NULL,
  `PRODUCT_COUNT` decimal(15,6) DEFAULT NULL,
  `REMARK` varchar(250) DEFAULT NULL,
  `REMARK2` varchar(250) DEFAULT NULL,
  `REMARK3` varchar(250) DEFAULT NULL,
  `REMARK4` decimal(15,6) DEFAULT NULL,
  `REMARK5` decimal(15,6) DEFAULT NULL,
  `DISP_WAREH_ID` varchar(32) DEFAULT NULL,
  `SRC_DOC_TYPE` varchar(5) DEFAULT NULL,
  `SRC_UNIT_ID` varchar(32) DEFAULT NULL,
  `SRC_DOC_NUM` varchar(15) DEFAULT NULL,
  `REMARK6` varchar(250) DEFAULT NULL,
  `REMARK7` varchar(250) DEFAULT NULL,
  `PROVIDE_TYPE` varchar(32) DEFAULT NULL,
  `NEED_SEND` varchar(2) DEFAULT '10',
  `PROD_YEAR` varchar(8) DEFAULT NULL,
  `PROD_SEASON` varchar(15) DEFAULT NULL,
  `WIF_NUM` varchar(32) DEFAULT NULL,
  `REMARK8` varchar(100) DEFAULT NULL,
  `REMARK9` varchar(100) DEFAULT NULL,
  `REMARK10` decimal(15,6) DEFAULT NULL,
  `BRAND_ID` varchar(10) DEFAULT NULL,
  `IS_PRECOMMIT` char(1) DEFAULT NULL,
  `PRECOMMIT_QTY` decimal(20,6) DEFAULT NULL,
  `PRECOMMIT_VAL` decimal(15,6) DEFAULT NULL,
  `CNTT_FIRST_AT` datetime DEFAULT NULL,
  `CNTT_FIRST_INVID` varchar(32) DEFAULT NULL,
  `CNTT_FIRST_QTY` decimal(20,6) DEFAULT NULL,
  `CNTT_FIRST_VAL` decimal(15,6) DEFAULT NULL,
  `CNTT_FIRST_BRE_QTY` decimal(20,6) DEFAULT NULL,
  `CNTT_FIRST_BRE_VAL` decimal(15,6) DEFAULT NULL,
  `CNTT_CURR_BRE_QTY` decimal(20,6) DEFAULT NULL,
  `CNTT_CURR_BRE_VAL` decimal(15,6) DEFAULT NULL,
  `BRE_ORD_NUMS` decimal(20,6) DEFAULT NULL,
  `BRE_INV_NUMS` decimal(20,6) DEFAULT NULL,
  `BRE_QTY` decimal(20,6) DEFAULT NULL,
  `BRE_VAL` decimal(15,6) DEFAULT NULL,
  `CANCELLED_REASON` varchar(2) DEFAULT NULL,
  `IS_LOWER_ORDER` char(1) DEFAULT NULL,
  `LOWER_SHOP_ID` varchar(32) DEFAULT NULL,
  `DPS_SEND` varchar(2) DEFAULT NULL,
  `IS_PRECOMMIT_ID` varchar(32) DEFAULT NULL,
  `LOWER_FUC_NUM` varchar(15) DEFAULT NULL,
  `IS_UPPER_FUC` char(1) DEFAULT NULL,
  `IS_AUTO` char(1) DEFAULT NULL,
  `UPPER_VENDER_ID` varchar(32) DEFAULT NULL,
  `UPPER_WAREH_ID` varchar(32) DEFAULT NULL,
  `AUTO_APPROVE_FLAG` char(2) DEFAULT NULL,
  `BEGIN_DIST_TIME` datetime DEFAULT NULL,
  `DIST_REMARK` varchar(225) DEFAULT NULL,
  `PROD_PROPERTY` varchar(2) DEFAULT NULL,
  `NEW_FUC_NUM` varchar(16) DEFAULT NULL,
  `APPROVED` varchar(2) DEFAULT NULL,
  `DATA_SOURCE` varchar(2) DEFAULT NULL,
  `LEFT_LOAN` decimal(25,6) DEFAULT NULL,
  `DIST_STATUS` varchar(4) DEFAULT 'IN',
  `ORDER_DIST_NUMBER` double DEFAULT '0',
  `FUC_TYPE` varchar(3) DEFAULT NULL,
  `RESERV_TYPE` varchar(3) DEFAULT NULL,
  `DIST_MSG` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`VENDER_ID`,`FUC_NUM`),
  KEY `IDX_FUC` (`VENDEE_ID`,`REMARK2`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

dba_sky

从错误信息看，是ORDER_DIST_NUMBER 列从oracle的number类型转换到mysql的double类型长度有问题。

voctor

从错误信息看，oracle 源端 number 类型转换到Mysql的 double类型后。经过mysql 端的 double类型长度有关；但是，double字段值调整以及变更新float或int 字段类型也是不行的。

Maclean Liu(刘相兵

ODM FINDING:

What Causes the GGS ERROR 160 Bad Column Index(xxxx) Error in Replicat

APPLIES TO:

Oracle GoldenGate - Version 4.0.0 and later
Information in this document applies to any platform.
***Checked for relevance on 10-Jun-2013***
GOAL

What causes the GGS ERROR 160  Bad Column Index(xxxx) error in Oracle GoldenGate (OGG) Replicat?

SOLUTION

There are some potential reasons and workarounds for this error:

1) Column mismatch between source and target

The GGS ERROR 160 Bad Column Index(xxxx) error in Replicat is caused by a Source Column Index "xxxx" greater than the number of columns in the Source Table Definition File Input to the Replicat process or if the ASSUMETARGETDEFS parameter is used and the Source Table and Target Table do not have the same structure, the Source Table has more columns than the Target Table.

Example
GGS ERROR 160 Bad column index(129) specified for table {table name}, max columns = 127

Explanation
The source table trail record has an index and data for column number 129 but only 127 columns are defined in the Source Table Definition File or when the ASSUMETARGETDEFS parameter is used in the Replicat Parameter File the Target Table contains 127 columns.

This is generally caused by changes in the Source Table or Target Table(i.e. columns have been added or deleted and a new Source Definition File has not been created to reflect the Source Table structure to match the Trail Records that Replicat is trying to process.

To resolve this error, run DEFGEN on the Source System for the Table causing the Replicat abend, copy that Definition File to the Target system.  Add this SOURCEDEFS file to the Replicat Parameter file and restart the Replicat process.

Note:  This applies to all Open Systems platforms except z/OS(IBM mainframe)


2) Encryption and decrypttrail

Check if the trail source is encrypted and if it is, then add the decrypttail parameter to the Replicat to eliminate the error.
If the extract does not use encryption, and the replicat has decrypttrail enabled, replicat will read an un-encrypted number and translate it to a column index that does not conform to the proper index number. In that case remove the decrypttail parameter from the Replicat.



There are different issues reported via GGS ERROR 160, for those check the error description (e.g. "Bad column length" and follow the related KM note in the reference section






尝试使用defgen 呢？