今天登陆到数据库做了一个小测试后 忘记了用命令删除表空间，直接在Linux下删掉了数据文件，

结果，悲剧就开始了。(弄明白了整理出来大家共同study)

实验环境：

[oracle@tyger ~]$ cat /etc/redhat-release 
Red Hat Enterprise Linux Server release 4.6 (Tikanga)
[oracle@tyger ~]$ . .bash_profile 
[oracle@tyger ~]$ sqlplus / as sysdba


SQL*Plus: Release 10.2.0.1.0 - Production on Sat Mar 1 15:54:53 2014


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




Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options


SYS@ORCL>select status from v$instance;


STATUS
------------
OPEN


SYS@ORCL>select * from v$version;


BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

查看一下当前数据库中的数据文件有哪些 

用到 数据字典  （dba_data_files        dba_tablespaces   )  和    v$datafile

SYS@ORCL>select tablespace_name,file_name from dba_data_files;


TABLESPACE_NAME
------------------------------
FILE_NAME
--------------------------------------------------------------------------------
USERS
/u01/app/oracle/oradata/ORCL/users01.dbf


SYSAUX
/u01/app/oracle/oradata/ORCL/sysaux01.dbf


UNDOTBS1
/u01/app/oracle/oradata/ORCL/undotbs01.dbf




TABLESPACE_NAME
------------------------------
FILE_NAME
--------------------------------------------------------------------------------
SYSTEM
/u01/app/oracle/oradata/ORCL/system01.dbf


EXAMPLE
/u01/app/oracle/oradata/ORCL/example01.dbf


SYS@ORCL>col file_name for a50
SYS@ORCL>col tablespace_name for a10
SYS@ORCL>/


TABLESPACE FILE_NAME
---------- --------------------------------------------------
USERS      /u01/app/oracle/oradata/ORCL/users01.dbf
SYSAUX     /u01/app/oracle/oradata/ORCL/sysaux01.dbf
UNDOTBS1   /u01/app/oracle/oradata/ORCL/undotbs01.dbf
SYSTEM     /u01/app/oracle/oradata/ORCL/system01.dbf
EXAMPLE    /u01/app/oracle/oradata/ORCL/example01.dbf


SYS@ORCL>select tablespace_name,status,contents from dba_tablespaces;


TABLESPACE STATUS    CONTENTS
---------- --------- ---------
SYSTEM     ONLINE    PERMANENT
UNDOTBS1   ONLINE    UNDO
SYSAUX     ONLINE    PERMANENT
TEMP       ONLINE    TEMPORARY
USERS      ONLINE    PERMANENT
EXAMPLE    ONLINE    PERMANENT


6 rows selected.


SYS@ORCL>select file#,ts#,name from v$datafile;


     FILE#        TS#
---------- ----------
NAME
--------------------------------------------------------------------------------
         1          0
/u01/app/oracle/oradata/ORCL/system01.dbf


         2          1
/u01/app/oracle/oradata/ORCL/undotbs01.dbf


         3          2
/u01/app/oracle/oradata/ORCL/sysaux01.dbf




     FILE#        TS#
---------- ----------
NAME
--------------------------------------------------------------------------------
         4          4
/u01/app/oracle/oradata/ORCL/users01.dbf


         5          6
/u01/app/oracle/oradata/ORCL/example01.dbf




SYS@ORCL>col name for a50
SYS@ORCL>l
  1* select file#,ts#,name from v$datafile
SYS@ORCL>/


     FILE#        TS# NAME
---------- ---------- --------------------------------------------------
         1          0 /u01/app/oracle/oradata/ORCL/system01.dbf
         2          1 /u01/app/oracle/oradata/ORCL/undotbs01.dbf
         3          2 /u01/app/oracle/oradata/ORCL/sysaux01.dbf
         4          4 /u01/app/oracle/oradata/ORCL/users01.dbf
         5          6 /u01/app/oracle/oradata/ORCL/example01.dbf

开始创建测试表空间 tyger  存储位置  ‘/u01/app/oracle/oradata/ORCL/tyger01.dbf‘ 大小为 5M。

SYS@ORCL>create tablespace tyger datafile ‘/u01/app/oracle/oradata/ORCL/tyger01.dbf‘ size 5M;


Tablespace created.


SYS@ORCL>select tablespace_name,file_name,status from dba_data_files;


TABLESPACE FILE_NAME                                          STATUS
---------- -------------------------------------------------- ---------
USERS      /u01/app/oracle/oradata/ORCL/users01.dbf           AVAILABLE
SYSAUX     /u01/app/oracle/oradata/ORCL/sysaux01.dbf          AVAILABLE
UNDOTBS1   /u01/app/oracle/oradata/ORCL/undotbs01.dbf         AVAILABLE
SYSTEM     /u01/app/oracle/oradata/ORCL/system01.dbf          AVAILABLE
EXAMPLE    /u01/app/oracle/oradata/ORCL/example01.dbf         AVAILABLE
TYGER      /u01/app/oracle/oradata/ORCL/tyger01.dbf           AVAILABLE


6 rows selected.


