How to recover a database to a point in time

[dbstudent]

I have been able to restore and recover a database to a point in time using:

RESTORE DATABASE UNTIL TIME "TO_DATE('16/03/2006 18:00:00','DD/MM/YYYY HH24:MI:SS')";

RECOVER DATABASE UNTIL TIME "TO_DATE('16/03/2006 18:00:00','DD/MM/YYYY HH24:MI:SS')";

I don't seem to be able to use the RECOVER DATABASE UNTIL CANCEL. I created a table and dropped it. When i execute the RECOVER command. I get a quick media recovery complete. But the table does not exist. I figured if i know the log sequence number before i dropped the table i should be able to recover until then. See example below of how it suppose to work.

SQL:>recover database until cancel;

Before Oracle applies each archived log sequence, it will return a message similar to the following suggesting an archived log to apply and allowing the operator to either continue or cancel the recovery:

ORA-00279: Change 36579 generated at <time/date> needed for thread 1
ORA-00289: Suggestion : \Oracle_Home\Oradata\<SID>\%SID%T00036579.ARC
ORA-00280: {<RET>=Suggested | filename | AUTO | FROM

when i execute recover command i never get the option above. Looking forward for your suggestions.

 

[SantaMufasa]

Student,

Before you attempt the recovery, are you first replacing the current tablespace data files with a backup version of the files that pre-dates the table drop? If not, that is why your "RECOVER UNTIL CANCEL" is not working. (If the DROP TABLE occurred recently [i.e., the DROP is in a transaction that is stored in the on-line redo log files], then I don't believe that RECOVER UNTIL CANCEL will work since Oracle has all of the data that it needs for recovery in the on-line redo log files and it doesn't even give you a chance to CANCEL.

Let us know,

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.