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 Thu Jan 10 2002 - 14:59:22 CST
