19.x:Database upgrade failed with ORA-20000: Statistics collection failed for...

刘泽宇

现象：
Production Oracle database upgrade from 12c to 19c failed with below errors

oracle_server.log

ERRORS FOUND: During Upgrade
FILENAME: /u01/app/oracle/cfgtoollogs/dbua/upgrade2025-02-01_02-34-07PM/<ORACLE_SID>/catupgrd0.log AT LINE NUMBER: 750993
------------------------------------------------------
Identifier CATPROC 25-02-01 02:49:18
SCRIPT = [/u01/app/oracle/product/19.24.0.0/rdbms/admin/execlmnrstats.sql]
ERROR = [ORA-20000: Statistics collection failed for 36 objects in the schema ORA-06512: at "SYS.DBMS_STATS", line 42300
ORA-06512: at "SYS.DBMS_STATS", line 42213
ORA-06512: at "SYS.DBMS_STATS", line 42181
ORA-06512: at "SYS.DBMS_STATS", line 42286
ORA-06512: at line 18
]
STATEMENT = [DECLARE
cursor table_name_cursor is
select x.name table_name
from sys.x$krvxdta x
where bitand(x.flags, 12) != 0;
|
end loop;
DBMS_STATS.GATHER_SCHEMA_STATS(OWNNAME=>'SYSTEM', objlist=>obj_lst, obj_filter_list=>filter_lst);
END;]
------------------------------------------------------
------------------------------------------------------
Identifier CATPROC 25-02-01 02:49:18
SCRIPT = [/u01/app/oracle/product/19.24.0.0/rdbms/admin/execlmnrstats.sql]
ERROR = [ORA-06512: at "SYS.DBMS_STATS", line 42300 ORA-06512: at "SYS.DBMS_STATS", line 42213
ORA-06512: at "SYS.DBMS_STATS", line 42181
ORA-06512: at "SYS.DBMS_STATS", line 42286
ORA-06512: at line 18
]

catupgrd0.log :

09:28:34 SQL> DECLARE
09:28:34 2 cursor table_name_cursor is
09:28:34 3 select x.name table_name
09:28:34 4 from sys.x$krvxdta x
09:28:34 5 where bitand(x.flags, 12) != 0;
09:28:34 6 filter_lst DBMS_STATS.OBJECTTAB := DBMS_STATS.OBJECTTAB();
09:28:34 7 obj_lst DBMS_STATS.OBJECTTAB := DBMS_STATS.OBJECTTAB();
09:28:34 8 ind number := 1;
09:28:34 9 BEGIN
09:28:34 10 for rec in table_name_cursor loop
09:28:34 11 begin
09:28:34 12 filter_lst.extend(1);
09:28:34 13 filter_lst(ind).ownname := 'SYSTEM';
09:28:34 14 filter_lst(ind).objname := 'LOGMNR_'|| rec.table_name||'';
09:28:34 15 ind := ind + 1;
09:28:34 16 end;
09:28:34 17 end loop;
09:28:34 18 DBMS_STATS.GATHER_SCHEMA_STATS(OWNNAME=>'SYSTEM', objlist=>obj_lst, obj_filter_list=>filter_lst);
09:28:34 19 END;
09:28:34 20 /
DECLARE
*
ERROR at line 1:
ORA-20000: Statistics collection failed for 36 objects in the schema
ORA-06512: at "SYS.DBMS_STATS", line 42300
ORA-06512: at "SYS.DBMS_STATS", line 42213
ORA-06512: at "SYS.DBMS_STATS", line 42181
ORA-06512: at "SYS.DBMS_STATS", line 42286
ORA-06512: at line 18
|
|
Component Current Full Elapsed Time
Name Status Version HH:MM:SS
Oracle Server
ORA-20000: Statistics collection failed for 36 objects in the schema ORA-0651
2: at "SYS.DBMS_STATS", line 42300
ORA-06512: at "SYS.DBMS_STATS", line 42213
|
|
ORA-06512: at "SYS.DBMS_STATS", line 42286
ORA-06512: at line 18
Oracle Server INVALID 19.24.0.0.0 00:07:34
Oracle Workspace Manager UPGRADED 19.24.0.0.0 00:00:26



原因：
Collected 10046 trace and identified issue was with index which were in unusable state .

<error>ORA-01502: index 'SYS.I_WRI$_OPTSTAT_SYNOPHEAD' or partition of such index is in unusable state</error>"
Bind#4

|

Line 11530: value="<error>ORA-01502: index 'SYS.I_WRI$_OPTSTAT_SYNOPHEAD' or partition of such index is in unusable state</error>"
Line 14447: value="<error>ORA-01502: index 'SYS.I_WRI$_OPTSTAT_SYNOPHEAD' or partition of such index is in unusable state</error>"
Line 15734: value="<error>ORA-01502: index 'SYS.I_WRI$_OPTSTAT_SYNOPHEAD' or partition of such index is in unusable state</error>"



处理方法：
a) Rebuild index which is in unusable state .
example :-
SQL> alter index SYS.I_WRI$_OPTSTAT_SYNOPHEAD rebuild online;

b) Re-run the upgrade
cd $ORACLE_HOME/bin
./dbupgrade

Note : If dbua is used to upgrade the database ,restore database back to previous release .Verify and fix unusable Index prior to rerun of the upgrade .