SYS@ORCL>!
[oracle@tyger ~]$ cd $ORACLE_BASE/oradata/ORCL/
[oracle@tyger ORCL]$ ll
total 1068456
-rw-r----- 1 oracle oinstall   7061504 Mar  1 16:06 control01.ctl
-rw-r----- 1 oracle oinstall   7061504 Mar  1 16:06 control02.ctl
-rw-r----- 1 oracle oinstall   7061504 Mar  1 16:06 control03.ctl
-rw-r----- 1 oracle oinstall 104865792 Mar  1 15:47 example01.dbf
-rw-r----- 1 oracle oinstall  52429312 Mar  1 15:47 redo01.log
-rw-r----- 1 oracle oinstall  52429312 Mar  1 16:06 redo02.log
-rw-r----- 1 oracle oinstall  52429312 Mar  1 15:47 redo03.log
-rw-r----- 1 oracle oinstall 251666432 Mar  1 16:05 sysaux01.dbf
-rw-r----- 1 oracle oinstall 503324672 Mar  1 16:05 system01.dbf
-rw-r----- 1 oracle oinstall  20979712 Mar  1 08:42 temp01.dbf
-rw-r----- 1 oracle oinstall   5251072 Mar  1 16:03 tyger01.dbf
-rw-r----- 1 oracle oinstall  26222592 Mar  1 16:05 undotbs01.dbf
-rw-r----- 1 oracle oinstall   5251072 Mar  1 15:47 users01.dbf

[oracle@tyger ORCL]$ rm tyger01.dbf      ----------干掉 tyger01.dbf 
[oracle@tyger ORCL]$ ll
total 1063316
-rw-r----- 1 oracle oinstall   7061504 Mar  1 16:08 control01.ctl
-rw-r----- 1 oracle oinstall   7061504 Mar  1 16:08 control02.ctl
-rw-r----- 1 oracle oinstall   7061504 Mar  1 16:08 control03.ctl
-rw-r----- 1 oracle oinstall 104865792 Mar  1 15:47 example01.dbf
-rw-r----- 1 oracle oinstall  52429312 Mar  1 15:47 redo01.log
-rw-r----- 1 oracle oinstall  52429312 Mar  1 16:08 redo02.log
-rw-r----- 1 oracle oinstall  52429312 Mar  1 15:47 redo03.log
-rw-r----- 1 oracle oinstall 251666432 Mar  1 16:05 sysaux01.dbf
-rw-r----- 1 oracle oinstall 503324672 Mar  1 16:05 system01.dbf
-rw-r----- 1 oracle oinstall  20979712 Mar  1 08:42 temp01.dbf
-rw-r----- 1 oracle oinstall  26222592 Mar  1 16:08 undotbs01.dbf
-rw-r----- 1 oracle oinstall   5251072 Mar  1 15:47 users01.dbf
[oracle@tyger ORCL]$ exit

SYS@ORCL>select tablespace_name,file_name,status from dba_data_files;


TABLESPACE FILE_NAME                                          STATUS
---------- -------------------------------------------------- ---------
USERS      /u01/app/oracle/oradata/ORCL/users01.dbf           AVAILABLE
SYSAUX     /u01/app/oracle/oradata/ORCL/sysaux01.dbf          AVAILABLE
UNDOTBS1   /u01/app/oracle/oradata/ORCL/undotbs01.dbf         AVAILABLE
SYSTEM     /u01/app/oracle/oradata/ORCL/system01.dbf          AVAILABLE
EXAMPLE    /u01/app/oracle/oradata/ORCL/example01.dbf         AVAILABLE
TYGER      /u01/app/oracle/oradata/ORCL/tyger01.dbf           AVAILABLE           ------- 数据库中还存在是因为没有识别到文件丢失


6 rows selected.

SYS@ORCL>shutdown immediate
ORA-03113: end-of-file on communication channel     这个错误也有很多原因，这里不做讨论




SYS@ORCL>exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
[oracle@tyger ~]$ sqlplus / as sysdba


SQL*Plus: Release 10.2.0.1.0 - Production on Sat Mar 1 16:11:27 2014


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


Connected to an idle instance.                                  --------很显然已经连接到了空闲实例


SYS@ORCL>startup
ORACLE instance started.


Total System Global Area  285212672 bytes
Fixed Size                  1218992 bytes
Variable Size             125830736 bytes
Database Buffers          155189248 bytes
Redo Buffers                2973696 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 6 - see DBWR trace file
ORA-01110: data file 6: ‘/u01/app/oracle/oradata/ORCL/tyger01.dbf‘                  ---------good !  问题出现了。




解决办法：

SYS@ORCL>alter database datafile 6 offline drop;


Database altered.


SYS@ORCL>alter database open;


Database altered.


SYS@ORCL>select ts#,file#,name from v$datafile;


       TS#      FILE# NAME
---------- ---------- --------------------------------------------------
         0          1 /u01/app/oracle/oradata/ORCL/system01.dbf
         1          2 /u01/app/oracle/oradata/ORCL/undotbs01.dbf
         2          3 /u01/app/oracle/oradata/ORCL/sysaux01.dbf
         4          4 /u01/app/oracle/oradata/ORCL/users01.dbf
         6          5 /u01/app/oracle/oradata/ORCL/example01.dbf
         8          6 /u01/app/oracle/oradata/ORCL/tyger01.dbf


6 rows selected.


SYS@ORCL>select tablespace_name,status from dba_tablespaces;


TABLESPACE_NAME                STATUS
------------------------------ ---------
SYSTEM                         ONLINE
UNDOTBS1                       ONLINE
SYSAUX                         ONLINE
TEMP                           ONLINE
USERS                          ONLINE
EXAMPLE                        ONLINE
TYGER                          ONLINE


7 rows selected.


SYS@ORCL>drop tablespace tyger including contents and datafiles;


Tablespace dropped.


SYS@ORCL>shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@ORCL>startup
ORACLE instance started.


Total System Global Area  285212672 bytes
Fixed Size                  1218992 bytes
Variable Size             130025040 bytes
Database Buffers          150994944 bytes
Redo Buffers                2973696 bytes
Database mounted.
Database opened.

【精】ORA-01157: cannot identify/lock data file 6 - see DBWR trace file ORA-01110: 解决方法,布布扣,bubuko.com

【精】ORA-01157: cannot identify/lock data file 6 - see DBWR trace file ORA-01110: 解决方法