rman恢复普通表空间文件(用户表空间)

本文描述：模拟普通表空间(用户表空间)丢失并恢复的过程

试验过程：

注：试验前先将数据库全备或者0级备份

全备： backup database format 'g:\rmanbak\db_%d_%T_%s.bak' plus archivelog format 'g:\rmanbak\arch_%d_%T_%s.bak';

0级备份： backup incremental level 0  database format 'g:\rmanbak\db_%d_%T_%s.bak' plus archivelog format 'g:\rmanbak\arch_%d_%T_%s.bak';

(1)登录sqlplus，match_data表空间丢失

C:\Users\Administrator>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on 星期三 6月 14 10:36:12 2017

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> conn match/match
Connected.
SQL> select * from t_odds_asian where rownum=1;
select * from t_odds_asian where rownum=1
              *
ERROR at line 1:
ORA-01116: 打开数据库文件 8 时出错
ORA-01110: 数据文件 8: 'F:\ORADATA\MATCH_DATA01.DBF'
ORA-27041: 无法打开文件
OSD-04002: unable to open file
O/S-Error: (OS 2) 系统找不到指定的文件。

SQL>

(2)登录rman进行恢复

C:\Users\Administrator>rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on 星期三 6月 14 12:44:27 2017

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORCL (DBID=1468451227)

RMAN> run{
2> sql 'alter database datafile 8 offline';
3> restore datafile 8;
4> recover datafile 8;
5> sql 'alter database datafile 8 online';
6> }

using target database control file instead of recovery catalog
sql statement: alter database datafile 8 offline

Starting restore at 14-6月 -17
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=197 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 00008 to F:\ORADATA\MATCH_DATA01.DBF
channel ORA_DISK_1: reading from backup piece G:\RMANBP\FDB_ORCL_20170613_14.BAK
channel ORA_DISK_1: piece handle=G:\RMANBP\FDB_ORCL_20170613_14.BAK tag=TAG20170613T123834
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:01:45
Finished restore at 14-6月 -17

Starting recover at 14-6月 -17
using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 74 is already on disk as file F:\APP\ADMINISTRATOR\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2017_06_13\O1_MF_1_74_D
MYVCR6Q_.ARC
archived log for thread 1 with sequence 75 is already on disk as file F:\APP\ADMINISTRATOR\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2017_06_13\O1_MF_1_75_D
MYVTGMD_.ARC
archived log for thread 1 with sequence 76 is already on disk as file F:\APP\ADMINISTRATOR\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2017_06_13\O1_MF_1_76_D
MZ8PWJ1_.ARC
archived log for thread 1 with sequence 77 is already on disk as file F:\APP\ADMINISTRATOR\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2017_06_14\O1_MF_1_77_D
N155JPS_.ARC
archived log for thread 1 with sequence 78 is already on disk as file F:\APP\ADMINISTRATOR\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2017_06_14\O1_MF_1_78_D
N16YS6W_.ARC
archived log for thread 1 with sequence 79 is already on disk as file F:\APP\ADMINISTRATOR\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2017_06_14\O1_MF_1_79_D
N17JN2C_.ARC
archived log for thread 1 with sequence 80 is already on disk as file F:\APP\ADMINISTRATOR\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2017_06_14\O1_MF_1_80_D
N17RDDN_.ARC
archived log for thread 1 with sequence 81 is already on disk as file F:\APP\ADMINISTRATOR\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2017_06_14\O1_MF_1_81_D
N1FMP5Z_.ARC
archived log for thread 1 with sequence 82 is already on disk as file F:\APP\ADMINISTRATOR\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2017_06_14\O1_MF_1_82_D
N1HKJSX_.ARC
archived log file name=F:\APP\ADMINISTRATOR\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2017_06_13\O1_MF_1_74_DMYVCR6Q_.ARC thread=1 sequence=74
archived log file name=F:\APP\ADMINISTRATOR\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2017_06_13\O1_MF_1_75_DMYVTGMD_.ARC thread=1 sequence=75
archived log file name=F:\APP\ADMINISTRATOR\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2017_06_13\O1_MF_1_76_DMZ8PWJ1_.ARC thread=1 sequence=76
archived log file name=F:\APP\ADMINISTRATOR\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2017_06_14\O1_MF_1_77_DN155JPS_.ARC thread=1 sequence=77
archived log file name=F:\APP\ADMINISTRATOR\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2017_06_14\O1_MF_1_78_DN16YS6W_.ARC thread=1 sequence=78
archived log file name=F:\APP\ADMINISTRATOR\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2017_06_14\O1_MF_1_79_DN17JN2C_.ARC thread=1 sequence=79
archived log file name=F:\APP\ADMINISTRATOR\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2017_06_14\O1_MF_1_80_DN17RDDN_.ARC thread=1 sequence=80
media recovery complete, elapsed time: 00:00:03
Finished recover at 14-6月 -17

sql statement: alter database datafile 8 online

RMAN>

(3)登录sqlplus查询成功，普通表空间恢复完成。(数据涉及公司隐私，就不贴出了，见谅哈)