首页 » ORACLE, ORACLE [C]系列 » Troubleshooting 19c ORA-1: unique constraint (sys.i_indpart_bopart$) during ALTER TABLE SPLIT PARTITION

Troubleshooting 19c ORA-1: unique constraint (sys.i_indpart_bopart$) during ALTER TABLE SPLIT PARTITION

开始了19c的躺雷模式, 再次建议选择ORACLE 19C版本时安装19.11 以上RU。 最近一客户升级了19C, 本月拆分区时遇到了ora-1 内部字典表数据唯一性冲突, 下面简单记录,报错信息如下:

ALTER TABLE ANBOB.TLOG SPLIT PARTITION PART_110_MAX AT (110, TO_DATE(' 2022-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')) INTO (PARTITION PART_110_202201 ,PARTITION PART_310_MAX )
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-00001: unique constraint (SYS.I_INDPART_BOPART$) violated

分析递归SQL,当然做个10046 event trace 就可以,从报错的索引也能知道对象。 下面先看10046 trace

-- 先找exec error
ERROR #140737256599760:err=1 tim=3429453098383

--  根据cursor# 找binds
EXEC #140737256599760:c=184,e=184,p=0,cr=2,cu=7,mis=0,r=1,dep=1,og=4,plh=2739666332,tim=3429453097448
CLOSE #140737256599760:c=1,e=1,dep=1,type=3,tim=3429453097465
PARSE #140737256599760:c=4,e=3,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=2739666332,tim=3429453097475
BINDS #140737256599760:

 Bind#0
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=00 fl2=1000001 frm=00 csi=00 siz=48 off=0
  kxsbbbfp=7ffff2f4b010  bln=22  avl=03  flg=05
  value=220
 Bind#1
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=00 fl2=1000001 frm=00 csi=00 siz=0 off=24
  kxsbbbfp=7ffff2f4b028  bln=22  avl=05  flg=01
  value=5832439

-- 根据 currsor# 找sql parse
PARSING IN CURSOR #140737256599760 len=46 dep=1 uid=0 oct=6 lid=0 tim=3429453095567 hv=3952657371 ad='559374eb0' sqlid='gk5aj2zptjhyv'
update indpart$ set part# = :1 where obj# = :2
END OF STMT

Note:
可见是在update indpart$表,把part#更新为220, obj#能确认业务索引对象, 违反了indpart$的唯一约束。

SQL> @ind SYS.I_INDPART_BOPART$
Display indexes where table or index name matches %SYS.I_INDPART_BOPART$%...

TABLE_OWNER          TABLE_NAME                     INDEX_NAME                     POS# COLUMN_NAME                    DSC
-------------------- ------------------------------ ------------------------------ ---- ------------------------------ ----
SYS                  INDPART$                       I_INDPART_BOPART$                 1 BO#
                                                                                      2 PART#


INDEX_OWNER          TABLE_NAME                     INDEX_NAME                     IDXTYPE    UNIQ STATUS   PART TEMP  H     LFBLKS           NDK   NUM_ROWS       CLUF LAST_ANAL DEGREE VISIBILIT
-------------------- ------------------------------ ------------------------------ ---------- ---- -------- ---- ---- -- ---------- ------------- ---------- ---------- --------- ------ ---------
SYS                  INDPART$                       I_INDPART_BOPART$              NORMAL     YES  VALID    NO   N     1          1           332        332        170 28-DEC-21 1      VISIBLE

SQL> @desc indpart$
           Name                            Null?    Type
           ------------------------------- -------- ----------------------------
    1      OBJ#                            NOT NULL NUMBER
    2      DATAOBJ#                                 NUMBER
    3      BO#                             NOT NULL NUMBER
    4      PART#                           NOT NULL NUMBER
    5      HIBOUNDLEN                      NOT NULL NUMBER
    6      HIBOUNDVAL                               LONG
    7      FLAGS                           NOT NULL NUMBER
    8      TS#                             NOT NULL NUMBER
    9      FILE#                           NOT NULL NUMBER
   10      BLOCK#                          NOT NULL NUMBER
   11      PCTFREE$                        NOT NULL NUMBER
   12      PCTTHRES$                                NUMBER
   13      INITRANS                        NOT NULL NUMBER
   14      MAXTRANS                        NOT NULL NUMBER
   15      ANALYZETIME                              DATE
   16      SAMPLESIZE                               NUMBER
   17      ROWCNT                                   NUMBER
   18      BLEVEL                                   NUMBER
   19      LEAFCNT                                  NUMBER
   20      DISTKEY                                  NUMBER
   21      LBLKKEY                                  NUMBER
   22      DBLKKEY                                  NUMBER
   23      CLUFAC                                   NUMBER
   24      SPARE1                                   NUMBER
   25      SPARE2                                   NUMBER
   26      SPARE3                                   NUMBER
   27      INCLCOL                                  NUMBER
   28      BHIBOUNDVAL                              BLOB


