首页 » GoldenGate, ORACLE 9i-23c » Oracle GoldenGate增加字段问题（上）： Extract OGG-01028 Number of digits N+ exceeds max N on column

Oracle GoldenGate增加字段问题（上）： Extract OGG-01028 Number of digits N+ exceeds max N on column

  • 2020/06/25
  • GoldenGate, ORACLE 9i-23c
  • 572 views
  • Oracle GoldenGate增加字段问题（上）： Extract OGG-01028 Number of digits N+ exceeds max N on column已关闭评论

“酒虽好，不要贪杯” !  OGG在同步效率、安全、兼容性上一致做的很出色，广泛用于数据同步，支持异构， 但是在它的使用过程中也要严格按照流程使用，如何在OGG同步的表上做DDL操作有严格流程，如果任性的使用，后期维护必将带给诸多麻烦， 这里简单记录在OGG进程正在同步事务的情况下增加字段导致的抽取进程异常终止。

ggserr.log

2020-06-23 00:26:37 INFO OGG-06507 Oracle GoldenGate Capture for Oracle, eaccta1.prm: MAP (TABLE) resolved (entry ANBOB.TAB_TEST1): table "ANBOB"."TAB_TEST1".
2020-06-23 00:26:37 INFO OGG-06509 Oracle GoldenGate Capture for Oracle, eaccta1.prm: Using the following key columns for source table ANBOB.TAB_TEST1: TARIFFPLAN_ID, PLANITEM_ID.
2020-06-23 00:26:37 ERROR OGG-01028 Oracle GoldenGate Capture for Oracle, eaccta1.prm: Formatting error on: table name ANBOB.TAB_TEST1, rowid AAInD5AGdAACB6IAAC, XID 3618.67.2080991
8, position (Seqno 210054, RBA 1845468456). Number of digits 24 exceeds max 19 on column START_CYCLE_OFFSET, value: -470000000000000000000000.
2020-06-23 00:26:37 ERROR OGG-01668 Oracle GoldenGate Capture for Oracle, eaccta1.prm: PROCESS ABENDING.
2020-06-23 00:29:48 INFO OGG-01738 Oracle GoldenGate Capture for Oracle, ext_f.prm: BOUNDED RECOVERY: CHECKPOINT: for object pool 1: p19204010_Redo_Thread_1: start=SeqNo: 210054, RBA: 174
2906384, SCN: 3897.3551655082 (16741039207594), Timestamp: 2020-06-23 00:14:03.000000, end=SeqNo: 210054, RBA: 2093275136, SCN: 3897.3553983944 (16741041536456), Timestamp: 2020-06-23 00:29:46.
000000, Thread: 1.

Note: 从上面的信息猜测看到是列长度map错位了，发生在START_CYCLE_OFFSET列，值长度超24但列精度最大19，值是已经无意义， 同时还有rowid信息。

查看表定义

SQL> @ind ANBOB.TAB_TEST1
Display indexes where table or index name matches %ANBOB.TAB_TEST1%...

TABLE_OWNER          TABLE_NAME                     INDEX_NAME                     POS# COLUMN_NAME                    DSC
-------------------- ------------------------------ ------------------------------ ---- ------------------------------ ----
ANBOB                TAB_TEST1                   INX_TAB_TEST1                        1 TARIFFPLAN_ID
                                                                                      2 PLANITEM_ID


INDEX_OWNER          TABLE_NAME                     INDEX_NAME                     IDXTYPE    UNIQ STATUS   PART TEMP 
-------------------- ------------------------------ ------------------------------ ---------- ---- -------- ---- ---- 
ANBOB                TAB_TEST1                      INX_TAB_TEST1                  NORMAL     YES  VALID    NO   N    


SQL> select START_CYCLE_OFFSET from ANBOB.TAB_TEST1 where rowid='AAInD5AGdAACB6IAAC';

START_CYCLE_OFFSET
------------------
                 0

