Lunar的oracle实验室

有朋友问起ORA-00704 ORA-00604 ORA-01502: index ‘SYS.I_DEPENDENCY1’ or partition of such index is in unusable state错误怎么处理。

11.2中I_DEPENDENCY1损坏的处理
I_DEPENDENCY2损坏的处理

当SYS.I_DEPENDENCY1损坏或者不可用时，就会报如下类似错误：

Mon Dec 22 09:13:32 2014
Errors in file /home/oracle/oracle/product/admin/orcl/udump/orcl_ora_19783_lunar.trc:
ORA-00704: bootstrap process failure
ORA-00604: error occurred at recursive SQL level 1
ORA-01502: index 'SYS.I_DEPENDENCY1' or partition of such index is in unusable state

首先说一下，10g中的处理方式，有两种：
1，直接在10g中修复
2，使用11.2将数据库拉起来到upgrade模式，然后修复

先看一下，数据库中跟依赖关系相关的表都有哪些：

[oracle@lunar oracle]$ ss

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Dec 22 08:10:07 2014

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SYS@lunar>select owner,object_id,object_name,object_type from dba_objects where object_name like '%DEPENDENCY%' and owner='SYS';

OWNER            OBJECT_ID OBJECT_NAME                    OBJECT_TYPE
--------------- ---------- ------------------------------ -------------------
SYS                     92 DEPENDENCY$                    TABLE
SYS                  46827 GENDEPENDENCYBLOCKSEQUENCE     TYPE
SYS                  46828 GENDEPENDENCYBLOCKSEQUENCESEQU TYPE
SYS                  46824 GENDEPENDENCYBLOCKSTRUCT       TYPE
SYS                  46826 GENDEPENDENCYBLOCKUNION        TYPE
SYS                  46825 GENINCLUDEDDEPENDENCYBLOCKSTRU TYPE
SYS                   1691 GV_$OBJECT_DEPENDENCY          VIEW
SYS                    122 I_DEPENDENCY1                  INDEX
SYS                    123 I_DEPENDENCY2                  INDEX
SYS                   3950 PUBLIC_DEPENDENCY              VIEW
SYS                  47048 SQLDEPENDENCYBLOCKSEQ          TYPE
SYS                  47049 SQLDEPENDENCYBLOCKSEQUENCE     TYPE
SYS                  47050 SQLDEPENDENCYBLOCKSEQUENCESEQ  TYPE
SYS                  47051 SQLDEPENDENCYBLOCKSEQUENCESEQU TYPE
SYS                  47045 SQLDEPENDENCYBLOCKSTRUCT       TYPE
SYS                  47047 SQLDEPENDENCYBLOCKUNION        TYPE
SYS                  47046 SQLINCLUDEDDEPENDENCYBLOCKSTRU TYPE
SYS                   1066 V_$OBJECT_DEPENDENCY           VIEW

18 rows selected.

SYS@lunar>

这里，我们关心的是DEPENDENCY$的索引信息，可以看到，DEPENDENCY$表存在两个索引。
这两个索引损坏时对数据库的影响是不同的，他们分别定义如下：

SYS@lunar>select owner,table_name,index_name,tablespace_name,status from dba_indexes where table_name='DEPENDENCY$';

OWNER           TABLE_NAME                     INDEX_NAME                     TABLESPACE_NAME                STATUS
--------------- ------------------------------ ------------------------------ ------------------------------ --------
SYS             DEPENDENCY$                    I_DEPENDENCY1                  SYSTEM                         VALID
SYS             DEPENDENCY$                    I_DEPENDENCY2                  SYSTEM                         VALID

SYS@lunar>

9i、10g、11.1以前，一些核心对象的定义就在$ORACLE_HOME/rdbms/admin/sql.bsq中。
比如tab$,obj$,ind$等等的定义都在其中。
从11.2开始，创建一些重要的核心基表的脚本仍然是sql.bsq，但是已经将各种对象的创建分类到不同的脚本：

dcore.bsq
dsqlddl.bsq
dmanage.bsq
dplsql.bsq
dtxnspc.bsq
dfmap.bsq
denv.bsq
drac.bsq
dsec.bsq
doptim.bsq
dobj.bsq
djava.bsq
dpart.bsq
drep.bsq
daw.bsq
dsummgt.bsq
dtools.bsq
dexttab.bsq
ddm.bsq
dlmnr.bsq
ddst.bsq