-- cdpart.bsq

create table indpart$ (
  obj#        number not null,                 /* object number of partition */
  /* DO NOT CREATE INDEX ON DATAOBJ#  AS IT WILL BE UPDATED IN A SPACE
   * TRANSACTION DURING TRUNCATE */
  dataobj#             number,                   /* data layer object number */
  bo#         number not null,                /* object number of base index */
  part#       number not null,
                         /* partition number (see discussion under TABPART$) */
  hiboundlen  number not null,      /* length of high bound value expression */
  hiboundval  long ,                  /* text of high bound value expression */
...

NOTE:
内部在更新 indpart$时,因为违反了bo#, part#唯一性冲突。索引基于的对象bo#是不变的,拆分区增加的就是part#. 查询indpart$表,确实记录已存在。 正常情况下基于某个段分区local索引和段分区顺序相同的part#也相同,如表分区part# 对应的分区索引part#是相等的。 这里是因为local索引更新出现了不一致。

下面手动创建个表并还原一下问题。

  CREATE TABLE "ANBOB"."T"
   (    "ID" NUMBER(*,0),
        "CTIME" DATE,
        "NAME" VARCHAR2(100)
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
  STORAGE(
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS"
  PARTITION BY RANGE ("CTIME")
 (PARTITION "P1"  VALUES LESS THAN (TO_DATE(' 2015-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) SEGMENT CREATION DEFERRED
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
  ...
;

  CREATE INDEX "ANBOB"."T_I1" ON "ANBOB"."T" ("CTIME", "NAME") LOCAL;

SQL> select  obj#,part# ,subobject_name from indpart$ a,dba_objects b where a.obj#=b.object_id and b.object_name='T_I1' and part#>150 order by 2;

      OBJ#      PART# SUBOBJECT_NAME
---------- ---------- ------------------------------
     78804        160 P16
     78805        170 P17
     78806        180 P18
     78807        190 P19
     79152        199 P19_1
     79156        200 P19_2
     79160        201 P19_3
     79164        202 P19_4
     79168        203 P19_5
     79172        204 P19_6
     79176        205 P19_7
     79180        206 P19_8
     79184        207 P19_9
     79188        208 P19_10
     79192        209 P19_11
     79196        219 P19_12
     79200        228 P19_13
     79199        230 P20
     79112        239 P21
     79116        249 P22
     79120        259 P23
     79124        269 P24
     79128        279 P25
     79132        289 P26
     79136        300 P27
     79140        311 P28
     79144        321 P29
     79148        331 P29_1
     79147        341 P30

29 rows selected.

SQL> update indpart$ set part#=280 where obj#=79132;
1 row updated.

SQL> commit;
Commit complete.

SQL> alter system flush shared_pool;
System altered.

SQL> alter table anbob.t split partition p25 into(partition p24_1 values less than (to_date('20190401 01','yyyymmdd hh24')),partition p25);

Table altered.

SQL> select  obj#,part# ,subobject_name from indpart$ a,dba_objects b where a.obj#=b.object_id and b.object_name='T_I1' and part#>150 order by 2;

      OBJ#      PART# SUBOBJECT_NAME
---------- ---------- ------------------------------
     78804        160 P16
     78805        170 P17
     78806        180 P18
     78807        190 P19
     79152        199 P19_1
     79156        200 P19_2
     79160        201 P19_3
     79164        202 P19_4
     79168        203 P19_5
     79172        204 P19_6
     79176        205 P19_7
     79180        206 P19_8
     79184        207 P19_9
     79188        208 P19_10
     79192        209 P19_11
     79196        219 P19_12
     79200        228 P19_13
     79199        230 P20
     79112        239 P21
     79116        249 P22
     79120        259 P23
     79124        269 P24
     79206        278 P24_1
     79205        280 P25
     79132        289 P26
     79136        300 P27
     79140        311 P28
     79144        321 P29
     79148        331 P29_1
     79147        341 P30

SQL> alter table anbob.t split partition p25 into(partition p24_2 values less than (to_date('20190401 02','yyyymmdd hh24')),partition p25);
Table altered.

SQL> select  obj#,part# ,subobject_name from indpart$ a,dba_objects b where a.obj#=b.object_id and b.object_name='T_I1' and part#>150 order by 2;
      OBJ#      PART# SUBOBJECT_NAME
---------- ---------- ------------------------------
     78804        160 P16
     78805        170 P17
     78806        180 P18
     78807        190 P19
     79152        199 P19_1
     79156        200 P19_2
     79160        201 P19_3
     79164        202 P19_4
     79168        203 P19_5
     79172        204 P19_6
     79176        205 P19_7
     79180        206 P19_8
     79184        207 P19_9
     79188        208 P19_10
     79192        209 P19_11
     79196        219 P19_12
     79200        228 P19_13
     79199        230 P20
     79112        239 P21
     79116        249 P22
     79120        259 P23
     79124        269 P24
     79206        278 P24_1
     79210        279 P24_2
     79209        281 P25
     79132        289 P26
     79136        300 P27
     79140        311 P28
     79144        321 P29
     79148        331 P29_1
     79147        341 P30

31 rows selected.

SQL> update indpart$ set part#=280 where obj#=79148;

1 row updated.

SQL> commit;

Commit complete.

SQL> select  obj#,part# ,subobject_name from indpart$ a,dba_objects b where a.obj#=b.object_id and b.object_name='T_I1' and part#>150 order by 2;

      OBJ#      PART# SUBOBJECT_NAME
---------- ---------- ------------------------------
     78804        160 P16
     78805        170 P17
     78806        180 P18
     78807        190 P19
     79152        199 P19_1
     79156        200 P19_2
     79160        201 P19_3
     79164        202 P19_4
     79168        203 P19_5
     79172        204 P19_6
     79176        205 P19_7
     79180        206 P19_8
     79184        207 P19_9
     79188        208 P19_10
     79192        209 P19_11
     79196        219 P19_12
     79200        228 P19_13
     79199        230 P20
     79112        239 P21
     79116        249 P22
     79120        259 P23
     79124        269 P24
     79206        278 P24_1
     79210        279 P24_2
     79148        280 P29_1
     79209        281 P25
     79132        289 P26
     79136        300 P27
     79140        311 P28
     79144        321 P29
     79147        341 P30

31 rows selected.

SQL> alter table anbob.t split partition p25 into(partition p24_3 values less than (to_date('20190401 03','yyyymmdd hh24')),partition p25);
alter table anbob.t split partition p25 into(partition p24_3 values less than (to_date('20190401 03','yyyymmdd hh24')),partition p25)
                  *
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-00001: unique constraint (SYS.I_INDPART_BOPART$) violated


SQL> select  obj#,part# ,subobject_name from indpart$ a,dba_objects b where a.obj#=b.object_id and b.object_name='T_I1' and part#>150 order by 2;

      OBJ#      PART# SUBOBJECT_NAME
---------- ---------- ------------------------------
     78804        160 P16
     78805        170 P17
     78806        180 P18
     78807        190 P19
     79152        199 P19_1
     79156        200 P19_2
     79160        201 P19_3
     79164        202 P19_4
     79168        203 P19_5
     79172        204 P19_6
     79176        205 P19_7
     79180        206 P19_8
     79184        207 P19_9
     79188        208 P19_10
     79192        209 P19_11
     79196        219 P19_12
     79200        228 P19_13
     79199        230 P20
     79112        239 P21
     79116        249 P22
     79120        259 P23
     79124        269 P24
     79206        278 P24_1
     79210        279 P24_2
     79148        280 P29_1
     79209        281 P25
     79132        289 P26
     79136        300 P27
     79140        311 P28
     79144        321 P29
     79147        341 P30

31 rows selected.

note:
因为oracle part#增长的控制分几种情况, 前2次没有触发,下一篇再分享part#的生成方法, 第3次模拟出了问题。

下面分析问题

select i.obj#, ip.obj#, tp.phypart#, ip.phypart#
from tabpartv$ tp, indpartv$ ip, ind$ i
where ip.bo# = i.obj#
and tp.bo# = i.bo#
and tp.part# = ip.part#
  6  and tp.phypart# != ip.phypart#;

      OBJ#       OBJ#   PHYPART#   PHYPART#
---------- ---------- ---------- ----------
     78788      79148        281        280
     78788      79209        289        281
     78788      79132        300        289
     78788      79136        311        300
     78788      79140        321        311
     78788      79144        331        321

6 rows selected.

create or replace view tabpartv$
  (obj#, dataobj#, bo#, part#, hiboundlen, hiboundval, ts#, file#, block#,
   pctfree$, pctused$, initrans, maxtrans, flags, analyzetime, samplesize,
   rowcnt, blkcnt, empcnt, avgspc, chncnt, avgrln, phypart#)
as select obj#, dataobj#, bo#,
          row_number() over (partition by bo# order by part#),
          hiboundlen, hiboundval, ts#, file#, block#, pctfree$, pctused$,
          initrans, maxtrans, flags, analyzetime, samplesize, rowcnt, blkcnt,
          empcnt, avgspc, chncnt, avgrln, part#
from tabpart$
where bitand(flags, 8388608) = 0           /* filter out hidden partitions */
/

create or replace view indpartv$
  (obj#, dataobj#, bo#, part#, hiboundlen, hiboundval, flags, ts#, file#,
   block#, pctfree$, pctthres$, initrans, maxtrans, analyzetime, samplesize,
   rowcnt, blevel, leafcnt, distkey, lblkkey, dblkkey, clufac, spare1,
   spare2, spare3, inclcol, phypart#)
as select obj#, dataobj#, bo#,
          row_number() over (partition by bo# order by part#),
          hiboundlen, hiboundval, flags, ts#, file#, block#,
          pctfree$, pctthres$, initrans, maxtrans, analyzetime, samplesize,
          rowcnt, blevel, leafcnt, distkey, lblkkey, dblkkey, clufac, spare1,
          spare2, spare3, inclcol, part#
from indpart$
where bitand(flags, 8388608) = 0           /* filter out hidden partitions */
/

note:
这里用到了2个内部视图tabpartv$ 和indpartv$,也是基于tabpart$和indpart$, 列出了问题part#是从拆的分区开始到倒数第二个分区(最大分区除外).

这里能想到的有3种解决方法:
1, 把索引删除,重新创建(not rebuild)
但是有特殊情况,对于本次出问题的是个lob index(SYS_ILxxxxx), 不可以删除index。并且move lob级联rebuild lob index也无法解决。
2, 安装one-off patch
Bug 32259535 ORA-1/ORA-00001: unique constraint (sys.i_indpart_bopart$) during ALTER TABLE SPLIT PARTITION, 影响范围RU<=19.10.
3, 更新字典基表

下面用使用最快的第3种方法

-- s1
SQL> lock table tabpart$ in exclusive mode;
Table(s) Locked.

SQL> lock table partobj$ in exclusive mode;
Table(s) Locked.

-- s2
create table bakpart_byanbob(ind_obj#, ip_obj#, tbl_phypart#, idx_phypart#) as
select i.obj#, ip.obj#, tp.phypart#, ip.phypart#
from tabpartv$ tp, indpartv$ ip, ind$ i
where ip.bo# = i.obj#
and tp.bo# = i.bo#
and tp.part# = ip.part#
and tp.phypart# != ip.phypart#;
 
update indpart$ ip
set part# = (select tbl_phypart# from bakpart_byanbob
where ip.obj# = ip_obj#)
   where ip.obj# in (select ip_obj# from bakpart_byanbob);

SQL> commit;
Commit complete.

SQL> alter system flush shared_pool;
System altered.

-- s1
SQL> commit;
Commit complete.

SQL> alter table anbob.t split partition p25 into(partition p24_3 values less than (to_date('20190401 03','yyyymmdd hh24')),partition p25);
Table altered.

注意:

问题解决。

以上操作危险,未在专业人员指导下,请勿操作。

打赏

,

目前这篇文章还没有评论(Rss)

我要评论