How to Recover From a DROP / TRUNCATE / DELETE TABLE with RMAN [ID 223543.1]

How to Recover From a DROP / TRUNCATE / DELETE TABLE with RMAN [ID 223543.1]
		Modified 02-FEB-2011     Type FAQ     Status PUBLISHED

In this Document
  Purpose
  Questions and Answers
     General overview of procedure to recover from a DROP or TRUNCATE table by using RMAN.
     Requirements
     1: Restore and mount the controlfile:
     2. Restore and recover a subset of the database:
     a. Connect to a mounted target:
     b. Use a 'SET UNTIL TIME':
     c. SET NEWNAME for all datafiles:
     d. Restore of the necessary tablespaces, RESTORE TABLESPACE:
     e. SWITCH DATAFILE ALL:
     f. ALTER DATABASE DATAFILE ... ONLINE:
     g. RECOVER DATABASE SKIP FOREVER TABLESPACE ......;
     h. ALTER DATABASE RENAME FILE all Online REDO log files:
     4: Export the table
     
     5: Import the export-dump
     6: Remove this AUX/DUMMY database
  References

---

 

 

Applies to:

Oracle Server - Enterprise Edition - Version: 8.1.5.0 to 10.2.0.1 - Release: 8.1.5 to 10.2
Information in this document applies to any platform.
***Checked for relevance on 02-Feb-2011***

Purpose

This document describes how to recover from a DROP or TRUNCATE table by using RMAN.

In Oracle10g, you can use the Recyclebin to recover a DROP table,  as long as it has not been manually 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 three options available:

1. 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 goes back in time.

2. 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 back in time. This is the standard tablespace point in time recovery (TSPITR) which will require an auxiliary database, but will bring the entire tablespace ba