实验条件：有完整可用备份
--查询表空间情况
SQL> select tablespace_name,status from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
SYSAUX ONLINE
TEMP ONLINE
USERS ONLINE
UNDOTBS2 ONLINE
GOLDGATE ONLINE
FDA01 ONLINE
FDA02 ONLINE
ANDY ONLINE
9 rows selected.
SQL>
col tablespace_name for a15
col file_name for a50
SELECT tablespace_name, file_id, file_name, round(bytes / (1024 * 1024), 0) total_space
FROM dba_data_files where tablespace_name='ANDY' ORDER BY tablespace_name;
TABLESPACE_NAME FILE_ID FILE_NAME TOTAL_SPACE
--------------- ---------- -------------------------------------------------- -----------
ANDY 3 /home/oracle/app/oradata/orcl/andy01.dbf 10
ANDY 5 /home/oracle/app/oradata/orcl/andy02.dbf 1
--模拟表空间损坏
[oracle@11g orcl]$ ll andy*
-rw-r-----. 1 oracle oinstall 10493952 Mar 24 17:41 andy01.dbf
-rw-r-----. 1 oracle oinstall 1056768 Mar 24 17:53 andy02.dbf
[oracle@11g orcl]$ rm -rf andy*
[oracle@11g trace]$ cat alert_orcl.log |grep ORA-
ORA-01116: error in opening database file 3
ORA-01110: data file 3: '/home/oracle/app/oradata/orcl/andy01.dbf'
ORA-27041: unable to open file
ORA-01116: error in opening database file 5
ORA-01110: data file 5: '/home/oracle/app/oradata/orcl/andy02.dbf'
ORA-27041: unable to open file
--恢复过程
SQL> alter tablespace andy offline;
alter tablespace andy offline
*
ERROR at line 1:
ORA-01116: error in opening database file 3
ORA-01110: data file 3: '/home/oracle/app/oradata/orcl/andy01.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
如果文件不存在，就加immediate参数
SQL> alter tablespace andy offline immediate;
Tablespace altered.
rman>restore tablespace andy;
rman>recover tablespace andy;
rman>sql 'alter tablespace andy online';
sql statement: alter tablespace andy online