"recover database until time" question

[ajhull]

I performed a test:

1) Created a user named "STEVE" at 10am
2) Shutdown the database
3) STARTUP MOUNT
4) Performed an incomplete time-based recovery by issuing command "recover database until time '2004-10-14:09:00:00'"
5) Issued cammand "ALTER DATABASE OPEN RESET LOGS;"

I then queried the database and found that the record I inserted after the time I specified in the time-based recovery. Why is that?

Adrian
Certified DBA/Developer

 

[SantaMufasa]

Adrian,

In the steps you list, above, you do not mention that you restored a complete set of backup data files (excluding your control files) that dates back to prior to October 14. If you do not restore a set of data files that pre-date your "RECOVER DATABASE UNTIL...", then your recover has no effect, thus explaining why you see data post-dating your "...UNTIL..." time.

Let us know,

Mufasa
(aka Dave of Sandy, Utah, USA)
@ 22:47 (14Nov04) UTC (aka "GMT" and "Zulu"),
@ 15:47 (14Nov04) Mountain Time

 

[ajhull]

You were correct Mufasa.

Here's the scoop...

I need to be able to do snapshots of the database and be able to restore the database to the snapshot timestamp. I have not had to provide this in the past but it is now being requested by some instructors that wish to be able to rollback the database to choosen snapshots.

What is the best way of going about this?

I have thought about exports and backups but these seem time-consuming. Is there a better way?

Thanks

Adrian

Adrian
Certified DBA/Developer

 

[SantaMufasa]

AJ,

If you have individuals who want to "go back" to a point in time for their schema, then, by far, your best solution is to manage that "go back" via schema exports. You do not want to manage schema "go backs" via point-in-time recoveries since that pushes the entire database back to that point in time.

We do what your instructors need on a regular basis, and we do so with schema-level restores of exported dump files.

Did this answer your question, AJ?

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

@ 17:44 (22Nov04) UTC (aka "GMT" and "Zulu"),
@ 10:44 (22Nov04) Mountain Time

 

[ajhull]

That answered my question Mufasa.

Thanks very much...I really appreciate it when people like you contribute your knowledge.

All the best.

Adrian

Adrian
Certified DBA/Developer