SQL> @desc ANBOB.TAB_TEST1
           Name                            Null?    Type
           ------------------------------- -------- ----------------------------
    1      TARIFFPLAN_ID                   NOT NULL NUMBER(8)
    2      PLANITEM_ID                     NOT NULL NUMBER(8)
    3      DISC_ENTITY                     NOT NULL NUMBER(1)
...
   10      REFITEM_CYCLE_OFFSET            NOT NULL NUMBER(4)
   11      REFVALUE_CALC_TYPE              NOT NULL NUMBER(1)
   12      REFVALUE_UNIT                   NOT NULL NUMBER(2)
   13      DISC_OBJECT_TYPE                NOT NULL NUMBER(1)
   14      DISC_ITEM_CODE                  NOT NULL VARCHAR2(256)
   15      START_CYCLE_OFFSET              NOT NULL NUMBER(4)
   16      VALID_CYCLE_TYPE                NOT NULL NUMBER(1)
   17      VALID_CYCLES                    NOT NULL NUMBER(4)
   18      INUSE                           NOT NULL NUMBER(1)
   19      TARIFFPLAN_TYPE                          NUMBER(4)
   20      DISC_ITEM_TYPE                  NOT NULL NUMBER(1)
   21      FREEREFITEM_MIN_USAGE           NOT NULL NUMBER(14)
   22      NOTE                                     VARCHAR2(255)
   23      ISDISCTSPEC                              VARCHAR2(1)
   24      SPECDISCT_EXPR                           VARCHAR2(512)
...
   32      FORCE_OUTPUT_EVENT              NOT NULL NUMBER(1)
   33      DISC_SOURCE_TYPE                NOT NULL NUMBER(1)
   34      REFITEM_CYCLES                           VARCHAR2(512)
   35      G_REFITEM_CYCLES                         VARCHAR2(512)
   36      REF_ITEMADD_EXPR                         VARCHAR2(512)
   37      G_REF_ITEMADD_EXPR                       VARCHAR2(512)

SQL> @o ANBOB.TAB_TEST1

owner                     object_name                    object_type        status           OID      D_OID CREATED           LAST_DDL_TIME
------------------------- ------------------------------ ------------------ --------- ---------- ---------- ----------------- -----------------
ANBOB                      TAB_TEST1                     TABLE              VALID        1930539    2257145 20140322 22:53:59 20200623 00:30:25

DB1:/interface/ogg> ggsci -V

Oracle GoldenGate Command Interpreter for Oracle
Version 12.2.0.1.160419 23261684
AIX 6, ppc, 64bit (optimized), Oracle 11g on Jul 10 2016 22:21:03

Copyright (C) 1995, 2016, Oracle and/or its affiliates. All rights reserved.

Note:
OGG 是12.2版本， 此表问题时间点有做DDL操作。正常启进程是启不来的， 12.1前OGG有个bug　Extract falsely abends with “OGG-01028 Formatting error … Number of digits xx exceeds max xx on column” (Doc ID 1939795.1)，这个表较小，进程已经终止无法使用SKIPTRANS跳过，也无法集成模式的ignore_transaction，临时跳过此表同步启动了进程。

后面尝试再加回该表，重启EXtract进程时又发现了LONG TRANSACTION， 顺便记录下分析长事务的方法。

GGSCI (DB1) 64> stop eaccta1

Sending STOP request to EXTRACT EACCTA1 ...

STOP request pending. There are open, long-running transactions.
Before you stop Extract, make the archives containing data for those transactions available for when Extract restarts.
To force Extract to stop, use the SEND EXTRACT EACCTA1, FORCESTOP command.Oldest redo log files necessary to restart Extract are:

Redo Thread 1, Redo Log Sequence Number 210061, SCN 3897.3601449869 (16741089002381), RBA 2777865232
Redo Thread 2, Redo Log Sequence Number 241889, SCN 3897.3601322118 (16741088874630), RBA 110276624.

2020-06-23 18:23:22  WARNING OGG-01742  Command sent to EXTRACT EACCTA1 returned with an invalid response.


GGSCI (DB1) 65> send extract EACCTA1, showtrans

