[12C] new feature: using RMAN to recover tables or table partitions


When a table is misoperated, such as deleting data, drop table, or truncate table, we can restore it by using flashback table, flashback drop, or tspitr. However, in the following scenarios, the above recovery technology will be helpless:

  • The table logic is damaged or the purge option is used to delete;
  • When Undo is not available, the flashback technology cannot be used to recover the table;
  • After misoperation of the table, the DDL operation is performed;
  • Using tspitr, all objects in the table space will be recovered, which is not conducive to the scenario of only recovering some tables.

Starting from Oracle 12C, Oracle introduces the recovery of tables and table partitions from RMAN backup, which greatly simplifies the recovery of tables and table partitions. This article will demonstrate this new feature.

1 backup database

[[email protected] ~]$ rman target /

Recovery Manager: Release - 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;

------------------------------------------------The backup process is omitted----------------------------------------------

2 create test data

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 using RMAN backup for recovery

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;

For the recover table syntax, please refer to:https://docs.oracle.com/database/121/RCMRF/rcmsynta2001.htm#GUID-CA98040F-9865-4F4F-BAF2-91C518612E95

4 view dump file

[[email protected] ~]$ ll dumpfiles/

total 140

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

5 import with impdp

[[email protected] ~]$ cd /u01/app/oracle/admin/ORCL/dpdump/

[[email protected] dpdump]$ cp /home/oracle/dumpfiles/scott.alen.dat ./

[[email protected] 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

[[email protected] dpdump]$ impdp system/system directory=DATA_PUMP_DIR dumpfile=scott.alen.dat tables=scott.alen

Import: Release - 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 - 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



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

6 validation results

SQL> select * from alen;


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

1 Alen

Recommended Today

Use of Android WebView (super detailed usage)

1.1 overview of WebView Android WebView is a special view on the Android platform. It can be used to display web pages. This WebView class can be used to display only one online web page in the app. Of course, it can also be used to develop browsers. The internal implementation of WebView uses WebKit […]