生产案例：开发不小心把某个表数据清了，没有逻辑备份，有物理备份

刚到公司坐下，开发人员就匆匆跑过来跟我说：“由于不知道哪一天不小心把某某表的数据清空了，能否帮我恢复一下。” 第一反应，只要备份，那很简单，所以下面是我的解决办法。

 

思路一：利用闪回找回

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;

 

 

搞定！！

 

转载于:https://www.cnblogs.com/galengao/p/6144742.html