重庆思庄Oracle、、PostgreSQL、Redhat认证学习论坛

 找回密码
 注册

QQ登录

只需一步,快速开始

搜索
查看: 405|回复: 0
打印 上一主题 下一主题

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

[复制链接]
跳转到指定楼层
楼主
发表于 2025-4-20 12:45:41 | 只看该作者 回帖奖励 |倒序浏览 |阅读模式
现象:
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 .

分享到:  QQ好友和群QQ好友和群 QQ空间QQ空间 腾讯微博腾讯微博 腾讯朋友腾讯朋友
收藏收藏 支持支持 反对反对
回复

使用道具 举报

您需要登录后才可以回帖 登录 | 注册

本版积分规则

QQ|手机版|小黑屋|重庆思庄Oracle、Redhat认证学习论坛 ( 渝ICP备12004239号-4 )

GMT+8, 2025-4-30 06:36 , Processed in 0.089388 second(s), 20 queries .

重庆思庄学习中心论坛-重庆思庄科技有限公司论坛

© 2001-2020

快速回复 返回顶部 返回列表