What exactly does an RMAN backup do

backupdisaster recoveryoraclerman

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.

Best Answer

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

Related Question