Oracle11.2.0.4从RMAN备份中快速恢复单个表的方法

---

前言

由于用户误操作导致某表中的数据错乱，导致业务不能正常使用，现需要将该表恢复到一天前的状态。
用户数据库版本是11.2.0.4,11g版本对于这种误操作恢复，有3中恢复手段：
1.通过查询保留在undo表空间中的数据库快照，来恢复保留在undo表空间中的数据快照，但是这种方式受限于快照保留时间和undo表空间大小限制，可能无法获取误操作前的数据快照。如果undo保留的数据快照无法恢复误操作之前的数据，还可以使用数据库闪回功能将该表状态闪回至误操作之前状态，该功能需要求数据库闪回功能在误操作的时候是打开的并且是否能恢复到误操作之前状态受限于闪回区的大小。
2.通过归档日志挖掘，获取误操作的反向sql，将误操作的数据恢复至操作之前的状态。
3.通过rman恢复数据库（前提是rman备份是可用的），恢复整个数据库到指定时间或者恢复单个表空间（包含所要恢复的表的表空间及系统表空间）到指定时间。
12C新增恢复手段：基于rman恢复单个表，12C版本可以直接恢复某个表而不用将整个数据库或者表空间恢复。

---

此次恢复因为误操作时间已经过去很长时间，从undo中已经无法恢复到误操作之前状态并且数据库也没有开启闪回。因为数据库整体数据量不大，整库恢复和表空间恢复时间快于归档挖掘时间，所以这里我们选择恢复表空间的方式恢复表数据。

一、查询所要恢复的表所涉及的表空间

with temp as  (
select tablespace_name from dba_tables where owner=UPPER('&SCHEMA') and table_name=UPPER('&TABNAME') union all
select tablespace_name from dba_TAB_PARTITIONS where TABLE_OWNER=UPPER('&SCHEMA') and table_name=UPPER('&TABNAME') union all
select tablespace_name from dba_indexes where owner=UPPER('&SCHEMA') and table_name=UPPER('&TABNAME') union all
select tablespace_name from dba_lobs where owner=UPPER('&SCHEMA') and table_name=UPPER('&TABNAME'))
select distinct tablespace_name "tbs info " from temp union all
select ' ' as "tbs info" from dual union all
select 'file_id   |   file_name          |     tablespace' as "tbs info" from dual union all
select '------------------------------------------------------------' as "tbs info" from dual union all
select ''||file_id||'   '||file_name||'   '||tablespace_name||''  "tbs_info " from dba_data_files where
tablespace_name in (select distinct tablespace_name "tbs info " from temp);

查询结果：

可以看到该表只涉及到一个表空间，所以我们只需针对改表空间进行恢复。

二、创建用于恢复的数据库

安装与主库相同版本的数据库并创建相同实例，将完整的备份和归档拷贝至新建测试库中。

三、恢复步骤

1.恢复控制文件

restore controlfile from '/backup/full_v82fhld7_1_1_20240102.bak';
alter database mount;--数据库打开到mount
catalog start with '/bak/';--注册备份集

2.修改redo日志名称

rac恢复到单机需要重命名redo日志文件名，不然恢复完成后redo不可用：

