max_string_size="EXTENDED" and ORA-14415: index in partially dropped state

We created the 12.1.0.2.11 non-CDB RAC DB on the Exadata.

It had worked well until we set  max_string_size="EXTENDED" and  run  utl32k.sql
according to
How to Increase the Maximum Size of VARCHAR2, NVARCHAR2, and RAW Columns in 12C Database using MAX_STRING_SIZE ? (Doc ID 1570297.1)

--------------------------------------------------------------------------------
[oracle@dc1exadbadm01 ~]$ sql

SQL*Plus: Release 12.1.0.2.0 Production on Tue Oct 20 18:05:55 2015

Copyright (c) 1982, 2014, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup upgrade
ORACLE instance started.

Total System Global Area 2.5770E+10 bytes
Fixed Size                  6870952 bytes
Variable Size            9730787416 bytes
Database Buffers         1.5972E+10 bytes
Redo Buffers               60235776 bytes
Database mounted.
Database opened.

SQL> @?/rdbms/admin/utl32k.sql

Session altered.

DOC>#######################################################################
DOC>#######################################################################
DOC>   The following statement will cause an "ORA-01722: invalid number"
DOC>   error if the database has not been opened for UPGRADE.
DOC>
DOC>   Perform a "SHUTDOWN ABORT"  and
DOC>   restart using UPGRADE.
DOC>#######################################################################
DOC>#######################################################################
DOC>#

no rows selected

DOC>#######################################################################
DOC>#######################################################################
DOC>   The following statement will cause an "ORA-01722: invalid number"
DOC>   error if the database does not have compatible >= 12.0.0
DOC>
DOC>   Set compatible >= 12.0.0 and retry.
DOC>#######################################################################
DOC>#######################################################################
DOC>#

PL/SQL procedure successfully completed.


Session altered.


2 rows updated.


Commit complete.


System altered.


PL/SQL procedure successfully completed.


Commit complete.


System altered.


Session altered.

DECLARE
*
ERROR at line 1:
ORA-14415: index in partially dropped state, submit DROP INDEX
ORA-06512: at line 121


[oracle@dc1exadbadm01 ~]$
[oracle@dc1exadbadm01 ~]$ sqlplus / as sysdba

Connected to an idle instance.

SQL> startup nomount
ORACLE instance started.

Total System Global Area 2.5770E+10 bytes
Fixed Size                  6870952 bytes
Variable Size            9730787416 bytes
Database Buffers         1.5972E+10 bytes
Redo Buffers               60235776 bytes
SQL> alter database mount;

Database altered.

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-14695: MAX_STRING_SIZE migration is incomplete
Process ID: 41850
Session ID: 282 Serial number: 37233




In "startup open" you will give the error.

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-14695: MAX_STRING_SIZE migration is incomplete
Process ID: 119999
Session ID: 282 Serial number: 61461  
So use "startup migrate":

SQL> startup migrate
--------------------------------------------------------------------------------

The solution:  drop bad indexes and run utl32k.sql again.

Bad indexes you can find via table name:
set     event '14415 trace name errorstack level 3'   
and   repeate the utl32k.sql

SQL> alter session set tracefile_identifier='14415error';
SQL> alter session set events '10046 trace name context forever, level 4';
SQL> alter session set events '14415 trace name errorstack level 3';
SQL> @?/rdbms/admin/utl32k.sql

In the trace file obtained you can find the TABLE name on which utl32k.sql make error.


I did the trace and found the error in it :

----- Error Stack Dump -----
ORA-14415: index in partially dropped state, submit DROP INDEX
----- Current SQL Statement for this session (sql_id=dz5k9x249a04x) -----
ALTER TABLE "PREODS_09_01"."LTB_OUT_SMS_V_RFH" MODIFY ("DEAL_LOAN_ID" VARCHAR2(4000 CHAR))
----- PL/SQL Stack -----
----- PL/SQL Call Stack -----
object line object
handle number name
0x63ffb88e0 121 anonymous block 
 ----- Call Stack Trace -----

and dropped the bad index :

