①创建测试表空间
SQL> create tablespace ts_test datafile '/u01/app/oracle/oradata/ORA11GR2/test.dbf'
2 size 20m autoextend on;
Tablespace created.
②在测试表空间上创建测试表
SQL> create table t1 tablespace ts_test as select 1 id from dual;
Table created.
③删除数据文件
[oracle@host02 ~]$ rm /u01/app/oracle/oradata/ORA11GR2/test.dbf
④再创建表,验证,数据文件已丢失
create table t2 tablespace ts_test as select 1 id from dual;
切记,此时,不能关闭数据库
⑤查看DBWR进程的pid
[oracle@host02 ~]$ ps -ef|grep dbw|grep -v grep
oracle 2398 1 0 04:06 ? 00:00:00 ora_dbw0_ORA11GR2
oracle 18300 1 0 00:53 ? 00:00:00 ora_dbw0_PROD
⑥找到句柄
ls -rtl /proc/2398/fd
lrwx------ 1 oracle oinstall 64 Oct 11 04:38 264 -> /u01/app/oracle/oradata/ORA11GR2/test.dbf (deleted)
⑦将找到的句柄拷贝回来
[oracle@host02 ~]$ cp /proc//2398/fd/264 /u01/app/oracle/oradata/ORA11GR2/test.dbf;
⑧查看数据文件的状态
SQL> select file#,status,name from v$datafile;
FILE# STATUS
---------- -------
NAME
--------------------------------------------------------------------------------
1 SYSTEM
/u01/app/oracle/oradata/ORA11GR2/system01.dbf
2 ONLINE
/u01/app/oracle/oradata/ORA11GR2/sysaux01.dbf
3 ONLINE
/u01/app/oracle/oradata/ORA11GR2/undotbs01.dbf
FILE# STATUS
---------- -------
NAME
--------------------------------------------------------------------------------
4 ONLINE
/u01/app/oracle/oradata/ORA11GR2/users01.dbf
5 ONLINE
/u01/app/oracle/oradata/ORA11GR2/example01.dbf
6 ONLINE
/u01/app/oracle/oradata/ORA11GR2/test.dbf
6 rows selected.
⑨将数据文件offline
SQL> alter database datafile 6 offline;
Database altered.
再次查看数据文件
SQL> select file#,status,name from v$datafile;
FILE# STATUS
---------- -------
NAME
--------------------------------------------------------------------------------
1 SYSTEM
/u01/app/oracle/oradata/ORA11GR2/system01.dbf
2 ONLINE
/u01/app/oracle/oradata/ORA11GR2/sysaux01.dbf
3 ONLINE
/u01/app/oracle/oradata/ORA11GR2/undotbs01.dbf
FILE# STATUS
---------- -------
NAME
--------------------------------------------------------------------------------
4 ONLINE
/u01/app/oracle/oradata/ORA11GR2/users01.dbf
5 ONLINE
/u01/app/oracle/oradata/ORA11GR2/example01.dbf
6 RECOVER
/u01/app/oracle/oradata/ORA11GR2/test.dbf
6 rows selected.
⑩恢复数据文件,将数据文件online
SQL> recover datafile 6;
Media recovery complete.
SQL> alter database datafile 6 online;
Database altered.