Re: Online backup: Backup online redologs?

"Charles Fisher" <Charles.Fisher_at_alcoa.com> wrote in message news:Pine.GSO.4.31.0105081439340.805-100000_at_unknown...
> On Mon, 7 May 2001, Spencer wrote:
>
> > the risk that an "old" backup copy of an online redo log
> > will be "restored" over top of a perfectly good online
> > redo log file, which could very well "wipe out" valid redo
> > entries that are needed for complete recovery.
> > so, including "backup" copies of online redo log files in
> > a "hot backup set" is of dubious value... since the backup
> > copy of the online redo log file should never be restored,
> > is not just useless, it is downright dangerous.
> > when the pressure is on to get the database files restored
> > and the database recovered, how are you going to make
> > sure that the junior dba restores ONLY datafiles and does
> > NOT restore the online redo log files ? having the backup
> > copies of the files available only raises the risk that these
> > files will be restored. Yikes!
>
> I have 3 reasons why I am going to reject your argument on what I consider
> to be extremely valid grounds.
>
> Number one:
>
> You probably didn't notice the following SQL in my backup script:
>
> # The ugly SQL below selects only a single member from each group.
> print -p "$CONNECT
> set sqlprompt ''
> set sqlnumber off
> set trimout on
> set head on
> set pagesize 2048
> set linesize 2048
>
> SELECT
> '+ '|| RTRIM(x.member)
> FROM
> v\$logfile x
> WHERE
> x.member IN
> (
> SELECT
> member
> FROM
> v\$logfile
> WHERE
> group# = x.group# AND rownum < 2
> );
> quit"
>
> ASSUMING that each log group has dual members, this code is only going to
> select for hot backup a SINGLE member, so that is all that is going to be
> available for my impetuous junior dba to restore. Granted, one of the
> members might have been lost, and this might overwrite the only remaining
> good member. HOWEVER, if archive logs still exist, then the recovery will
> be complete until the last log switch.

It rather depends on the first member in each group (which I think is what your SQL is selecting for each time) never being corrupted, doesn't it? Suppose you get corruption in your first member -the database will run happily by using the second, but you've just backed up the non-functional member. How robust is that?

> Also, my script dumps copies of all the data into a single $DEST
> directory. If the system is laid out under OFS guidelines, then it will be
> impossible to wipe out both log members just by doing a simple restore
> from tape.
>
>
> Number two:
>
> What if the server catches fire? What if some idiot spills a coke into the
> disk array? What if we have a tornado and I see my server sailing past my
> office window?
>
> It might be a tad difficult to extract the online redo logs in that case.
>

That's what clustering and disaster recovery procedures are for.

> I ABSOLUTELY MUST have everything that I need to restore the database on a
> tape that I can hold in my hand, and it must be from a hot backup.

And you ABSOLUTELY can't. Not with 100% reliability, anyway.

>If I
> can't have that, then I don't want Oracle (and I don't know why anybody
> else would).
>
> I don't know why there isn't a furious uproar of people asking the same
> set of questions about this that I am. I just don't understand it.
>

I agree with that last sentence. It's *what* you don't understand that worries me.

>
> Number three:
>
> I need to clone from time to time from our production to development
> systems. My backup method works great double-duty.
>

It's inherently flawed, and the fact that you've got away with it in the past does not make it a reliable method. We've been round this one before.

>
> p.s. There is actually a case where I could get stung pretty badly by this
> procedure. If when I start copying the redo logs, the active log group is
> the LAST log group, and it is nearly full, and granted that I get a
> complete copy of the first log group, but also granting that before I get
> to the last log group, a log switch occurs, then there will be no active
> log group in the backup set, and my goose will be cooked when I try to
> restore. However, things are pretty quiescent when I am doing this,

Ah! I knew there had to be a reason for you getting away with it in the past, and this is it.

The problem with copying hot redo logs (in fact, that should be singular, because there's only ever one group that's hot, of course) is simply that the operating system will grab chunks to be copied at random -whatever happens to swing into view as the platter rotates. If that chunk happens to be the bit getting currently written to, your copy will be internally corrupt, and never mind 'backup markers' or anything else. The thing will simply be unreadable. I presume you do not have a mechanism to guarantee which bits of a file the O/S should decide to copy at anyone time? Therefore, your hot copying method is intrinsically fallible. The one thing that is saving you is that "things are pretty quiescent" -in other words, you try to make sure that no-one is *writing* to the file. Guess what taking a cold backup does? Er, that's right: makes sure (only, 100% sure this time) that no-one is writing to the file.

>but
> maybe I ought to look into forcing the first log group to go active before
> I take the hot backups.
>

Maybe you should ask yourself why RMAN, which as an Oracle utility understands the internal workings of these files rather better than you or I could ever do, will simply NOT allow you to take hot copies of online redo logs.

HJR
> --------------------------------------------------------------------------