This question already has an answer here:

I want to complete a task of backup and restore using oracle in the simplest way possible because I never did it before and I don't really know how to do it. What I want to do is backup the database, and inside the database I'll have a user with one table. I'll back up in this state, and I want to delete that table, and restore the database in a previous state where it had the table. All I tried is here, but still I can't solve it. Oracle isn't restoring correctly a backup

I'm asking again so maybe now somebody knows how to do it.

marked as duplicate by Philᵀᴹ oracle May 28 at 15:59

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

  • Why cross-post? See responses in your other thread, which you cited and linked to. – EdStevens May 28 at 15:14
  • Maybe I didn't solve the problem and I'm not getting more answers @EdStevens – Adrián Jaramillo May 28 at 15:47

When it comes to recovering data, restoring the database is actually the last weapon in the Oracle DBA's arsenal.

Look into the "Flashback" suite of technologies; there are about six of them, all using slightly different methods to achieve slightly different things.

For example, to recover a dropped table, you might use "Flashback Table", which simply gets that one table back from the Recycle bin (unless some clever Bod uses "drop table .. purge" to obliterate it completely).

To recover data that's been deleted, you can use "Flashback Query" to query the table as it was in the past (but only as far as back as Guaranteed Undo will support).

The quickest way to recover the database to a point in time is "Flashback Database", which uses completely separate Flashback Logs to [very] rapidly roll the whole database back to a point in time (this time limited by the amount of Flashback logs in the FRA).

To do a full-blown recovery of the database to a point in time, you need the database to be in archivelog mode and you need to be backing up the database, controlfiles and archived redo logs. Assuming you've got all of that:

  set until time "to_date('20190528120000','YYYYMMDDHH24MISS')";
  restore database;
  recover database;
  alter database open resetlogs;

BTW, the most pointless activity you can do as an Oracle DBA is to omit the "set time" clause in the above. That will destroy the database, overwrite it with its [latest] backup and then roll forward the archived redo logs, thereby putting the database back into exactly the same state as when you started the operation!

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