环境：RHEL 6.5 Oracle 11.2.0.3
创新互联公司专注为客户提供全方位的互联网综合服务，包含不限于成都网站设计、网站制作、长洲网络推广、小程序设计、长洲网络营销、长洲企业策划、长洲品牌公关、搜索引擎seo、人物专访、企业宣传片、企业代运营等，从售前售中售后，我们都将竭诚为您服务，您的肯定，是我们最大的嘉奖；创新互联公司为所有大学生创业者提供长洲建站搭建服务，24小时服务热线：028-86922220，官方网址：www.cdcxhl.com
如果有RMAN备份的话，如果恢复数据文件就可以完成，本次我们测试在没有备份的情况下如何拉起数据库
重命名UNDO表空间的数据文件
[oracle@test-db orcl]$ mv undotbs02.dbf undotbs02.dbf_bak
启动数据库
SYS@orcl> startup ORACLE instance started. Total System Global Area 784998400 bytes Fixed Size 2232472 bytes Variable Size 591400808 bytes Database Buffers 188743680 bytes Redo Buffers 2621440 bytes Database mounted. ORA-01157: cannot identify/lock data file 7 - see DBWR trace file ORA-01110: data file 7: '/u01/app/oracle/oradata/orcl_data/orcl/undotbs02.dbf'
根据提示信息数据 7 有问题，查看alert.log文件
ALTER DATABASE OPEN Errors in file /u01/app/oracle/diag/rdbms/primary_orcl/orcl/trace/orcl_dbw0_2532.trc: ORA-01157: cannot identify/lock data file 7 - see DBWR trace file ORA-01110: data file 7: '/u01/app/oracle/oradata/orcl_data/orcl/undotbs02.dbf' ORA-27037: unable to obtain file status Linux-x86_64 Error: 2: No such file or directory Additional information: 3 Block change tracking file is current. Errors in file /u01/app/oracle/diag/rdbms/primary_orcl/orcl/trace/orcl_ora_2621.trc: ORA-01157: cannot identify/lock data file 7 - see DBWR trace file ORA-01110: data file 7: '/u01/app/oracle/oradata/orcl_data/orcl/undotbs02.dbf' ORA-1157 signalled during: ALTER DATABASE OPEN... Sun Jan 13 15:32:08 2019 Checker run found 1 new persistent data failures
日志文件中也显示 7号数据文件找不到了
###########################################################################
开始强制拉起数据库
启动数据库至MOUNT状态
SYS@orcl> shutdown immediate ORA-01109: database not open Database dismounted. ORACLE instance shut down. SYS@orcl> startup mount; ORACLE instance started. Total System Global Area 784998400 bytes Fixed Size 2232472 bytes Variable Size 591400808 bytes Database Buffers 188743680 bytes Redo Buffers 2621440 bytes Database mounted.
将 文件号为 7 的数据库文件offline 并 打开数据库
SYS@orcl> alter database datafile 7 offline drop; Database altered. SYS@orcl> alter database open; Database altered.
创建新的UNDO表空间，并设置为数据库默认表空间
SYS@orcl> create undo tablespace undotbs1 datafile '/u01/app/oracle/oradata/orcl_data/orcl/undotbs1.dbf' size 500m ; Tablespace created. SYS@orcl> show parameter undo NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ undo_management string AUTO undo_retention integer 900 undo_tablespace string UNDOTBS2 SYS@orcl> alter system set undo_tablespace='UNDOTBS1' scope=spfile; System altered.
重启数据库
SYS@orcl> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SYS@orcl> startup ORACLE instance started. Total System Global Area 784998400 bytes Fixed Size 2232472 bytes Variable Size 591400808 bytes Database Buffers 188743680 bytes Redo Buffers 2621440 bytes Database mounted. Database opened. SYS@orcl> show parameter undo NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ undo_management string AUTO undo_retention integer 900 undo_tablespace string UNDOTBS1 SYS@orcl>
总结：
这是最简单的一种情况下恢复undo表空间丢失的情况，也是很容易的。
直接 offile 相关数据文件，打开数据库重新创建UNDO表空间并默认为数据库默认表空间