联系:QQ(5163721)
标题:ORA-00704 ORA-00604 ORA-01502: index ‘SYS.I_ACCESS1’ or partition of such index is in unusable state
作者:Lunar©版权所有[文章允许转载,但必须以链接方式注明源地址,否则追究法律责任.]
如何处理下面的错误?
ORA-00704 ORA-00604 ORA-01502: index ‘SYS.I_ACCESS1’ or partition of such index is in unusable state
方法跟ORA-00704 ORA-00604 ORA-01502 SYS.I_DEPENDENCY1损坏或者不可用的是一样的,这里不赘述,简单模拟损坏并修复的过程如下:
I_DEPENDENCY2损坏的处理
例如:
Mon Dec 22 11:38:44 2014 SMON: enabling cache recovery Mon Dec 22 11:38:45 2014 Deleted Oracle managed file /home/oracle/oracle/product/flash_recovery_area/ORCL/archivelog/2009_11_21/o1_mf_1_48_5jgg8461_.arc Mon Dec 22 11:38:45 2014 Errors in file /home/oracle/oracle/product/admin/orcl/udump/orcl_ora_27945.trc: ORA-00704: bootstrap process failure ORA-00604: error occurred at recursive SQL level 1 ORA-01502: index 'SYS.I_ACCESS1' or partition of such index is in unusable state Mon Dec 22 11:38:45 2014 Error 704 happened during db open, shutting down database USER: terminating instance due to error 704 Instance terminated by USER, pid = 27945 ORA-1092 signalled during: ALTER DATABASE OPEN...
相应的trace如下:
PARSING IN CURSOR #5 len=56 dep=1 uid=0 oct=3 lid=0 tim=1385956621228493 hv=3993603298 ad='29b047b0' select order#,columns,types from access$ where d_obj#=:1 END OF STMT PARSE #5:c=2000,e=1891,p=1,cr=24,cu=0,mis=1,r=0,dep=1,og=4,tim=1385956621228490 ===================== PARSING IN CURSOR #2 len=84 dep=2 uid=0 oct=3 lid=0 tim=1385956621229181 hv=2686874206 ad='29b03e00' 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=531,p=0,cr=0,cu=0,mis=1,r=0,dep=2,og=4,tim=1385956621229176 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=b72384c8 bln=22 avl=03 flg=05 value=124 EXEC #2:c=0,e=710,p=0,cr=0,cu=0,mis=1,r=0,dep=2,og=4,tim=1385956621229957 WAIT #2: nam='db file sequential read' ela= 11 file#=1 block#=98 blocks=1 obj#=-1 tim=1385956621230031 WAIT #2: nam='db file sequential read' ela= 13 file#=1 block#=90 blocks=1 obj#=-1 tim=1385956621230124 FETCH #2:c=1000,e=182,p=2,cr=5,cu=0,mis=0,r=1,dep=2,og=4,tim=1385956621230156 STAT #2 id=1 cnt=1 pid=0 pos=1 obj=0 op='NESTED LOOPS (cr=5 pr=2 pw=0 time=187 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=146 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=96 us)' ORA-00704: bootstrap process failure ORA-00604: error occurred at recursive SQL level 1 ORA-01502: index 'SYS.I_ACCESS1' or partition of such index is in unusable state EXEC #1:c=144978,e=1804415,p=119,cr=759,cu=0,mis=0,r=0,dep=0,og=1,tim=1385956622211453 ERROR #1:err=1092 tim=433705993
解决问题:
SYS@lunar>alter database open upgrade; Database altered. SYS@lunar>select obj# from obj$ where name='I_ACCESS1'; OBJ# ---------- 124 SYS@lunar>update ind$ set flags=1024 where obj#=124; 1 row updated. SYS@lunar>commit; Commit complete. SYS@lunar>
然后将数据库启动到限制模式,rebuild该index:
SYS@lunar>alter index I_ACCESS1 rebuild; Index altered. SYS@lunar>