oracle 10.0.2.4单实例ORA-01555:错误导致数据库无法启动

luckboy

如题：
ORA-00704: bootstrap process failure
ORA-00704: bootstrap process failure
ORA-00604: error occurred at recursive SQL level 1
ORA-01555: snapshot too old: rollback segment number 10 with name "_SYSSMU10$" too small
Error 704 happened during db open, shutting down database
USER: terminating instance due to error 704
Thu Mar 13 07:30:05 2014
Errors in file /home/u01/app/admin/orcl/bdump/orcl_pmon_5038.trc:
ORA-00704: bootstrap process failure
Instance terminated by USER, pid = 12806
ORA-1092 signalled during: alter database Open...


部分alter日志如上，希望各位能给予指教。

附件信息有alter，10046，db_recover.html

xifenfei

1. 数据库版本10.2.0.4
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod
PL/SQL Release 10.2.0.4.0 - Production
CORE        10.2.0.4.0        Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production


2. 报错数据库有备份
BACKUP SET         SET_STAMP         Type LV         inc         STATUS         Device Type         Start Time         Completion Time         Elapsed Seconds         Tag         Path
26         838261421         Full         NO         AVAILABLE         DISK         2014-01-31 02:23:42         2014-01-31 02:25:56         134         TAG20140131T022341         /tmp/back2/ORCL/backupset/2014_01_31/o1_mf_nnndf_TAG20140131T022341_9go65g3n_.bk p
25         838261537         Full         NO         AVAILABLE         DISK         2014-01-31 02:25:37         2014-01-31 02:25:42         5         TAG20140131T022341         /tmp/back2/ORCL/backupset/2014_01_31/o1_mf_nnsnf_TAG20140131T022341_9go69213_.bk p
24         838261527         Full         YES         AVAILABLE         DISK         2014-01-31 02:25:34         2014-01-31 02:25:35         1         TAG20140131T022341         /tmp/back2/ORCL/backupset/2014_01_31/o1_mf_ncnnf_TAG20140131T022341_9go68yo7_.bk p



具体见附件里面的db_recover_xifenfei.html 脚本，也可以考虑使用备份恢复

3. 启动数据库报错语句
PARSING IN CURSOR #6 len=142 dep=2 uid=0 oct=3 lid=0 tim=1361979497496649 hv=361892850 ad='44af2c28'
select /*+ rule */ name,file#,block#,status$,user#,undosqn,xactsqn,scnbas,scnwrp,DECODE(inst#,0,NULL,inst#),ts#,spare1 from undo$ where us#=:1
END OF STMT
PARSE #6:c=0,e=121,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=3,tim=1361979497496642
BINDS #6:
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=b7e4e9c4  bln=22  avl=02  flg=05
  value=10
EXEC #6:c=0,e=200,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=3,tim=1361979497497074
FETCH #6:c=0,e=34,p=0,cr=2,cu=0,mis=0,r=1,dep=2,og=3,tim=1361979497497153
STAT #6 id=1 cnt=1 pid=0 pos=1 obj=15 op='TABLE ACCESS BY INDEX ROWID UNDO$ (cr=2 pr=0 pw=0 time=36 us)'
STAT #6 id=2 cnt=1 pid=1 pos=1 obj=34 op='INDEX UNIQUE SCAN I_UNDO1 (cr=1 pr=0 pw=0 time=13 us)'
WAIT #5: nam='db file sequential read' ela= 171 file#=2 block#=2446 blocks=1 obj#=-1 tim=1361979497497704
FETCH #5:c=15997,e=17072,p=7,cr=9,cu=0,mis=0,r=0,dep=1,og=4,tim=1361979497498976
ORA-00704: bootstrap process failure
ORA-00704: bootstrap process failure
ORA-00604: error occurred at recursive SQL level 1
ORA-01555: snapshot too old: rollback segment number 10 with name "_SYSSMU10$" too small


初步可以确定，是在数据库启动的时候读取undo$的记录时候，需要_SYSSMU10$ 回滚段信息，但是因为某种原因导致该回滚段信息丢失或者覆盖，出现该错误


尝试dump file 2 block 2446 ，dump file 1 block  106和202，上传trace，我分析下

认真就输

一般情况下推SCN的值，可以解决当前的报错。

travel.liu

推下scn 试试吧

luckboy

dump datafile 时出现如下情况
alter system dump datafile/tempfile: file 2 not readable
*** 2014-03-14 01:11:56.630
alter system dump datafile/tempfile: file 1 not readable
alter system dump datafile/tempfile: file 1 not readable

xifenfei

alter system dump datafile '/home/db/orcl/system01.dbf' block 106;
alter system dump datafile '/home/db/orcl/system01.dbf' block 202;
alter system dump datafile '/home/db/orcl/undotbs01.dbf' block 2446;

luckboy

谢谢飞总提醒，之前可能收集有误，见谅！~

xifenfei

Block header dump:  0x0040006a
Object id on Block? Y
seg/obj: 0xf  csc: 0x00.29b6a1  itc: 1  flg: O  typ: 1 - DATA
     fsl: 0  fnx: 0x0 ver: 0x01

Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0000.01b.00000035  0x0040000e.003c.18  --U-    1  fsc 0x0000.0029b6a2

你可以尝试使用bbed 提交该事物，然后看看

xifenfei

这个数据块的scn为 :2733729，且为提交的事务
数据库的scn为:917440
所以数据库读到undo$的记录的时候，发现有个未提交事务，然后去找undo段，但是因为这个scn比较大，可能在回滚段中未发现记录，从而出现启动时候异常提示

luckboy

谢谢飞总！~我试试你说的办法，然后会回馈信息