XDB upgrade from 11G TO 11.2.0.4/12.1.0.1 with Structured XMLIndex is slow (Doc ID 1684384.1)

Last updated on JULY 15, 2021

Applies to:

Symptoms

-- DBUA stuck at "Upgrading Oracle XML Database" for a prolonged period of time
-- Looking at the upgrade logs or when manually upgrading the database, the SQL command where it is stuck is similar to to the following:

INSERT /*+ APPEND */ ALL WHEN 1=1 THEN INTO <INDEX_OWNER>.<INDEX_NAME>(KEY, RID, SETTLEMENT_ID, VERSION_ID,
4SOURCE_SYSTEM, AM_SOURCE_SYSTEM_ID, AM_ID, AM_VER_ID, ST_STAT_ID,ST_MATCH_ID, ST_FAIL_ID) VALUES(T1KEY,
RID, T1COL1, T1COL2, T1COL3, T1COL4, T1COL5, T1COL6,T1COL7, T1COL8, T1COL9) SELECT /*+ NO_XMLINDEX_REWRITE */
utl_raw.concat(utl_raw.cast_to_raw(SYS_ALIAS_0.rowid),utl_raw.cast_to_raw(to_char(SYS_ALIAS_1.T1RNO, '9999999999')))
AS T1KEY, SYS_ALIAS_0.rowid AS RID, SYS_ALIAS_1.T1RNO AS T1RNO, SYS_ALIAS_1.SETTLEMENT_ID AS T1COL1,
SYS_ALIAS_1.VERSION_ID AS T1COL2, SYS_ALIAS_1.SOUR CE_SYSTEM AS T1COL3, SYS_ALIAS_1.AM_SOURCE_SYSTEM_ID AS T1COL4,
SYS_ALIAS_1.AM_ID AS T1COL5, SYS_ALIAS_1.AM_VER_ID AS T1COL6, SYS_ALIAS_1.ST_STAT_ID AS T1COL7, SYS_ALIAS_1.ST_MATCH_ID AS T1COL8,
SYS_ALIAS_1.ST_FAIL_ID AS T1COL9 FROM SCPP_ODS_DBO."SETTLEMENT" SYS_ALIAS_0, XMLTABLE('/path1/path2' PASSING SYS_ALIAS_0.SYS_NC00013$ COLUMNS
SETTLEMENT_ID varchar2(4000) PATH 'path3') SYS_ALIAS_1

-- If the following query returns rows which indicates a non-XDB structured XMLIndex along with the base table name then it would be recommended to apply the patch:

SQL> set pagesize 1000
SQL> select i.index_owner, i.index_name, i.index_type, i.table_name, i.table_owner, s.bytes
  2  from dba_segments s, dba_xml_indexes i
  3  where i.table_name = s.segment_name and i.index_type = 'STRUCTURED' and i.index_owner ! = 'XDB';

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.