在没备份undo的情况下，undo丢失，重启
数据库报ORA-01157错误解决
今天做了一下undo隐藏参数的实验
在没有备份的情况下，删除正在使用的undo,然后关机
(本次使用的的oracle的隐藏参数，慎用！！！！！！！！！！！！！！)
idle> select * from V$VERSION;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0Production
TNS for
Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
sys@DNDN> startup
ORACLE instance started.
Total System Global Area 335544320 bytes
Fixed Size 2020640 bytes
Variable Size 117443296 bytes
Database Buffers 213909504 bytes
Redo Buffers 2170880 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 2 - see DBWR trace file
ORA-01110: data file 2: '/u01/app/oracle/oradata/dndn/undotbs1.dbf'
sys@DNDN> show parameter undo_t
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_tablespace stringUNDOTBS1
在此之前我有建立有一个未使用的undo 名字叫undotbs(区别上面的undotbs1)（*注：若没有多余的undo,则见文章最后解决办法）
sys@DNDN>shutdown immediate;
idle> startup nomount;
ORACLE instance started.
Total System Global Area 335544320 bytes
Fixed Size 2020640 bytes
Variable Size 117443296 bytes
Database Buffers 213909504 bytes
Redo Buffers 2170880 bytes
#把undo_tablespace 修改成备用的undo
idle>alter system set undo_tablespace=undotbs scope=spfile;
#把undo改成手动管理
idle> alter system set undo_management=manual scope=spfile;
System altered.
#使用隐藏参数
idle> alter system set "_offline_rollback_segments"=true scope=spfile;
System altered.
idle> shutdown immediate;
idle> startup
ORACLE instance started.
Total System Global Area 335544320 bytes
Fixed Size 2020640 bytes
Variable Size 117443296 bytes
Database Buffers 213909504 bytes
Redo Buffers 2170880 bytes
Database mounted.
Database opened.
这样数据库就可以打开了！
查看UNDOTBS1 (之前丢失的表空间) 中的段的状态是否都是offline
idle> select segment_name,tablespace_name,status from dba_rollback_segs;
SEGMENT_NAME TABLESPACE_NAME STATUS
------------------------------ ------------------------------ ----------------
SYSTEM SYSTEM ONLINE
_SYSSMU1$ UNDOTBS1 OFFLINE
_SYSSMU2$ UNDOTBS1 OFFLINE
_SYSSMU3$ UNDOTBS1 OFFLINE
_SYSSMU4$ UNDOTBS1 OFFLINE
_SYSSMU5$ UNDOTBS1 OFFLINE
_SYSSMU6$ UNDOTBS1 OFFLINE
_SYSSMU7$ UNDOTBS1 OFFLINE
_SYSSMU8$ UNDOTBS1 OFFLINE
_SYSSMU9$ UNDOTBS1 OFFLINE
_SYSSMU10$ UNDOTBS1 OFFLINE
_SYSSMU11$ UNDOTBS OFFLINE
_SYSSMU12$ UNDOTBS OFFLINE
_SYSSMU13$ UNDOTBS OFFLINE
_SYSSMU14$ UNDOTBS OFFLINE
_SYSSMU15$ UNDOTBS OFFLINE
_SYSSMU16$ UNDOTBS OFFLINE
_SYSSMU17$ UNDOTBS OFFLINE
_SYSSMU18$ UNDOTBS OFFLINE
_SYSSMU19$ UNDOTBS OFFLINE
_SYSSMU20$ UNDOTBS OFFLINE
21 rows selected.
若UNDOTBS1 (之前丢失的表空间) 中的段的状态都是offline，则可以删除该undo表空间（否则，则要用到另一个隐藏参数"_corrupted_rollback_segments"把不为offline的段