Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question.

Asked: August 29, 2016 - 3:31 pm UTC

Last updated: August 30, 2016 - 3:00 am UTC

Version: 11g

Viewed 1000+ times

You Asked

I have rman backup of a database.
But by my mistake I purge a table which is calendars-master .

So how I recover table from rman backup?

and Connor said...

In 12c, there is a native "recover table" command. But in 11g, you'll need to do the hard work yourself. There is a good MOS note on the process: 223543.1

I've copied it into here, but its formatted better on support.oracle.com

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

In this Document
Purpose
Questions and Answers
  General overview of procedure to recover from a DROP or TRUNCATE table by using RMAN.
  Requirements
  2. Restore and recover a subset of the database:
  a. Connect to a mounted target:
  b. Specify 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:
  3: Open auxiliary database with RESETLOGS
  4: Export the table
  5: Import the export dump
  6: Remove this AUX/DUMMY database
References
APPLIES TO:

Oracle Database - Enterprise Edition - Version 8.1.5.0 to 12.1.0.2 [Release 8.1.5 to 12.1]
Information in this document applies to any platform.
***Checked for relevance on 24-Aug-2015***


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

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.  

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, but must include SYSTEM, UNDO (or ROLLBACK), SYSAUX, and the tablespace(s) where the dropped/truncated table resides.

The simplest and recommended method to create this 'dummy' database is to use the RMAN duplicate command. See:

Note 1375864.1 Perform Backup Based RMAN DUPLICATE Without Connecting To Target Database For Both Disk & Tape Backups

Note 452868.1 RMAN 'Duplicate From Active Database' Feature in 11G
Note 259694.1 Oracle10G RMAN Database Duplication
Note 228257.1 RMAN Duplicate Database in Oracle9i
Note 73912.1 RMAN Creating a Duplicate Database -- Oracle8i

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.  Again, it is recommended to use the RMAN duplicate to clone the database. 
Requirements

RMAN backup of the primary database should be available to the host where it is being restored.
Auxiliary instance (the clone) is created.  This means an initSID.ora is created and is used to start instance in NOMOUNT.  This can be copied from primary database taking into consideration the following:
!!!!! IMPORTANT !!!!!!!!

If the same host as the primary is being used then be VERY careful as you do not want this procedure to overwrite existing files 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 database
BE SURE to include the 'alter database rename file' command at the end of the script to change 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')";
    }
If a recovery catalog is NOT being used, then RMAN will always restore the LATEST controlfile autobackup from the date of the specified until time.  This is explained in the following note: 

RMAN not Choosing the Correct Controlfile Autobackup to Restore and Failing with RMAN-06095 (Doc ID 1326114.1). 

RMAN will need an autobackup that was generated just BEFORE the specified time.  This may not necessarily be the last autobackup generated on that day.  So if no catalog is being used, use the command: 

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

 Now mount:

RMAN> alter database mount clone database;
 

NOTE:  MOUNT CLONE DATABASE forces all datafiles to be put OFFLINE.  This is a precaution.  

The MOUNT CLONE is NOT available in 'Standard Edition' and will report an ORA-439 "Function not enabled: Point-in-time tablespace recovery".  The 'Standard Edition' equivalent is :
            SQL> startup mount
                     alter database datafile <file#> offline;   <--- for each datafile

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 you MUST exit rman and connect ONLY to TARGET.   Otherwise, the original target information in the catalog may be affected. 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. Specify 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 production 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 ' command.
d. Restore of the necessary tablespaces, RESTORE TABLESPACE:

Restore the tablespaces which need to be recovered. 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 contains 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');
NOTE:  In a RAC environment, ALL undo tablespaces must be included.  I.e., no undo tablespace used by any thread can be skipped.  
e. SWITCH DATAFILE ALL:

Updates controlfile with path/name (set by SET NEWNAME) of the datafiles restored. 

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.,do NOT include the tablespace containing the data you want to recover. 

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.

A good alternative is to recreate the controlfile and specify names for the redo log files.  To generate a CREATE CONTROLFILE script, execute :

   SQL> alter database backup controlfile to trace as '/tmp/control.sql' resetlogs; 

and edit the script with new log file names (non OMF). This will avoid deletion of online redolog files of the source database.

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

RMAN> connect target /

run
{
allocate channel t1 type sbt_tape
parms='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.


Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A reader, August 30, 2016 - 3:43 am UTC

Thank you.

A reader, November 02, 2017 - 1:00 pm UTC


Often, backups are done on the standby

A reader, June 04, 2018 - 9:56 am UTC

Often, companies backup on their standbys. This document needs to be changed a bit to take into account a standby backup AND to ensure that the restored controlfile is actually from the primary.

More to Explore

Backup/Recovery

Check out the complete guide to all of the Backup & Recovery techniques in the Oracle Database.