通过以下事例来说明表空间的恢复，删除表空间内的数据文件，删除后在针对位于该表空间的表进行插入记录以及实施检查点进程
SQL> select a.tablespace_name from dba_tablespaces a; TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
USERS
1.1查询表空间
RMAN> backup tablespace users format '/data2/backup/tb_%d_%T_%U'; Starting backup at 14-OCT-15
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00004 name=/data2/orcl/user01.dbf
channel ORA_DISK_1: starting piece 1 at 14-OCT-15
channel ORA_DISK_1: finished piece 1 at 14-OCT-15
piece handle=/data2/backup/tb_ORCL_20151014_24qjn1b5_1_1 tag=TAG20151014T172700 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 14-OCT-15 Starting Control File and SPFILE Autobackup at 14-OCT-15
piece handle=/u01/app/oracle/fast_recovery_area/ORCL/autobackup/2015_10_14/o1_mf_s_893093222_c1w7z7xo_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 14-OCT-15
1.2备份表空间
SQL> ! rm /data2/orcl/user01.dbf; 1.查看数据文件是否存:
[oracle@TEST144239 ~]$ ll /data2/orcl/
总用量 1712960
-rw-r----- 1 oracle oinstall 9846784 10月 14 17:35 control01.ctl
-rw-r----- 1 oracle oinstall 9748480 10月 12 15:18 control01.ctl.bak
drwxr-xr-x 3 oracle oinstall 4096 10月 10 10:48 recover_log
-rw-r----- 1 oracle oinstall 52429312 10月 14 17:35 redo01.log
-rw-r----- 1 oracle oinstall 52429312 10月 14 16:03 redo02.log
-rw-r----- 1 oracle oinstall 52429312 10月 14 16:03 redo03.log
-rw-r----- 1 oracle oinstall 723525632 10月 14 17:35 sysaux01.dbf
-rw-r----- 1 oracle oinstall 754982912 10月 14 17:33 system01.dbf
-rw-r----- 1 oracle oinstall 30416896 10月 14 17:10 temp01.dbf
-rw-r----- 1 oracle oinstall 94380032 10月 14 17:35 undotbs01.dbf
SQL> insert into scott.dept
2 (deptno, dname, loc)
3 values
4 (99, 'xuzhengzhu', '中国'); 1 row created. SQL> commit;
Commit complete. 2.手工执行一次检查点(进行检查点时,会触发数据块从数据缓存区写入到数据文件)
SQL> alter system checkpoint;
alter system checkpoint
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 18465
Session ID: 191 Serial number: 3 3.强制检查点后，告警日志出现错误提示，视图v$recover_file给出了故障数据文件:
[oracle@TEST144239 alert]$ cd /
[oracle@TEST144239 /]$ cd /u01/app/oracle/diag/rdbms/orcl/orcl/alert
[oracle@TEST144239 alert]$ ll
总用量 644
-rw-r----- 1 oracle oinstall 652116 10月 14 17:39 log.xml
[oracle@TEST144239 alert]$ tail -n 50 log.xml
</txt>
</msg>
<msg time='2015-10-14T17:39:08.856+08:00' org_id='oracle' comp_id='rdbms'
client_id='' type='UNKNOWN' level=''
host_id='TEST144239.localdomain' host_addr='10.88.144.239' module=''
pid=''>
<txt>Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ckpt_18394.trc:
ORA-63999: data file suffered media failure
ORA-01116: error in opening database file 4
ORA-01110: data file 4: '/data2/orcl/user01.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
1.3试验(删除表空间)
使用RMAN命令恢复数据文件，此时数据库处于OPEN状态，因此首先需要将表空间脱机，恢复完成之后再将其联机。
RMAN> run{
2> sql 'alter tablespace users offline immediate';
3> set newname for datafile 4 to '/data2/orcl/user01.dbf';
4> restore tablespace users ;
5> switch datafile all;
6> recover tablespace users ;
7> sql 'alter tablespace users online';
8> }
sql statement: alter tablespace users offline immediate
executing command: SET NEWNAME
Starting restore at 14-OCT-15
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 10/14/2015 18:08:26
RMAN-20021: database not set
RMAN-06019: could not translate tablespace name "users"
2.1恢复表空间
问题解决:需要在nomount 的运行状态下,设置BDID,例如该数据库，set DBID=1420421951
RMAN> startup nomount force;
Oracle instance started
Total System Global Area 1937457152 bytes
Fixed Size 2229584 bytes
Variable Size 1241516720 bytes
Database Buffers 687865856 bytes
Redo Buffers 5844992 bytes RMAN> set DBID=1420421951;
executing command: SET DBID RMAN> startup mount force;
Oracle instance started
database mounted
Total System Global Area 1937457152 bytes
Fixed Size 2229584 bytes
Variable Size 1241516720 bytes
Database Buffers 687865856 bytes
Redo Buffers 5844992 bytes RMAN> alter database open; database opened RMAN> run{
2> sql 'alter tablespace users offline immediate';
3> set newname for datafile 4 to '/data2/orcl/user01.dbf';
4> restore tablespace users ;
5> switch datafile all;
6> recover tablespace users ;
7> sql 'alter tablespace users online';
8> } sql statement: alter tablespace users offline immediate executing command: SET NEWNAME Starting restore at 15-OCT-15
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=131 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=199 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=72 device type=DISK channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00004 to /data2/orcl/user01.dbf
channel ORA_DISK_1: reading from backup piece /data2/backup/tb_ORCL_20151014_24qjn1b5_1_1
channel ORA_DISK_1: piece handle=/data2/backup/tb_ORCL_20151014_24qjn1b5_1_1 tag=TAG20151014T172700
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 15-OCT-15 Starting recover at 15-OCT-15
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3 starting media recovery archived log for thread 1 with sequence 13 is already on disk as file /data2/orcl/recover_log/archive_log/1_13_892917066.dbf
archived log for thread 1 with sequence 14 is already on disk as file /data2/orcl/recover_log/archive_log/1_14_892917066.dbf
archived log for thread 1 with sequence 15 is already on disk as file /data2/orcl/recover_log/archive_log/1_15_892917066.dbf
archived log for thread 1 with sequence 16 is already on disk as file /data2/orcl/recover_log/archive_log/1_16_892917066.dbf
archived log for thread 1 with sequence 17 is already on disk as file /data2/orcl/recover_log/archive_log/1_17_892917066.dbf
archived log file name=/data2/orcl/recover_log/archive_log/1_13_892917066.dbf thread=1 sequence=13
archived log file name=/data2/orcl/recover_log/archive_log/1_14_892917066.dbf thread=1 sequence=14
media recovery complete, elapsed time: 00:00:00
Finished recover at 15-OCT-15 sql statement: alter tablespace users online
2.2解决恢复失败问题
SQL> conn sys/Sina.2015@study as sysdba;
Connected.
SQL> select * from scott.dept; DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
99 xuzhengzhu ??????
3.0查看恢复后结果
另外: 也可以使用下面的命令完成同样的功能，注意表空间内有多个数据文件的情形，而仅有单个数据文件损坏则采用下面的方式处理更为妥当。
run{
sql 'alter database datafile 4 offline';
set newname for datafile 4 to '/data2/orcl/user01.dbf';
restore datafile 4;
switch datafile all;
recover datafile 4;
sql 'alter database datafile 4 online';}
恢复单个数据文件