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

2025-04-10 技术教程

环境：RHEL 6.5 Oracle 11.2.0.3

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

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

[oracle@test-dborcl]$mvundotbs02.dbfundotbs02.dbf_bak

启动数据库

SYS@orcl>startupORACLEinstancestarted.TotalSystemGlobalArea784998400bytesFixedSize2232472bytesVariableSize591400808bytesDatabaseBuffers188743680bytesRedoBuffers2621440bytesDatabasemounted.ORA-01157:cannotidentify/lockdatafile7-seeDBWRtracefileORA-01110:datafile7:'/u01/app/oracle/oradata/orcl_data/orcl/undotbs02.dbf'

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

ALTERDATABASEOPENErrorsinfile/u01/app/oracle/diag/rdbms/primary_orcl/orcl/trace/orcl_dbw0_2532.trc:ORA-01157:cannotidentify/lockdatafile7-seeDBWRtracefileORA-01110:datafile7:'/u01/app/oracle/oradata/orcl_data/orcl/undotbs02.dbf'ORA-27037:unabletoobtainfilestatusLinux-x86_64Error:2:NosuchfileordirectoryAdditionalinformation:3Blockchangetrackingfileiscurrent.Errorsinfile/u01/app/oracle/diag/rdbms/primary_orcl/orcl/trace/orcl_ora_2621.trc:ORA-01157:cannotidentify/lockdatafile7-seeDBWRtracefileORA-01110:datafile7:'/u01/app/oracle/oradata/orcl_data/orcl/undotbs02.dbf'ORA-1157signalledduring:ALTERDATABASEOPEN...SunJan1315:32:082019Checkerrunfound1newpersistentdatafailures

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

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

开始强制拉起数据库

启动数据库至MOUNT状态

SYS@orcl>shutdownimmediateORA-01109:databasenotopenDatabasedismounted.ORACLEinstanceshutdown.SYS@orcl>startupmount;ORACLEinstancestarted.TotalSystemGlobalArea784998400bytesFixedSize2232472bytesVariableSize591400808bytesDatabaseBuffers188743680bytesRedoBuffers2621440bytesDatabasemounted.

将文件号为 7 的数据库文件offline 并打开数据库

SYS@orcl>alterdatabasedatafile7offlinedrop;Databasealtered.SYS@orcl>alterdatabaseopen;Databasealtered.

创建新的UNDO表空间，并设置为数据库默认表空间

SYS@orcl>createundotablespaceundotbs1datafile'/u01/app/oracle/oradata/orcl_data/orcl/undotbs1.dbf'size500m;Tablespacecreated.SYS@orcl>showparameterundoNAMETYPEVALUE-----------------------------------------------------------------------------undo_managementstringAUTOundo_retentioninteger900undo_tablespacestringUNDOTBS2SYS@orcl>altersystemsetundo_tablespace='UNDOTBS1'scope=spfile;Systemaltered.

重启数据库

SYS@orcl>shutdownimmediateDatabaseclosed.Databasedismounted.ORACLEinstanceshutdown.SYS@orcl>startupORACLEinstancestarted.TotalSystemGlobalArea784998400bytesFixedSize2232472bytesVariableSize591400808bytesDatabaseBuffers188743680bytesRedoBuffers2621440bytesDatabasemounted.Databaseopened.SYS@orcl>showparameterundoNAMETYPEVALUE-----------------------------------------------------------------------------undo_managementstringAUTOundo_retentioninteger900undo_tablespacestringUNDOTBS1SYS@orcl>

总结：

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

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