Coments below. HJR -- ---------------------------------------------- Resources for Oracle: http://www.hjrdba.com ===============================
"Christian M." <c...@sdv.fr> wrote in message news:email@example.com... > 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.
> 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" <d...@hjrdba.com> a écrit dans le message news: > firstname.lastname@example.org... > > 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" <c...@sdv.fr> wrote in message > > news:email@example.com... > > > 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. > > > > > > > > > > > >