RE: alarming experience with controlfile autobackup on

From: Mark W. Farnham <mwf_at_rsiz.com>
Date: Thu, 25 Oct 2012 21:52:09 -0400
Message-ID: <013d01cdb31c$837f59f0$8a7e0dd0$_at_rsiz.com>

So, if you protect yourself with saved images of the control files immediately before and after structural changes, then you are all set.

Now I'm not saying RMAN cannot get it right, just that it is too complex to grok whether RMAN covers all possibilities.

Saving a series of the controlfiles (I always save them in binary and to text on systems I am responsible for) also has uses as a crosscheck with your log book of choices you have made about a given database.

-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Sigrid Keydana Sent: Thursday, October 25, 2012 12:33 PM To: oracle-l_at_freelists.org
Subject: 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


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

Received on Fri Oct 26 2012 - 03:52:09 CEST