申明

首先申明，这种做法无法恢复数据。文章的目的就是把数据库恢复正常，数据可以丢弃，多为在测试环境进行的操作。

现象

删除了表空间的数据文件，导致在shutdown数据库和startup数据库的时候报错，如下：

SQL> startup;
ORACLE instance started.


Total System Global Area 4008546304 bytes
Fixed Size		    2259440 bytes
Variable Size		  872416784 bytes
Database Buffers	 3120562176 bytes
Redo Buffers		   13307904 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 7 - see DBWR trace file
ORA-01110: data file 7: '/data/oracle/oradata/orclstd/PSR1.dbf'

• 因为一个8K的表空间最大只能有32G，所以这个表空间指定了多个dbf文件，在删除的时候也是一次性删除了好几个。前面有操作【drop user xxx cascade;】，删除了用户的,表空间也不存在了，但是【startup;】数据库的时候，还是报错。
• 使用下列语句关闭数据文件：

SQL> alter database datafile '/data/oracle/oradata/orclstd/PSR1.dbf' offline drop;


Database altered.


SQL>  alter database open;
 alter database open
*
ERROR at line 1:
ORA-01157: cannot identify/lock data file 7 - see DBWR trace file
ORA-01110: data file 7: '/data/oracle/oradata/orclstd/PSR2.dbf'




SQL> alter database datafile '/data/oracle/oradata/orclstd/PSR2.dbf' offline drop;


Database altered.


SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01157: cannot identify/lock data file 9 - see DBWR trace file
ORA-01110: data file 9: '/data/oracle/oradata/orclstd/psr1_02.dbf'




SQL>  alter database datafile '/data/oracle/oradata/orclstd/psr1_02.dbf' offline drop;


Database altered.


SQL> alter database open;


Database altered.


SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.


Total System Global Area 4008546304 bytes
Fixed Size		    2259440 bytes
Variable Size		  872416784 bytes
Database Buffers	 3120562176 bytes
Redo Buffers		   13307904 bytes
Database mounted.
Database opened.
SQL> exit

• 如果有其他的dbf影响数据库启动的话，执行【alter database open;】的时候会报错，继续循环步骤，直到不报错为止。