point-in-time recovery with snapshots and transaction logs

[Mladen Despic]

If one recovers a database from a snapshot, which was taken, say, at 9:00AM (via something like Online JFS, or Business Copy XP), is it then possible (or can it be made possible) to apply transaction logs corresponding to the time interval 9:00 AM - 9:30 AM, so that the final result is the point-in-time recovery for 9:30 AM?

I'm primarily interested in Sybase, ASE 12.5, but I'm also curious to hear about your experiences with Oracle. Some people say that, with Sybase, you can only apply transaction logs if you recover from the Sybase dump first, but others say that Oracle allows combining the snapshots with the transaction logs. Have you tried anything along these lines, and if so, does it work?

Mladen Despic

[James A. Donovan · Accepted solution]

You can certainly do it that with Oracle. It's one method of an incomplete recovery, called time-based recovery.

Here's a nice link w/explanations:

http://www.infomaxweb.com/Oracle8i/IncompleteRecovery.htm

[Brian Crabtree]

Mladen,

Jim is correct, and the link would be good to read through. To point you to it a bit easier, you are looking for the 'time based' recovery.

The command is:
recover database until time 'YYYY-MM-DD:HH24:MI:SS';

The date notation I used is the default for Oracle, however it can be changed (nls_date_format and nls_time_format), so you need to be careful. For 2/19/2004 at 9:30am, it would be:

recover database until time '2004-02-19:09:30:00';

Thanks,

Brian