当误删除表空间数据文件（特指非关键数据文件，而不是关键数据文件），则可以在不备份就可以修复数据文件，但有个前提，即不能关库，假如已经关库了，则没有备份，只能offline再删除表空间；

1.创建测试表空间
SYS@PROD1>create tablespace ts_test datafile '/u01/app/oracle/oradata/PROD1/ts_01.dbf' size 20m;

Tablespace created.

2.创建测试表
SYS@PROD1>create table t1 tablespace ts_test as select 1 id from dual;

Table created.

SYS@PROD1>select * from t1;

ID
----------
1

3.删除数据文件
SYS@PROD1>create table t2 tablespace ts_test as select 1 id from dual;

Table created.

SYS@PROD1>drop table t2 purge;
drop table t3 purge
*
ERROR at line 1:
ORA-01116: error in opening database file 6
ORA-01110: data file 6: '/u01/app/oracle/oradata/PROD1/ts_01.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

5.切记，此时不要关闭数据库

6.查看DBWR进程的pid
[oracle@host01 PROD1]$ ps -ef | grep dbw|grep -v grep
oracle 15630 1 0 10:55 ? 00:00:00 ora_dbw0_PROD1

7.找到句柄
[oracle@host01 PROD1]$ ll -rt /proc/15630/fd
total 0
lrwx------ 1 oracle oinstall 64 Dec 15 11:43 9 -> /u01/app/oracle/product/11.2.0/dbhome_1/dbs/hc_PROD1.dat
lr-x------ 1 oracle oinstall 64 Dec 15 11:43 8 -> /dev/zero
lr-x------ 1 oracle oinstall 64 Dec 15 11:43 7 -> /proc/15630/fd
lr-x------ 1 oracle oinstall 64 Dec 15 11:43 6 -> /u01/app/oracle/product/11.2.0/dbhome_1/rdbms/mesg/oraus.msb
lr-x------ 1 oracle oinstall 64 Dec 15 11:43 5 -> /dev/null
lr-x------ 1 oracle oinstall 64 Dec 15 11:43 4 -> /dev/null
lr-x------ 1 oracle oinstall 64 Dec 15 11:43 3 -> /dev/null
lrwx------ 1 oracle oinstall 64 Dec 15 11:43 265 -> /u01/app/oracle/oradata/PROD1/ts_01.dbf (deleted)
lrwx------ 1 oracle oinstall 64 Dec 15 11:43 264 -> /u01/app/oracle/oradata/PROD1/temp01.dbf
lrwx------ 1 oracle oinstall 64 Dec 15 11:43 263 -> /u01/app/oracle/oradata/PROD1/example01.dbf
lrwx------ 1 oracle oinstall 64 Dec 15 11:43 262 -> /u01/app/oracle/oradata/PROD1/users01.dbf
lrwx------ 1 oracle oinstall 64 Dec 15 11:43 261 -> /u01/app/oracle/oradata/PROD1/undotbs01.dbf
lrwx------ 1 oracle oinstall 64 Dec 15 11:43 260 -> /u01/app/oracle/oradata/PROD1/sysaux01.dbf
lrwx------ 1 oracle oinstall 64 Dec 15 11:43 259 -> /u01/app/oracle/oradata/PROD1/system01.dbf
lrwx------ 1 oracle oinstall 64 Dec 15 11:43 258 -> /u01/app/oracle/oradata/PROD1/control03.ctl
lrwx------ 1 oracle oinstall 64 Dec 15 11:43 257 -> /u01/app/oracle/fast_recovery_area/PROD1/control02.ctl
lrwx------ 1 oracle oinstall 64 Dec 15 11:43 256 -> /u01/app/oracle/oradata/PROD1/control01.ctl
l-wx------ 1 oracle oinstall 64 Dec 15 11:43 2 -> /dev/null
lr-x------ 1 oracle oinstall 64 Dec 15 11:43 13 -> /u01/app/oracle/product/11.2.0/dbhome_1/rdbms/mesg/oraus.msb
lrwx------ 1 oracle oinstall 64 Dec 15 11:43 10 -> /u01/app/oracle/product/11.2.0/dbhome_1/dbs/lkPROD1
l-wx------ 1 oracle oinstall 64 Dec 15 11:43 1 -> /dev/null
lr-x------ 1 oracle oinstall 64 Dec 15 11:43 0 -> /dev/null

8.将找到的句柄拷贝回来
[oracle@host01 PROD1]$ cp /proc//15630/fd/265 /u01/app/oracle/oradata/PROD1/ts_01.dbf

9.查看数据文件状态
SYS@PROD1>select file#,name,status from v$datafile;

FILE# NAME STATUS
---------- -------------------------------------------------- -------
1 /u01/app/oracle/oradata/PROD1/system01.dbf SYSTEM
2 /u01/app/oracle/oradata/PROD1/sysaux01.dbf ONLINE
3 /u01/app/oracle/oradata/PROD1/undotbs01.dbf ONLINE
4 /u01/app/oracle/oradata/PROD1/users01.dbf ONLINE
5 /u01/app/oracle/oradata/PROD1/example01.dbf ONLINE
6 /u01/app/oracle/oradata/PROD1/ts_01.dbf ONLINE

6 rows selected.

10.将6号数据文件offline
SYS@PROD1>alter database datafile 6 offline;

Database altered.

再次查看数据文件状态
SYS@PROD1>select file#,status,name from v$datafile;

FILE# STATUS NAME
---------- ------- --------------------------------------------------
1 SYSTEM /u01/app/oracle/oradata/PROD1/system01.dbf
2 ONLINE /u01/app/oracle/oradata/PROD1/sysaux01.dbf
3 ONLINE /u01/app/oracle/oradata/PROD1/undotbs01.dbf
4 ONLINE /u01/app/oracle/oradata/PROD1/users01.dbf
5 ONLINE /u01/app/oracle/oradata/PROD1/example01.dbf
6 RECOVER /u01/app/oracle/oradata/PROD1/ts_01.dbf

6 rows selected.

11.恢复数据文件
SYS@PROD1>recover datafile 6;
Media recovery complete.

12.将6号数据文件online
SYS@PROD1>alter database datafile 6 online;

Database altered.

13验证.（文件已经修复回来了）
SYS@PROD1>! ls /u01/app/oracle/oradata/PROD1/ts_01.dbf
/u01/app/oracle/oradata/PROD1/ts_01.dbf

SYS@PROD1>select * from t1;

ID
----------
1

ITPub博客

【备份恢复】无备份在线恢复非关键数据文件

最新文章