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.

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 (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 database
• BE 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':

1. Open Terminal and set the environment to 11g. I have. upgr script to set the necessary environment for the database

RMAN>
run {
     restore controlfile from autobackup
     until time "TO_DATE('05/NOV/2013 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 the MOUNT CLONE is NOT available in 'Standard Edition' and it will report an ORA-439 "Function not enabled: Point-in-time tablespace recovery"
In 'Standard Edition' the equivalent is             

SQL> startup mount
SQL>alter database datafile <file#> offline;   <--- for each datafile

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.

A good alternative is to generate a CREATE CONTROLFILE-script :
   SQL> alter database backup controlfile to trace as '/tmp/control.sql' resetlogs

and edit the 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-2013 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.

And database Upgrade is completed successfully.