If you cannot recover data by yourself, ask Parnassusdata, the professional ORACLE database recovery team for help.

Parnassusdata Software Database Recovery Team

Service Hotline:  +86 13764045638 E-mail: service@parnassusdata.com

    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でデータベースをアップグレードモードに調整して、リカバリする まずはデータベースに依頼関係があるテーブルは何があるかを見てみよう：
[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) /
次に、どんなブロックを使ったかを探ってみよう：
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によって、データベースが起動できなくなる場合に、このマークでリカバリできる。 例えば、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のエラによって、データベースが起動できなくなるかもしれない： 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       データベースがアップグレードモードを起動した。先のエラ文を探し出して、アップグレードモードにいると見つけた 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モードにいれば、いくつか普通の起動と違った操作がある。 正常にデータベースを起動した時に、アーカイブsqlでOBJ#とUSER#を獲得すると、実行計画はI_DEPENDENCY1でDEPENDENCY1をスキャンする。 インディクスが使えなくなったからデータベースも起動できなくなった。それに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>
ここで、データベースを完璧にリカバリした