alarming experience with controlfile autobackup on

Hi all,

I've wanted to demonstrate to my colleagues that it's safe to configure RMAN CONTROLFILE AUTOBACKUP ON, using the following general setup:

• retention policy set to 1 (not advisable of course, but could occur, e.g., in a test environment)
• backup script is just simply run { backup database FORMAT ... plus ARCHIVELOG FORMAT ...; delete obsolete; }
• backup is not done to FRA, therefore delete obsolete is neccessary

Now the basic danger, with CONTROLFILE AUTOBACKUP ON, I thought, was that if the following occurs:

step 1: do backup 
step 2: perform structural change, e.g., add tablespace
step 3: wait till autobackup has been created (this was another strange thing, I've found no way to influence this myself, not even by a database restart!)
step 4: someone does a DELETE OBSOLETE. The controlfile from the backup will be deleted as obsolete, we have the more recent one now only ...
step 5: delete all DB files

... I might not be able to perform incomplete recovery until just before the structural change.

So in my first test scenario, for the structural change in step 2, what I did was adding a tablespace. Indeed, even with the controlfile having information about that tablespace, I was able to get back to a state without - the controlfile was "rolled back".

For the second scenario, in step 2, I dropped a tablespace. So the controlfile backup would not longer contain the respective information, but the backupset would.

Basically, I expected it to work the same way as above - the controlfile getting back any missing information from the data dicionary.

But then in step 4 already, I was in for a shock: Not only was the controlfile backup from step 1 obsolete, also the backupset containing the new tablespace's datafile was (backups were performed with filesperset 1). Just for test's sake, I really did the "delete obsolete", and then restored and recovered until before the tablespace removal - just to see it it would work. And it did work - and so, I've recovered to a state of the database that never existed...

What do you think? I wouldn't say the whole setup is too far-fetched, someone perhaps wanting to free up space and doing a "report obsolete" independently of the backup schedule... And even with a questionable retention policy of 1, you would assume you are always able to go back to that one backup, wouldn't you?

I'd very much like to get your opinion on this,

thanks,

Sigrid

--
http://www.freelists.org/webpage/oracle-l