I am developing and practicing backup and recovery process.
Primary test database : TEST
second test database : STEST
Nov/10 09:20 AM
A developer dropped a table called A in TEST.
I checked log list
Oldest online log sequence 41
Next log sequence to archive 43
Current log sequence 43
I stared hot backup of TEST.
From the hot backup I got all datafiles, archived log files, control files and text control file.
I decided to do Point-In-Time recovery in a Primary node since nothing was inserted or modified between 09:20 and 10:00.
Shutdown TEST , start up mount and then issued
Recover database until time '2000-11-10 :09:00:00' using backup controlfile;
Oracle responded ;
Media Recovery Start
Media Recovery Not Required
Completed: ALTER DATABASE RECOVER database until time '2000-11-10 :09:00:00'
Then I opened database with resetlog mode.
-obviously, Nothing was recovered and I opened database (stupidly)using reset log.
- I can not perform recovery in this database since I opened database with reset log option.
- Before I performed recovery I didn't make sure the time the object was dropped, probably I should have went before 09:00 o'clock.
- I decide to clone the database( recovery) in the second node.
My question and confusion is that since I have a hotbackup of TEST database before I resetlog, with this backup, Can I go back to before 9:00 o'clock.
In other word, if I perform a point - in -time recovery in the second node, using hot backups but can I recover to the point in time before the object was dropped?
I performed this process and I applied archive log # 43 but I can not find a table A in the new database(STEST).
I guess that since I didn't have online redo logs(hot backup), I could not roll forward. Is this reason or what I 've done was totally wrong?
Please share your experience with me.
Yes, nothing was recovered, it was all up to date. The step that you are missing is that you have to restore your previous datafiles, controlfiles, redo logfiles and THEN recover the database to a point in time. There's actually a note (note# 69468.1) on [url]http://metalink.oracle.com[/url] that explains the whole process.
Thank you for the help, but I don't quite understand the meaning of 'all up to date'
Do you mean my first recovery try in TEST database ?
or my second recovery try in STEST database?
I red the Meta Link that you told me, but that is the exactly what I tried at the second time in STEST database.
Difference is that I didn't make cold backup, so I didn't restore redo log files.
I will try one more time Tomorrow with my hot backup.
I will let you know the result ..
Thank you again.
The recovery of TEST did not succeed, since you probably recovered until after the table was dropped. The recovery of STEST did not succeed since you recovered the db completely. To restore from HOT backup, you need all dbf, controlfile and arch files (not online redo log files). Only when you recover/startup from cold backup, you need the online redo log files along with controlfile, all dbf and arch files.
Find out exactly when the table was dropped. Restore from hot backup and recover until about a few seconds before the table was dropped.