在一次升级12c的过程中，由于之前有些问题，每修复一个问题，就需要重新执行一次升级脚本。由于重复多次执行了升级脚本，当再次执行升级脚本到phase 65的时候，dbms_stats_internal这个包创建不了了。
我们在$ORACLE_HOME/diagnostics/catupgrd0.log的日志中可以看到：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 |
…… 18:26:17 SQL> create or replace package dbms_stats_internal wrapped 18:26:17 2 a000000 18:26:17 3 1 18:26:17 4 abcd 18:26:17 5 abcd 18:26:17 6 abcd 18:26:17 7 abcd 18:26:17 8 abcd 18:26:17 9 abcd 18:26:17 10 abcd 18:26:17 11 abcd 18:26:17 12 abcd 18:26:17 13 abcd 18:26:17 14 abcd 18:26:17 15 abcd 18:26:17 16 abcd 18:26:17 17 abcd 18:26:17 18 abcd 18:26:17 19 9 18:26:17 20 18cc6 5c16 18:26:17 21 cxYivjWOIihPTnK17IB9s8TlEHAwg80Q9rcFYPGPEAD5noqT/S6D9a63VfFDjQXzyqbxadam 18:26:17 22 RuGjVbxThDB+zaP9h9kXocTE806GlgNBkwIV1lViBez3YO+2ZzzVD42NjY1cQLNcUwysLLWS ... 18:26:17 338 Hvuy9wx/RPMZ5z3lt2i/H72Ir7v//gQkqCKk9Y40jUf25YSI8OrzMjeV0NJSbRwOx1COHN2x 18:26:17 339 fU18+TOFe1vOHLb3cOykfEMri99rfLokd0ADt6dnBFQwMR0k9iru7DaXITL9Zcfw9M5VwGSQ 18:26:17 340 vtNnusA7E+QUULCkUFe96EH0b2bMflyGSLpmR6DHY8ZVSxQGjGmdEykJQf1MQq5OdXCSjbvv 18:26:17 341 lr4cPr4zxV7VHQUd1PqvoKXmKYhJx9Rb42gDKjR85/W8tqRorRxAZig8EiusacGJBau5eN+u 18:26:17 342 3LZfbMPlVCyWSDTG9BMLFzKAXuvoua9ofUr0ZchmMraMP391G0fcwK8BHL8Ki9zA8PhAsqMI 18:26:17 343 oXoYgq2JZo+wLYhrCDKcblDtTzGi/zwWevBuqCNN7+b5Dos2dwrGrU6KnXOq/ie4ng== 18:26:17 344 18:26:17 345 / Warning: Package created with compilation errors. Elapsed: 00:00:00.50 18:26:18 SQL> show errors Errors for PACKAGE DBMS_STATS_INTERNAL: LINE/COL ERROR -------- ----------------------------------------------------------------- 0/0 ORA-21700: object does not exist or is marked for delete 18:26:18 SQL> …… |
报错是ora-21700，这是一个非常头疼的报错。这个报错常常意味着数据字典不一致，需要手动修复。
在Doc ID 2046611.1中提到，可以通过drop+重建解决。
|
1 2 3 4 5 6 7 8 |
sqlplus `/ as sysdba' alter session set container = CUSTPDB; exec dbms_pdb.exec_as_oracle_script('drop package dbms_stats'); exec dbms_pdb.exec_as_oracle_script('drop package dbms_stats_internal'); @?/rdbms/admin/dbmsstat.sql @?/rdbms/admin/prvtstas.plb @?/rdbms/admin/prvtstat.plb @?/rdbms/admin/prvtstai.plb |
文档说的是PDB，但是我估计non-cdb的数据库应该也可以类似解决，所以我决定采用：
|
1 2 3 4 5 6 |
drop package dbms_stats; drop package dbms_stats_internal; @?/rdbms/admin/dbmsstat.sql @?/rdbms/admin/prvtstas.plb @?/rdbms/admin/prvtstat.plb @?/rdbms/admin/prvtstai.plb |
但是发现drop dbms_stats_internal的时候，也报错ora-21700了：
|
1 2 3 4 5 6 7 8 9 |
SQL> drop package dbms_stats; Package dropped. SQL> drop package dbms_stats_internal; drop package dbms_stats_internal * ERROR at line 1: ORA-21700: object does not exist or is marked for delete |
看来只能手工修复数据字典了。
对drop dbms_stats_internal的过程做了10046，发现是在drop 一个type的时候遭遇ora-21700的：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
PARSING IN CURSOR #18446744071425138736 len=32 dep=0 uid=0 oct=96 lid=0 tim=7819265336298 hv=3484713138 ad='ffffffff77d1fdf0' sqlid='apvgpzm7v905k' drop package dbms_stats_internal END OF STMT PARSE #18446744071425138736:c=2652,e=2653,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=0,tim=7819265336295 …… PARSING IN CURSOR #18446744071423920880 len=47 dep=1 uid=0 oct=78 lid=0 tim=7819266312750 hv=366365679 ad='ffffffff77d1f9f8' sqlid='41x6bchaxckzg' drop type "SYS".SYS_PLSQL_15AD49DF_1971_1 force END OF STMT PARSE #18446744071423920880:c=1771,e=1771,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,plh=0,tim=7819266312747 BINDS #18446744071455720616: Bind#0 …… WAIT #18446744071423918224: nam='db file sequential read' ela= 1894 file#=1 block#=104924 blocks=1 obj#=338 tim=7819266321638 EXEC #18446744071423920880:c=7572,e=9191,p=1,cr=21,cu=0,mis=0,r=0,dep=1,og=4,plh=0,tim=7819266322114 ERROR #18446744071423920880:err=21700 tim=7819266322144 CLOSE #18446744071423920880:c=12,e=12,dep=1,type=0,tim=7819266322482 EXEC #18446744071425138736:c=291884,e=984932,p=504,cr=1760,cu=0,mis=0,r=0,dep=0,og=1,plh=0,tim=7819266322568 ERROR #18446744071425138736:err=21700 tim=7819266322612 …… |
查了一下这个type：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
SQL> select object_id from dba_objects where owner='SYS_PLSQL_15AD49DF_1971_1'; no rows selected SQL> SQL> select name ,obj# from obj$ where name='SYS_PLSQL_15AD49DF_1971_1' SQL> / NAME OBJ# -------------------------------------------------------------------------------- ---------- SYS_PLSQL_15AD49DF_1971_1 429029 SQL> --这里已经看到dba_objects和obj$出现不一致。一个没记录一个有记录。 |
我们开始手动修复数据字典。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 |
--这里开始第一轮修复数据字典的过程： SQL> set transaction use rollback segment system; Transaction set. SQL> delete from type$ where tvoid=(select oid$ from obj$ where obj# = 429029); 0 rows deleted. SQL> delete from type_misc$ where obj#=429029; 1 row deleted. SQL> delete from source$ where obj#=429029; 2 rows deleted. SQL> delete from idl_ub1$ where obj#=429029; 3 rows deleted. SQL> delete from idl_char$ where obj#=429029; 1 row deleted. SQL> delete from idl_ub2$ where obj#=429029; 2 rows deleted. SQL> delete from idl_sb4$ where obj#=429029; 3 rows deleted. SQL> delete from settings$ where obj# = 429029; 8 rows deleted. SQL> delete from dependency$ where d_obj#=429029; 1 row deleted. SQL> delete from oid$ where oid$=(select oid$ from obj$ where obj# = 429029); 0 rows deleted. SQL> delete from obj$ where obj# = 429029; 1 row deleted. SQL> commit; Commit complete. SQL> SQL> shutdown abort ORACLE instance shut down. SQL> exit Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options z4cs2011:t2gpolp > cd $ORACLE_HOME/rdbms/admin z4cs2011:t2gpolp > sqlplus "/ as sysdba" SQL*Plus: Release 12.1.0.2.0 Production on Fri Sep 30 15:09:57 2016 Copyright (c) 1982, 2014, Oracle. All rights reserved. Connected to an idle instance. SQL> startup UPGRADE ORACLE instance started. Total System Global Area 4211081216 bytes Fixed Size 6067648 bytes Variable Size 3640658496 bytes Database Buffers 536870912 bytes Redo Buffers 27484160 bytes Database mounted. Database opened. SQL> SQL> SQL> --第一轮修复数据字典过程完毕 |
修复完成后，再次尝试dbms_stats_internal，还是报错ora-21700。继续做10046的trace，发现还有另外的一个type SYS_PLSQL_15AD49DF_1984_1，顺便也查了一下，类似的type有几个，发现如下：
|
1 2 3 4 5 6 7 8 9 10 |
SQL > select name,obj# from obj$ where name like 'SYS_PLSQL_15AD49DF%' NAME OBJ# ------------------------------------------------ ---------- SYS_PLSQL_15AD49DF_1984_1 429030 SYS_PLSQL_15AD49DF_4613_1 429026 SYS_PLSQL_15AD49DF_4641_1 429028 SYS_PLSQL_15AD49DF_DUMMY_1 429027 SQL> |
虽然我很想一次性的删除所有的这些type，但是我还是谨慎点，用删除dbms_stats_internal，如果遇到报错，再10046，再找到对应的type，再修复数据字典。
最后的结果是，这些type都需要删除，所以我一共做了5轮数据字典的手工修复。
修复完成后，可以顺利drop dbms_stats_internal了，可以重建dbms_stats了。重建后dbms_stats_internal状态为valid。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 |
SQL> shutdown abort ORACLE instance shut down. SQL> startup UPGRADE ORACLE instance started. Total System Global Area 4211081216 bytes Fixed Size 6067648 bytes Variable Size 3640658496 bytes Database Buffers 536870912 bytes Redo Buffers 27484160 bytes Database mounted. Database opened. SQL> drop package dbms_stats; Package dropped. SQL> drop package dbms_stats_internal; Package dropped. SQL> @?/rdbms/admin/dbmsstat.sql Session altered. Package created. No errors. Synonym created. Grant succeeded. Role created. Grant succeeded. Grant succeeded. Grant succeeded. Role created. Grant succeeded. Grant succeeded. Library created. Session altered. SQL> SQL> @?/rdbms/admin/prvtstas.plb Session altered. Type created. Synonym created. Grant succeeded. Type created. No errors. Synonym created. Synonym created. Type created. Synonym created. Synonym created. Type dropped. Type created. No errors. Synonym created. Synonym created. Type created. Synonym created. Synonym created. Type created. Synonym created. Synonym created. No errors. Type created. No errors. Synonym created. Synonym created. Type created. Synonym created. Synonym created. Type created. No errors. Synonym created. Synonym created. Type created. Synonym created. Synonym created. No errors. Package created. No errors. Session altered. SQL> SQL> @?/rdbms/admin/prvtstat.plb Session altered. Type body created. No errors. Package body created. No errors. Session altered. SQL> SQL> @?/rdbms/admin/prvtstai.plb Session altered. Package body created. No errors. Session altered. SQL> |
|
1 2 3 4 5 6 7 8 |
SQL> select status from dba_objects where object_name='DBMS_STATS_INTERNAL'; STATUS ------- VALID VALID SQL> |
参考：Doc ID 437558.1 INTERNAL