申明
首先申明,这种做法无法恢复数据。文章的目的就是把数据库恢复正常,数据可以丢弃,多为在测试环境进行的操作。
现象
删除了表空间的数据文件,导致在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;】的时候会报错,继续循环步骤,直到不报错为止。