上一篇，我们测试了10.2中，i_dependency1失效的过程和处理方法。
参考10g中I_DEPENDENCY1损坏的处理
这里，我们在11.2中模拟i_dependency1失效（测试环境使用11.2.0.3）：
11.2中：先看一下，数据库中跟依赖关系相关的表都有哪些：
SQL> select owner,object_id,object_name,object_type from dba_objects where object_name like '%DEPENDENCY%'; OWNER OBJECT_ID OBJECT_NAME OBJECT_TYPE --------------- ---------- ------------------------------ ------------------- SYS 104 DEPENDENCY$ TABLE SYS 106 I_DEPENDENCY1 INDEX SYS 107 I_DEPENDENCY2 INDEX SYS 1511 V_$OBJECT_DEPENDENCY VIEW SYS 2200 GV_$OBJECT_DEPENDENCY VIEW SYS 2837 GV_$SQL_FEATURE_DEPENDENCY VIEW SYS 2839 V_$SQL_FEATURE_DEPENDENCY VIEW SYS 2857 GV_$RESULT_CACHE_DEPENDENCY VIEW SYS 2859 V_$RESULT_CACHE_DEPENDENCY VIEW SYS 4841 PUBLIC_DEPENDENCY VIEW PUBLIC 1512 V$OBJECT_DEPENDENCY SYNONYM PUBLIC 2201 GV$OBJECT_DEPENDENCY SYNONYM PUBLIC 2838 GV$SQL_FEATURE_DEPENDENCY SYNONYM PUBLIC 2840 V$SQL_FEATURE_DEPENDENCY SYNONYM PUBLIC 2858 GV$RESULT_CACHE_DEPENDENCY SYNONYM PUBLIC 2860 V$RESULT_CACHE_DEPENDENCY SYNONYM PUBLIC 4842 PUBLIC_DEPENDENCY SYNONYM SYSMAN 14862 MGMT_INV_DEPENDENCY_RULE TABLE SYSMAN 15200 MGMT_METRIC_DEPENDENCY_DEF TABLE SYSMAN 15201 PK_MGMT_METRIC_DEPENDENCY_DEF INDEX SYSMAN 15202 MGMT_METRIC_DEPENDENCY TABLE SYSMAN 15203 PK_MGMT_METRIC_DEPENDENCY INDEX SYSMAN 15204 MGMT_METRIC_DEPENDENCY_DETAILS TABLE SYSMAN 15851 MGMT_METRIC_DEPENDENCY_IDX_01 INDEX 24 rows selected. SQL>
可以看到，10g是18个，11.2是24个。
SQL> 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_DEPENDENCY2 SYSTEM VALID SYS DEPENDENCY$ I_DEPENDENCY1 SYSTEM VALID SQL>
10g和11g中，DEPENDENCY$表都存在两个索引，这两个索引损坏时对数据库的影响是不同的，他们分别定义如下：
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中：
SQL> col segment_name for a30 SQL> 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 I_DEPENDENCY2 INDEX 0 1 864 SYS I_DEPENDENCY2 INDEX 1 1 8584 SYS I_DEPENDENCY2 INDEX 2 1 8856 SYS I_DEPENDENCY2 INDEX 3 1 9000 SYS I_DEPENDENCY2 INDEX 4 1 10072 SYS I_DEPENDENCY2 INDEX 5 1 12792 SYS I_DEPENDENCY2 INDEX 6 1 16128 SYS I_DEPENDENCY2 INDEX 7 1 18752 SYS I_DEPENDENCY2 INDEX 8 1 19960 SYS I_DEPENDENCY2 INDEX 9 1 21328 SYS I_DEPENDENCY2 INDEX 10 1 21584 SYS I_DEPENDENCY2 INDEX 11 1 22288 SYS I_DEPENDENCY2 INDEX 12 1 22888 SYS I_DEPENDENCY2 INDEX 13 1 23408 SYS I_DEPENDENCY2 INDEX 14 1 25616 SYS I_DEPENDENCY2 INDEX 15 1 25672 SYS I_DEPENDENCY2 INDEX 16 1 28672 SYS I_DEPENDENCY1 INDEX 0 1 856 SYS I_DEPENDENCY1 INDEX 1 1 8720 SYS I_DEPENDENCY1 INDEX 2 1 8984 SYS I_DEPENDENCY1 INDEX 3 1 10712 SYS I_DEPENDENCY1 INDEX 4 1 14968 SYS I_DEPENDENCY1 INDEX 5 1 19248 SYS I_DEPENDENCY1 INDEX 6 1 20760 SYS I_DEPENDENCY1 INDEX 7 1 21312 SYS I_DEPENDENCY1 INDEX 8 1 21608 SYS I_DEPENDENCY1 INDEX 9 1 22344 SYS I_DEPENDENCY1 INDEX 10 1 23368 SYS I_DEPENDENCY1 INDEX 11 1 25608 SYS I_DEPENDENCY1 INDEX 12 1 25688 SYS I_DEPENDENCY1 INDEX 13 1 29192 SYS I_DEPENDENCY1 INDEX 14 1 32760 SYS I_DEPENDENCY1 INDEX 15 1 34240 SYS I_DEPENDENCY1 INDEX 16 1 37504 SYS DEPENDENCY$ TABLE 0 1 840 SYS DEPENDENCY$ TABLE 1 1 8624 SYS DEPENDENCY$ TABLE 2 1 8800 SYS DEPENDENCY$ TABLE 3 1 8968 SYS DEPENDENCY$ TABLE 4 1 9640 SYS DEPENDENCY$ TABLE 5 1 10080 SYS DEPENDENCY$ TABLE 6 1 12992 SYS DEPENDENCY$ TABLE 7 1 15544 SYS DEPENDENCY$ TABLE 8 1 18728 SYS DEPENDENCY$ TABLE 9 1 19848 SYS DEPENDENCY$ TABLE 10 1 20768 SYS DEPENDENCY$ TABLE 11 1 21296 SYS DEPENDENCY$ TABLE 12 1 21576 SYS DEPENDENCY$ TABLE 13 1 21832 SYS DEPENDENCY$ TABLE 14 1 22808 SYS DEPENDENCY$ TABLE 15 1 23344 SYS DEPENDENCY$ TABLE 16 1 24704 SYS DEPENDENCY$ TABLE 17 1 45440 SYSMAN PK_MGMT_METRIC_DEPENDENCY_DEF INDEX 0 2 14608 SYSMAN MGMT_METRIC_DEPENDENCY_DEF TABLE 0 2 14600 54 rows selected. SQL>
10g和11g的DEPENDENCY$ 和 I_DEPENDENCY1、I_DEPENDENCY2定义都一致的，只是处理方法不同。
现在我们在11.2.0.3模拟i_dependency1失效，看看什么效果：
SYS@lunarp>alter index i_dependency1 unusable; Index altered. Elapsed: 00:00:00.42 SYS@lunarp>commit; Commit complete. Elapsed: 00:00:00.00 SYS@lunarp>
相应的alert.log信息如下:
Wed Mar 05 01:50:18 2014 Index SYS.I_DEPENDENCY1 or some [sub]partitions of the index have been marked unusable
说明已经标示 SYS.I_DEPENDENCY1 为无效了。
现在启动数据库看看：
SYS@lunarp>startup mount Welcome Lunar's oracle world! Love you , baby ! ORACLE instance started. Total System Global Area 626327552 bytes Fixed Size 2230952 bytes Variable Size 243271000 bytes Database Buffers 373293056 bytes Redo Buffers 7532544 bytes Database mounted. Welcome Lunar's oracle world! Love you , baby ! SYS@lunarp>oradebug setmypid Statement processed. SYS@lunarp>alter session set db_file_multiblocK_read_count=1; Session altered. Elapsed: 00:00:00.00 SYS@lunarp>alter session set tracefile_identifier='lunar'; Session altered. Elapsed: 00:00:00.01 SYS@lunarp>oradebug event 10046 trace name context forever,level 12; Statement processed. SYS@lunarp>oradebug tracefile_name /u01/app/oracle/diag/rdbms/lunarp/lunarp/trace/lunarp_ora_8257_lunar.trc SYS@lunarp>alter database open; ----------注意这里，直接open Database altered. Elapsed: 00:00:08.91 SYS@lunarp>oradebug event 10046 trace name context off Statement processed. SYS@lunarp>oradebug close_trace Statement processed. SYS@lunarp>
没错，就是这个结果，从11.2开始，类似I_DEPENDENCY1这样的index的失效不会数据库不能open了。
而在10.2中，需要open upgrade模式。
现在修复这个索引：
SYS@lunarp>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_DEPENDENCY2 SYSTEM VALID SYS DEPENDENCY$ I_DEPENDENCY1 SYSTEM UNUSABLE Elapsed: 00:00:00.16 SYS@lunarp>alter index sys.I_DEPENDENCY1 rebuild online; Index altered. Elapsed: 00:00:02.76 SYS@lunarp>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_DEPENDENCY2 SYSTEM VALID SYS DEPENDENCY$ I_DEPENDENCY1 SYSTEM VALID Elapsed: 00:00:00.00 SYS@lunarp>
注意 ：11.2跟10.2还有一个小区别（感觉无关紧要）：11.2多取了一个字段“type#”：
select owner#,name,namespace,remoteowner,linkname,p_timestamp,p_obj#, nvl(property,0),subname,type#,d_attrs from dependency$ d, obj$ o where d_obj#=:1 and p_obj#=obj#(+) order by order#
那么11.2为什么可以直接open呢？
首先，我们来看一下，当 I_DEPENDENCY1 正常时，数据库open的相关动作：
PARSING IN CURSOR #140636885644848 len=185 dep=1 uid=0 oct=3 lid=0 tim=1394010624511477 hv=1850944673 ad='850425a8' sqlid='3ktacv9r56b51' select owner#,name,namespace,remoteowner,linkname,p_timestamp,p_obj#, nvl(property,0),subname,type#,d_attrs from dependency$ d, obj$ o where d_obj#=:1 and p_obj#=obj#(+) order by order# END OF STMT PARSE #140636885644848:c=5999,e=20725,p=2,cr=41,cu=0,mis=1,r=0,dep=1,og=4,plh=0,tim=1394010624511472 BINDS #140636885644848: 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=7fe8939c71e8 bln=22 avl=03 flg=05 value=426 EXEC #140636885644848:c=1000,e=1100,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,plh=4184428695,tim=1394010624512679 WAIT #140636885644848: nam='db file sequential read' ela= 4373 file#=1 block#=857 blocks=1 obj#=106 tim=1394010624517146 WAIT #140636885644848: nam='db file sequential read' ela= 299 file#=1 block#=858 blocks=1 obj#=106 tim=1394010624517612 FETCH #140636885644848:c=1000,e=4978,p=2,cr=2,cu=0,mis=0,r=0,dep=1,og=4,plh=4184428695,tim=1394010624517698 STAT #140636885644848 id=1 cnt=0 pid=0 pos=1 obj=0 op='SORT ORDER BY (cr=2 pr=2 pw=0 time=5006 us cost=0 size=0 card=0)' STAT #140636885644848 id=2 cnt=0 pid=1 pos=1 obj=0 op='NESTED LOOPS OUTER (cr=2 pr=2 pw=0 time=4974 us)' STAT #140636885644848 id=3 cnt=0 pid=2 pos=1 obj=104 op='TABLE ACCESS BY INDEX ROWID DEPENDENCY$ (cr=2 pr=2 pw=0 time=4956 us)' STAT #140636885644848 id=4 cnt=0 pid=3 pos=1 obj=106 op='INDEX RANGE SCAN I_DEPENDENCY1 (cr=2 pr=2 pw=0 time=4936 us)' STAT #140636885644848 id=5 cnt=0 pid=2 pos=2 obj=18 op='TABLE ACCESS BY INDEX ROWID OBJ$ (cr=0 pr=0 pw=0 time=0 us)' STAT #140636885644848 id=6 cnt=0 pid=5 pos=1 obj=36 op='INDEX RANGE SCAN I_OBJ1 (cr=0 pr=0 pw=0 time=0 us)' CLOSE #140636885644848:c=0,e=90,dep=1,type=0,tim=1394010624517803 BINDS #140636885393152:
该语句执行7次，七次的执行计划都是上面这样的。
当I_DEPENDENCY1为UNUSABLE时，该语句仍然执行7次，但是7次的执行计划都类似下面的，走“TABLE ACCESS FULL DEPENDENCY$”。
这说明oracle 11.2的二进制代码多了一个判断的功能，当发现I_DEPENDENCY1为UNUSABLE时自动修改了执行计划（没有验证其他类似索引是否都是这样的）：
select owner#,name,namespace,remoteowner,linkname,p_timestamp,p_obj#,
nvl(property,0),subname,type#,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 33 0.00 0.02 2 41 0 0
Execute 33 0.00 0.00 0 0 0 0
Fetch 127 0.11 0.55 285 8764 0 94
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 193 0.12 0.58 287 8805 0 94
Misses in library cache during parse: 2
Misses in library cache during execute: 2
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 1)
Number of plan statistics captured: 7
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
0 2 6 SORT ORDER BY (cr=263 pr=37 pw=0 time=42164 us cost=0 size=0 card=0)
0 2 6 NESTED LOOPS OUTER (cr=263 pr=37 pw=0 time=41858 us)
0 2 6 TABLE ACCESS FULL DEPENDENCY$ (cr=259 pr=37 pw=0 time=38319 us)
0 2 6 TABLE ACCESS BY INDEX ROWID OBJ$ (cr=4 pr=1 pw=0 time=3497 us)
0 2 6 INDEX RANGE SCAN I_OBJ1 (cr=3 pr=0 pw=0 time=995 us)(object id 36)
推测：11.2的代码里面写死了一个判断当open数据库时，如果遇到类似DEPENDENCY$这样的表的索引失效了，那么直接对DEPENDENCY$进行全表扫描。
