SQL> select file_name,tablespace_name, status from dba_data_files;
FILE_NAME TABLESPACE_NAME STATUS
/u01/app/oracle/oradata/orcl/users01.dbf USERS AVAILABLE
/u01/app/oracle/oradata/orcl/undotbs01.dbf UNDOTBS1 AVAILABLE
/u01/app/oracle/oradata/orcl/sysaux01.dbf SYSAUX AVAILABLE
/u01/app/oracle/oradata/orcl/system01.dbf SYSTEM AVAILABLE
/u01/app/oracle/oradata/orcl/example01.dbf EXAMPLE AVAILABLE
/u01/app/oracle/oradata/orcl/back.dbf BACKUPSPACE AVAILABLE
数据库中有backupspace这样的表空间,对应的数据文件是 /u01/app/oracle/oradata/orcl/back.dbf 很可惜这个文件被我误删了,数据库现在状态是开启,但是很多操作都进行不了,怎么办?怎么恢复?
首先,我们想到的是删除表空间连带其所包含的内容和datafile。====
SQL> drop tablespace backupspace including contents and datafiles;
drop tablespace backupspace including contents and datafiles
*
ERROR at line 1:
ORA-01116: error in opening database file 6
ORA-01110: data file 6: ‘/u01/app/oracle/oradata/orcl/back.dbf’
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3
SQL> select file#,name from v$datafile;
FILE# NAME
1 /u01/app/oracle/oradata/orcl/system01.dbf
2 /u01/app/oracle/oradata/orcl/sysaux01.dbf
3 /u01/app/oracle/oradata/orcl/undotbs01.dbf
4 /u01/app/oracle/oradata/orcl/users01.dbf
5 /u01/app/oracle/oradata/orcl/example01.dbf
6 /u01/app/oracle/oradata/orcl/back.dbf
===oralce错误提示:不能打开数据文件6,这个6 就是被误删的数据文件=
=或者是否可以先将表空间改为offline呢?=======
SQL> alter tablespace backupspace offline;
alter tablespace backupspace offline
*
ERROR at line 1:
ORA-01116: error in opening database file 6
ORA-01110: data file 6: ‘/u01/app/oracle/oradata/orcl/back.dbf’
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3
提示同样错误,依旧不行,思路就只有一个了,手动将数据文件从Linux回收站copy回原来directory,然后recover database矫正SCN====
[oracle@orcl orcl]$ ps -eaf|grep dbw0 |grep -v grep
oracle 9901 1 0 Oct22 ? 00:00:08 ora_dbw0_orcl
[oracle@orcl orcl]$ cd /proc/9901/fd
[oracle@orcl fd]$ ll
total 0
lr-x------ 1 oracle oinstall 64 Oct 23 14:37 0 -> /dev/null
l-wx------ 1 oracle oinstall 64 Oct 23 14:37 1 -> /dev/null
lrwx------ 1 oracle oinstall 64 Oct 23 14:37 10 -> /u01/app/oracle/product/11.2.0/db_1/dbs/lkORCL
lr-x------ 1 oracle oinstall 64 Oct 23 14:37 11 -> /u01/app/oracle/product/11.2.0/db_1/rdbms/mesg/oraus.msb
lrwx------ 1 oracle oinstall 64 Oct 23 14:37 12 -> socket:[138258]
l-wx------ 1 oracle oinstall 64 Oct 23 14:37 2 -> /dev/null
lrwx------ 1 oracle oinstall 64 Oct 23 14:37 256 -> /u01/app/oracle/oradata/orcl/control01.ctl
lrwx------ 1 oracle oinstall 64 Oct 23 14:37 257 -> /u01/app/oracle/fast_recovery_area/orcl/control02.ctl
lrwx------ 1 oracle oinstall 64 Oct 23 14:37 258 -> /u01/app/oracle/oradata/orcl/system01.dbf
lrwx------ 1 oracle oinstall 64 Oct 23 14:37 259 -> /u01/app/oracle/oradata/orcl/sysaux01.dbf
lrwx------ 1 oracle oinstall 64 Oct 23 14:37 260 -> /u01/app/oracle/oradata/orcl/undotbs01.dbf
lrwx------ 1 oracle oinstall 64 Oct 23 14:37 261 -> /u01/app/oracle/oradata/orcl/users01.dbf
lrwx------ 1 oracle oinstall 64 Oct 23 14:37 262 -> /u01/app/oracle/oradata/orcl/temp01.dbf
lrwx------ 1 oracle oinstall 64 Oct 23 14:37 263 -> /u01/app/oracle/oradata/orcl/tbs_data01.dbf (deleted)----这个就是要恢复的数据文件。
[oracle@orcl fd]$ cp /proc/9901/fd/263 /u01/app/oracle/oradata/orcl/tbs_data01.dbf
[oracle@oracle fd]$ sqlplus / as sysdba
SQL> alter database datafile ‘/u01/app/oracle/oradata/orcl/testdata.dbf’ offline;
Database altered.
SQL> recover datafile ‘/u01/app/oracle/oradata/orcl/testdata.dbf’;
Media recovery complete.
SQL> alter database datafile ‘/u01/app/oracle/oradata/orcl/testdata.dbf’ online;
Database altered.
SQL> startup force
ORACLE instance started.
Total System Global Area 368263168 bytes
Fixed Size 1345016 bytes
Variable Size 239077896 bytes
Database Buffers 121634816 bytes
Redo Buffers 6205440 bytes
Database mounted.
Database opened.
=任务完成,切记前提条件,数据库一直要保持open状态,一旦发生了数据库关闭或者mount,alter数据文件offline都没办法操作了,就算是手动从Linux回收站取回数据也没办法恢复了==