alter database rename file'+DATADG/orcldg/onlinelog/group_4.300.1134422071' to '/u01/app/oracle/oradata/orcl/group_4.300.1134422071';
alter database rename file'+DATADG/orcldg/onlinelog/group_6.301.1134422071' to '/u01/app/oracle/oradata/orcl/group_6.301.1134422071';
alter database rename file'+DATADG/orcldg/onlinelog/group_1.297.1134422069' to '/u01/app/oracle/oradata/orcl/group_1.297.1134422069';
alter database rename file'+DATADG/orcldg/onlinelog/group_2.298.1134422071' to '/u01/app/oracle/oradata/orcl/group_2.298.1134422071';
alter database rename file'+DATADG/orcldg/onlinelog/group_3.299.1134422071' to '/u01/app/oracle/oradata/orcl/group_3.299.1134422071';
alter database rename file'+DATADG/orcldg/onlinelog/group_15.305.1134422073' to '/u01/app/oracle/oradata/orcl/group_15.305.1134422073';
alter database rename file'+DATADG/orcldg/onlinelog/group_11.302.1134422071' to '/u01/app/oracle/oradata/orcl/group_11.302.1134422071';
alter database rename file'+DATADG/orcldg/onlinelog/group_12.303.1134422071' to '/u01/app/oracle/oradata/orcl/group_12.303.1134422071';
alter database rename file'+DATADG/orcldg/onlinelog/group_13.304.1134422071' to '/u01/app/oracle/oradata/orcl/group_13.304.1134422071';
alter database rename file'+DATADG/orcldg/onlinelog/group_16.306.1134422073' to '/u01/app/oracle/oradata/orcl/group_16.306.1134422073';
alter database rename file'+DATADG/orcldg/onlinelog/group_17.307.1134422073' to '/u01/app/oracle/oradata/orcl/group_17.307.1134422073';
alter database rename file'+DATADG/orcldg/onlinelog/group_18.308.1134422073' to '/u01/app/oracle/oradata/orcl/group_18.308.1134422073';

3.表空间恢复

恢复单个表空间的话，需要连同系统表空间一起恢复：

run {
allocate channel d1 type disk;
allocate channel d2 type disk;
allocate channel d3 type disk;
allocate channel d4 type disk;
set newname for datafile 1 to '/u01/app/oracle/oradata/orcl/system01.dbf';
set newname for datafile 2 to '/u01/app/oracle/oradata/orcl/sysaux01.dbf';
set newname for datafile 20 to '/u01/app/oracle/oradata/orcl/sysaux02.dbf';
set newname for datafile 26 to '/u01/app/oracle/oradata/orcl/sysaux03.dbf';
set newname for datafile 3 to '/u01/app/oracle/oradata/orcl/undotbs01.dbf';
set newname for datafile 4 to '/u01/app/oracle/oradata/orcl/undotbs02.dbf';
set newname for datafile 41 to '/u01/app/oracle/oradata/orcl/undotbs011.dbf';
set newname for datafile 42 to '/u01/app/oracle/oradata/orcl/undotbs021.dbf';
set newname for datafile 5 to '/u01/app/oracle/oradata/orcl/users01.dbf';
set newname for datafile 7 to '/u01/app/oracle/oradata/orcl/bshrp561.dbf';
set newname for datafile 8 to '/u01/app/oracle/oradata/orcl/bshrp562.dbf';
set newname for datafile 9 to '/u01/app/oracle/oradata/orcl/bshrp563.dbf';
set newname for datafile 10 to '/u01/app/oracle/oradata/orcl/bshrp564.dbf';
set newname for datafile 11 to '/u01/app/oracle/oradata/orcl/bshrp565.dbf';
set newname for datafile 12 to '/u01/app/oracle/oradata/orcl/bshrp566.dbf';
set newname for datafile 32 to '/u01/app/oracle/oradata/orcl/bshrp567.dbf';
set newname for datafile 33 to '/u01/app/oracle/oradata/orcl/bshrp568.dbf';
set newname for datafile 34 to '/u01/app/oracle/oradata/orcl/bshrp569.dbf';
restore tablespace SYSTEM,SYSAUX,USERS,UNDOTBS1,UNDOTBS2,BSHRP56;
--注意这里所列出的表空间所包含的所有数据文件都必须在上面写全了，不然恢复会失败。
switch datafile all;
release channel d1;
release channel d2;
release channel d3;
release channel d4;
}

4.表空间recover

RMAN> recover database skip tablespace JCKH,"BS-WHIS",ANHENG,JSYB2021,HSXX,BSMRC,BSHIP_PLATFORM_MRC,BSHSS56,EXAMPLE,BSEMR56,LIS56,BSENR56,PORTAL56,TEMP;
--跳过不需要的表空间
Starting recover at 04-MAY-2024 06:48:38
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1700 device type=DISK

