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 !
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 !
