I_DEPENDENCY2损坏的处理

我们知道，在10.2中如果SYS.I_DEPENDENCY1损坏或者不可用时，数据库open时就会报如下类似错误：
10g中I_DEPENDENCY1损坏的处理

11.2中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

那么如果I_DEPENDENCY2有问题时，什么结果呢？
我们还是在10.2.0.1中测试：

[oracle@lunar oracle]$ ss

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Dec 22 11:13:21 2014

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

Connected to an idle instance.

SYS@lunar>startup
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.
Database opened.
SYS@lunar>
SYS@lunar>alter index i_dependency2 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                         VALID
SYS             DEPENDENCY$                    I_DEPENDENCY2                  SYSTEM                         UNUSABLE

SYS@lunar>

测试结果，数据库可以正常open，但是alert后报错，显示如下：

Mon Dec 22 11:12:17 2014
Successfully onlined Undo Tablespace 1.
Mon Dec 22 11:12:17 2014
SMON: enabling tx recovery
Mon Dec 22 11:12:17 2014
Errors in file /home/oracle/oracle/product/admin/orcl/bdump/orcl_smon_19955.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-01502: index 'SYS.I_DEPENDENCY2' or partition of such index is in unusable state
Mon Dec 22 11:12:17 2014
Database Characterset is ZHS16GBK
replication_dependency_tracking turned off (no async multimaster replication found)
WARNING: AQ_TM_PROCESSES is set to 0. System operation might be adversely affected.
Mon Dec 22 11:12:20 2014
db_recovery_file_dest_size of 2048 MB is 43.37% used. This is a
user-specified limit on the amount of space that will be used by this
database for recovery-related files, and does not reflect the amount of
space available in the underlying filesystem or ASM diskgroup.
Mon Dec 22 11:12:21 2014
Starting background process CJQ0
CJQ0 started with pid=20, OS id=19979
Mon Dec 22 11:12:21 2014
Completed: alter database open

修复的方法很简单，直接将该索引的状态设置为失效，然后rebuild，具体操作，参考ORA-00704 ORA-00604 ORA-01502 SYS.I_DEPENDENCY1损坏或者不可用

SYS@lunar>alter index i_dependency2 rebuild
  2  ;

Index altered.

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

Table analyzed.

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

      OBJ#      FLAGS
---------- ----------
       123          2

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>