Re: point in time recovery and moving datafiles online

On Thu, 2002-03-07 at 18:00, Tatsuo Ishii wrote:
> > > (1) backup process starts (and records LSN)
> > > (2) DROP TABLE t1 starts
> > > (3) DROP TABLE t1 commits
> > > (4) backup process ends
> > 
> > > I think the database status should be able to go back to (1) using
> > > the archive log recovery. No?
> > 
> > No.  It is not reasonable to expect the backup to allow you to recreate
> > any state occurring before the *end* of the backup process.  After the
> > backup is complete, you can use the backup and the WAL to duplicate the
> > state of any later instant.

> I guess Oracle and other commercial DBMSs declare the start of backup
> process explicitly and that would be the point where the archive log
> recovery could go back. I'm interested in how Oracle accomplishes this
> (I know DROP TABLE is not rollbackable in Orale).

Actually it is possible to get back to state 1 but for this we need a
backup from earlier.  To restate the scenario:

0) Backup A completes
1) Database activity happens
2) Backup B begins
3) Drop table t1 starts
4) Drop table t1 commits
5) Backup B completes
6) More database activity

We can recover any database activity for which we have WALs from backups
prior to that event.  If we want to restore to any point in time prior
to step 5, we must use backup A.  For points after step 5 we can use
backup B.

This is exactly the way that we would recover databases using Oracle.  I
have known DBAs attempt to restore a backup only to discover media
problems, and then revert to backup N-1, N-2 and so on.  Once a good
backup is finally found, recovery (roll-forward) is performed.  It takes
longer from an older backup and there is more risk of encountering a
lost log file but it works just fine.


-- 
Marc        marc@bloodnok.com