Sending SHOWTRANS request to EXTRACT EACCTA1 ...

Oldest redo log files necessary to restart Extract are:

Redo Thread 1, Redo Log Sequence Number 210061, SCN 3897.3601449869 (16741089002381), RBA 2777865232
Redo Thread 2, Redo Log Sequence Number 241889, SCN 3897.3601322118 (16741088874630), RBA 110276624

------------------------------------------------------------
XID:                  3325.61.6323268       
Items:                1        
Extract:              EACCTA1   
Redo Thread:          1      
Start Time:           2020-06-23:04:57:18  
SCN:                  3897.3601449869 (16741089002381)  
Redo Seq:             210061
Redo RBA:             2777865232          
Status:               Running             

------------------------------------------------------------
XID:                  4639.52.12009229      
Items:                1        
Extract:              EACCTA1   
Redo Thread:          1      
Start Time:           2020-06-23:04:57:20  
SCN:                  3897.3601459776 (16741089012288)  
Redo Seq:             210061
Redo RBA:             2779562000          
Status:               Running             
...

查看当前归档是否已删除

SQL> select thread#,max(sequence#) from v$archived_log where deleted='YES' group by thread#;

   THREAD# MAX(SEQUENCE#)
---------- --------------
         1         210084
         2         241919

是否是可以跳过空事务？使用SKIPEMPTYTRANS

XID:                  3325.61.6323268       
Items:                1        ###　not empty transaction
Extract:              EACCTA1   
Redo Thread:          1      
Start Time:           2020-06-23:04:57:18  
SCN:                  3897.3601449869 (16741089002381)  
Redo Seq:             210061
Redo RBA:             2777865232

根据XID找到数据库内的长事务

SQL> select t.xidusn||'.'||t.xidslot||'.'||xidsqn XID ,s.sid,s.status ses_state,machine,s.sql_id,start_time, username, r.name,  ubafil, ubablk, t.status tx_state, (used_ublk*p.value)/1024 blk, used_urec,decode(bitand(t.flag,power(2,7)),0, 'Normal','TX rolling') tx_state
   from v$transaction t, v$rollname r, v$session s, v$parameter p
   where xidusn=usn
   and s.saddr=t.ses_addr
   and p.name='db_block_size'
   and xidusn=3325 and xidslot=61 and xidsqn=6323268;

XID                                   SID SES_STAT MACHINE    SQL_ID          START_TIME           USERNAME    NAME                               UBAFIL     UBABLK TX_STATE                BLK  USED_UREC TX_STATE
------------------------------ ---------- -------- ---------- --------------- -------------------- ----------- ------------------------------ ---------- ---------- ---------------- ---------- ---------- ----------
3325.61.6323268                      3552 INACTIVE kinjk6                     06/23/20 04:57:17    ACCOUNT     _SYSSMU3325_2049892258$                 0          0 ACTIVE                   16          1 Normal


-- file: long_transactions.sql
-- author: weejar zhang(www.anbob.com)
-- purpose: check current long transactions
col xid for a25
col uba for a20
col machine for a15
select t.xidusn||'.'||t.xidslot||'.'||xidsqn XID ,s.sid,s.status ses_state,machine,s.sql_id,start_time, username, r.name,  ubafil||'.'||ubablk||'.'||UBAREC UBA, t.status tx_state, (used_ublk*p.value)/1024 blk, used_urec,decode(bitand(t.flag,power(2,7)),0, 'Normal','TX rolling') tx_state
   from v$transaction t, v$rollname r, v$session s, v$parameter p
   where xidusn=usn
   and s.saddr=t.ses_addr
   and p.name='db_block_size'
   order by start_time desc;

Tip:
又是dblink 引起的长事务， 1个undo block, undo block DBA 为0， 可以参考我之前的分享。

<<Lots of Long transaction caused by database link, and undo hdr show DBA for that slot is 0x00000000>>

打赏

,

对不起，这篇文章暂时关闭评论。

上一篇：

下一篇：