0

I have a database with multiple schemas and data in the tables. I create an rman backup and backup archivelogs. I drop 90% of the tables. I do rman restore and recovery with controfile from autobackup. Checking database tables, nothing has been recovered?

I noticed that if I remove actual oracle database files(.ctl,.dbf/etc) the rman restores those, but I thought the tables would automatically be restored as well.

Please let me know if more info is needed, new to Oracle and Rman.

migrated from stackoverflow.com Jul 24 '15 at 7:47

This question came from our site for professional and enthusiast programmers.

  • 1
    Are you restoring to a point in time? Or are you applying all the archived logs? If you restored to a point in time before you dropped the tables, the tables would be there (and anything you did after that would not have taken place). If you roll forward through the last redo log, however, you'd re-apply the DROP TABLE commands. RMAN has no idea which statements you want to apply and which you want to skip unless you tell it somehow. – Justin Cave Jul 23 '15 at 18:33
  • Thank you very much for clearing things up, I hadn't even thought of that. I believe it is as you said where I drop the tables and it is applying those changes through the REDO log. If you take your comment and make it an answer I can mark it as an answer. – gallly Jul 23 '15 at 18:45
  • Trying to answer this in less than 600 characters ... RMAN is not like a Recycle Bin on Windows : the "remove" actions, don't have corresponding - pending - undo actions waiting. Instead, the database is only concerned with "the status" of data, at any point in time. And when you tell RMAN to recover, he will recover - by default - to the last moment in time, technically possible. Were the tables dropped already then ? Too bad, in that case you must do a restore to a specific point in time (and accept all other data to be in synch). So, restore to the point in time, just prior to the drop. – tvCa Aug 25 '15 at 21:42
  • Note that you actually have a Recycle Bin in an Oracle database. Not sure if that would suit the need of your initial question, but it may be that case. – tvCa Aug 25 '15 at 21:44
4

In the simplest case, when you restore a database, you tell RMAN to recover it to the most recent point in time by applying all the redo (archived and online) that is available. That will re-apply any statements that were executed during normal database operations including things like dropping tables and deleting data. If you want to restore a database to a point in time before something bad happened, you'd need to do a point-in-time recovery with something like

RUN
{ 
    SET UNTIL SCN 1000;    
  # Alternatives:
  # SET UNTIL TIME 'Nov 15 2004 09:00:00';
  # SET UNTIL SEQUENCE 9923;  
  RESTORE DATABASE;
  RECOVER DATABASE;
}

Normally, you'd specify an UNTIL TIME or UNTIL SCN that was just before the first erroneous command.

In general, though, you would try to avoid using RMAN to recover from human errors. Generally, doing a full database restore is a last resort and generally there are other transactions going on that you don't want to lose. If a DBA inadvertently drops a table, for example, you don't want to restore the database and lose all the transactions that other people had made after the table was dropped. You would generally do a FLASHBACK DROP

FLASHBACK TABLE table_I_should_not_have_dropped
       TO BEFORE DROP

to restore the table from the recycle bin (which is almost instantaneous). If you deleted

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.