Friday, February 24, 2023

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 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> Dump file set for SYS.TSPITR_EXP_kDmz_cCbt is:
   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> 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
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\SALMAN12C\DATAFILE\O1_MF_SYSAUX_KZFS5M08_.DBF deleted
auxiliary instance file
auxiliary instance file
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

No comments:

Post a Comment

Popular Posts - All Times