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, 可以参考我之前的分享。
对不起,这篇文章暂时关闭评论。