Hi there --

We have a disaster recovery solution for our database where a dump file is generated daily at 1:00 AM. We have tested importing the file, along with running another sql file that generates the appropriate database user accounts, into a database on a third-party server.

One question that came up was the following: Suppose the database crashes in the middle of the day, prior to that there were transactions that were entered into the database between the time the dump file was generated, and the crash occurred.

We can restore the dump file to either the main or backup server. How can the transactions that were made between the time of the dump file and the crash be restored as well?

Thanks.

In the scenario you describe, some sort of magic (time machine, Harry Potter, etc.), is needed. Otherwise, think about what you're asking.

Unless you TAKE the snapshot, you won't HAVE the snapshot. So either take snapshots at multiple times during the day, or have your database do hot replication to an offsite server. You can also try a database backup tool (like TDP/RMAN for Tivoli), that can back up individual records/tables, and have it do a hot backup periodically. Otherwise, the best you can do is to restore the backup you've got, and manually re-enter the information that posted in the meantime.

Have you read this, particularly the Point-in-Time section?