How to Recover From a DROP / TRUNCATE / DELETE TABLE with RMAN (Doc ID 223543.1)

Last updated on SEPTEMBER 12, 2023

Applies to:

Oracle Database Exadata Express Cloud Service - Version N/A and later
Oracle Database Cloud Service - Version N/A and later
Oracle Database - Enterprise Edition - Version 8.1.5.0 to 12.1.0.2 [Release 8.1.5 to 12.1]
Oracle Database Cloud Schema Service - Version N/A and later
Gen 1 Exadata Cloud at Customer (Oracle Exadata Database Cloud Machine) - Version N/A and later
Information in this document applies to any platform.

Purpose

This document describes how to recover from a DROP/TRUNCATE/DELETE or any type of data mistake you need to recover using RMAN.

In Oracle10g and higher, you can use the Recyclebin to recover a DROP table assuming it has not been explicitly disabled. See:

<Note 265254.1> Flashback Table feature in Oracle Database 10g

If the Recyclebin has been disabled or Database is running Oracle9i, there are several options available:

1. Use RMAN duplicate to create a subset of the database as a clone to the point in time before the drop. The auxiliary database can be a subset of the tablespaces so only necessary tablespaces are restored and recovered. Once RMAN creates the clone and opens the database, the table can be exported from the auxiliary database and imported back into production. This is the RECOMMENDED method for recovering a table (or data) as it has very little affect on the rest of the objects within the database.

2. Restore and recover the primary database to a point in time before the drop. This is an extreme measure for one table as the entire database data goes back in time.

3. Restore and recover the tablespace to a point in time before the drop. This is a better option, but again, it takes the entire tablespace's data back in time. This is the standard tablespace point in time recovery (TSPITR) which will require an auxiliary database, but will bring all the data of the tablespace back in time in the target database. See <Note 109979.1> for details.

If for some reason you are unable to use RMAN duplicate, there is a forth option:

4. Restore and recover a subset of the database as a DUMMY database to export the table data and import it into the primary database. This option should ONLY be used if the RMAN duplicate command cannot. With 11.2's new feature of targetless duplicate, the RMAN duplicate command can be used in most cases.

Note, in 12c RMAN has an option for 'recover table'. See: RMAN RECOVER TABLE Feature New to Oracle Database 12c <Note 1521524.1>

If you want to recover a table in PDB, best option is to use table recovery feature of RMAN.

For options 1 -3, see the appropriate information for that option. This document will deal with the fourth option which should ONLY be used when the first three are not possible.