SQL> select INDEX_OWNER,INDEX_NAME,COLUMN_LENGTH,CHAR_LENGTH from dba_ind_columns where TABLE_OWNER='PREODS_09_01' and TABLE_NAME='LTB_OUT_SMS_V_RFH' and COLUMN_NAME='DEAL_LOAN_ID';

INDEX_OWNER  INDEX_NAME             COLUMN_LENGTH CHAR_LENGTH  
------------ ---------------------- ------------- -----------
PREODS_09_01 PK_LTB_OUT_SMS_V_RFH_2          4000        4000

SQL> drop index PREODS_09_01.PK_LTB_OUT_SMS_V_RFH_2;

Index dropped.

SQL> @?/rdbms/admin/utl32k.sql


We droped indexes on this table and run utl32k.sql again.
After successful utl32k.sql we created these indexes back.

There is difficult to recognize the error in the trace.
The next obstruction was on the materialized view, but there were no clear message:

ANONYMOUS BLOCK:
library unit=63f8a20a8 line=89 opcode=219 static link=0 scope=0
FP=0x7f2e742a2e60 PC=0x62fff1bd8 Page=0 AP=(nil) ST=0x7f2e742a4598
...
7F2E742A2EE0 0008001B 00000000 525F5522 524F5045 [........"U_REPOR]
7F2E742A2EF0 222E2254 535F564D 5F504554 41574F54 [T"."MV_STEP_TOWA]
7F2E742A2F00 49225344 49544143 22224E4F 22524444 [DS"ICATION""DDR"]
7F2E742A2F10 45224F46 00002222 00000000 00000000 [FO"E""..........]

I decided to drop this MV and it was right !

Now  utl32k.sql finished without errors. 



For advanced users:

In the trace file you can find the PL/SQL block which is the core of utl32k.sql :

PARSING IN CURSOR #140460508916688 len=4473 dep=0 uid=0 oct=47 lid=0 tim=199344978636 hv=1486675569 ad='63ff20400' sqlid='6d8xyc9c9trmj'
DECLARE
  cursor candidate_columns is
    select u.name UNAME,      -- 45593 rows selected
           o.name TNAME,
           o.obj# OBJ#,
           DECODE(c.type#,
                    1, DECODE(c.charsetform, 2, 'NVARCHAR2', 'VARCHAR2'),
                   23, 'RAW') DATA_TYPE,
           c.spare3 DATA_LEN,
           decode(bitand(c.property, 8388608), 0, 'BYTE', 'CHAR') CHAR_USED,
           c.name CNAME,
           c.intcol# INTCOL#,
           decode(c.segcol#, 0, 'Y', 'N') VIRTUAL,
           decode(bitand(t.flags, 262144), 0, 'N', 'Y') MV
     from sys.tab$ t, sys.obj$ o, sys.user$ u, sys.col$ c
     where t.obj# = o.obj# and o.owner# = u.user# and c.obj# = t.obj#
       and bitand(o.flags, 196608) = 0               -- not a common object
       and (c.segcol# = 0 or                      -- virtual column
            bitand(c.property, 8388608) != 0 or   -- CHAR length semantics
            bitand(t.flags, 262144) != 0)         -- materialized view tbl
       and ( (c.type# = 1  and c.length >= 4000) or
             (c.type# = 23 and c.length >= 2000) );
  schema_table varchar2(512);         /* "SCHEMA"."TABLE" for SQL generation */
  data_type    varchar2(128);
  cur          integer;
  col_cnt      integer;
  len          integer;
  off          integer;
  discard      integer;
  str          varchar2(32767);
  strlen       integer;
  col_desc     DBMS_SQL.DESC_TAB;
  col_expr     clob;
  sqlstr       clob;
  long_chunk_sz constant int := 256;
BEGIN
  cur := DBMS_SQL.OPEN_CURSOR;
  for target in candidate_columns loop
    schema_table := DBMS_ASSERT.ENQUOTE_NAME(target.UNAME, FALSE) || '.'
                 || DBMS_ASSERT.ENQUOTE_NAME(target.TNAME, FALSE);
    IF (target.virtual = 'Y') THEN
      DBMS_SQL.PARSE(cur,
        'select deflength, default$ from col$ where obj# = :1 and name = :2',
        DBMS_SQL.NATIVE);
      DBMS_SQL.BIND_VARIABLE(cur, ':1', target.obj#);
      DBMS_SQL.BIND_VARIABLE(cur, ':2', target.cname);
      DBMS_SQL.DEFINE_COLUMN(cur, 1, len);
      DBMS_SQL.DEFINE_COLUMN_LONG(cur, 2);
      discard := DBMS_SQL.EXECUTE(cur);
      discard := DBMS_SQL.FETCH_ROWS(cur);
      DBMS_SQL.COLUMN_VALUE(cur, 1, len);
      col_expr := '';
      off := 0;
      WHILE len > 0 LOOP
        DBMS_SQL.COLUMN_VALUE_LONG(cur, 2, long_chunk_sz, off, str, strlen);
        col_expr := col_expr || str;
        off := off + strlen;
        len := len - strlen;
      END LOOP;
      /* Bug 16237862: use an alias for the column to avoid column
       * name overflow errors in dbms_sql.describe_columns.
       */
      sqlstr := 'SELECT ' || col_expr || ' expr FROM ' || schema_table;
      DBMS_SQL.PARSE(cur, sqlstr, DBMS_SQL.NATIVE);
      DBMS_SQL.DESCRIBE_COLUMNS(cur, col_cnt, col_desc);
      data_type := target.DATA_TYPE || '(' || col_desc(1).col_max_len || ')';
    ELSIF (target.mv = 'Y') THEN
      DBMS_SQL.PARSE(cur,
        'select sumtextlen, sumtext from sys.sum$ where containerobj# = :1',
        DBMS_SQL.NATIVE);
      DBMS_SQL.BIND_VARIABLE(cur, ':1', target.obj#);
      DBMS_SQL.DEFINE_COLUMN(cur, 1, len);
      DBMS_SQL.DEFINE_COLUMN_LONG(cur, 2);
      discard := DBMS_SQL.EXECUTE(cur);
      discard := DBMS_SQL.FETCH_ROWS(cur);
      DBMS_SQL.COLUMN_VALUE(cur, 1, len);
      sqlstr := '';
      off := 0;
      WHILE len > 0 LOOP
        DBMS_SQL.COLUMN_VALUE_LONG(cur, 2, long_chunk_sz, off, str, strlen);
        sqlstr := sqlstr || str;
        off := off + strlen;
        len := len - strlen;
      END LOOP;
      execute immediate
        'alter session set current_schema = '
        || DBMS_ASSERT.ENQUOTE_NAME(target.UNAME, FALSE);
      DBMS_SQL.PARSE(cur, sqlstr, DBMS_SQL.NATIVE);
      DBMS_SQL.DESCRIBE_COLUMNS(cur, col_cnt, col_desc);
      data_type := target.DATA_TYPE
                || '(' || col_desc(target.intcol#).col_max_len || ')';
      execute immediate
       'alter session set current_schema = SYS';
    ELSE
      IF (target.DATA_TYPE = 'NVARCHAR2') THEN
        data_type := target.DATA_TYPE
                  || '(' || target.DATA_LEN || ')';
      ELSE
        data_type := target.DATA_TYPE
                  || '(' || target.DATA_LEN || ' ' || target.CHAR_USED || ')';
      END IF;
    END IF;
    sqlstr := 'ALTER TABLE ' || schema_table
           || ' MODIFY (' || DBMS_ASSERT.ENQUOTE_NAME(target.CNAME, FALSE)
           || ' ' || data_type || ')';
    dbms_output.put_line(sqlstr);
    EXECUTE IMMEDIATE sqlstr;
  end loop;
  DBMS_SQL.CLOSE_CURSOR(cur);
END;

You can add DBMS_OUTPUT.PUT_LINE(sqlstr) to this block in order to find the error TABLE, before the bold lines.


Good troubleshootings to you !