SQL> set line 200
SQL> col NAME FOR a60
SQL> select file#,tablespace_name,name from v$datafile_header;
FILE# TABLESPACE_NAMENAME
----------------------------------------------------------------------------------------
1 SYSTEM/u01/app/oracle/oradata/PROD/disk1/system01.dbf
2 UNDOTBS/u01/app/oracle/oradata/PROD/disk1/undotbs01.dbf
3 SYSAUX/u01/app/oracle/oradata/PROD/disk1/sysaux01.dbf
4 EXAMPLE/u01/app/oracle/oradata/PROD/disk1/EXAMPLE_01.dbf
5 INDX/u01/app/oracle/oradata/PROD/disk1/INDX_01.dbf
6 TOOLS/u01/app/oracle/oradata/PROD/disk1/TOOLS_01.dbf
7 USERS/u01/app/oracle/oradata/PROD/disk1/USERS_01.dbf
8 OLTP/u01/app/oracle/oradata/PROD/disk1/OLTP_01.dbf
9 ANING/u01/app/oracle/oradata/PROD/disk1/ANING01.dbf
9 rows selected.
SQL> create table aning(id number) tablespace aning;
Table created.
SQL> select * from aning;
no rows selected
SQL> insert into aning values(1);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from aning;
ID
----------
1
[oracle@secdb1 ~]$ ll /u01/app/oracle/oradata/PROD/disk1/ANING01.dbf
-rw-r----- 1 oracle oinstall 94380032 Jan 24 15:04 /u01/app/oracle/oradata/PROD/disk1/ANING01.dbf
SQL> !rm -f /u01/app/oracle/oradata/PROD/disk1/ANING01.dbf
SQL>
[oracle@secdb1 ~]$ ll /u01/app/oracle/oradata/PROD/disk1/ANING01.dbf
ls: /u01/app/oracle/oradata/PROD/disk1/ANING01.dbf: No such file or directory
SQL> create table aningt1 tablespace aning as select * from dba_objects;
create table aningt1 tablespace aning as select * from dba_objects
*
ERROR at line 1:
ORA-01116: error in opening database file 9
ORA-01110: data file 9: '/u01/app/oracle/oradata/PROD/disk1/ANING01.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3
dbw进程pid
[oracle@secdb1 ~]$ ps -ef|grep ora_dbw
oracle392610 14:54 ?00:00:00 ora_dbw0_PROD
oracle642062000 15:17 pts/200:00:00 grep ora_dbw
根据pid查找句柄
[oracle@secdb1 ~]$ cd /proc/3926/fd
[oracle@secdb1 fd]$ ll
total 0
lr-x------ 1 oracle oinstall 64 Jan 24 15:20 0 -> /dev/null
lr-x------ 1 oracle oinstall 64 Jan 24 15:20 1 -> /dev/null
lrwx------ 1 oracle oinstall 64 Jan 24 15:20 10 -> /u01/app/oracle/admin/PROD/adump/ora_3918.aud
lr-x------ 1 oracle oinstall 64 Jan 24 15:20 11 -> /dev/zero
lr-x------ 1 oracle oinstall 64 Jan 24 15:20 12 -> /dev/zero
lr-x------ 1 oracle oinstall 64 Jan 24 15:20 13 -> /u01/app/oracle/product/10.2.0/db_1/rdbms/mesg/oraus.msb
lrwx------ 1 oracle oinstall 64 Jan 24 15:20 14 -> /u01/app/oracle/product/10.2.0/db_1/dbs/hc_PROD.dat
lrwx------ 1 oracle oinstall 64 Jan 24 15:20 15 -> /u01/app/oracle/product/10.2.0/db_1/dbs/lkPROD
lrwx------ 1 oracle oinstall 64 Jan 24 15:20 16 -> /u01/app/oracle/oradata/PROD/disk1/control01.ctl
lrwx------ 1 oracle oinstall 64 Jan 24 15:20 17 -> /u01/app/oracle/oradata/PROD/disk2/control02.ctl
lrwx------ 1 oracle oinstall 64 Jan 24 15:20 18 -> /u01/app/oracle/oradata/PROD/disk3/control03.ctl
lrwx------ 1 oracle oinstall 64 Jan 24 15:20 19 -> /u01/app/oracle/oradata/PROD/disk1/system01.dbf
lr-x------ 1 oracle oinstall 64 Jan 24 15:20 2 -> /dev/null
lrwx------ 1 oracle oinstall 64 Jan 24 15:20 20 -> /u01/app/oracle/oradata/PROD/disk1/undotbs01.dbf
lrwx------ 1 oracle oinstall 64 Jan 24 15:20 21 -> /u01/app/oracle/oradata/PROD/disk1/sysaux01.dbf
lrwx------ 1 oracle oinstall 64 Jan 24 15:20 22 -> /u01/app/oracle/oradata/PROD/disk1/EXAMPLE_01.dbf
lrwx------ 1 oracle oinstall 64 Jan 24 15:20 23 -> /u01/app/oracle/oradata/PROD/disk1/INDX_01.dbf
lrwx------ 1 oracle oinstall 64 Jan 24 15:20 24 -> /u01/app/oracle/oradata/PROD/disk1/TOOLS_01.dbf
lrwx------ 1 oracle oinstall 64 Jan 24 15:20 25 -> /u01/app/oracle/oradata/PROD/disk1/USERS_01.dbf
lrwx------ 1 oracle oinstall 64 Jan 24 15:20 26 -> /u01/app/oracle/oradata/PROD/disk1/OLTP_01.dbf
lrwx------ 1 oracle oinstall 64 Jan 24 15:20 27-> /u01/app/oracle/oradata/PROD/disk1/ANING01.dbf (deleted)
lrwx------ 1 oracle oinstall 64 Jan 24 15:20 28 -> /u01/app/oracle/oradata/PROD/disk1/temp01.dbf
lrwx------ 1 oracle oinstall 64 Jan 24 15:20 29 -> /u01/app/oracle/oradata/PROD/disk1/temp1_01.dbf
lr-x------ 1 oracle oinstall 64 Jan 24 15:20 3 -> /dev/null
lrwx------ 1 oracle oinstall 64 Jan 24 15:20 30 -> /u01/app/oracle/oradata/PROD/disk1/temp2_01.dbf
lr-x------ 1 oracle oinstall 64 Jan 24 15:20 4 -> /dev/null
l-wx------ 1 oracle oinstall 64 Jan 24 15:20 5 -> /u01/app/oracle/admin/PROD/udump/prod_ora_3918.trc
l-wx------ 1 oracle oinstall 64 Jan 24 15:20 6 -> /u01/app/oracle/admin/PROD/bdump/alert_PROD.log
lrwx------ 1 oracle oinstall 64 Jan 24 15:20 7 -> /u01/app/oracle/product/10.2.0/db_1/dbs/lkinstPROD (deleted)
l-wx------ 1 oracle oinstall 64 Jan 24 15:20 8 -> /u01/app/oracle/admin/PROD/bdump/alert_PROD.log
lrwx------ 1 oracle oinstall 64 Jan 24 15:20 9 -> /u01/app/oracle/product/10.2.0/db_1/dbs/hc_PROD.dat
[oracle@secdb1 fd]$ cp 27 /u01/app/oracle/oradata/PROD/disk1/ANING01.dbf
[oracle@secdb1 fd]$ ll /u01/app/oracle/oradata/PROD/disk1/ANING01.dbf
-rw-r----- 1 oracle oinstall 94380032 Jan 24 15:23 /u01/app/oracle/oradata/PROD/disk1/ANING01.dbf
SQL> select file#,tablespace_name,status from v$datafile_header;
FILE# TABLESPACE_NAMESTATUS
---------- ------------------------------ -------
1 SYSTEMONLINE
2 UNDOTBSONLINE
3 SYSAUXONLINE
4 EXAMPLEONLINE
5 INDXONLINE
6 TOOLSONLINE
7 USERSONLINE
8 OLTPONLINE
9 ANINGONLINE
9 rows selected.
在ANING表空间插入测试数据
SQL> create table aningrestore(id number) tablespace aning;
Table created.
SQL> insert into aningrestore values(2);
1 row created.
SQL> commit;
Commit complete.
SQL> create table aningt tablespace aning as select * from dba_objects;
Table created.
恢复ANING表空间数据文件
SQL> alter database datafile 9 offline;
Database altered.
SQL> select file#,tablespace_name,status from v$datafile_header;
FILE# TABLESPACE_NAMESTATUS
---------- ------------------------------ -------
1 SYSTEMONLINE
2 UNDOTBSONLINE
3 SYSAUXONLINE
4 EXAMPLEONLINE
5 INDXONLINE
6 TOOLSONLINE
7 USERSONLINE
8 OLTPONLINE
9 ANINGOFFLINE
9 rows selected.
SQL> recover datafile 9;
Media recovery complete.
SQL> alter database datafile 9 online;
Database altered.
SQL> select file#,tablespace_name,status from v$datafile_header;
FILE# TABLESPACE_NAMESTATUS
---------- ------------------------------ -------
1 SYSTEMONLINE
2 UNDOTBSONLINE
3 SYSAUXONLINE
4 EXAMPLEONLINE
5 INDXONLINE
6 TOOLSONLINE
7 USERSONLINE
8 OLTPONLINE
9 ANINGONLINE
9 rows selected.