生产案例：开发不小心把某个表数据清了，没有逻辑备份，有物理备份
刚到公司坐下，开发人员就匆匆跑过来跟我说：“由于不知道哪一天不小心把某某表的数据清空了，能否帮我恢复一下。” 第一反应，只要备份，那很简单，所以下面是我的解决办法。
思路一：利用闪回找回
select * from INFO_FEEDBACK_ORCLE as of timestamp to_timestamp('2016-12-06 12:00:30','YYYY-MM-DD HH24:MI:SS') --提示闪回时间已很久
现在时间是2016年12月8号10点，我马上用闪回查询到能查到的最早时间12月6号12:00，发现那时的数据就已经清空了，所以闪回找不到了，放弃。
思路二：利用逻辑备份恢复
因为逻辑备份，无论是expdp或者exp都能指定恢复某一张表，所以赶快到服务器的调度任务里查看有没有逻辑备份，发现没有用逻辑备份，只有物理备份RMAN，所以逻辑备份恢复还是无法还原。
思路三：RMAN物理备份
最后只能用最麻烦的RMAN物理备份里，为什么说麻烦呢，因为如果在本机直接用RMAN恢复的话，就会导致其它所以表的数据都恢复了，那最近的数据就没了，所以这是万万不行的，只能利用RMAN异地恢复，然后再exp导出该表，在到服务器导入该表，所以操作有点麻烦，不管如何我答应开发，一个小时搞定，所以得赶快动手，一下是我的恢复步骤：
前提：我有一个安装oracle的测试环境
1、把原库的备份文件拷贝到测试库
备份文件包括archive归档，datafile数据，spfile参数文件，control控制文件，原库备份脚本如下：
run { allocate channel d1 type disk; backup as COMPRESSED BACKUPSET database format='/backup/oracle/b2bdb/full_%d_%T_%s_%p.bak'; sql 'alter system archive log current'; backup as COMPRESSED BACKUPSET archivelog all format '/backup/oracle/b2bdb/arch_log_%d_%T_%s_%p.bak' delete all input; backup current controlfile format='/backup/oracle/b2bdb/controlfile_%d_%T_%s_%p.bak' spfile format='/backup/oracle/b2bdb/spfile_%d_%T_%s_%p.bak'; release channel d1; } crosscheck backup; delete noprompt expired backup; delete noprompt obsolete;
2、生成密码文件
orapwd file=?/dbs/orapwb2bdb password=oracle
3、RMAN连接备库，并启动到nomount状态，恢复init参数文件
--指定SID export ORACLE_SID=b2bdb --连接RMAN测试库 rman target / --启动到nomount状态 startup nomount --restire spfile RMAN> restore spfile to pfile'/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initb2bdb.ora' from '/u01/bak/spfile_B2BDB_20161201_959_1.bak';
4、把恢复的参数文件initb2bdb.ora修改里面的路径内容
为了能让恢复的速度更快，我几乎不修改里面的内容，我仅仅把里面参数文件提到的目录都在测试环境建一个，与原库一样。
5、利用新生产的初始参数文件启动到nomount
另外开一个窗口，用我们修改会的initb2bdb.ora重启数据库倒nomount
sqlplus / as sysdba --由于刚开始启动过，所以先关闭一下 shutdown immediate --指定我们恢复的参数文件启动 startup nomount pfile=?/dbs/initb2bdb.ora
6、恢复控制文件
上面启动到nomount状态后，我们就可以恢复控制文件了，退出，或者另开一个窗口
--指定SID [oracle@oratest bak]$ export ORACLE_SID=b2bdb --连接RMAN [oracle@oratest bak]$ rman target / Recovery Manager: Release 11.2.0.4.0 - Production on Thu Dec 1 01:42:42 2016 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. connected to target database: B2BDB (not mounted) --恢复控制文件，from指向我们刚才拷贝过来的备份文件 RMAN> restore controlfile from '/u01/bak/controlfile_B2BDB_20161201_958_1.bak'; Starting restore at 01-DEC-16 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=19 device type=DISK channel ORA_DISK_1: restoring control file channel ORA_DISK_1: restore complete, elapsed time: 00:00:01 output file name=/u01/app/oracle/oradata/b2bdb/control01.ctl output file name=/u01/app/oracle/oradata/b2bdb/control02.ctl Finished restore at 01-DEC-16
7、将数据库启动到mount状态
RMAN> alter database mount; database mounted released channel: ORA_DISK_1
8、restore数据库（同目录）
此处我是为了能更快速度的恢复，所以数据目录那些建的跟原库是一样的，同目录情况下，我只要执行以下命令即可：
restore database;
8、restore数据库（不同目录）
若需要恢复到不同的目录，需要查出原库数据文件对应的序列号，然后set以下，如下：
注意：此次恢复不执行如下步骤，因为我的是同目录
（1）先到原库查询一下如下信息：
select file_id,file_name from dba_data_files; 1 /oradata/b2bdb/system01.dbf 2 /oradata/b2bdb/sysaux01.dbf 3 /oradata/b2bdb/undotbs01.dbf 4 /oradata/b2bdb/users01.dbf 5 /oradata/b2bdb/tbs_sso_001.dbf 6 /oradata/b2bdb/tbs_cableex_001.dbf 7 /oradata/b2bdb/tbs_b2b_001.dbf 8 /oradata/b2bdb/tbs_kh_test_001.dbf 9 /oradata/b2bdb/tbs_mmbao2_001.dbf select file_id,file_name from dba_temp_files; 1 /oradata/b2bdb/temp01.dbf
注意，restore 的时候不会对temp 表空间进行restore。所以等restore 之后，我们需要手工创建temp表空间。
利用rman脚本还原
RMAN> run { set newname for datafile 1 to "/u01/app/oracle/oradata/system01.dbf"; set newname for datafile 2 to "/u01/app/oracle/oradata/sysaux01.dbf"; set newname for datafile 3 to "/u01/app/oracle/oradata/undotbs01.dbf"; set newname for datafile 4 to "/u01/app/oracle/oradata/users01.dbf"; set newname for datafile 5 to "/u01/app/oracle/oradata/tbs_sso_001.dbf"; set newname for datafile 6 to "/u01/app/oracle/oradata/tbs_cableex_001.dbf"; set newname for datafile 7 to "/u01/app/oracle/oradata/tbs_b2b_001.dbf"; set newname for datafile 8 to "/u01/app/oracle/oradata/tbs_kh_test_001.dbf"; set newname for datafile 9 to "/u01/app/oracle/oradata/tbs_mmbao2_001.dbf"; restore database; switch datafile all; }
9、recover 数据库
然后恢复数据库
RMAN> recover database; Starting recover at 01-DEC-16 using channel ORA_DISK_1 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=1814 channel ORA_DISK_1: restoring archived log archived log thread=1 sequence=1815 channel ORA_DISK_1: restoring archived log archived log thread=1 sequence=1816 channel ORA_DISK_1: reading from backup piece /backup/oracle/b2bdb/arch_log_B2BDB_20161201_957_1.bak channel ORA_DISK_1: errors found reading piece handle=/backup/oracle/b2bdb/arch_log_B2BDB_20161201_957_1.bak channel ORA_DISK_1: failover to piece handle=/u01/bak/arch_log_B2BDB_20161201_957_1.bak tag=TAG20161201T050152 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:00:25 archived log file name=/u01/app/oracle/oradata/b2bdb_archivelog/archivelog/1_1814_915284737.arch thread=1 sequence=1814 archived log file name=/u01/app/oracle/oradata/b2bdb_archivelog/archivelog/1_1815_915284737.arch thread=1 sequence=1815 archived log file name=/u01/app/oracle/oradata/b2bdb_archivelog/archivelog/1_1816_915284737.arch thread=1 sequence=1816 unable to find archived log archived log thread=1 sequence=1817 RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of recover command at 12/01/2016 02:20:41 RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 1817 and starting SCN of 180140127
若后面报这个错RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 1817 and starting SCN of 180140127
我们可以直接恢复到他指定的SCN点
RMAN> recover database until scn 180140127; Starting recover at 01-DEC-16 using channel ORA_DISK_1 starting media recovery media recovery complete, elapsed time: 00:00:00 Finished recover at 01-DEC-16
10、用open resetlogs 打开数据库
SQL> alter database open resetlogs; Database altered.
到此就够用了，至于后续一些小问题，如临时空间，日志路径问题等看着错误去处理。
11、用exp把需要恢复的那张表备份出来
exp xxx/xxx file=xxx tables=xx
12、先在原库里把那张表备份一下，然后删除
create table xx_bak as select * from xx; drop table xx;
13、用imp把备份恢复进去
imp xxx/xxx file=xx fromuser=xxx touser=xxx
14、把刚刚备份的数据也插入到刚刚恢复的表里
insert into xx select * from xx_bak;
搞定！！
