来模拟一种刚建的表空间，还没来的及备份，数据文件被删除，但archive log 都在时的恢复。
sys@ANBOB>create tablespace tbs_rm datafile '/oradata/anbob/tbs_rm01.dbf' size 10m;
Tablespace created.
sys@ANBOB>conn anbob
Enter password:
Connected.
anbob@ANBOB>create table test_rm tablespace tbs_rm as select rownum id from dual connect by level<=10;
Table created.
anbob@ANBOB>select * from test_rm;
ID
--------------------
1
...
9
10
破坏数据文件，使用mv
[oracle@db231 ~]$ mv /oradata/anbob/tbs_rm01.dbf /oradata/anbob/tbs_rm01_dbf
sys@ANBOB>alter system flush buffer_cache;
System altered.
anbob@ANBOB>select * from test_rm;
ID
--------------------
1
...
10
anbob@ANBOB>exit
Disconnected
sys@ANBOB>alter system flush buffer_cache;
System altered.
sys@ANBOB>select * from anbob.test_rm;
select * from anbob.test_rm
*
ERROR at line 1:
ORA-01116: error in opening database file 12
ORA-01110: data file 12: '/oradata/anbob/tbs_rm01.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
恢复数据文件
sys@ANBOB>col name for a55
sys@ANBOB>select file#,name,status from v$datafile;
FILE# NAME STATUS
-------------------- ------------------------------------------------------- -------
1 /u01/app/oracle/oradata/anbob/system01.dbf SYSTEM
2 /u01/app/oracle/oradata/anbob/sysaux01.dbf ONLINE
3 /u01/app/oracle/oradata/anbob/undotbs01.dbf ONLINE
4 /u01/app/oracle/oradata/anbob/users01.dbf ONLINE
...
12 /oradata/anbob/tbs_rm01.dbf ONLINE
sys@ANBOB>select tablespace_name,status from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS ONLINE
..
TBS_RM ONLINE
sys@ANBOB>select file_name,tablespace_name,status from dba_data_files;
ERROR:
ORA-01116: error in opening database file 12
ORA-01110: data file 12: '/oradata/anbob/tbs_rm01.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
sys@ANBOB>select name,status,error,file# from v$datafile_header;
NAME STATUS ERROR FILE#
------------------------------------------------------- ------- ------------------------------------------------------
/u01/app/oracle/oradata/anbob/system01.dbf ONLINE 1
/u01/app/oracle/oradata/anbob/sysaux01.dbf ONLINE 2
/u01/app/oracle/oradata/anbob/undotbs01.dbf ONLINE 3
/u01/app/oracle/oradata/anbob/users01.dbf ONLINE 4
...
ONLINE CANNOT OPEN FILE 12
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@db231 ~]$ rman target /
Recovery Manager: Release 11.2.0.3.0 - Production on Mon Feb 24 17:02:59 2014
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: ANBOB (DBID=1190288265)
RMAN> restore datafile 12;
Starting restore at 2014-02-24 17:03:10
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1343 device type=DISK
creating datafile file number=12 name=/oradata/anbob/tbs_rm01.dbf
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 02/24/2014 17:03:10
ORA-01182: cannot create database file 12 - file is in use or recovery
ORA-01110: data file 12: '/oradata/anbob/tbs_rm01.dbf'
sys@ANBOB>alter tablespace tbs_rm offline;
alter tablespace tbs_rm offline
*
ERROR at line 1:
ORA-01116: error in opening database file 12
ORA-01110: data file 12: '/oradata/anbob/tbs_rm01.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
sys@ANBOB>alter database create datafile 12;
alter database create datafile 12
*
ERROR at line 1:
ORA-01182: cannot create database file 12 - file is in use or recovery
ORA-01110: data file 12: '/oradata/anbob/tbs_rm01.dbf'
sys@ANBOB>alter database datafile '/oradata/anbob/tbs_rm01.dbf' offline;
Database altered.
[oracle@db231 ~]$ rman target /
RMAN> restore datafile 12;
Starting restore at 2014-02-24 17:09:31
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=578 device type=DISK
creating datafile file number=12 name=/oradata/anbob/tbs_rm01.dbf >>> In 10g or later, Oracle creates the missing datafile .
restore not done; all files read only, offline, or already restored
Finished restore at 2014-02-24 17:09:32
RMAN> recover datafile 12;
sys@ANBOB>alter database datafile '/oradata/anbob/tbs_rm01.dbf' online;
Database altered.
sys@ANBOB>select * from anbob.test_rm;
ID
--------------------
1
..
10
Other way:
[oracle@db231 ~]$ mv /oradata/anbob/tbs_rm01.dbf /oradata/anbob/tbs_rm01_dbf
[oracle@db231 ~]$ ora
bash: ora: command not found
[oracle@db231 ~]$ exit
exit
sys@ANBOB>alter system flush buffer_cache;
System altered.
sys@ANBOB>select * from anbob.test_rm;
select * from anbob.test_rm
*
ERROR at line 1:
ORA-01116: error in opening database file 12
ORA-01110: data file 12: '/oradata/anbob/tbs_rm01.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
sys@ANBOB>alter database datafile 12 offline;
Database altered.
sys@ANBOB>alter database create datafile 12;
Database altered.
sys@ANBOB>alter database datafile '/oradata/anbob/tbs_rm01.dbf' online;
alter database datafile '/oradata/anbob/tbs_rm01.dbf' online
*
ERROR at line 1:
ORA-01113: file 12 needs media recovery
ORA-01110: data file 12: '/oradata/anbob/tbs_rm01.dbf'
sys@ANBOB>recover datafile 12;
Media recovery complete.
sys@ANBOB>alter database datafile '/oradata/anbob/tbs_rm01.dbf' online;
Database altered.
sys@ANBOB>select * from anbob.test_rm;
ID
--------------------
1
...
10