删除undo表空间数据库启动报ORA-01157 和ORA-01110

[路人呵呵]

Errors in file /u01/app/oracle/diag/rdbms/yongdb/YONGDB/trace/YONGDB_dbw0_2898.trc:
ORA-01157: cannot identify/lock data file 3 - see DBWR trace file
ORA-01110: data file 3: '/u01/app/oracle/oradata/YONGDB/undotbs01.dbf'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Errors in file /u01/app/oracle/diag/rdbms/yongdb/YONGDB/trace/YONGDB_ora_2948.trc:
ORA-01157: cannot identify/lock data file 3 - see DBWR trace file
ORA-01110: data file 3: '/u01/app/oracle/oradata/YONGDB/undotbs01.dbf'


undo表空间的数据文件被我删除了.数据库处于归档状态

我尝试
①、创建pfile
startup nomount
create pfile=<path> from spfile;

②、修改pfile
undo_tablespace='SYSTEM'
undo_management='MANUAL'
*._allow_resetlogs_corruption=true
（注：允许在数据库文件SCN不一致的情况下启动数据库）
*._corrupted_rollback_segments=(_SYSSMU1$,_SYSSMU2$,_SYSSMU3$,_SYSSMU4$,_SYSSMU5$,_SYSSMU6$,_SYSSMU7$,_SYSSMU8$,_SYSSMU9$,_SYSSMU10$)
（注：允许在rollback segments损坏的情况下启动数据库）
(注：rollback的名称要与已经损坏的undo表空间的rollback segment号保持一致，不然导致无法删除损坏的undo表空间）

③、使用修改后pfile启动数据库
startup mount restrict pfile='<pfile_path>'

④、修改损坏数据文件状态
select name,file#,status from v$datafile;
SQL> select name,file#,status from v$datafile;

NAME FILE# STATUS
--------------------------------------------- ---------- -------
/u01/app/oracle/oradata/YONGDB/system01.dbf 1 SYSTEM
/u01/app/oracle/oradata/YONGDB/sysaux01.dbf 2 ONLINE
/u01/app/oracle/oradata/YONGDB/undotbs01.dbf 3 RECOVER
/u01/app/oracle/oradata/YONGDB/users01.dbf 4 ONLINE
/u01/app/oracle/oradata/YONGDB/example01.dbf 5 ONLINE
/u01/app/oracle/oradata/YONGDB/app01.dbf 6 ONLINE
/u01/app/oracle/oradata/YONGDB/app02_00.dbf 7 ONLINE
/u01/app/oracle/oradata/YONGDB/app03_01.dbf 8 ONLINE
/u01/app/oracle/oradata/YONGDB/ts_16k.dbf 9 ONLINE
/u01/app/oracle/oradata/YONGDB/spot.dbf 10 ONLINE




alter database datafile 3 offline drop;
Database altered.

④、打开数据库
alter database open;

ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00604: error occurred at recursive SQL level 1
ORA-00376: file 3 cannot be read at this time
ORA-01110: data file 3: '/u01/app/oracle/oradata/YONGDB/undotbs01.dbf'
Process ID: 6816
Session ID: 89 Serial number: 3

然后数据库挂掉...
求大神帮助

[luoyoumou]

-- alter database open force;
-- 然后：重建undo表空间的数据文件，OK了!

[luoyoumou]

19:51:31 SYS@myora > alter database open;
alter database open
*
第 1 行出现错误:
ORA-01157: 无法标识/锁定数据文件 3 - 请参阅 DBWR 跟踪文件
ORA-01110: 数据文件 3: 'D:\APP\ADMINISTRATOR\ORADATA\MYORA\UNDOTBS01.DBF'


19:51:36 SYS@myora > alter database datafile 'D:\APP\ADMINISTRATOR\ORADATA\MYORA\UNDOTBS01.DBF' offline drop;

数据库已更改。

19:52:59 SYS@myora > alter database open;

数据库已更改。

19:53:18 SYS@myora > create undo tablespace undotbs2 datafile 'D:\app\Administrator\oradata\myora\undotbs2_01.dbf' size 1024M;

表空间已创建。

19:54:31 SYS@myora > alter system set undo_tablespace=undotbs2 scope=both;

系统已更改。

19:54:57 SYS@myora > drop tablespace undotbs1;

表空间已删除。

[惜分飞]

可以加我qq

[路人呵呵]

[Quote=引用 3 楼 的回复:]

可以加我qq
[/Quote]

已发送邀请 （¯﹃¯）口水