RE: RE: alarming experience with controlfile autobackup on

From: Mark W. Farnham <mwf_at_rsiz.com>
Date: Fri, 26 Oct 2012 06:48:03 -0400
Message-ID: <01d801cdb367$60e0a1b0$22a1e510$_at_rsiz.com>

I would advise making manual controlfile backups before and after structural changes.

I did NOT recommend turning off controlfile autobackup.

Belts and braces.

mwf

-----Original Message-----
From: Sigrid Keydana [mailto:keydana_at_gmx.de] Sent: Friday, October 26, 2012 3:54 AM
To: Mark W. Farnham; oracle-l_at_freelists.org Subject: Re: RE: alarming experience with controlfile autobackup on

Hi Mark,

thanks for your reply! So you'd advise turning this feature off and making manual backups before/after changes (including backups to trace)? I am also wondering whether using a recovery window instead of redundancy would eliminate such problems.

Ciao
Sigrid

• Original-Nachricht --------
  > Datum: Thu, 25 Oct 2012 21:52:09 -0400
  > Von: "Mark W. Farnham" <mwf_at_rsiz.com>
  > An: keydana_at_gmx.de, oracle-l_at_freelists.org
  > Betreff: RE: alarming experience with controlfile autobackup on
  

> 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 - 12:48:03 CEST