Re: PITR involving physical db changes (new files/tablespaces)

Charles Davis wrote:

> Perhaps someone can assist me in understanding this recovery scenario.
>
> I have very large SAP (2TB+) databases that are up and running for weeks
> at
> a time. During that time files are added to tablespaces as necessary. On
> very rare occasions a new tablespace will be added. Archiving is enabled.
>
> During a Point-In-Time-Recovery, which I haven't had to do yet, what will
> happen when during that recovery process a physical change is encountered
> in the archive log?

All PITRecoveries require that you restore all DBFs from backup and roll them forward. You seem to be suggesting that you don't have backups for some data files which have been added whilst the database was hot. Therefore, you ask what will happen.

What will happen is that your recovery will blow up! Simple answer, huh?!

*Why* haven't you backed up those new tablespaces and new data files? I know you don't want to shut down your database, but what do you think hot backups are for?? (OK, rhetorical question, because you go on to explain why... but read on).

> Will the recovery process pause, tell me perhaps a file was added, and
> give me a change to again add that file?

No. You have to have all the files there before you start the recovery. And this would be true for a complete recovery that happened to touch the new datafiles and tablespaces, not just a PIT recovery.

Now, in truth, it is not quite the disaster that I originally painted, because it is possible to re-create a 'blank' datafile that looks like what the missing data file was when it was first created, and then apply all the redo generated since the time of the file's creation. The command to do this is 'alter database create datafile...' (off the top of my head). The main point is that you can recover a datafile which has never been backed up -but there's really no excuse for not backing it up in the first place!

And you ought to re-create the missing data files before you start the recovery process. If you don't, the first bit of redo that should be applied to a non-existent data file will cause the recovery process to abort messily. I suppose you could regard that as a 'pause' in the recovery process, and take the opportunity offered to fix the problem, but it would be far simpler just to have everything there before you started. It certainly won't pause gracefully for you, anyway.

The dangers of this approach, in any case, are that you need to retain ALL redo since the time that the datafile was created. If there is just one archive missing, for example, you really are in trouble.

> I'd surely appreciate the input of the experts here, and/or a better
> strategy or process that I could follow when making physical changes to
> the databases.

Every physical change to the database that you make should be immediately followed by an 'alter database backup controlfile to trace' and an 'alter database backup controlfile to '/bing/bong/blah/control.bkp'.

Immediately after *that*, you should put the affected tablespace into hot backup mode, and take a backup of it.

Preferably, fire up RMAN and do the lot in one go.

> I take split-mirror backups 3 times a week on the database, and really
> don't take online backups since they run for days at a time.

Well, that's not really a valid excuse, since that is precisely what (a) hot backups were invented for and (b) why RMAN was invented. And (c), how long does it take to do a split-mirror backup, and why can't you just re-split your mirror and do another one whenever the database physically alters?

However, given the frequency with which you take split-mirror backups, you will not (it seems to me) ever have to re-create a datafile which is more than 2 or 3 days old. Therefore, it is unlikely(but not, unfortunately, impossible) you'll have gaps in your redo, and therefore you'll probably be OK.

But it will always be a bit of a wing-and-a-prayer job unless you really do backup after every physical change. A new backup guarantees recovery. With your method, you have to pray nothing happens to your redo.

At the very least, I hope you backup the controlfile after every physical change. And I hope it's multiplexed (not just 3-way mirrored). And, especially in your case, I hope your online redo logs are multiplexed (and not just 3-way mirrored). You cannot afford *ever* to lose your current redo log.

Regards
HJR
> I'd also appreciate being directed to a whitepaper or something more than
> just RTFM.

>
>
> Thank you all.
>
> Charles
Received on Mon Sep 13 2004 - 16:27:32 CDT