How to recover a missing table using RMAN backup
You have a user that
dropped its table at 8:30am on March 3, 2014. He comes to you and ask
you to recover the lost table using RMAN backup.
Oracle12c or higher
Your database is in
the archivelog mode
You have a good RMAN
Notice that on the
Oracle12c, you can perform any SQL statements at RMAN without using SQL.
Once you have the time,
check the SCN (System Change Number) associated with the date and time
that your user provided to you.
HH24:MI:SS')) SCN from v$database;
This output shows the snapshot from a specific date and time
The date and time has to
be before he dropped the table.
Make an auxiliary
destination so RMAN can perform its data manipulation to recover table.
Notice that RMAN will export the data from backup and then import it to
your database. Therefore, it needs space to do so.
# mkdir /recovery_area/recover_table
Login to RMAN and
recover table using created auxiliary destination and SCN.
# . ./.profile
# rman target /
TABLE scott.mytable UNTIL SCN 2733999 DESTINATION "/recovery_area/recover_table";
Starting recover at "
Performing export of tables "
Performing import of tables "
Auxiliary instance file " Deleted
Finished recover at "
Now, call the user to
check the table was recovered successfully.
SQL> connect /
count(*) FROM scott.mytable;