手动删除数据文件数据库打不开

没有通过drop tablespace 或者drop user 去删除没用的数据文件，而是从操作系统层面rm，就可以这样解决，记录一下。想要恢复的话另当谈论了，方法源自网络，自己试验了一下。

创建表空间test和用户sb
SQL> create tablespace test
  2  datafile '/oracle/oradata/orcl/test.dbf'
  3  size 10M autoextend on
  4  next 10M maxsize unlimited;
Tablespace created.

SQL> SQL> create user sb identified by oracle default tablespace test;
User created.
SQL>

授权
SQL> grant connect,resource to sb;
Grant succeeded.

SQL> conn sb/oracle
Connected.

查看默认表空间
SQL> select default_tablespace from user_users;
DEFAULT_TABLESPACE
------------------------------
TEST

创建一个test1表
SQL> create table test1(
  2  a number,
  3  b varchar2(4)
  4  );
Table created.

插入一行数据
SQL> insert into test1 values(1,'nish');
1 row created.

查看一下
SQL> select * from test1;
         A B
---------- ----
         1 nish
SQL>

模拟删除数据文件（这时候数据库是打开的）
[root@10g ~]# su - oracle
[oracle@10g ~]$ cd /oracle/oradata/orcl

total 1377668
-rw-r-----. 1 oracle oinstall  52429312 Dec 13 16:19 redo01.log
-rw-r-----. 1 oracle oinstall  52429312 Jan 13 16:25 redo03.log
-rw-r-----. 1 oracle oinstall  52429312 Jan 13 16:25 redo01_a.log
-rw-r-----. 1 oracle oinstall   5251072 Jan 13 16:25 users01.dbf
-rw-r-----. 1 oracle oinstall 157294592 Jan 13 16:25 ant_1.dbf
-rw-r-----. 1 oracle oinstall  10493952 Jan 13 16:25 EPICARD.DBF
-rw-r-----. 1 oracle oinstall  20979712 Jan 13 16:30 temp1.dbf
-rw-r-----. 1 oracle oinstall 178266112 Jan 13 16:42 undotbs01.dbf
-rw-r-----. 1 oracle oinstall  10493952 Jan 13 17:21 test.dbf
-rw-r-----. 1 oracle oinstall  20979712 Jan 13 17:35 undo2.dbf
-rw-r-----. 1 oracle oinstall 283123712 Jan 13 17:39 sysaux01.dbf
-rw-r-----. 1 oracle oinstall 471867392 Jan 13 17:39 system01.dbf
-rw-r-----. 1 oracle oinstall  52429312 Jan 13 17:40 redo02.log
-rw-r-----. 1 oracle oinstall   7061504 Jan 13 17:43 control02.ctl
-rw-r-----. 1 oracle oinstall   7061504 Jan 13 17:43 control03.ctl
-rw-r-----. 1 oracle oinstall   7061504 Jan 13 17:43 control01.ctl
[oracle@10g orcl]$ rm test.dbf
[oracle@10g orcl]$ ll test.dbf
ls: cannot access test.dbf: No such file or directory
[oracle@10g orcl]$

shutdown数据库
SQL> show user
USER is "SB"
SQL> conn sys / as sysdba
Enter password:
Connected.
SQL> show user
USER is "SYS"
SQL> shutdown immediate
ORA-01116: error in opening database file 2
ORA-01110: data file 2: '/oracle/oradata/orcl/test.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
SQL>
SQL> select name,open_mode from v$database;
NAME      OPEN_MODE
--------- ----------
ORCL      READ WRITE
SQL>

abort数据库
SQL> shutdown abort
ORACLE instance shut down.
SQL> select name,open_mode from v$database;
select name,open_mode from v$database
*
ERROR at line 1:
ORA-01034: ORACLE not available
SQL>

再startup
[oracle@10g ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.5.0 - Production on Wed Jan 13 23:10:41 2016
Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.
Connected to an idle instance.

SQL> startup
ORACLE instance started.
Total System Global Area  201326592 bytes
Fixed Size                  2095056 bytes
Variable Size             146802736 bytes
Database Buffers           46137344 bytes
Redo Buffers                6291456 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 2 - see DBWR trace file
ORA-01110: data file 2: '/oracle/oradata/orcl/test.dbf'
SQL>

看看alert日志
...
Successful mount of redo thread 1, with mount id 1428838517
Wed Jan 13 23:10:49 CST 2016
Database mounted in Exclusive Mode
Completed: ALTER DATABASE   MOUNT
Wed Jan 13 23:10:49 CST 2016
ALTER DATABASE OPEN
Wed Jan 13 23:10:49 CST 2016

ORA-01157: cannot identify/lock data file 2 - see DBWR trace file
ORA-01110: data file 2: '/oracle/oradata/orcl/test.dbf'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
ORA-1157 signalled during: ALTER DATABASE OPEN...
[oracle@10g bdump]$

再看看这个/oracle/admin/orcl/bdump/orcl_dbw0_3163.trc
[oracle@10g bdump]$ more /oracle/admin/orcl/bdump/orcl_dbw0_3163.trc
/oracle/admin/orcl/bdump/orcl_dbw0_3163.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /oracle/product/10.2.0/db_1
System name:    Linux
Node name:      10g
Release:        2.6.32-358.el6.x86_64
Version:        #1 SMP Tue Jan 29 11:47:41 EST 2013
Machine:        x86_64
Instance name: orcl
Redo thread mounted by this instance: 1
Oracle process number: 5
Unix process pid: 3163, image: oracle@10g (DBW0)

*** SERVICE NAME:() 2016-01-13 23:10:49.504
*** SESSION ID:(167.1) 2016-01-13 23:10:49.504
ORA-01157: cannot identify/lock data file 2 - see DBWR trace file
ORA-01110: data file 2: '/oracle/oradata/orcl/test.dbf'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
[oracle@10g bdump]$

说的都一样的

网上有人说这样
1. 在mount下
alter database datafile 'xxxxxxxx' offline drop;
2. alter database open
3. drop user
4. drop tablespace xxxx including contents;
 
448

SQL> alter database datafile '/oracle/oradata/orcl/test.dbf' offline drop;
Database altered.

SQL> alter database open;
Database altered.

SQL> drop user sb cascade;
User dropped.

SQL> drop tablespace test including contents;
Tablespace dropped.
SQL>

看起来像那么回事，重启数据库看看
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup
ORACLE instance started.
Total System Global Area  201326592 bytes
Fixed Size                  2095056 bytes
Variable Size             146802736 bytes
Database Buffers           46137344 bytes
Redo Buffers                6291456 bytes
Database mounted.
Database opened.

SQL> select file_name from dba_data_files;
FILE_NAME
--------------------------------------------------------------------------------
/oracle/oradata/orcl/temp1.dbf
/oracle/oradata/orcl/users01.dbf
/oracle/oradata/orcl/sysaux01.dbf
/oracle/oradata/orcl/system01.dbf
/oracle/oradata/orcl/undo1.dbf
/oracle/oradata/orcl/ant_1.dbf
/oracle/oradata/orcl/EPICARD.DBF
/oracle/oradata/orcl/undo2.dbf
8 rows selected.

好像成了