数据库不能open

lottery009

操作:
  对表做了move后,没有rebuild index,然后就关闭数据库了,导致数据库不能重新启动

alter<sid>.log
  Errors in file /space/oracle/admin/test/udump/test_ora_22836.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 May 28 16:31:10 2007
Error 704 happened during db open, shutting down database
USER: terminating instance due to error 704
Instance terminated by USER, pid = 22836
ORA-1092 signalled during: ALTER DATABASE OPEN...


more /space/oracle/admin/test/udump/test_ora_22836.trc:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
ORACLE_HOME = /space/oracle/product/9.2.0
System name:    SunOS
Node name:      test
Release:        5.10
Version:        Generic
Machine:        sun4u
Instance name: test
Redo thread mounted by this instance: 1
Oracle process number: 12
Unix process pid: 22836, image: oracle@test (TNS V1-V3)

*** SESSION ID11.3) 2007-05-28 16:31:08.284
Thread 1 recovery from rba:0x000722.00000002.0010 scn:0x0000.0386d5a1
----- Redo read statistics for thread 1 -----
Read rate (ASYNC) = 0Kb/sec => 1 blocks in 1s
Read buffer = 8192Kb (16384 blocks)
Longest record = 0Kb
Record moves = 0/1 (0%)
----------------------------------------------
*** 2007-05-28 16:31:08.330
KCRA: start recovery claims for 0 data blocks
*** 2007-05-28 16:31:08.330
KCRA: buffers claimed = 0/0, eliminated = 0
- Created archivelog as '/space/oracle/archive/1_1824.dbf'
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

eygle

晕，SYS的对象你也Move了？

d.c.b.a

没事去move系统表做什么啊?

stronghearted

　　案例七：有人Move了系统表Dependencie＄表， Crash了

　　今天有人问我这样之后能不能恢复， 我想基本上已经不能了。 在open时报ORA-01092号错误， 我查了一下event也没有这方面的合适的event啊， 我推荐用不完全恢复， 不过好象是没有备份， 运行在noarchivelog模式。

　　从trc文件中得到的内容：

KCRA: buffers claimed = 0/0, eliminated = 0
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
oerr ora 704
00704, 00000, "bootstrap process failure"
// *Cause: Failure in processing bootstrap data - see accompanying error.
// *Action: Contact your customer support representative.
SQL_TRACE打开的情况下生成的Trace:
PARSING IN CURSOR #9 len=84 dep=2 uid=0 oct=3 lid=0 tim=18446744073254091198
hv=2287793623 ad='66f6c06c'
select o.name, u.name from obj＄ o, user＄ u where o.obj# = :1 and o.owner# = u.user#
END OF STMT
PARSE #9:c=0,e=343,p=0,cr=0,cu=0,mis=1,r=0,dep=2,og=0,tim=18446744073254091193
EXEC #9:c=0,e=186,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=4,tim=18446744073254091456
FETCH #9:c=0,e=28019,p=2,cr=5,cu=0,mis=0,r=1,dep=2,og=4,tim=18446744073254119501
STAT #9 id=1 cnt=1 pid=0 pos=1 obj=0 op='NESTED LOOPS '
STAT #9 id=2 cnt=1 pid=1 pos=1 obj=18 op='TABLE ACCESS BY INDEX ROWID OBJ#(18) '
STAT #9 id=3 cnt=1 pid=2 pos=1 obj=36 op='INDEX UNIQUE SCAN OBJ#(36) '
STAT #9 id=4 cnt=1 pid=1 pos=2 obj=22 op='TABLE ACCESS CLUSTER OBJ#(22) '
STAT #9 id=5 cnt=1 pid=4 pos=1 obj=11 op='INDEX UNIQUE SCAN OBJ#(11) '
ORA-00704: 引导程序进程失败
ORA-00604: 递归 SQL 层 1 出现错误
ORA-01502: 索引'SYS.I_DEPENDENCY1'或这类索引的分区处于不可用状态
EXEC #1:c=109375,e=5578667,p=44,cr=616,cu=1,mis=0,r=0,dep=0,og=4,
tim=18446744073255895570
ERROR #1:err=1092 tim=23012387


　　DBA做事一定要细心， 在运行批处理时一定要审了再审。

　　补充：

　　后来我用AnySQL UnLoader去恢复数据了， 和客户一起花了24小时， 最后他们说OK了。

　　Eygle和Chensq对这个问题也有研究， 他们想出了更好的办法解决此事， 不过最后原来的库肯定是不能再用了， 必须要exp/imp到别的库了， 我是用AUL帮客户恢复数据的， 数据量在30G以上。

stronghearted

有一条路，备份了没？
引用eygle的一句话：备份重于一切！

zhang41082

好像没治了吧？

eagle_fan

简单，用备份恢复啊：）

lsmiler

这种问题貌似很难。。。。
要是没有恢复，没有第三方软件