1.准备工作，我们模拟删除users表空间的数据文件，该表空间只有一个users01.dbf的文件。我们先在这个表空间建个表
SQL> create table t as select * from dba_segments;
Table created.
T表确实是存储在USERS表空间。
SQL> select segment_name,tablespace_name from user_segments;
SEGMENT_NAME
--------------------------------------------------------------------------------
TABLESPACE_NAME
------------------------------
T
USERS
SQL> select file_id,file_name from dba_data_files;
FILE_ID
----------
FILE_NAME
--------------------------------------------------------------------------------
1
/opt/u01/app/oracle/oradata/cwt/system01.dbf
2
/opt/u01/app/oracle/oradata/cwt/sysaux01.dbf
3
/opt/u01/app/oracle/oradata/cwt/undotbs01.dbf
4
/opt/u01/app/oracle/oradata/cwt/users01.dbf
USER01.dbf的file_id是4，路径为 /opt/u01/app/oracle/oradata/cwt/users01.dbf
2.删除/opt/u01/app/oracle/oradata/cwt/users01.dbf(需注意这时候数据库必须是启动的)
[oracle@rhel6-5 ~]$ rm /opt/u01/app/oracle/oradata/cwt/users01.dbf
[oracle@rhel6-5 ~]$
3.查询看看T表是否还能访问。
SQL> select * from t;
select * from t
*
ERROR at line 1:
ORA-01116: error in opening database file 4
ORA-01110: data file 4: '/opt/u01/app/oracle/oradata/cwt/users01.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
文件没有了，打不开了，现在开始恢复。
[oracle@rhel6-5 ~]$ ps -ef|grep dbw
oracle 14087 1 0 18:54 ? 00:00:00 ora_dbw0_cwt
oracle 14497 12303 0 19:15 pts/0 00:00:00 grep dbw
[oracle@rhel6-5 ~]$ cd /proc/14087/fd
[oracle@rhel6-5 fd]$ ls -al
total 0
dr-x------ 2 oracle oinstall 0 Jun 23 18:54 .
dr-xr-xr-x 8 oracle oinstall 0 Jun 23 18:54 ..
lr-x------ 1 oracle oinstall 64 Jun 23 19:16 0 -> /dev/null
l-wx------ 1 oracle oinstall 64 Jun 23 19:16 1 -> /dev/null
lrwx------ 1 oracle oinstall 64 Jun 23 19:16 10 -> /opt/u01/app/oracle/product/11.2.0/db_1/dbs/lkCWT
lr-x------ 1 oracle oinstall 64 Jun 23 19:16 11 -> /opt/u01/app/oracle/product/11.2.0/db_1/rdbms/mesg/oraus.msb
l-wx------ 1 oracle oinstall 64 Jun 23 19:16 2 -> /dev/null
lrwx------ 1 oracle oinstall 64 Jun 23 19:16 256 -> /opt/u01/app/oracle/oradata/cwt/control01.ctl
lrwx------ 1 oracle oinstall 64 Jun 23 19:16 257 -> /opt/u01/app/oracle/oradata/cwt/control02.ctl
lrwx------ 1 oracle oinstall 64 Jun 23 19:16 258 -> /opt/u01/app/oracle/oradata/cwt/control03.ctl
lrwx------ 1 oracle oinstall 64 Jun 23 19:16 259 -> /opt/u01/app/oracle/oradata/cwt/control04.ctl
lrwx------ 1 oracle oinstall 64 Jun 23 19:16 260 -> /opt/u01/app/oracle/oradata/cwt/system01.dbf
lrwx------ 1 oracle oinstall 64 Jun 23 19:16 261 -> /opt/u01/app/oracle/oradata/cwt/sysaux01.dbf
lrwx------ 1 oracle oinstall 64 Jun 23 19:16 262 -> /opt/u01/app/oracle/oradata/cwt/undotbs01.dbf
lrwx------ 1 oracle oinstall 64 Jun 23 19:16 263 -> /opt/u01/app/oracle/oradata/cwt/users01.dbf (deleted)
lrwx------ 1 oracle oinstall 64 Jun 23 19:16 264 -> /opt/u01/app/oracle/oradata/cwt/users101.dbf
lrwx------ 1 oracle oinstall 64 Jun 23 19:16 265 -> /opt/u01/app/oracle/oradata/cwt/temp01.dbf
lr-x------ 1 oracle oinstall 64 Jun 23 19:16 3 -> /dev/null
lr-x------ 1 oracle oinstall 64 Jun 23 19:16 4 -> /dev/null
lr-x------ 1 oracle oinstall 64 Jun 23 19:16 5 -> /dev/null
lr-x------ 1 oracle oinstall 64 Jun 23 19:16 6 -> /opt/u01/app/oracle/product/11.2.0/db_1/rdbms/mesg/oraus.msb
lr-x------ 1 oracle oinstall 64 Jun 23 19:16 7 -> /proc/14087/fd
lr-x------ 1 oracle oinstall 64 Jun 23 19:16 8 -> /dev/zero
lrwx------ 1 oracle oinstall 64 Jun 23 19:16 9 -> /opt/u01/app/oracle/product/11.2.0/db_1/dbs/hc_cwt.dat
复制回去。
[oracle@rhel6-5 fd]$ cp 263 /opt/u01/app/oracle/oradata/cwt/users01.dbf
SQL> alter database datafile 4 offline; --必须是归档模式才能offline。
Database altered.
SQL> recover datafile 4;
Media recovery complete.
SQL> alter database datafile 4 online;
Database altered.
测试一下吧。
SQL> select * from t where rownum<2;
OWNER SEGMENT_NAME PARTITION_NAME
------------------------------ -------------------- ------------------------------
SEGMENT_TYPE SEGMENT_SU TABLESPACE_NAME HEADER_FILE HEADER_BLOCK BYTES
------------------ ---------- ------------------------------ ----------- ------------ ----------
BLOCKS EXTENTS INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS MAX_SIZE RETENTI
---------- ---------- -------------- ----------- ----------- ----------- ---------- -------
MINRETENTION PCT_INCREASE FREELISTS FREELIST_GROUPS RELATIVE_FNO BUFFER_ FLASH_C CELL_FL
------------ ------------ ---------- --------------- ------------ ------- ------- -------
SYS I_USER1
INDEX MSSM SYSTEM 1 416 65536
8 1 65536 1048576 1 2147483645 2147483645
1 1 1 DEFAULT DEFAULT DEFAULT
恢复成功了。
