【12c】新特性：使用RMAN恢复表或表分区

当对一张表进行误操作，比如删除了数据、drop表，或者truncate表，我们可以通过使用闪回表、闪回drop，或者TSPITR可以进行恢复，但在以下场景下，上面的恢复技术将会束手无策：

• 表逻辑损坏或使用了purge选项的删除；
• UNDO不可用时，便不可使用闪回技术对表进行恢复；
• 对表误操作后，执行了DDL操作；
• 使用TSPITR，将会恢复表空间里的所有对象，不利于只恢复某些表的场景。

从Oracle 12c版本开始，Oracle引入了可以从RMAN备份中进行表和表分区的恢复，这样可大大简化了对表和表分区的恢复，本篇将对这一新特性进行演示。

1 备份数据库

[oracle@odd ~]$ rman target /



Recovery Manager: Release 12.1.0.2.0 - Production on Sun May 3 16:15:03 2020



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



connected to target database: ORCL (DBID=1567069190)



RMAN> backup database;

------------------------------------------------备份过程省略----------------------------------------------

2 创建测试数据

SQL> create table alen(id number,name varchar2(100));



Table created.



SQL> insert into alen values(1,'Alen');



1 row created.



SQL> commit;



Commit complete.

SQL> drop table alen purge;



Table dropped.



SQL> show recyclebin;

3 利用RMAN备份进行恢复

RMAN> recover table scott.alen

2> until time '2020-05-03 16:38:03'

3> auxiliary destination '/home/oracle/recover'

4> datapump destination '/home/oracle/dumpfiles'

5> dump file 'scott.alen.dat'

6> notableimport;

RECOVER TABLE语法，可参考：https://docs.oracle.com/database/121/RCMRF/rcmsynta2001.htm#GUID-CA98040F-9865-4F4F-BAF2-91C518612E95

4 查看DUMP文件

[oracle@odd ~]$ ll dumpfiles/

total 140

-rw-r----- 1 oracle oinstall 143360 May 3 16:44 scott.alen.dat

5 使用IMPDP导入

[oracle@odd ~]$ cd /u01/app/oracle/admin/ORCL/dpdump/

[oracle@odd dpdump]$ cp /home/oracle/dumpfiles/scott.alen.dat ./

[oracle@odd dpdump]$ ll

total 144

-rw-r-----. 1 oracle oinstall 116 May 1 19:19 dp.log

-rw-r----- 1 oracle oinstall 143360 May 3 16:48 scott.alen.dat

[oracle@odd dpdump]$ impdp system/system directory=DATA_PUMP_DIR dumpfile=scott.alen.dat tables=scott.alen



Import: Release 12.1.0.2.0 - Production on Sun May 3 16:53:14 2020



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



Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

Master table "SYSTEM"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded

Starting "SYSTEM"."SYS_IMPORT_TABLE_01": system/******** directory=DATA_PUMP_DIR dumpfile=scott.alen.dat tables=scott.alen

Processing object type TABLE_EXPORT/TABLE/TABLE

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

. . imported "SCOTT"."ALEN" 5.476 KB 1 rows

Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER

Job "SYSTEM"."SYS_IMPORT_TABLE_01" successfully completed at Sun May 3 16:53:20 2020 elapsed 0 00:00:03

6 验证结果

SQL> select * from alen;



ID NAME

---------- ----------------------------------------------------------------------------------------------------

1 Alen