关于oracle数据库误删表空间文件后无法登陆sqlplus问题的解决方法

背景： 今天学习了oracle数据库表空间的创建，于是兴冲冲的跑去创建了两个表空间，文件名分别是是/u01/test/my_01.dbf和/u01/test/my_02.dbf。软件系统：CentOS-6.7-x86_64，linux.x64_11gR2_database。创建命令如下：

create tablespace my_01 logging datafile '/u01/test/my_01.dbf' size 128M

alter tablespace my_01 add datafile '/u01/test/my_01.dbf' size 128M

看着/u01/test/下生成两个表空间文件，欣喜若狂。毕竟是自己创造的。然后，我嫌这两个表空间文件占空间，于是在centos图形界面将其move to trash,效果如下：

当我再次使用sqlplus命令时，发现如下错误：

当看到'/u01/test/my_01.dbf'我就想：莫非跟我删除表空间文件有关吧。。。既然问题来了，就要上网找资料，毕竟rp爆发，找到了原因：在oracle启动后，用户登录时是要将方案中原有的配置信息装载进入，装载过程中有文件未找到，所以就报出错误。感谢这位网友的倾情奉献 http://www.doc88.com/p-634723326544.html

解决过程：

[oracle@jie ~]$ sqlplus /nolog

SQL*Plus: Release 11.2.0.1.0 Production on Mon Feb 29 16:05:31 2016

Copyright (c) 1982, 2009, Oracle. All rights reserved.

SQL> conn /as sysdba

Connected to an idle instance.

SQL> startup

ORACLE instance started.

Total System Global Area 830930944 bytes

Fixed Size 2217912 bytes

Variable Size 507512904 bytes

Database Buffers 318767104 bytes

Redo Buffers 2433024 bytes

Database mounted.

ORA-01157: cannot identify/lock data file 6 - see DBWR trace file --错误信息

ORA-01110: data file 6: '/u01/test/my_01.dbf'

SQL> alter database datafile 6 offline drop; --这里的6是根据上面错误信息里的data file 6

Database altered.

SQL> alter database open resetlogs;

alter database open resetlogs

*

ERROR at line 1:

ORA-01139: RESETLOGS option only valid after an incomplete database recovery

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: '/u01/test/my_02.dbf'

(因为我有误删了两个表空间文件，所以还有个7要处理，重复上面步骤)

SQL> alter database datafile 7 offline drop;

Database altered.

SQL> alter database open resetlogs;

alter database open resetlogs

*

ERROR at line 1:

ORA-01139: RESETLOGS option only valid after an incomplete database recovery

SQL> alter database open;

Database altered.

SQL> shutdown normal

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount;

ORACLE instance started.

Total System Global Area 830930944 bytes

Fixed Size 2217912 bytes

Variable Size 507512904 bytes

Database Buffers 318767104 bytes

Redo Buffers 2433024 bytes

Database mounted.

SQL> conn scott

Enter password:

ERROR:

ORA-01033: ORACLE initialization or shutdown in progress

Process ID: 0

Session ID: 0 Serial number: 0

Warning: You are no longer connected to ORACLE.

(重新启动下oracle就好!)

SQL> quit

[oracle@jie ~]$ su - oracle

Password:

[oracle@jie ~]$ sqlplus /nolog

SQL*Plus: Release 11.2.0.1.0 Production on Mon Feb 29 16:44:51 2016

Copyright (c) 1982, 2009, Oracle. All rights reserved.

SQL> conn /as sysdba

Connected.

SQL> startup

ORA-01081: cannot start already-running ORACLE - shut it down first

SQL> shutdown immediate

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area 830930944 bytes

Fixed Size 2217912 bytes

Variable Size 507512904 bytes

Database Buffers 318767104 bytes

Redo Buffers 2433024 bytes

Database mounted.

Database opened.

SQL> conn scott

Enter password:

Connected.

至此，问题解决!!