Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Backup Oracle ? Online Redo Log.

Re: Backup Oracle ? Online Redo Log.

From: Fred Pierce <fpierce_at_avialantic.com>
Date: Fri, 11 Jan 2002 08:26:03 -0500
Message-ID: <3C3EE7EB.79A1E2A1@avialantic.com>


I guess that's why it's a good idea to shut down and take a full backup before attempting a restore - even a simple one. A matter of time cost vs risk of course.

fdp



Fred Pierce (DNRC) QSS Group Oracle DBA
Avialantic.com fpierce_at_avialantic.com
www.avialantic.com
MAAM WWII Weekend Airshow 2002 - June 7-9 MAAM Transportation Show 2002 - Sept. 21-22 www.maam.org/transhow.html

"Howard J. Rogers" wrote:
>
> Coments below.
> HJR
> --
> ----------------------------------------------
> Resources for Oracle: http://www.hjrdba.com
> ===============================
>
> "Christian M." <cm_at_sdv.fr> wrote in message
> news:3c3da31b$0$24015$4d4eb98e_at_read.news.fr.uu.net...
> > Thanks for your answer !
> >
> > I've read the file (.PDF) which explain backup and recovery but I not sure
> > understanding why the online redolog files should never be backuped in a
> hot
> > backup.
> >
>
> Because the backup will be internally inconsistent, and therefore unuseable.
> And there is no mechanism provided to make the copy internally consistent.
>
> > For example :
> > If I have a HOT BACKUP of :
> > - the whole database at 03h00 am
> > - the control files,archived logs and online redo logs at 10h00 am ,
> 12h00,
> > 04:00 pm.
> > Imagine that my server burned at the end of the evening ( 05h00 ).
> > Which is the most recent version of my database that can I restore ?
> >
>
> I can only keep saying this so many times: you would not, must not, can not,
> take hot backups of your online redo logs. Ever.
>
> As to your scenario. Let me simplify. I took a hot backup of all data
> files last night. I also have a tracefile backup of the control file which
> I also took last night. During the next day, I switch through the online
> logs every 30 minutes or so (thus generating archive redo logs which are
> miraculously copied off by batch file commands to a remote destination). At
> 5pm the next day, my entire server blows up, taking every damn file in the
> system with it.
>
> Then I can perform an incomplete recovery of the entire database to 4.30pm
> (the time of the last log switch). I restore all datafiles from last
> night's backup (to a new machine, obviously). I run my controlfile trace
> script to re-create the controlfile. I don't have ANY online redo logs at
> this point, of course. I perform recovery until cancel -at which point all
> archives generated throughout the day are applied to the restored datafiles.
> That leaves just one redo log's-worth of redo unable to be applied -the
> current log's. So when the recovery process fails to locate the current log
> in its usual position, I cancel recovery and open the database with a
> 'resetlogs'. That operation re-creates all my online logs for me, but of
> course, I am one log short of a complete recovery. Still, having every file
> in the system blow up, and being nevertheless able to recover all bar 30
> minutes' of work, is impressive, I think.
>
> Let me modify the scenario just slightly: when my box blew up, it took every
> single disk bar one with it. That sole surviving disk happened to contain
> the mirror-members of my two redo log groups. What can I recover now? The
> answer is: absolutely everything, right up to the point of failure. The
> restore operation would be: restore all datafiles from last night's backup;
> copy the surviving redo members over to where they are supposed to be, run
> the tracefile script to recreate the controlfile. Now perform normal,
> complete recovery (in fact the tracefile script will do that for you), and
> when Oracle wants to apply the last dollop of redo out of the current log...
> Lo! It *will* be able to do so.
>
> So the secret of complete recoveries is not losing your current redo log,
> and you do that by multiplexing it across several (I recommend three)
> separate physical devices... NOT by attempting to back it up.
>
> I'll tell you one final true story. Junior DBA (no relation to the present
> author, honestly!) has developed his own script that performs a hot backup
> of all datafiles, plus a tracefile script of the controlfile, plus (big
> mistake) a hot copy of ALL redo logs. This database log switches about once
> an hour. He takes a backup one night.
>
> The next day, after about three hours of work on the database, just one
> fairly small datafile gets corrupted. Ordinarily no problem... you'd expect
> to restore the one datafile, and the recover it, and thus perform a complete
> recovery -except that in the heat of the moment, Junior DBA performs this
> particular recovery operation by issuing a command not a million miles
> dissimilar to 'copy backup\*.* oradata\*.*'
>
> So suddenly all datafiles have to be recovered, not just one. Even that's
> not a problem usually, it just means that recovery takes longer than it
> otherwise would have done. But the real kicker is that the 'restore'
> operation has just replaced two perfectly functional online redo logs with
> copies of said logs as they were last night. What's worse, one of those
> copies is internally inconsistent (because it was the current log) and can't
> be used. So when the 'recover database' command is issued, all archives
> since the time of the backup are applied... but the recovery process bombs
> out when it tries to apply the last drop of redo from what it thinks is the
> current log -because it's not a functioning redo log at all, just a pile of
> inconsistent and incoherent bits that *resembles* a redo log. At this point
> the unhappy Senior DBA realises what is going on (roundly berates
> incompetent Junior), and has to re-perform the entire restore of all
> datafiles (because the loss of a perfectly good current redo log requires an
> incomplete recovery), and then perform a 'recovery until cancel', with its
> attendant (and very expensive) 'open resetetlogs' afterwards. The resulting
> brawl in the server room was not a pretty site -and the lost hour's-worth of
> work (not to mention all the downtime) was not exactly pleasant either,
> particularly because it was utterly and completely unnecessary. There was
> no need to lose *any* data on that database, but data was lost because the
> presence in the hot backup of something that looked like a current log
> persuaded the person in charge to restore it. Had it never been there in
> the first place, the worst that could have happened was that what should
> have been a quick five minute recovery would have taken maybe 45 minutes to
> perform... but no data would have been lost.
>
> So: never backup your online redo logs. Always multiplex them (mirror them
> with hardware mirroring as well, if you like). And you'll never have to
> worry about total loss of the current redo log ever again.
>
> Regards
> HJR
>
> > If I restore all datafiles saved at 03h00 am and all control files , redo
> > log files saved at 04:00pm,
> > Why it is not possible replay all the archived redo log saved between
> > 03h00am and 04h00pm ?
> >
> > if not, can you tell me what is the best backup strategy for this case of
> > damaged ?
> >
> > Thanks.
> >
> > Christian.
> >
> > "Howard J. Rogers" <dba_at_hjrdba.com> a écrit dans le message news:
> > 3c3d744b$0$1586$afc38c87_at_news.optusnet.com.au...
> > > You cannot, ever, in a million years, backup an online redo log. It
> can't
> > > be done, and you'll risk data loss by attempting it (I've seen a junior
> > DBA
> > > restore everything from a backup set, including a hot-copied online log,
> > and
> > > as a result over-write a perfectly functioning current redo log with a
> > pile
> > > of poo).
> > >
> > > Rogers' first rule of hot backups is simply this: You cannot ever hot
> > backup
> > > any part of an Oracle database without the end result being internally
> > > inconsistent. That goes for online redo logs as well as datafiles or
> > > control files.
> > >
> > > Rogers' second rule of hot backups is that internally inconsistent files
> > are
> > > not a problem provided Oracle's supplied a mechanism to get them
> > internally
> > > consistent. The application of redo makes an internally inconsistent
> data
> > > file consistent. But for control files and redo logs, there is no such
> > > mechanism (you can't apply redo to either).
> > >
> > > Now, for control files, Oracle thoughtfully provided the 'alter database
> > > backup controlfile to...' command, which uses its own special mechanism
> to
> > > generate either a trace file (and there's no worries about a text file
> > being
> > > inconsistent) or a guaranteed consistent binary image of the control
> file.
> > >
> > > But no such provision was made for taking a hot backup of the current
> redo
> > > log.
> > >
> > > Therefore, combine rules one and two with the lack of an alternative
> > > mechanism and the conclusion is inescapable: hot backups of the current
> > redo
> > > log will be internally inconsistent, utterly unusable, and no mechanism
> > > exists to rectify the situation.... therefore, hot backups of them are a
> > > complete waste of time.
> > >
> > > You can of course perform a cold backup of said redo log, by the simple
> > > expedient of shutting the database down.... at which point, you don't
> > > actually have a current redo log any more, just a bunch of dead o/s
> files.
> > > There is no problem copying such a closed redo log at all... but it will
> > be
> > > out of date the second you re-open the database.
> > >
> > > Visit my site, read the backup and recovery documents there, especially
> > the
> > > large one under the 'books' link, and you'll see that unless you are
> > running
> > > in noarchivelog, there is zero usefulness in backing up the current log
> at
> > > all. What's more, doing so risks inadvertent (and unnecessary) loss of
> > data
> > > when it comes time to recovery. Without doubt, the current redo log is
> > the
> > > Achilles' heel of the entire Oracle architecture -which is why they
> > invented
> > > redo log mirroring (or, more accurately) multiplexing so that total loss
> > of
> > > the current log should be a very rare occurrence -making attempted
> backups
> > > of it even more of a waste of time than they otherwise would be.
> > >
> > > Regards
> > > HJR
> > > --
> > > ----------------------------------------------
> > > Resources for Oracle: http://www.hjrdba.com
> > > ===============================
> > >
> > >
> > > "Christian MERCK" <cm_at_sdv.fr> wrote in message
> > > news:3c3d4dda$0$14589$4d4eb98e_at_read.news.fr.uu.net...
> > > > Hi,
> > > >
> > > > It is useful to backup regularly the online redo log ?
> > > > If yes, how should I do it ? Just an OS copy ?
> > > >
> > > > Thanks in advance.
> > > >
> > > > Christian.
> > > >
> > > >
> > >
> > >
> >
> >
Received on Fri Jan 11 2002 - 07:26:03 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US