剔除undo表空间数据库启动报ORA-01157 和ORA-01110
删除undo表空间数据库启动报ORA-01157 和ORA-01110
本帖最后由 lurenheheyy 于 2012-10-31 19:39:04 编辑 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.
本帖最后由 lurenheheyy 于 2012-10-31 19:39:04 编辑 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.