一般Oracle的bootstrap index（引导表的索引和一些核心对象）都可以尝试类似方法处理，比如下面查询语句中的I_OBJxxxxx。
.
测试环境 11.2.0.3数据库：
[oracle@lunarpri ~]$ ss SQL*Plus: Release 11.2.0.3.0 Production on Fri Mar 27 19:12:57 2015 Copyright (c) 1982, 2011, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning and Real Application Testing options SYS@lunar>col OBJECT_NAME for a30 SYS@lunar>select OWNER,OBJECT_NAME,OBJECT_TYPE,status,LAST_DDL_TIME from dba_objects where OBJECT_NAME like 'I_OBJ%'; OWNER OBJECT_NAME OBJECT_TYPE STATUS LAST_DDL_ ------------------------------ ------------------------------ ------------------- ------- --------- SYS I_OBJ# INDEX VALID 08-FEB-13 SYS I_OBJ5 INDEX VALID 08-FEB-13 SYS I_OBJ3 INDEX VALID 08-FEB-13 SYS I_OBJ1 INDEX VALID 08-FEB-13 SYS I_OBJ2 INDEX VALID 08-FEB-13 SYS I_OBJ4 INDEX VALID 08-FEB-13 SYS I_OBJAUTH1 INDEX VALID 08-FEB-13 SYS I_OBJAUTH2 INDEX VALID 08-FEB-13 SYS I_OBJ#_INTCOL# INDEX VALID 08-FEB-13 SYS I_OBJTYPE INDEX VALID 08-FEB-13 10 rows selected. Elapsed: 00:00:00.06 SYS@lunar>
在数据库中长开启时，不能操作一些bootstrap index，例如：
有些bootstrap索引在upgrade模式下也不可以修改，有些可以：
SYS@lunar>alter index SYS.I_OBJ5 unusable * ERROR at line 1: ORA-00701: object necessary for warmstarting database cannot be altered Elapsed: 00:00:00.01 SYS@lunar>alter index SYS.I_OBJ3 unusable * ERROR at line 1: ORA-00701: object necessary for warmstarting database cannot be altered Elapsed: 00:00:00.02 SYS@lunar>alter index SYS.I_OBJ1 unusable * ERROR at line 1: ORA-00701: object necessary for warmstarting database cannot be altered Elapsed: 00:00:00.01 SYS@lunar>
可以启动到升级模式，这模式数据库会自动增加一些类似屏蔽system trigger等的操作
可以执行部分bootstrage对象的操作，例如：
SYS@lunar>startup upgrade ORACLE instance started. Total System Global Area 626327552 bytes Fixed Size 2230952 bytes Variable Size 184550744 bytes Database Buffers 432013312 bytes Redo Buffers 7532544 bytes Database mounted. Database opened. SYS@lunar> SYS@lunar>alter index SYS.I_OBJAUTH2 unusable; alter index SYS.I_OBJ#_INTCOL# unusable; alter index SYS.I_OBJTYPE unusable; Index altered. Elapsed: 00:00:00.04 SYS@lunar>alter index SYS.I_OBJ# unusable Index altered. Elapsed: 00:00:00.20 SYS@lunar> Index altered. Elapsed: 00:00:00.03 SYS@lunar> Index altered. Elapsed: 00:00:00.03 SYS@lunar>
升级模式自动添加的参数如下：
Fri Mar 27 19:21:48 2015 MMNL started with pid=16, OS id=15218 ALTER SYSTEM enable restricted session; ALTER SYSTEM SET _system_trig_enabled=FALSE SCOPE=MEMORY; Autotune of undo retention is turned off. ALTER SYSTEM SET _undo_autotune=FALSE SCOPE=MEMORY; ALTER SYSTEM SET undo_retention=900 SCOPE=MEMORY; ALTER SYSTEM SET aq_tm_processes=0 SCOPE=MEMORY; ALTER SYSTEM SET enable_ddl_logging=FALSE SCOPE=MEMORY; Resource Manager disabled during database migration: plan '' not set ALTER SYSTEM SET resource_manager_plan='' SCOPE=MEMORY; ALTER SYSTEM SET recyclebin='OFF' DEFERRED SCOPE=MEMORY; Resource Manager disabled during database migration replication_dependency_tracking turned off (no async multimaster replication found)
此时，数据字典因为遭到破坏，很多相关查询等功能都失效了：
SYS@lunar>col OBJECT_NAME for a30
SYS@lunar>select OWNER,OBJECT_NAME,OBJECT_TYPE,status,LAST_DDL_TIME from dba_objects where OBJECT_NAME like 'I_OBJ%';
select OWNER,OBJECT_NAME,OBJECT_TYPE,status,LAST_DDL_TIME from dba_objects where OBJECT_NAME like 'I_OBJ%'
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01502: index 'SYS.I_OBJ#_INTCOL#' or partition of such index is in unusable state
Elapsed: 00:00:00.20
SYS@lunar>
且启动时会报错ORA-00604 ORA-04024:
SYS@lunar>startup ORACLE instance started. Total System Global Area 626327552 bytes Fixed Size 2230952 bytes Variable Size 184550744 bytes Database Buffers 432013312 bytes Redo Buffers 7532544 bytes Database mounted. ORA-00604: error occurred at recursive SQL level 4 ORA-04024: self-deadlock detected while trying to mutex pin cursor 0x07EF125E8 SYS@lunar>
正常关闭数据库也关闭不了，应该是需要执行某些核心递归SQL时遇到问题了，只能shutdown abort：
SYS@lunar>shutdown immediate ORA-00604: error occurred at recursive SQL level 4 ORA-04024: self-deadlock detected while trying to mutex pin cursor 0x07EF125E8 SYS@lunar>shutdown abort ORACLE instance shut down SYS@lunar>
然后以升级模式启动数据库，修复这些索引：.
SYS@lunar>startup upgrade ORACLE instance started. Total System Global Area 626327552 bytes Fixed Size 2230952 bytes Variable Size 184550744 bytes Database Buffers 432013312 bytes Redo Buffers 7532544 bytes Database mounted. Database opened. SYS@lunar> SYS@lunar>show parameter NLS_LENGTH_SEMANTICS ORA-00604: error occurred at recursive SQL level 1 ORA-01502: index 'SYS.I_OBJ#_INTCOL#' or partition of such index is in unusable state SYS@lunar>
执行迷你升级脚本进行修复：
SYS@lunar>ALTER SESSION SET NLS_LENGTH_SEMANTICS = BYTE; Session altered. Elapsed: 00:00:00.00 SYS@lunar>@?/rdbms/admin/utlmmig.sql View created. Elapsed: 00:00:01.32 PL/SQL procedure successfully completed. Elapsed: 00:00:00.34 Commit complete. Elapsed: 00:00:00.01 Table dropped. Elapsed: 00:00:00.01 Table created. Elapsed: 00:00:00.53 Index created. Elapsed: 00:00:00.12 Index created. Elapsed: 00:00:00.05 Index created. Elapsed: 00:00:00.02 Index created. Elapsed: 00:00:00.03 Index created. Elapsed: 00:00:00.04 Table dropped. Elapsed: 00:00:00.02 Table created. Elapsed: 00:00:00.07 Index created. Elapsed: 00:00:00.03 Index created. Elapsed: 00:00:00.03 Table dropped. Elapsed: 00:00:00.00 Table created. Elapsed: 00:00:00.04 Table dropped. Elapsed: 00:00:00.01 Table created. Elapsed: 00:00:00.05 declare * ERROR at line 1: ORA-01502: index 'SYS.I_OBJ#_INTCOL#' or partition of such index is in unusable state ORA-06512: at line 13 ORA-06512: at line 137 Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning and Real Application Testing options [oracle@lunarpri ~]$
这里我们看到SYS.I_OBJ#_INTCOL#索引为unusable，不能在升级模式下被修复
但是，10g以后，这个索引是可以通过event 38003屏蔽的：
SYS@lunar>create pfile='/tmp/spfile.bak' from spfile;
File created.
Elapsed: 00:00:00.01
SYS@lunar>show parameter spfile
ORA-00604: error occurred at recursive SQL level 1
ORA-01502: index 'SYS.I_OBJ#_INTCOL#' or partition of such index is in unusable state
SYS@lunar>shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@lunar>
[oracle@lunarpri ~]$ tail /tmp/spfile.bak
*.db_recovery_file_dest_size=10485760000
*.deferred_segment_creation=FALSE
*.diagnostic_dest='/u01/app/oracle'
*.open_cursors=300
*.pga_aggregate_target=153092096
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=629145600
*.undo_tablespace='UNDOTBS1'
*.EVENT="38003 trace name context forever, level 10"
[oracle@lunarpri ~]$
SYS@lunar>startup pfile=/tmp/spfile.bak upgrade
ORACLE instance started.
Total System Global Area 626327552 bytes
Fixed Size 2230952 bytes
Variable Size 184550744 bytes
Database Buffers 432013312 bytes
Redo Buffers 7532544 bytes
Database mounted.
Database opened.
SYS@lunar>col OBJECT_NAME for a30
SYS@lunar>select OWNER,OBJECT_NAME,OBJECT_TYPE,status,LAST_DDL_TIME from dba_objects where OBJECT_NAME like 'I_OBJ%';
select OWNER,OBJECT_NAME,OBJECT_TYPE,status,LAST_DDL_TIME from dba_objects where OBJECT_NAME like 'I_OBJ%'
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01502: index 'SYS.I_OBJ#_INTCOL#' or partition of such index is in unusable state
Elapsed: 00:00:00.08
SYS@lunar>alter index SYS.I_OBJ#_INTCOL# rebuild;
Index altered.
Elapsed: 00:00:00.46
SYS@lunar>
这里我们已经修复了损坏的索引。
在此基础上，我们重新执行迷你升级脚本：
SYS@lunar>select OWNER,OBJECT_NAME,OBJECT_TYPE,status,LAST_DDL_TIME from dba_objects where OBJECT_NAME like 'I_OBJ%';
OWNER OBJECT_NAME OBJECT_TYPE STATUS LAST_DDL_
------------------------------ ------------------------------ ------------------- ------- ---------
SYS I_OBJ# INDEX VALID 08-FEB-13
SYS I_OBJ5 INDEX VALID 08-FEB-13
SYS I_OBJ3 INDEX VALID 08-FEB-13
SYS I_OBJ1 INDEX VALID 08-FEB-13
SYS I_OBJ2 INDEX VALID 08-FEB-13
SYS I_OBJ4 INDEX VALID 08-FEB-13
SYS I_OBJAUTH1 INDEX VALID 27-MAR-15
SYS I_OBJAUTH2 INDEX VALID 27-MAR-15
SYS I_OBJ#_INTCOL# INDEX VALID 27-MAR-15
SYS I_OBJTYPE INDEX VALID 27-MAR-15
SYS I_OBJ_MIG1 INDEX VALID 27-MAR-15
SYS I_OBJ_MIG2 INDEX VALID 27-MAR-15
SYS I_OBJ_MIG3 INDEX VALID 27-MAR-15
SYS I_OBJ_MIG4 INDEX VALID 27-MAR-15
SYS I_OBJ_MIG5 INDEX VALID 27-MAR-15
15 rows selected.
Elapsed: 00:00:00.49
SYS@lunar>@?/rdbms/admin/utlmmig.sql
View created.
Elapsed: 00:00:00.69
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.07
Commit complete.
Elapsed: 00:00:00.00
Table dropped.
Elapsed: 00:00:00.28
Table created.
Elapsed: 00:00:00.08
Index created.
Elapsed: 00:00:00.09
Index created.
Elapsed: 00:00:00.03
Index created.
Elapsed: 00:00:00.02
Index created.
Elapsed: 00:00:00.03
Index created.
Elapsed: 00:00:00.03
Table dropped.
Elapsed: 00:00:00.12
Table created.
Elapsed: 00:00:00.08
Index created.
Elapsed: 00:00:00.02
Index created.
Elapsed: 00:00:00.03
Table dropped.
Elapsed: 00:00:00.08
Table created.
Elapsed: 00:00:00.03
Table dropped.
Elapsed: 00:00:00.06
Table created.
Elapsed: 00:00:00.03
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.20
PL/SQL procedure successfully completed.
Elapsed: 00:00:26.30
49 rows created.
Elapsed: 00:00:00.03
60 rows created.
Elapsed: 00:00:00.00
Commit complete.
Elapsed: 00:00:00.01
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.14
PL/SQL procedure successfully completed.
Elapsed: 00:00:11.16
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.09
10 rows deleted.
Elapsed: 00:00:00.03
Commit complete.
Elapsed: 00:00:00.00
10 rows created.
Elapsed: 00:00:00.01
Commit complete.
Elapsed: 00:00:00.00
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.82
COUNT(*)
----------------
60
Elapsed: 00:00:00.00
COUNT(*)
----------------
60
Elapsed: 00:00:00.00
COUNT(*)
----------------
60
Elapsed: 00:00:00.01
COUNT(*)
----------------
60
Elapsed: 00:00:00.01
COUNT(*)
----------------
60
Elapsed: 00:00:00.00
COUNT(*)
----------------
60
Elapsed: 00:00:00.00
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.15
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@lunar>
然后正常启动数据库：
SYS@lunar>startup ORACLE instance started. Total System Global Area 626327552 bytes Fixed Size 2230952 bytes Variable Size 184550744 bytes Database Buffers 432013312 bytes Redo Buffers 7532544 bytes Database mounted. Database opened. SYS@lunar> SYS@lunar>col OBJECT_NAME for a30 SYS@lunar>select OWNER,OBJECT_NAME,OBJECT_TYPE,status,LAST_DDL_TIME from dba_objects where OBJECT_NAME like 'I_OBJ%'; OWNER OBJECT_NAME OBJECT_TYPE STATUS LAST_DDL_ ------------------------------ ------------------------------ ------------------- ------- --------- SYS I_OBJ# INDEX VALID 08-FEB-13 SYS I_OBJ_MIG1 INDEX VALID 08-FEB-13 SYS I_OBJ_MIG2 INDEX VALID 08-FEB-13 SYS I_OBJ_MIG3 INDEX VALID 08-FEB-13 SYS I_OBJ_MIG4 INDEX VALID 08-FEB-13 SYS I_OBJ_MIG5 INDEX VALID 08-FEB-13 SYS I_OBJAUTH1 INDEX VALID 27-MAR-15 SYS I_OBJAUTH2 INDEX VALID 27-MAR-15 SYS I_OBJ#_INTCOL# INDEX VALID 27-MAR-15 SYS I_OBJTYPE INDEX VALID 27-MAR-15 SYS I_OBJ1 INDEX VALID 27-MAR-15 SYS I_OBJ2 INDEX VALID 27-MAR-15 SYS I_OBJ3 INDEX VALID 27-MAR-15 SYS I_OBJ4 INDEX VALID 27-MAR-15 SYS I_OBJ5 INDEX VALID 27-MAR-15 15 rows selected. Elapsed: 00:00:00.14 SYS@lunar>
删除第一次执行迷你升级脚本因为异常而中断残留在数据库中的临时索引：
SYS@lunar>DROP INDEX SYS.I_OBJ_MIG1; Index dropped. Elapsed: 00:00:00.44 SYS@lunar>C/1/2 1* DROP INDEX SYS.I_OBJ_MIG2 SYS@lunar>/ Index dropped. Elapsed: 00:00:00.06 SYS@lunar>C/2/3 1* DROP INDEX SYS.I_OBJ_MIG3 SYS@lunar>/ Index dropped. Elapsed: 00:00:00.05 SYS@lunar>C/3/4 1* DROP INDEX SYS.I_OBJ_MIG4 SYS@lunar>/ Index dropped. Elapsed: 00:00:00.08 SYS@lunar>C/4/5 1* DROP INDEX SYS.I_OBJ_MIG5 SYS@lunar>/ Index dropped. Elapsed: 00:00:00.05 SYS@lunar> SYS@lunar>col OBJECT_NAME for a30 SYS@lunar>select OWNER,OBJECT_NAME,OBJECT_TYPE,status,LAST_DDL_TIME from dba_objects where OBJECT_NAME like 'I_OBJ%'; OWNER OBJECT_NAME OBJECT_TYPE STATUS LAST_DDL_ ------------------------------ ------------------------------ ------------------- ------- --------- SYS I_OBJ# INDEX VALID 08-FEB-13 SYS I_OBJAUTH1 INDEX VALID 27-MAR-15 SYS I_OBJAUTH2 INDEX VALID 27-MAR-15 SYS I_OBJ#_INTCOL# INDEX VALID 27-MAR-15 SYS I_OBJTYPE INDEX VALID 27-MAR-15 SYS I_OBJ1 INDEX VALID 27-MAR-15 SYS I_OBJ2 INDEX VALID 27-MAR-15 SYS I_OBJ3 INDEX VALID 27-MAR-15 SYS I_OBJ4 INDEX VALID 27-MAR-15 SYS I_OBJ5 INDEX VALID 27-MAR-15 10 rows selected. Elapsed: 00:00:00.07 SYS@lunar>shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SYS@lunar>startup ORACLE instance started. Total System Global Area 626327552 bytes Fixed Size 2230952 bytes Variable Size 184550744 bytes Database Buffers 432013312 bytes Redo Buffers 7532544 bytes Database mounted. Database opened. SYS@lunar> SYS@lunar>col OBJECT_NAME for a30 SYS@lunar>select OWNER,OBJECT_NAME,OBJECT_TYPE,status,LAST_DDL_TIME from dba_objects where OBJECT_NAME like 'I_OBJ%'; OWNER OBJECT_NAME OBJECT_TYPE STATUS LAST_DDL_ ------------------------------ ------------------------------ ------------------- ------- --------- SYS I_OBJ# INDEX VALID 08-FEB-13 SYS I_OBJAUTH1 INDEX VALID 27-MAR-15 SYS I_OBJAUTH2 INDEX VALID 27-MAR-15 SYS I_OBJ#_INTCOL# INDEX VALID 27-MAR-15 SYS I_OBJTYPE INDEX VALID 27-MAR-15 SYS I_OBJ1 INDEX VALID 27-MAR-15 SYS I_OBJ2 INDEX VALID 27-MAR-15 SYS I_OBJ3 INDEX VALID 27-MAR-15 SYS I_OBJ4 INDEX VALID 27-MAR-15 SYS I_OBJ5 INDEX VALID 27-MAR-15 10 rows selected. Elapsed: 00:00:00.13 SYS@lunar>
至此，完美恢复！
