11.2中I_DEPENDENCY1损坏的处理

上一篇,我们测试了10.2中,i_dependency1失效的过程和处理方法。
参考10g中I_DEPENDENCY1损坏的处理

I_DEPENDENCY2损坏的处理

这里,我们在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$进行全表扫描。

此条目发表在 未分类 分类目录。将固定链接加入收藏夹。

评论功能已关闭。