上周一客户现场由于存储过程原因导致删除了2000多张表，需要通过不完全恢复来恢复这些表数据，如何能缩短恢复时间喃？
考虑只恢复这些表所在的表空间以及必要的表空间（system、undo等），忽略掉其他没有关系的表空间来缩短恢复时间（这个问题曾经被熊哥用于考核一同事的晋级考核，O(∩_∩)O哈！）
测试步骤如下：
1、创建测试表空间
2、创建测试用户
3、创建测试表空间
4、创建测试表
5、切换日志后备份
6、异机通过pfile文件启动数据库并恢复控制文件，使数据库进入mount状态
7、通过restore database skip tablespace命令还原数据文件，忽略掉不需要的表空间
8、通过recover database skip tablespace命令恢复数据，忽略掉不需要的表空间
9、通过open resetlogs打开数据库
10、查看数据
通过忽略还原恢复不需要的表空间做不完全恢复找回数据，对于只删除了部分表空间中的表数据，其他表空间数据量又非常巨大的oracle系统来讲，是一种有效缩短恢复时间的可选方法。
考虑只恢复这些表所在的表空间以及必要的表空间（system、undo等），忽略掉其他没有关系的表空间来缩短恢复时间（这个问题曾经被熊哥用于考核一同事的晋级考核，O(∩_∩)O哈！）
测试步骤如下：
1、创建测试表空间
SQL>
SQL> select file_name,tablespace_name from dba_data_files;
FILE_NAME TABLESPACE_NAME
------------------------------------------------------------ ------------------------------
/oradata/ora11g/users01.dbf USERS
/oradata/ora11g/undotbs01.dbf UNDOTBS1
/oradata/ora11g/sysaux01.dbf SYSAUX
/oradata/ora11g/system01.dbf SYSTEM
/oradata/ora11g/example01.dbf EXAMPLE
SQL>
SQL> create tablespace test1 datafile '/oradata/ora11g/test01.dbf' size 100m;
Tablespace created.
SQL>
2、创建测试用户
SQL> create user hcn identified by oracle default tablespace test1;
User created.
SQL>
SQL>
SQL> grant resource,create session to hcn;
Grant succeeded.
SQL>
3、创建测试表空间
SQL>
SQL> create tablespace test2 datafile '/oradata/ora11g/test21.dbf' size 100m;
Tablespace created.
SQL> create tablespace test3 datafile '/oradata/ora11g/test31.dbf' size 100m;
Tablespace created.
SQL>
SQL>
SQL> select file_name,tablespace_name from dba_data_files;
FILE_NAME TABLESPACE_NAME
------------------------------------------------------------ ------------------------------
/oradata/ora11g/users01.dbf USERS
/oradata/ora11g/undotbs01.dbf UNDOTBS1
/oradata/ora11g/sysaux01.dbf SYSAUX
/oradata/ora11g/system01.dbf SYSTEM
/oradata/ora11g/example01.dbf EXAMPLE
/oradata/ora11g/test01.dbf TEST1
/oradata/ora11g/test21.dbf TEST2
/oradata/ora11g/test31.dbf TEST3
8 rows selected.
SQL>
4、创建测试表
SQL>
SQL> create table hcn.test1 as select * from dba_objects;
Table created.
SQL>
SQL>
SQL> select owner,table_name,tablespace_name from dba_tables where owner='HCN';
OWNER TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------
HCN TEST1 TEST1
SQL>
SQL>
SQL>
SQL> select count(*) from hcn.test1;
COUNT(*)
----------
86956
SQL>
5、切换日志后备份
SQL>
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL>
/* 备份命令如下：*/
[oracle@orl5 rmanbak]$ rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Mon Mar 21 22:22:22 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORA11G (DBID=8338623)
RMAN> backup database format '/oradata/rmanbak/full_bkl_%u%p%s.rmn' include current controlfile plus archivelog format '/oradata/rmanbak/control_%u%p%s.rmn';
Starting backup at 21-MAR-16
current log archived
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=134 device type=DISK
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=5 RECID=1 STAMP=907106422
input archived log thread=1 sequence=6 RECID=2 STAMP=907107326
input archived log thread=1 sequence=7 RECID=3 STAMP=907107538
input archived log thread=1 sequence=8 RECID=4 STAMP=907107541
input archived log thread=1 sequence=9 RECID=5 STAMP=907107543
input archived log thread=1 sequence=10 RECID=6 STAMP=907107745
channel ORA_DISK_1: starting piece 1 at 21-MAR-16
channel ORA_DISK_1: finished piece 1 at 21-MAR-16
piece handle=/oradata/rmanbak/control_01r12nd111.rmn tag=TAG20160321T222225 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 21-MAR-16
Starting backup at 21-MAR-16
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/oradata/ora11g/system01.dbf
input datafile file number=00002 name=/oradata/ora11g/sysaux01.dbf
input datafile file number=00006 name=/oradata/ora11g/test01.dbf
input datafile file number=00005 name=/oradata/ora11g/example01.dbf
input datafile file number=00003 name=/oradata/ora11g/undotbs01.dbf
input datafile file number=00007 name=/oradata/ora11g/test21.dbf
input datafile file number=00008 name=/oradata/ora11g/test31.dbf
input datafile file number=00004 name=/oradata/ora11g/users01.dbf
channel ORA_DISK_1: starting piece 1 at 21-MAR-16
channel ORA_DISK_1: finished piece 1 at 21-MAR-16
piece handle=/oradata/rmanbak/full_bkl_02r12nd212.rmn tag=TAG20160321T222226 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 21-MAR-16
channel ORA_DISK_1: finished piece 1 at 21-MAR-16
piece handle=/oradata/rmanbak/full_bkl_03r12ndr13.rmn tag=TAG20160321T222226 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 21-MAR-16
Starting backup at 21-MAR-16
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=11 RECID=7 STAMP=907107773
channel ORA_DISK_1: starting piece 1 at 21-MAR-16
channel ORA_DISK_1: finished piece 1 at 21-MAR-16
piece handle=/oradata/rmanbak/control_04r12ndu14.rmn tag=TAG20160321T222254 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 21-MAR-16
RMAN> backup archivelog all format '/oradata/rmanbak/arch_%u%p%s.rmn' ;
Starting backup at 21-MAR-16
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=5 RECID=1 STAMP=907106422
input archived log thread=1 sequence=6 RECID=2 STAMP=907107326
input archived log thread=1 sequence=7 RECID=3 STAMP=907107538
input archived log thread=1 sequence=8 RECID=4 STAMP=907107541
input archived log thread=1 sequence=9 RECID=5 STAMP=907107543
input archived log thread=1 sequence=10 RECID=6 STAMP=907107745
input archived log thread=1 sequence=11 RECID=7 STAMP=907107773
input archived log thread=1 sequence=12 RECID=8 STAMP=907107788
channel ORA_DISK_1: starting piece 1 at 21-MAR-16
channel ORA_DISK_1: finished piece 1 at 21-MAR-16
piece handle=/oradata/rmanbak/arch_05r12ned15.rmn tag=TAG20160321T222308 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 21-MAR-16
RMAN>
RMAN>
RMAN> exit
Recovery Manager complete.
[oracle@orl5 rmanbak]$ ls -lrt
total 1413952
-rw-r----- 1 oracle oinstall 86824960 Mar 21 22:22 control_01r12nd111.rmn
-rw-r----- 1 oracle oinstall 1262960640 Mar 21 22:22 full_bkl_02r12nd212.rmn
-rw-r----- 1 oracle oinstall 9830400 Mar 21 22:22 full_bkl_03r12ndr13.rmn
-rw-r----- 1 oracle oinstall 3072 Mar 21 22:22 control_04r12ndu14.rmn
-rw-r----- 1 oracle oinstall 86827008 Mar 21 22:23 arch_05r12ned15.rmn
6、异机通过pfile文件启动数据库并恢复控制文件，使数据库进入mount状态
SQL> startup nomount pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/initora11g2.ora.bak';
ORACLE instance started.
Total System Global Area 1653518336 bytes
Fixed Size 2253784 bytes
Variable Size 1023413288 bytes
Database Buffers 620756992 bytes
Redo Buffers 7094272 bytes
SQL> startup nomount pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/initora11g.ora';
ORACLE instance started.
Total System Global Area 1653518336 bytes
Fixed Size 2253784 bytes
Variable Size 1023413288 bytes
Database Buffers 620756992 bytes
Redo Buffers 7094272 bytes
SQL>
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@orl5 rmanbak]$ rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Mon Mar 21 22:50:14 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORA11G (not mounted)
RMAN> restore controlfile from '/oradata/rmanbak/full_bkl_03r12ndr13.rmn';
Starting restore at 21-MAR-16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=63 device type=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/oradata/ora11g/control01.ctl
output file name=/oradata/fast_recovery_area/ora11g/control02.ctl
Finished restore at 21-MAR-16
RMAN> alter database mount;
database mounted
released channel: ORA_DISK_1
RMAN> catalog start with '/oradata/rmanbak';
Starting implicit crosscheck backup at 21-MAR-16
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=63 device type=DISK
Crosschecked 2 objects
Finished implicit crosscheck backup at 21-MAR-16
Starting implicit crosscheck copy at 21-MAR-16
using channel ORA_DISK_1
Finished implicit crosscheck copy at 21-MAR-16
searching for all files in the recovery area
cataloging files...
no files cataloged
searching for all files that match the pattern /oradata/rmanbak
List of Files Unknown to the Database
=====================================
File Name: /oradata/rmanbak/full_bkl_03r12ndr13.rmn
File Name: /oradata/rmanbak/control_04r12ndu14.rmn
File Name: /oradata/rmanbak/arch_05r12ned15.rmn
Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /oradata/rmanbak/full_bkl_03r12ndr13.rmn
File Name: /oradata/rmanbak/control_04r12ndu14.rmn
File Name: /oradata/rmanbak/arch_05r12ned15.rmn
RMAN> crosscheck backupset;
using channel ORA_DISK_1
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/oradata/rmanbak/control_01r12nd111.rmn RECID=1 STAMP=907107745
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/oradata/rmanbak/full_bkl_02r12nd212.rmn RECID=2 STAMP=907107746
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/oradata/rmanbak/full_bkl_03r12ndr13.rmn RECID=3 STAMP=907109445
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/oradata/rmanbak/control_04r12ndu14.rmn RECID=4 STAMP=907109445
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/oradata/rmanbak/arch_05r12ned15.rmn RECID=5 STAMP=907109445
Crosschecked 5 objects
7、通过restore database skip tablespace命令还原数据文件，忽略掉不需要的表空间
RMAN> restore database skip tablespace TEST2,TEST3,USERS;
Starting restore at 21-MAR-16
using channel ORA_DISK_1
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 00001 to /oradata/ora11g/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /oradata/ora11g/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /oradata/ora11g/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00005 to /oradata/ora11g/example01.dbf
channel ORA_DISK_1: restoring datafile 00006 to /oradata/ora11g/test01.dbf
channel ORA_DISK_1: reading from backup piece /oradata/rmanbak/full_bkl_02r12nd212.rmn
channel ORA_DISK_1: piece handle=/oradata/rmanbak/full_bkl_02r12nd212.rmn tag=TAG20160321T222226
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:35
Finished restore at 21-MAR-16
RMAN>
8、通过recover database skip tablespace命令恢复数据，忽略掉不需要的表空间
RMAN> recover database skip tablespace TEST2,TEST3,USERS;
Starting recover at 21-MAR-16
using channel ORA_DISK_1
Executing: alter database datafile 7 offline
Executing: alter database datafile 8 offline
Executing: alter database datafile 4 offline
starting media recovery
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=11
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=12
channel ORA_DISK_1: reading from backup piece /oradata/rmanbak/arch_05r12ned15.rmn
channel ORA_DISK_1: piece handle=/oradata/rmanbak/arch_05r12ned15.rmn tag=TAG20160321T222308
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=/oradata/fast_recovery_area/ORA11G/archivelog/2016_03_21/o1_mf_1_11_ch02pmo2_.arc thread=1 sequence=11
channel default: deleting archived log(s)
archived log file name=/oradata/fast_recovery_area/ORA11G/archivelog/2016_03_21/o1_mf_1_11_ch02pmo2_.arc RECID=7 STAMP=907109587
archived log file name=/oradata/fast_recovery_area/ORA11G/archivelog/2016_03_21/o1_mf_1_12_ch02pmom_.arc thread=1 sequence=12
channel default: deleting archived log(s)
archived log file name=/oradata/fast_recovery_area/ORA11G/archivelog/2016_03_21/o1_mf_1_12_ch02pmom_.arc RECID=8 STAMP=907109587
unable to find archived log
archived log thread=1 sequence=13
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 03/21/2016 22:53:09
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 13 and starting SCN of 1002997
RMAN>
9、通过open resetlogs打开数据库
10、查看数据
SQL>
SQL> select count(*) from hcn.test1;
COUNT(*)
----------
86956
SQL>
通过忽略还原恢复不需要的表空间做不完全恢复找回数据，对于只删除了部分表空间中的表数据，其他表空间数据量又非常巨大的oracle系统来讲，是一种有效缩短恢复时间的可选方法。
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
