ORA-00704 ORA-00604 ORA-01502: index ‘SYS.I_ACCESS1’ or partition of such index is in unusable state

联系: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损坏的处理

11.2中I_DEPENDENCY1损坏的处理

例如:

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>
此条目发表在 backup&recovery, ORA-600 or ORA-7445 分类目录,贴了 , 标签。将固定链接加入收藏夹。

发表评论

电子邮件地址不会被公开。 必填项已用 * 标注