How to Recover From a DROPTRUNCATEDELETE 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.2Information 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 back in time in the target database. See Note 109979.1 or Note 180436.1 for details.3. 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 is the best option as only the dropped table goes back in time to before the drop. For the first, see information on performing incomplete recovery on a database. The second and third options are virtually the same, it depends if the entire tablespace data should be taken back in time, or just recovering the one table. The tablespace point in time recovery (TSPITR) may be useful if there are dependencies between the dropped/truncated table and other tables in the database. Both procedures for the second and third options are very much the same. The differences are that the export/import of TSPITR is fully automated by RMAN. This document will deal with the third option.

Questions and Answers

General overview of procedure to recover from a DROP or TRUNCATE table by using RMAN.

To recover from a dropped or truncated table, a dummy database (copy of primary) will be restored and recovered to point in time so the table can be exported.  Once the table export is complete, the table can be imported into the primary  database. This dummy database can be a subset of the primary database. However, the 'dummy' database must include the SYSTEM, UNDO (or ROLLBACK), and the  tablespace(s) where the dropped/truncated table resides.The simpliest method to create this 'dummy' database is to use the RMAN duplicate command. See:Note 259694.1 Oracle10G RMAN Database DuplicationNote 228257.1 RMAN Duplicate Database in Oracle9iNote 73912.1 RMAN Creating a Duplicate Database -- Oracle8iWith Oracle9i RMAN the 'skip tablespace' option allows for a duplication of  a subset of the database. In Oracle8i, you cannot 'skip' tablespaces when using duplicate, so you must duplicate the entire database. If this is not a desired option, or you must restore the original database and thus cannot use the RMAN DUPLICATE.

NOTE: The remainder of this information is for users who cannot use the RMAN DUPLICATE command.

Requirements

RMAN backup of the primary database should be available to the host where it is being restored. Auxiliary instance created and started in NOMOUNT (See Note 180436.1 step I. Create the auxiliary initSID.ora.) This can be copied from primary database taking into consideration the  following:

!!!!! IMPORTANT !!!!!!!!

If the same host as the primary is being used than be VERY careful as you do not want to restore on top of existing files being used by the primary (production database).  Doing so can corrupt and crash the production database!!!!!!

Be sure that all paths for this AUX instance are different than primary. Be sure that the CONTROL_FILES parameter has different location but more importantly has a DIFFERENT NAME. add LOCK_NAME_SPACE/DB_UNIQUE_NAME to any value other than the primary database name.change/add SERVICE_NAME=AUX1.use the SAME DB_NAME as for the production databaseBE SURE to include the 'alter database rename file' command at the end of the script. This changes the location and/or name of the online redo log files.Set ORACLE_HOME and ORACLE_SID set to the auxiliary instance Example :% set ORACLE_SID=AUX1% set ORACLE_HOME=<....>

!!!!! IMPORTANT !!!!!!!!

NOTE: ORACLE_HOME and ORACLE_SID set to the auxiliary instance NOTE: The Auxiliary instance IS the target to rman at this point.

1: Restore and mount the controlfile:

Restore a controlfile using the appropriate 'set until':

RMAN> run {     restore controlfile from autobackup      until time "TO_DATE('05/NOV/2007 15:40:00','DD/MON/YYYY HH24:MI:SS')";    }

RMAN> sql "alter database mount clone database";

NOTE:  MOUNT CLONE DATABASE forces all datafiles to be put OFFLINE.  Just for safety reasons.

See Note 372996.1 and/or Note 403883.1 for more details on restoring a controlfile.

2. Restore and recover a subset of the database:

a. Connect to a mounted target:

If a catalog is used to restore the controlfile, at this point it is best to exit rman and connect ONLY to TARGET. Thus not effecting the original target information in the catalog. Once the backup controlfile is restored and mounted, it's information can be used. If the controlfile information is not old enough, be careful when opening the database (see step 3).

b. Use a 'SET UNTIL TIME':

User specified time, just before the DROP/TRUNACTE table

c. SET NEWNAME for all datafiles:

This specifies a new path for the datafile to be restored. Keep in mind that this is done on the auxiliary instance and should NOT interfere/overwrite the prodution database.

NOTE: As the DUPLICATE command is not being used the db_file_name_convert and log_file_name_convert parameters are ignored. Thus datafile name and location must be made with the 'set newname for datafile' command.

