oracle修复数据字典,修复ora-21700数据字典错误

在一次升级12c的过程中，由于之前有些问题，每修复一个问题，就需要重新执行一次升级脚本。由于重复多次执行了升级脚本，当再次执行升级脚本到phase 65的时候，dbms_stats_internal这个包创建不了了。

我们在$ORACLE_HOME/diagnostics/catupgrd0.log的日志中可以看到：

……

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>

……

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:17SQL>createorreplacepackagedbms_stats_internalwrapped

18:26:172a000000

18:26:1731

18:26:174abcd

18:26:175abcd

18:26:176abcd

18:26:177abcd

18:26:178abcd

18:26:179abcd

18:26:1710abcd

18:26:1711abcd

18:26:1712abcd

18:26:1713abcd

18:26:1714abcd

18:26:1715abcd

18:26:1716abcd

18:26:1717abcd

18:26:1718abcd

18:26:17199

18:26:172018cc65c16

18:26:1721cxYivjWOIihPTnK17IB9s8TlEHAwg80Q9rcFYPGPEAD5noqT/S6D9a63VfFDjQXzyqbxadam

18:26:1722RuGjVbxThDB+zaP9h9kXocTE806GlgNBkwIV1lViBez3YO+2ZzzVD42NjY1cQLNcUwysLLWS

...

18:26:17338Hvuy9wx/RPMZ5z3lt2i/H72Ir7v//gQkqCKk9Y40jUf25YSI8OrzMjeV0NJSbRwOx1COHN2x

18:26:17339fU18+TOFe1vOHLb3cOykfEMri99rfLokd0ADt6dnBFQwMR0k9iru7DaXITL9Zcfw9M5VwGSQ

18:26:17340vtNnusA7E+QUULCkUFe96EH0b2bMflyGSLpmR6DHY8ZVSxQGjGmdEykJQf1MQq5OdXCSjbvv

18:26:17341lr4cPr4zxV7VHQUd1PqvoKXmKYhJx9Rb42gDKjR85/W8tqRorRxAZig8EiusacGJBau5eN+u

18:26:173423LZfbMPlVCyWSDTG9BMLFzKAXuvoua9ofUr0ZchmMraMP391G0fcwK8BHL8Ki9zA8PhAsqMI

18:26:17343oXoYgq2JZo+wLYhrCDKcblDtTzGi/zwWevBuqCNN7+b5Dos2dwrGrU6KnXOq/ie4ng==

18:26:17344

18:26:17345/

Warning:Packagecreatedwithcompilationerrors.

Elapsed:00:00:00.50

18:26:18SQL>showerrors

ErrorsforPACKAGEDBMS_STATS_INTERNAL:

LINE/COLERROR

-------------------------------------------------------------------------

0/0ORA-21700:objectdoesnotexistorismarkedfordelete

18:26:18SQL>

……

报错是ora-21700，这是一个非常头疼的报错。这个报错常常意味着数据字典不一致，需要手动修复。

在Doc ID 2046611.1中提到，可以通过drop+重建解决。

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

1

2

3

4

5

6

7

8