dependency$，i_dependency1和i_dependency1的定义如下（10.2和11.2没有区别）：

create table dependency$                                 /* dependency table */
( d_obj#        number not null,                  /* dependent object number */
  d_timestamp   date not null,   /* dependent object specification timestamp */
  order#        number not null,                             /* order number */
  p_obj#        number not null,                     /* parent object number */
  p_timestamp   date not null,      /* parent object specification timestamp */
  d_owner#      number,                           /*  dependent owner number */
  property      number not null,                   /* 0x01 = HARD dependency */
                                                   /* 0x02 = REF  dependency */
                                          /* 0x04 = FINER GRAINED dependency */
  d_attrs       raw("M_CSIZ"), /* Finer grain attr. numbers if finer grained */
  d_reason      raw("M_CSIZ"))  /* Reason mask of attrs causing invalidation */
storage (initial 10k next 100k maxextents unlimited pctincrease 0)
/

create unique index i_dependency1 on
  dependency$(d_obj#, d_timestamp, order#)
  storage (initial 10k next 100k maxextents unlimited pctincrease 0)
/

create index i_dependency2 on
  dependency$(p_obj#, p_timestamp)
  storage (initial 10k next 100k maxextents unlimited pctincrease 0)
/

下面我们看一下他们使用了哪些block：

SYS@lunar>select owner,segment_name,segment_type,extent_id,file_id,block_id from DBA_EXTENTS where segment_name like '%DEPENDENCY%';

OWNER           SEGMENT_NAME                   SEGMENT_TYPE        EXTENT_ID    FILE_ID   BLOCK_ID
--------------- ------------------------------ ------------------ ---------- ---------- ----------
SYS             DEPENDENCY$                    TABLE                       0          1        689
SYS             DEPENDENCY$                    TABLE                       1          1       6329
SYS             DEPENDENCY$                    TABLE                       2          1       6665
SYS             DEPENDENCY$                    TABLE                       3          1       6945
SYS             DEPENDENCY$                    TABLE                       4          1       8481
SYS             DEPENDENCY$                    TABLE                       5          1      10505
SYS             DEPENDENCY$                    TABLE                       6          1      12825
SYS             DEPENDENCY$                    TABLE                       7          1      13425
SYS             DEPENDENCY$                    TABLE                       8          1      14753
SYS             DEPENDENCY$                    TABLE                       9          1      15513
SYS             DEPENDENCY$                    TABLE                      10          1      17169
SYS             DEPENDENCY$                    TABLE                      11          1      19209
SYS             DEPENDENCY$                    TABLE                      12          1      19897
SYS             DEPENDENCY$                    TABLE                      13          1      26689
SYS             DEPENDENCY$                    TABLE                      14          1      28153
SYS             DEPENDENCY$                    TABLE                      15          1      29377
SYS             DEPENDENCY$                    TABLE                      16          1      31241
SYS             DEPENDENCY$                    TABLE                      17          1      45321
SYS             DEPENDENCY$                    TABLE                      18          1      51465
SYS             DEPENDENCY$                    TABLE                      19          1      55433
SYSMAN          MGMT_INV_DEPENDENCY_RULE       TABLE                       0          3      24009
SYSMAN          MGMT_METRIC_DEPENDENCY_DEF     TABLE                       0          3      26193
SYSMAN          MGMT_METRIC_DEPENDENCY         TABLE                       0          3      26209
SYSMAN          MGMT_METRIC_DEPENDENCY_DETAILS TABLE                       0          3      26225
SYS             I_DEPENDENCY1                  INDEX                       0          1        929
SYS             I_DEPENDENCY1                  INDEX                       1          1       6313
SYS             I_DEPENDENCY1                  INDEX                       2          1       6777
SYS             I_DEPENDENCY1                  INDEX                       3          1       8905
SYS             I_DEPENDENCY1                  INDEX                       4          1      12185
SYS             I_DEPENDENCY1                  INDEX                       5          1      13433
SYS             I_DEPENDENCY1                  INDEX                       6          1      14761
SYS             I_DEPENDENCY1                  INDEX                       7          1      15537
SYS             I_DEPENDENCY1                  INDEX                       8          1      18425
SYS             I_DEPENDENCY1                  INDEX                       9          1      19273
SYS             I_DEPENDENCY1                  INDEX                      10          1      26705
SYS             I_DEPENDENCY1                  INDEX                      11          1      28297
SYS             I_DEPENDENCY1                  INDEX                      12          1      30609
SYS             I_DEPENDENCY1                  INDEX                      13          1      32297
SYS             I_DEPENDENCY1                  INDEX                      14          1      34353
SYS             I_DEPENDENCY1                  INDEX                      15          1      37129
SYS             I_DEPENDENCY1                  INDEX                      16          1      38665
SYS             I_DEPENDENCY1                  INDEX                      17          1      47113
SYS             I_DEPENDENCY1                  INDEX                      18          1      51721
SYS             I_DEPENDENCY2                  INDEX                       0          1        937
SYS             I_DEPENDENCY2                  INDEX                       1          1       6337
SYS             I_DEPENDENCY2                  INDEX                       2          1       6657
SYS             I_DEPENDENCY2                  INDEX                       3          1       6961
SYS             I_DEPENDENCY2                  INDEX                       4          1       9545
SYS             I_DEPENDENCY2                  INDEX                       5          1      11481
SYS             I_DEPENDENCY2                  INDEX                       6          1      13281
SYS             I_DEPENDENCY2                  INDEX                       7          1      14369
SYS             I_DEPENDENCY2                  INDEX                       8          1      14841
SYS             I_DEPENDENCY2                  INDEX                       9          1      16617
SYS             I_DEPENDENCY2                  INDEX                      10          1      18409
SYS             I_DEPENDENCY2                  INDEX                      11          1      19889
SYS             I_DEPENDENCY2                  INDEX                      12          1      26681
SYS             I_DEPENDENCY2                  INDEX                      13          1      28129
SYS             I_DEPENDENCY2                  INDEX                      14          1      29369
SYS             I_DEPENDENCY2                  INDEX                      15          1      30649
SYS             I_DEPENDENCY2                  INDEX                      16          1      32137
SYS             I_DEPENDENCY2                  INDEX                      17          1      45449
SYS             I_DEPENDENCY2                  INDEX                      18          1      51593
SYS             I_DEPENDENCY2                  INDEX                      19          1      59785
SYSMAN          PK_MGMT_METRIC_DEPENDENCY_DEF  INDEX                       0          3      26201
SYSMAN          PK_MGMT_METRIC_DEPENDENCY      INDEX                       0          3      26217

65 rows selected.

SYS@lunar>

下面我们在10.2总模拟i_dependency1失效（测试环境使用10.2.0.1）：

[oracle@lunar oracle]$ ss

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Dec 22 08:10:07 2014

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SYS@lunar>alter index i_dependency1 unusable;

Index altered.

SYS@lunar>select owner,table_name,index_name,tablespace_name,status from dba_indexes where table_name='DEPENDENCY$';

OWNER           TABLE_NAME                     INDEX_NAME                     TABLESPACE_NAME                STATUS
--------------- ------------------------------ ------------------------------ ------------------------------ --------
SYS             DEPENDENCY$                    I_DEPENDENCY1                  SYSTEM                         UNUSABLE
SYS             DEPENDENCY$                    I_DEPENDENCY2                  SYSTEM                         VALID

SYS@lunar>select obj#,flags,to_char(flags,'xxxxxxxxxxxxx') from ind$ where obj# in (92,122,123);

      OBJ#      FLAGS TO_CHAR(FLAGS,
---------- ---------- --------------
       122       2051            803   ------ 2000+10+40+1=====>/* unusable (dls) : 0x01 ，这里也可以看出来被为unusable了
       123       2050            802

SYS@lunar>

执行alter index i_dependency1 unusable后，alert.log的信息如下：

Mon Dec 22 09:08:37 2014
Index SYS.I_DEPENDENCY1 or some [sub]partitions of the index have been marked unusable

[oracle@lunar bdump]$ date
Mon Dec 22 09:11:29 CST 2014
[oracle@lunar bdump]$ 

ind$的flags字段的定义（该定义可以查询基表定义，10.2中在sql.bsq，11.1以后，在dcore.bsq）：

  flags         number not null,      																												
                /* mutable flags: anything permanent should go into property */
                                                    /* unusable (dls) : 0x01 */
                                                    /* analyzed       : 0x02 */
                                                    /* no logging     : 0x04 */
                                    /* index is currently being built : 0x08 */
                                     /* index creation was incomplete : 0x10 */
                                           /* key compression enabled : 0x20 */
                                              /* user-specified stats : 0x40 */
                                            /* secondary index on IOT : 0x80 */
                                      /* index is being online built : 0x100 */
                                    /* index is being online rebuilt : 0x200 */   
                                                /* index is disabled : 0x400 */   ---------注意这里，设置一个index失效的标示（转换成10进制是1024）
                                                     /* global stats : 0x800 */
                                            /* fake index(internal) : 0x1000 */
                                       /* index on UROWID column(s) : 0x2000 */
                                            /* index with large key : 0x4000 */
                             /* move partitioned rows in base table : 0x8000 */
                                 /* index usage monitoring enabled : 0x10000 */   
                      /* 4 bits reserved for bitmap index version : 0x1E0000 */
                                      /* Delayed Segment Creation: 0x4000000 */

通过对比，可以发现实际上对于ind$.flags的定义并没有改变，只是内部操作有所变化。
这些标示很有用，比如orcacle 9204有监控SYS的XXX索引的bug会导致数据库不能open，可以通过这个标示来修复。
再比如，10g以前online 创建索引中断后很麻烦，也可以通过修改这个来修复。
10.2以后可以通过dbms_repair.online_index_clean来清理索引状态。不过该功能没有记录在10.2的官方文档中（但是用desc dbms_repair可以看到该功能）。
Oracle官方把这个在文档漏掉如此优秀功能的做法解释为Bug 3965042 : NEW FUNCTION DBMS_REPAIR.ONLINE_INDEX_CLEAN NEEDS TO BE DOCUMENTED）
ALTER TABLE MOVE ONLINE一个IOT表，也可能有类似问题，处理方法也雷同。

这时候，启动数据库会收到ORA-00704 ORA-00604 ORA-01502: index ‘SYS.I_DEPENDENCY1’ or partition of such index is in unusable state报错：

SYS@lunar>shutdown abort
ORACLE instance shut down.
SYS@lunar>startup mount
ORACLE instance started.

Total System Global Area  167772160 bytes
Fixed Size                  1218316 bytes
Variable Size              71305460 bytes
Database Buffers           92274688 bytes
Redo Buffers                2973696 bytes
Database mounted.
SYS@lunar>oradebug setmypid
alter session set db_file_multiblocK_read_count=1;
alter session set tracefile_identifier='lunar';
Statement processed.
SYS@lunar>
Session altered.

SYS@lunar>oradebug event 10046 trace name context forever,level 12;

Session altered.

SYS@lunar>Statement processed.
SYS@lunar>oradebug tracefile_name
/home/oracle/oracle/product/admin/orcl/udump/orcl_ora_19783_lunar.trc
SYS@lunar>alter database open; 
alter database open
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced


SYS@lunar>

alert.log中信息如下

Mon Dec 22 09:13:29 2014
SMON: enabling cache recovery
Mon Dec 22 09:13:29 2014
ARC2: Archival started
ARC0: STARTING ARCH PROCESSES COMPLETE
ARC0: Becoming the heartbeat ARCH
ARC2 started with pid=17, OS id=19789
Mon Dec 22 09:13:32 2014
Deleted Oracle managed file /home/oracle/oracle/product/flash_recovery_area/ORCL/archivelog/2009_11_21/o1_mf_1_42_5jg30x9m_.arc
Mon Dec 22 09:13:32 2014
Errors in file /home/oracle/oracle/product/admin/orcl/udump/orcl_ora_19783_lunar.trc:
ORA-00704: bootstrap process failure
ORA-00604: error occurred at recursive SQL level 1
ORA-01502: index 'SYS.I_DEPENDENCY1' or partition of such index is in unusable state
Mon Dec 22 09:13:32 2014
Error 704 happened during db open, shutting down database
USER: terminating instance due to error 704
Instance terminated by USER, pid = 19783
ORA-1092 signalled during: alter database open...

trace文件信息：

PARSING IN CURSOR #5 len=179 dep=1 uid=0 oct=3 lid=0 tim=1385948058756927 hv=2812844157 ad='29b07834'
select owner#,name,namespace,remoteowner,linkname,p_timestamp,p_obj#, nvl(property,0),subname,d_attrs from dependency$ d, obj$ o where d_obj#=:1 and p_obj#=obj#(+) order by order#
END OF STMT
PARSE #5:c=6999,e=70263,p=8,cr=40,cu=0,mis=1,r=0,dep=1,og=4,tim=1385948058756919
=====================
PARSING IN CURSOR #2 len=84 dep=2 uid=0 oct=3 lid=0 tim=1385948058757830 hv=2686874206 ad='29b0652c'
select o.name, u.name from obj$ o, user$ u  where o.obj# = :1 and o.owner# = u.user#
END OF STMT
PARSE #2:c=1000,e=266,p=0,cr=0,cu=0,mis=1,r=0,dep=2,og=4,tim=1385948058757826
BINDS #2:
kkscoacd
 Bind#0
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=08 fl2=0001 frm=00 csi=00 siz=24 off=0
  kxsbbbfp=b72f40bc  bln=22  avl=03  flg=05
  value=122  ---------》》obj#=122的正是I_DEPENDENCY1
EXEC #2:c=0,e=658,p=0,cr=0,cu=0,mis=1,r=0,dep=2,og=4,tim=1385948058758543
WAIT #2: nam='db file sequential read' ela= 15160 file#=1 block#=98 blocks=1 obj#=-1 tim=1385948058773774
WAIT #2: nam='db file sequential read' ela= 481 file#=1 block#=90 blocks=1 obj#=-1 tim=1385948058774587
FETCH #2:c=2000,e=16086,p=2,cr=5,cu=0,mis=0,r=1,dep=2,og=4,tim=1385948058774648
STAT #2 id=1 cnt=1 pid=0 pos=1 obj=0 op='NESTED LOOPS  (cr=5 pr=2 pw=0 time=16090 us)'
STAT #2 id=2 cnt=1 pid=1 pos=1 obj=18 op='TABLE ACCESS BY INDEX ROWID OBJ$ (cr=3 pr=0 pw=0 time=28 us)'
STAT #2 id=3 cnt=1 pid=2 pos=1 obj=36 op='INDEX UNIQUE SCAN I_OBJ1 (cr=2 pr=0 pw=0 time=16 us)'
STAT #2 id=4 cnt=1 pid=1 pos=2 obj=22 op='TABLE ACCESS CLUSTER USER$ (cr=2 pr=2 pw=0 time=16050 us)'
STAT #2 id=5 cnt=1 pid=4 pos=1 obj=11 op='INDEX UNIQUE SCAN I_USER# (cr=1 pr=1 pw=0 time=15264 us)'
ORA-00704: bootstrap process failure
ORA-00604: error occurred at recursive SQL level 1
ORA-01502: index 'SYS.I_DEPENDENCY1' or partition of such index is in unusable state
EXEC #1:c=252962,e=5328707,p=155,cr=730,cu=0,mis=0,r=0,dep=0,og=1,tim=1385948059778098
ERROR #1:err=1092 tim=432829200

可以看到，下面的递归SQL导致报错和数据库不能open：
select owner#,name,namespace,remoteowner,linkname,p_timestamp,p_obj#, nvl(property,0),subname,d_attrs
from dependency$ d, obj$ o where d_obj#=:1 and p_obj#=obj#(+) order by order#

这里绑定变量为122的正是I_DEPENDENCY1。

现在我们把数据库启动到upgrade然后修改flags=1024（即，标记该索引为无效）试试看：

[oracle@lunar oracle]$ ss

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Dec 22 09:22:06 2014

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to an idle instance.

SYS@lunar>startup mount
ORACLE instance started.

Total System Global Area  167772160 bytes
Fixed Size                  1218316 bytes
Variable Size              71305460 bytes
Database Buffers           92274688 bytes
Redo Buffers                2973696 bytes
Database mounted.
SYS@lunar>oradebug setmypid
Statement processed.
SYS@lunar>alter session set db_file_multiblocK_read_count=1;

Session altered.

SYS@lunar>alter session set tracefile_identifier='lunar';

Session altered.

SYS@lunar>oradebug event 10046 trace name context forever,level 12;
Statement processed.
SYS@lunar>oradebug tracefile_name
/home/oracle/oracle/product/admin/orcl/udump/orcl_ora_19834_lunar.trc
SYS@lunar>alter database open upgrade;      

Database altered.

SYS@lunar>

alert.log信息如下：

Mon Dec 22 09:23:02 2014
Successfully onlined Undo Tablespace 1.
Mon Dec 22 09:23:02 2014
SMON: enabling tx recovery
Mon Dec 22 09:23:03 2014
Database Characterset is ZHS16GBK
Mon Dec 22 09:23:06 2014
Stopping background process MMNL
Mon Dec 22 09:23:06 2014
Stopping background process MMON
Starting background process MMON
MMON started with pid=10, OS id=19842
Mon Dec 22 09:23:06 2014
Starting background process MMNL
MMNL started with pid=11, OS id=19844
Mon Dec 22 09:23:06 2014
ALTER SYSTEM enable restricted session;
Mon Dec 22 09:23:07 2014
ALTER SYSTEM SET _system_trig_enabled=FALSE SCOPE=MEMORY;
Mon Dec 22 09:23:07 2014
ALTER SYSTEM SET aq_tm_processes=0 SCOPE=MEMORY;
Mon Dec 22 09:23:07 2014
ALTER SYSTEM SET resource_manager_plan='' SCOPE=MEMORY;
replication_dependency_tracking turned off (no async multimaster replication found)
kwqiconfy: Warning AQ Kchunk open notifier failed with 1502 
 XDB UNINITIALIZED: XDB$SCHEMA not accessible 
Completed: alter database open upgrade
1



可以看到数据库可以启动到upgrade模式，找到刚才报错的语句，发现在upgrade模式下
1
=====================
PARSING IN CURSOR #4 len=221 dep=2 uid=0 oct=3 lid=0 tim=1385948615586776 hv=1926936385 ad='29b04538'
select /* ordered use_nl(o) index(d) index(o) */ owner#,name,namespace,remoteowner,linkname,p_timestamp,p_obj#, nvl(property,0),subname,d_attrs from dependency$ d, obj$ o where d_obj#=:1 and p_obj#=obj#(+) order by order#
END OF STMT
PARSE #4:c=2999,e=3438,p=4,cr=35,cu=0,mis=1,r=0,dep=2,og=4,tim=1385948615586774
=====================
PARSING IN CURSOR #2 len=84 dep=3 uid=0 oct=3 lid=0 tim=1385948615587498 hv=2686874206 ad='29b0396c'
select o.name, u.name from obj$ o, user$ u  where o.obj# = :1 and o.owner# = u.user#
END OF STMT
PARSE #2:c=0,e=279,p=0,cr=0,cu=0,mis=1,r=0,dep=3,og=4,tim=1385948615587494
BINDS #2:
kkscoacd
 Bind#0
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=08 fl2=0001 frm=00 csi=00 siz=24 off=0
  kxsbbbfp=b725753c  bln=22  avl=03  flg=05
  value=122
EXEC #2:c=1000,e=534,p=0,cr=0,cu=0,mis=1,r=0,dep=3,og=4,tim=1385948615588087
FETCH #2:c=0,e=56,p=0,cr=5,cu=0,mis=0,r=1,dep=3,og=4,tim=1385948615588159
STAT #2 id=1 cnt=1 pid=0 pos=1 obj=0 op='NESTED LOOPS  (cr=5 pr=0 pw=0 time=60 us)'
STAT #2 id=2 cnt=1 pid=1 pos=1 obj=18 op='TABLE ACCESS BY INDEX ROWID OBJ$ (cr=3 pr=0 pw=0 time=26 us)'
STAT #2 id=3 cnt=1 pid=2 pos=1 obj=36 op='INDEX UNIQUE SCAN I_OBJ1 (cr=2 pr=0 pw=0 time=15 us)'
STAT #2 id=4 cnt=1 pid=1 pos=2 obj=22 op='TABLE ACCESS CLUSTER USER$ (cr=2 pr=0 pw=0 time=23 us)'
STAT #2 id=5 cnt=1 pid=4 pos=1 obj=11 op='INDEX UNIQUE SCAN I_USER# (cr=1 pr=0 pw=0 time=6 us)'

具体SQL:

select /* ordered use_nl(o) index(d) index(o) */ owner#,name,namespace,remoteowner,linkname,p_timestamp,p_obj#, nvl(property,0),subname,d_attrs 
from dependency$ d, obj$ o where d_obj#=:1 and p_obj#=obj#(+) order by order#

查询一下dba_objects就会报ORA-00604 ORA-01502: index ‘SYS.I_DEPENDENCY1’ or partition of such index is in unusable state错误。
道理跟上面说的一样，索引损坏了，但是Oracle傻乎乎的非要走这个索引，因此就会报错：

SYS@lunar>select owner,object_id,object_name,object_type from dba_objects where object_id in (92,122,123);
select owner,object_id,object_name,object_type from dba_objects where object_id in (92,122,123)
                                                    *
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01502: index 'SYS.I_DEPENDENCY1' or partition of such index is in unusable state
SYS@lunar>

那么Oracle启动时为什么可以启动呢？
这是因为Oracle在Upgrade模式下会有几项不同于普通open的动作。
猜测正常启动数据库时因为执行递归sql来获取OBJ#和USER#时，执行计划中首先根据I_DEPENDENCY1来对DEPENDENCY1进行扫描。
因此该索引的不可用状态导致了数据库不能open。而upgrade模式启动数据库时，使用了/* ordered use_nl(o) index(d) index(o) */ hint。
因此获取OBJ#时使用了oid$来获取obj#，而没有使用SYS.I_DEPENDENCY1索引，因此，可以upgrade模式打开库。
通过tkprof可以看到这个递归sql的执行计划：

select /* ordered use_nl(o) index(d) index(o) */ owner#,name,namespace,
  remoteowner,linkname,p_timestamp,p_obj#, nvl(property,0),subname,d_attrs 
from
 dependency$ d, obj$ o where d_obj#=:1 and p_obj#=obj#(+) order by order#


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        5      0.00       0.00          0          0          0           0
Execute      0      0.00       0.00          0          0          0           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        5      0.00       0.00          0          0          0           0

Misses in library cache during parse: 5
Optimizer mode: CHOOSE
Parsing user id: SYS   (recursive depth: 2)
********************************************************************************

select obj# 
from
 oid$ where user#=:1 and oid$=:2


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      2      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.01          3          5          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        5      0.00       0.02          3          5          0           1

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: SYS   (recursive depth: 1)

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  TABLE ACCESS BY INDEX ROWID OID$ (cr=2 pr=2 pw=0 time=7433 us)
      0   INDEX UNIQUE SCAN I_OID1 (cr=2 pr=2 pw=0 time=7419 us)(object id 179)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  db file sequential read                         3        0.00          0.01

oid$的定义如下：

create table oid$                    /* OID mapping table for schema objects */
(
  user#         number not null,   /* user this mapping is for (user$.user#) */
  oid$          raw(16) not null,        /* OID for typed table/view or type */
  obj#          number not null)         /* target object number (obj$.obj#) */
                                                       /* key: (user#, oid$) */
/

现在我们来修复：

[oracle@lunar oracle]$ ss

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Dec 22 08:10:07 2014

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SYS@lunar>select obj# from obj$ where name='I_DEPENDENCY1';

      OBJ#
----------
       122


SYS@lunar>update ind$ set flags=1024 where obj#=122;

1 row updated.

SYS@lunar>select flags,obj# from ind$ where obj#=122;

     FLAGS       OBJ#
---------- ----------
      1024        122

SYS@lunar>commit;

Commit complete.

SYS@lunar>

重启数据库到限制模式：
SYS@lunar>alter index i_dependency1 rebuild
  2  ;

Index altered.

SYS@lunar>analyze table dependency$ validate structure cascade;

Table analyzed.

SYS@lunar>select owner,table_name,index_name,tablespace_name,status from dba_indexes where table_name='DEPENDENCY$';

OWNER           TABLE_NAME                     INDEX_NAME                     TABLESPACE_NAME                STATUS
--------------- ------------------------------ ------------------------------ ------------------------------ --------
SYS             DEPENDENCY$                    I_DEPENDENCY1                  SYSTEM                         VALID
SYS             DEPENDENCY$                    I_DEPENDENCY2                  SYSTEM                         VALID

SYS@lunar>

至此，数据库已经修复