d. Restore of the necessary tablespaces, RESTORE TABLESPACE:

Restore the tablespaces which need to be recoverd. This always includes the SYSTEM, SYSAUX, UNDO/Rollback tablespace, and the tablespace(s )where the dropped/truncated table resides. The SYSTEM tablespace is always included as it containts most / all of the objects owned by SYS and SYSTEM. Some other tablespaces might be included as well when they contain objects owned by SYS and SYSTEM. SQL> select distinct tablespace_name from dba_segments where owner in ('SYS', 'SYSTEM');

e. SWITCH DATAFILE ALL:

Make the changes in the pathname (set by SET NEWNAME) active in the controlfile.

f. ALTER DATABASE DATAFILE ... ONLINE:

Online the datafiles which are restored and have to be recovered.

g. RECOVER DATABASE SKIP FOREVER TABLESPACE ......;

You need to specify the complete list of tablespaces which will not be  recovered. Otherwise, the recovery will fail looking for files which are NOT restored.  The SKIP FOREVER clause causes RMAN to take the datafiles offline using  the DROP option. Only use skip forever when the specified tablespaces will  be dropped after opening the database. I.e.,all tablespaces except the  one which contains your data.

h. ALTER DATABASE RENAME FILE all Online REDO log files:

This is required to change the location of the online log files. When the  'resetlogs' is issued, Oracle will create online logs based on specification  in the controlfile. This command changes the location and/or name. If  this is being performed on the SAME server, not issuing a rename will  cause Oracle to reset the production online log files. This will corrupt and crash the production database!!!!!! Warning: If redolog files are OMF, when we execute rename file for the redolog  files on clone instance, then it will try to delete the online redologs of source database  so you should not try this method on same machine if you are using OMF.

FOR EXAMPLE:------------

RMAN> connect target /run{allocate channel t1 type sbt_tapeparms='SBT_LIBRARY=/home/usupport/liblsm.so';set until time "to_date( '08-10-2007 06:00', 'DD-MM-RRRR HH24:MI')";set newname for datafile 1 to '/fs01/oradata/tspitr/system01.dbf';set newname for datafile 2 to '/fs01/oradata/tspitr/undotbs01.dbf';set newname for datafile 4 to '/fs01/oradata/tspitr/tools01.dbf';restore tablespace system, undotbs1, tools;switch datafile all;sql "alter database datafile 1,2,4 online";recover database skip forever tablespace TEMP,INDX,USERS,OLTS_ATTRSTORE,OLTS_CT_DN,OLTS_CT_CN, OLTS_CT_OBJCL,OLTS_CT_STORE,OLTS_DEFAULT,OLTS_TEMP,OLTS_IND_ATTRSTORE,OLTS_IND_CT_DN,OLTS_IND_CT_CN,OLTS_IND_CT_OBJCL,OLTS_IND_CT_STORE,P1TS_ATTRSTORE,P1TS_IND_STORE;sql "alter database rename file ''/fs01/oradata/primary/REDO01.LOG'' to ''/fs01/oradata/tspitr/REDO01.LOG''";sql "alter database rename file ''/fs01/oradata/primary/REDO02.LOG'' to ''/fs01/oradata/tspitr/REDO02.LOG''";sql "alter database rename file ''/fs01/oradata/primary/REDO03.LOG'' to ''/fs01/oradata/tspitr/REDO03.LOG''";/*NOTE: Syntax within rman is two single quotes around each name, this may be operating system specific. */release channel t1;}

 

NOTE: In this example, a Tape channel allocated. The need for this (or use of disk channel) depends on the location of the backups.

 

3: Open auxiliary database with RESETLOGS RMAN> alter database open resetlogs;

NOTE: As suggested a catalog connection should not be made during the  database restore. However, if this was necessary to get older backup information at this point, open the database in Sqlplus, rather than RMAN. Otherwise, the next target connection will receive an error like: RMAN-20011 "target database incarnation is not current in recovery catalog"

4: Export the table

The database is recovered and open, so it can be used by export.Example:

$ exp userid=system/<password> file=table.dmp   tables=(<owner>.<tablename>, ...) rows=Y

5: Import the export-dump

Import the data of the dropped table back into the primary/production database.Example:

$ imp userid=system/<password> file=table.dmp ignore=Y

6: Remove this AUX/DUMMY database

Shutdown and remove all files associated with this database. It has satisfied your purpose. I.e., to give you an export of this table.