How to Recover from a Truncate Command [ID 117055.1]

---

 修改时间 21-OCT-2010     类型 BULLETIN     状态 PUBLISHED 

PURPOSE
-------

By accident you issue a truncate command on the wrong table.
Since there is no undo command for this truncate, you will need to
recover the database to get the data back in the table.
This document outlines the steps to follow to recover
from the truncate command.

 
SCOPE & APPLICATION
-------------------

This document is intended to give a step by step guide for experienced
users.
It assumes that the some basic knowledge about backup and restore of an
Oracle database is available.


Step by Step solution
---------------------

*NOTE:  Always backup the database before starting to recover or restore old
        backups.


   1. Is there an export dump of the table which I can use to restore the data?
       a. yes -> import the file
       b. no  -> go to step 2.

   2. Is the database is archivelog mode ?
       a. yes -> go to step 3.
       b. no  -> go to step 4 
  
   3. The database is in archivelog mode.  This makes it possible to recover the
      database until a moment just before the truncate command.
       a. restore a backup of the database to another location.
       b. recover this database to a moment in time BEFORE the truncate command
          RECOVER DATABASE UNTIL TIME .....
       c. export the table data from this recovered database.
       d. import the data back into the original database
       e. remove the restored database.

   4. Since the database is not in archivelog mode, there is no way to recover
      the database to a time just before the truncate command.
      Contact Oracle Support to discuss the possible options left.


RELATED DOCUMENTS
-----------------

Backup and recovery guide