Oracle undo 表空间数据文件丢失强制启动数据库（没有未提交的事务）

环境：RHEL 6.5 Oracle 11.2.0.3

如果有RMAN备份的话，如果恢复数据文件就可以完成，本次我们测试在没有备份的情况下如何拉起数据库

• 重命名UNDO表空间的数据文件

[oracle@test-db orcl]$ mv undotbs02.dbf undotbs02.dbf_bak

• 启动数据库

SYS@orcl> startupORACLE instance started.Total System Global Area  784998400 bytesFixed Size                  2232472 bytesVariable Size             591400808 bytesDatabase Buffers          188743680 bytesRedo Buffers                2621440 bytesDatabase mounted.ORA-01157: cannot identify/lock data file 7 - see DBWR trace fileORA-01110: data file 7: '/u01/app/oracle/oradata/orcl_data/orcl/undotbs02.dbf'

• 根据提示信息数据 7 有问题，查看alert.log文件

ALTER DATABASE OPENErrors 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 fileORA-01110: data file 7: '/u01/app/oracle/oradata/orcl_data/orcl/undotbs02.dbf'ORA-27037: unable to obtain file statusLinux-x86_64 Error: 2: No such file or directoryAdditional information: 3Block 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 fileORA-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 2019Checker run found 1 new persistent data failures

• 日志文件中也显示 7号数据文件找不到了

###########################################################################

开始强制拉起数据库

• 启动数据库至MOUNT状态
  

SYS@orcl> shutdown immediateORA-01109: database not openDatabase dismounted.ORACLE instance shut down.SYS@orcl> startup mount;ORACLE instance started.Total System Global Area  784998400 bytesFixed Size                  2232472 bytesVariable Size             591400808 bytesDatabase Buffers          188743680 bytesRedo Buffers                2621440 bytesDatabase 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 undoNAME                                 TYPE        VALUE------------------------------------ ----------- ------------------------------undo_management                      string      AUTOundo_retention                       integer     900undo_tablespace                      string      UNDOTBS2SYS@orcl> alter system set undo_tablespace='UNDOTBS1' scope=spfile;System altered.

• 重启数据库
  

  
  

SYS@orcl> shutdown immediateDatabase closed.Database dismounted.ORACLE instance shut down.SYS@orcl> startupORACLE instance started.Total System Global Area  784998400 bytesFixed Size                  2232472 bytesVariable Size             591400808 bytesDatabase Buffers          188743680 bytesRedo Buffers                2621440 bytesDatabase mounted.Database opened.SYS@orcl> show parameter undoNAME                                 TYPE        VALUE------------------------------------ ----------- ------------------------------undo_management                      string      AUTOundo_retention                       integer     900undo_tablespace                      string      UNDOTBS1SYS@orcl>

总结：

这是最简单的一种情况下恢复undo表空间丢失的情况，也是很容易的。

直接 offile 相关数据文件，打开数据库重新创建UNDO表空间并默认为数据库默认表空间