Executing: alter database datafile 25 offline
Executing: alter database datafile 31 offline
Executing: alter database datafile 23 offline
Executing: alter database datafile 24 offline
Executing: alter database datafile 27 offline
Executing: alter database datafile 35 offline
Executing: alter database datafile 36 offline
Executing: alter database datafile 29 offline
Executing: alter database datafile 22 offline
Executing: alter database datafile 30 offline
Executing: alter database datafile 40 offline
Executing: alter database datafile 39 offline
Executing: alter database datafile 17 offline
Executing: alter database datafile 16 offline
Executing: alter database datafile 15 offline
Executing: alter database datafile 18 offline
Executing: alter database datafile 19 offline
Executing: alter database datafile 21 offline
Executing: alter database datafile 28 offline
Executing: alter database datafile 37 offline
Executing: alter database datafile 38 offline
Executing: alter database datafile 14 offline
Executing: alter database datafile 13 offline
Executing: alter database datafile 6 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=118701
channel ORA_DISK_1: restoring archived log
archived log thread=2 sequence=118429
channel ORA_DISK_1: restoring archived log
archived log thread=2 sequence=118430
channel ORA_DISK_1: restoring archived log
archived log thread=2 sequence=118431
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=118702
channel ORA_DISK_1: restoring archived log
archived log thread=2 sequence=118432
channel ORA_DISK_1: restoring archived log
archived log thread=2 sequence=118433
channel ORA_DISK_1: restoring archived log
archived log thread=2 sequence=118434
channel ORA_DISK_1: restoring archived log
archived log thread=2 sequence=118435
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=118703
channel ORA_DISK_1: restoring archived log
archived log thread=2 sequence=118436
channel ORA_DISK_1: restoring archived log
archived log thread=2 sequence=118437
channel ORA_DISK_1: reading from backup piece /backup/arch_vb2fhlmg_1_1_20240102.bak
channel ORA_DISK_1: piece handle=/backup/arch_vb2fhlmg_1_1_20240102.bak tag=TAG20240102T005646
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:25
archived log file name=/u01/archivelog/1_118701_1006647421.dbf thread=1 sequence=118701
archived log file name=/u01/archivelog/2_118429_1006647421.dbf thread=2 sequence=118429
archived log file name=/u01/archivelog/2_118430_1006647421.dbf thread=2 sequence=118430
archived log file name=/u01/archivelog/2_118431_1006647421.dbf thread=2 sequence=118431
。
。
。
archived log file name=/u01/archivelog/1_118918_1006647421.dbf thread=1 sequence=118918
archived log file name=/u01/archivelog/2_118589_1006647421.dbf thread=2 sequence=118589
archived log file name=/u01/archivelog/1_118919_1006647421.dbf thread=1 sequence=118919
archived log file name=/u01/archivelog/2_118590_1006647421.dbf thread=2 sequence=118590
archived log file name=/u01/archivelog/1_118920_1006647421.dbf thread=1 sequence=118920
archived log file name=/u01/archivelog/2_118591_1006647421.dbf thread=2 sequence=118591
archived log file name=/u01/archivelog/1_118921_1006647421.dbf thread=1 sequence=118921
archived log file name=/u01/archivelog/2_118592_1006647421.dbf thread=2 sequence=118592
archived log file name=/u01/archivelog/1_118922_1006647421.dbf thread=1 sequence=118922
archived log file name=/u01/archivelog/1_118923_1006647421.dbf thread=1 sequence=118923
archived log file name=/u01/archivelog/2_118593_1006647421.dbf thread=2 sequence=118593
archived log file name=/u01/archivelog/1_118924_1006647421.dbf thread=1 sequence=118924
unable to find archived log
archived log thread=1 sequence=118925
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 05/04/2024 07:21:51
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 118925 and starting SCN of 15417637288132

RMAN> 

5.查询数据

[oracle@hisre orcl]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Sat May 4 07:23:05 2024

Copyright ? 1982, 2013, Oracle. All rights reserved.

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

SQL> alter database open resetlogs;

Database altered.

SQL> select count(*) from bshrp56.gy_ylml;

count(*)

9050

SQL>