sqlplus`/assysdba'

alter session set container = CUSTPDB;

exec dbms_pdb.exec_as_oracle_script('droppackagedbms_stats');

exec dbms_pdb.exec_as_oracle_script('droppackagedbms_stats_internal');

@?/rdbms/admin/dbmsstat.sql

@?/rdbms/admin/prvtstas.plb

@?/rdbms/admin/prvtstat.plb

@?/rdbms/admin/prvtstai.plb

文档说的是PDB，但是我估计non-cdb的数据库应该也可以类似解决，所以我决定采用：

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

1

2

3

4

5

6

droppackagedbms_stats;

droppackagedbms_stats_internal;

@?/rdbms/admin/dbmsstat.sql

@?/rdbms/admin/prvtstas.plb

@?/rdbms/admin/prvtstat.plb

@?/rdbms/admin/prvtstai.plb

但是发现drop dbms_stats_internal的时候，也报错ora-21700了：

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

1

2

3

4

5

6

7

8

9

SQL>droppackagedbms_stats;

Packagedropped.

SQL>droppackagedbms_stats_internal;

droppackagedbms_stats_internal

*

ERRORatline1:

ORA-21700:objectdoesnotexistorismarkedfordelete

看来只能手工修复数据字典了。

对drop dbms_stats_internal的过程做了10046，发现是在drop 一个type的时候遭遇ora-21700的：

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

……

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

PARSINGINCURSOR#18446744071425138736 len=32 dep=0 uid=0 oct=96 lid=0 tim=7819265336298 hv=3484713138 ad='ffffffff77d1fdf0' sqlid='apvgpzm7v905k'

droppackagedbms_stats_internal

ENDOFSTMT

PARSE#18446744071425138736:c=2652,e=2653,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=0,tim=7819265336295

……

PARSINGINCURSOR#18446744071423920880 len=47 dep=1 uid=0 oct=78 lid=0 tim=7819266312750 hv=366365679 ad='ffffffff77d1f9f8' sqlid='41x6bchaxckzg'

droptype"SYS".SYS_PLSQL_15AD49DF_1971_1force

ENDOFSTMT

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：

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

SQL>selectobject_idfromdba_objectswhereowner='SYS_PLSQL_15AD49DF_1971_1';

norowsselected

SQL>

SQL>selectname,obj# from obj$ where name='SYS_PLSQL_15AD49DF_1971_1'

SQL>/

NAMEOBJ#

------------------------------------------------------------------------------------------

SYS_PLSQL_15AD49DF_1971_1429029

SQL>--这里已经看到dba_objects和obj$出现不一致。一个没记录一个有记录。

我们开始手动修复数据字典。

--这里开始第一轮修复数据字典的过程：

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>

--第一轮修复数据字典过程完毕

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>settransactionuserollbacksegmentsystem;

Transactionset.

SQL>deletefromtype$wheretvoid=(selectoid$fromobj$whereobj# = 429029);

0rowsdeleted.

SQL>deletefromtype_misc$whereobj#=429029;

1rowdeleted.

SQL>deletefromsource$whereobj#=429029;

2rowsdeleted.

SQL>deletefromidl_ub1$whereobj#=429029;

3rowsdeleted.

SQL>deletefromidl_char$whereobj#=429029;

1rowdeleted.

SQL>deletefromidl_ub2$whereobj#=429029;

2rowsdeleted.

SQL>deletefromidl_sb4$whereobj#=429029;

3rowsdeleted.

SQL>deletefromsettings$whereobj# = 429029;

8rowsdeleted.

SQL>deletefromdependency$whered_obj#=429029;

1rowdeleted.

SQL>deletefromoid$whereoid$=(selectoid$fromobj$whereobj# = 429029);

0rowsdeleted.

SQL>deletefromobj$whereobj# = 429029;

1rowdeleted.

SQL>commit;

Commitcomplete.

SQL>

SQL>shutdownabort

ORACLEinstanceshutdown.

SQL>exit

DisconnectedfromOracleDatabase12cEnterpriseEditionRelease12.1.0.2.0-64bitProduction

WiththePartitioning,OLAP,AdvancedAnalyticsandRealApplicationTestingoptions

z4cs2011:t2gpolp>cd$ORACLE_HOME/rdbms/admin

z4cs2011:t2gpolp>sqlplus"/ as sysdba"

SQL*Plus:Release12.1.0.2.0ProductiononFriSep3015:09:572016

Copyright(c)1982,2014,Oracle.Allrightsreserved.

Connectedtoanidleinstance.

SQL>startupUPGRADE

ORACLEinstancestarted.

TotalSystemGlobalArea4211081216bytes

FixedSize6067648bytes

VariableSize3640658496bytes

DatabaseBuffers536870912bytes

RedoBuffers27484160bytes

Databasemounted.

Databaseopened.

SQL>

SQL>

SQL>

--第一轮修复数据字典过程完毕

修复完成后，再次尝试dbms_stats_internal，还是报错ora-21700。继续做10046的trace，发现还有另外的一个type SYS_PLSQL_15AD49DF_1984_1，顺便也查了一下，类似的type有几个，发现如下：

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>

1

2

3

4

5

6

7

8

9

10

SQL>selectname,obj# from obj$ where name like 'SYS_PLSQL_15AD49DF%'

NAMEOBJ#

----------------------------------------------------------

SYS_PLSQL_15AD49DF_1984_1429030

SYS_PLSQL_15AD49DF_4613_1429026

SYS_PLSQL_15AD49DF_4641_1429028

SYS_PLSQL_15AD49DF_DUMMY_1429027

SQL>

虽然我很想一次性的删除所有的这些type，但是我还是谨慎点，用删除dbms_stats_internal，如果遇到报错，再10046，再找到对应的type，再修复数据字典。

最后的结果是，这些type都需要删除，所以我一共做了5轮数据字典的手工修复。

修复完成后，可以顺利drop dbms_stats_internal了，可以重建dbms_stats了。重建后dbms_stats_internal状态为valid。

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

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

ORACLEinstanceshutdown.

SQL>startupUPGRADE

ORACLEinstancestarted.

TotalSystemGlobalArea4211081216bytes

FixedSize6067648bytes

VariableSize3640658496bytes

DatabaseBuffers536870912bytes

RedoBuffers27484160bytes

Databasemounted.

Databaseopened.

SQL>droppackagedbms_stats;

Packagedropped.

SQL>droppackagedbms_stats_internal;

Packagedropped.

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

Sessionaltered.

Packagecreated.

Noerrors.

Synonymcreated.

Grantsucceeded.

Rolecreated.

Grantsucceeded.

Grantsucceeded.

Grantsucceeded.

Rolecreated.

Grantsucceeded.

Grantsucceeded.

Librarycreated.

Sessionaltered.

SQL>

SQL>@?/rdbms/admin/prvtstas.plb

Sessionaltered.

Typecreated.

Synonymcreated.

Grantsucceeded.

Typecreated.

Noerrors.

Synonymcreated.

Synonymcreated.

Typecreated.

Synonymcreated.

Synonymcreated.

Typedropped.

Typecreated.

Noerrors.

Synonymcreated.

Synonymcreated.

Typecreated.

Synonymcreated.

Synonymcreated.

Typecreated.

Synonymcreated.

Synonymcreated.

Noerrors.

Typecreated.

Noerrors.

Synonymcreated.

Synonymcreated.

Typecreated.

Synonymcreated.

Synonymcreated.

Typecreated.

Noerrors.

Synonymcreated.

Synonymcreated.

Typecreated.

Synonymcreated.

Synonymcreated.

Noerrors.

Packagecreated.

Noerrors.

Sessionaltered.

SQL>

SQL>@?/rdbms/admin/prvtstat.plb

Sessionaltered.

Typebodycreated.

Noerrors.

Packagebodycreated.

Noerrors.

Sessionaltered.

SQL>

SQL>@?/rdbms/admin/prvtstai.plb

Sessionaltered.

Packagebodycreated.

Noerrors.

Sessionaltered.

SQL>

SQL> select status from dba_objects where object_name='DBMS_STATS_INTERNAL';

STATUS

-------

VALID

VALID

SQL>

1

2

3

4

5

6

7

8

SQL>selectstatusfromdba_objectswhereobject_name='DBMS_STATS_INTERNAL';

STATUS

-------

VALID

VALID

SQL>

参考：Doc ID 437558.1 INTERNAL