Since 1999     References  |  Search more  | Oracle Syntax  | Free Online Oracle Training

    Home      .Services     Login       Start Learning     Certification      .                 .Share your BELIEF(s)...


. Online Accounting        .Copyright & User Agreement   |
    .Vision      .Biography     .Acknowledgement

.Contact Us      .Comments/Suggestions       Email2aFriend    |


Oracle 12c New Features

Online Oracle Training


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 backup

   Your username: scott

   His/her tablename: mytable


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.


SQL> select timestamp_to_scn(to_date('03-04-2014 08:31:00','MM-DD-YYYY HH24:MI:SS')) SCN from v$database;



This output shows the snapshot from a specific date and time ('03-04-2014 08:31:00').


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 /

RMAN> RECOVER 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 "


RMAN> exit


Now, call the user to check the table was recovered successfully.

SQL> connect / as sysdba

SQL> SELECT count(*) FROM scott.mytable;


Good Luck!



Web web site