Recovering a Dropped Table using RMAN Backup

There are different ways to recover back a dropped table i.e. recovering from recycle bin if it is set to on, using flashback, importing from an export backup of the table, or using RMAN backup. In this article I will explain how to recover a table using RMAN backup. Database must be running in archivelog mode with RMAN backup available. Backup should have been from the time when table existed. Using this method, we can also recover tables to a previous state (keeping existing version of table) by providing SCN or timestamp.

For this example, I have 2 tables MYTB and MYTB1 in pluggable database PDB1. I want to recover both tables to a date in the past to see the data they had at that time. Same method is usable if tables have been dropped and I want to recover the tables. In that case, I would need to provide SCN/Timestamp just before when tables were dropped. I performed this on Windows based system. You need to update path and this will work on Linux as well.

RMAN> recover table SALMAN.MYTB,  SALMAN.MYTB1 of pluggable database pdb1 until time "to_date('22-FEB-23 14:33:00','DD-MON-YY HH24:MI:SS')" auxiliary destination 'd:\app\oracle' remap table SALMAN.MYTB:MTB,SALMAN.MYTB1:MTB1 remap tablespace users:users1;

AUXILIARY DESTINATION clause is used to specify destination where auxiliary instance files will be created during recovery process. A datapump export and import process is also performed during this recovery process and datapump export file is also created on this path. Alternatively we can use DATAPUMP DESTINATION clause to specify a different location for datapump export file.

REMAP TABLE clause is used to rename the recovered tables. You can omit this clause to have tables recovered with the same name as they had.

REMAP TABLESPACE clause is used if tables are to be recovered into a different tablespace than their original tablespace where they existed before drop.

During the recovery, an auxiliary instance will be creatd (that does not take many resources, normally around 2GB SGA). Then point in time recovery of the auxiliary instance is done, after which export of the tables and then import into the database is done. Lastly, auxiliary files are removed, and space is cleared.

Following is last part of the output of this command where export and import of the table was performed.

 

… … …    EXPDP> . . exported "SALMAN"."MYTB"                             20.91 MB  183646 rows    EXPDP> . . exported "SALMAN"."MYTB1"                            10.46 MB   91823 rows    EXPDP> Master table "SYS"."TSPITR_EXP_kDmz_cCbt" successfully loaded/unloaded    EXPDP> ******************************************************************************    EXPDP> Dump file set for SYS.TSPITR_EXP_kDmz_cCbt is:    EXPDP>   D:\APP\ORACLE\TSPITR_KDMZ_80712.DMP    EXPDP> Job "SYS"."TSPITR_EXP_kDmz_cCbt" completed with 2 error(s) at Thu Feb 23 12:04:19 023 elapsed 0 00:00:13 Export completed     contents of Memory Script: { # shutdown clone before import shutdown clone abort } executing Memory Script   Oracle instance shut down   Performing import of tables...    IMPDP> Master table "SYS"."TSPITR_IMP_kDmz_gmCB" successfully loaded/unloaded    IMPDP> Starting "SYS"."TSPITR_IMP_kDmz_gmCB":    IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE    IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE_DATA    IMPDP> . . imported "SALMAN"."MTB"                              20.91 MB  183646 rows    IMPDP> . . imported "SALMAN"."MTB1"                             10.46 MB   91823 rows    IMPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS    IMPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER    IMPDP> Job "SYS"."TSPITR_IMP_kDmz_gmCB" successfully completed at Thu Feb 23 12:04:37 023 elapsed 0 00:00:02 Import completed     Removing automatic instance Automatic instance removed auxiliary instance file :\APP\ORACLE\SALMAN12C\DATAFILE\O1_MF_TEMP_KZFS7NK3_.TMP deleted auxiliary instance file D:\APP\ORACLE\SALMAN12C\DATAFILE\O1_MF_TEMP_KZFS6V91_.TMP deleted auxiliary instance file D:\APP\ORACLE\KDMZ_PITR_PDB1_SALMAN12\ONLINELOG\O1_MF_6_KZFSDNHK_.LOG deleted auxiliary instance file D:\APP\ORACLE\KDMZ_PITR_PDB1_SALMAN12\ONLINELOG\O1_MF_5_KZFSDKL4_.LOG deleted auxiliary instance file D:\APP\ORACLE\KDMZ_PITR_PDB1_SALMAN12\ONLINELOG\O1_MF_4_KZFSDD1M_.LOG deleted auxiliary instance file D:\APP\ORACLE\KDMZ_PITR_PDB1_SALMAN12\ONLINELOG\O1_MF_3_KZFSDCXV_.LOG deleted auxiliary instance file D:\APP\ORACLE\KDMZ_PITR_PDB1_SALMAN12\ONLINELOG\O1_MF_2_KZFSDCTK_.LOG deleted auxiliary instance file D:\APP\ORACLE\KDMZ_PITR_PDB1_SALMAN12\ONLINELOG\O1_MF_1_KZFSDCPY_.LOG deleted auxiliary instance file D:\APP\ORACLE\KDMZ_PITR_PDB1_SALMAN12\DATAFILE\O1_MF_USERS_KZFSD4ST_.DBF deleted auxiliary instance file D:\APP\ORACLE\SALMAN12C\DATAFILE\O1_MF_SYSAUX_KZFS5M08_.DBF deleted auxiliary instance file D:\APP\ORACLE\SALMAN12C\DATAFILE\O1_MF_SYSTEM_KZFS5M0F_.DBF deleted auxiliary instance file D:\APP\ORACLE\SALMAN12C\DATAFILE\O1_MF_SYSAUX_KZFS3KT8_.DBF deleted auxiliary instance file D:\APP\ORACLE\SALMAN12C\DATAFILE\O1_MF_UNDOTBS1_KZFS3KTH_.DBF deleted auxiliary instance file D:\APP\ORACLE\SALMAN12C\DATAFILE\O1_MF_SYSTEM_KZFS3KTD_.DBF deleted auxiliary instance file D:\APP\ORACLE\SALMAN12C\CONTROLFILE\O1_MF_KZFS3C8Z_.CTL deleted auxiliary instance file tspitr_kDmz_80712.dmp deleted Finished recover at 23-FEB-23