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.
Hi ,
AFAIK no. If You have clean cold backup You can restore , You will need
only archive logs to roll database forward.
REDO logs will be applicable in case of crash-recovery, or instance failure.
Regards
marcin
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:3c3d4dda$0$14589$4d4e...@read.news.fr.uu.net...
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.
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 ?
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:
3c3d744b$0$1586$afc3...@news.optusnet.com.au...
you would take the backup from 3am, restore it.
you would roll forward through all of the archived redo logs you have.
if the current online redo logs are available (the ones that were current as of
the crash), you will apply them as well -- else you lose their work.
It is 100% possible, and quite normal in fact, to replay all of the ARCHIVED
redo log accumulated during the day.
It is not possible however to make use of any ONLINE REDO log files that were
backed up -- they are useless. The only thing they could do for you is mess
things up.
what happens if you back them up is that you tend to panic during the restore,
you restore the datafiles AND online redo log files -- now you have just wiped
out the CURRENT ONLINE redo log files. You will LOSE that data. The only thing
these backed up files could do is ruin your day.
You backup datafiles.
You backup archives.
You create control file backups (and hopefully you never need them).
You do not backup online redo log, it is never necessary, even with a cold
backup (cold backups should be done after a shutdown immediate or normal, redo
logs are not necessary to recover).
--
Thomas Kyte (tk...@us.oracle.com) http://asktom.oracle.com/
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/
Opinions are mine and do not necessarily reflect those of Oracle Corp
"Christian M." <c...@sdv.fr> wrote in message
news:3c3da31b$0$24015$4d4e...@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
The redo log is written to sequentially, starting at the beginning. It is
only inconsistent because the tail end of the file is from an earlier
version. One would merely need to write a continuously incrementing log
write number as part of each log record written to the file to ensure that
the earlier entries could be identified.
Of course, normally one would hope that the current redo log is available
during recovery and a log file switch is a normal part of the backup
sequence so the archived log files are as current as they can be therefore
you wouldn't gain much.
Is there any error in my logic?
"Howard J. Rogers" <d...@hjrdba.com> wrote in message
news:3c3e0068$0$4216$afc3...@news.optusnet.com.au...
Welll...same answer...not nearly as much prose :)
Only small off-topic remark. Just few days back I had here two
consultants from well known worldwide consulting company. They trying
to sell us some new system based on Oracle database. One of my many
comments to their design was absence of any backup strategy for this
extremely important server. They responded me in way, which almost
knock me out.
There is (they've said) no need for online, offline backup or running
in ARCHIVELOG, because when using Oracle you will always lose, in case
of failure, data from active redo log, so everything you need are only
daily !exports!. And that's 1TB database we're talking about. This
perfect logic made me speechless. :-)))
--
_________________________________________
Dusan Bolek, Ing.
Oracle team leader
Note: pages...@usa.net has been cancelled due to changes (maybe we
can call it an overture to bankruptcy) on that server. I'm still using
this email to prevent SPAM. Maybe one day I will change it and have a
proper mail even for news, but right now I can be reached by this
email.
The first test is without online redo logs :
1) Restore of my hot backup of the night ( DATAFILES )
2) Restore of the archived logs of the day since 10:00 am
3) Create blank redo logs files corresponding to the files I have lost.
If not I have an error with the CREATE CONTROLFILE.
4) Create crontrol files with command CREATE CONTROFILE with option
RESETLOGS
5) RECOVER DATABASE UNTIL CANCEL USING BACKUP CONTROLFILE
6) ALTER DATABASE OPEN RESETLOGS
It works but I have lost the last transactions stored in the online redo
logs since 10h00am.
The second test is :
1) Restore of my hot backup of the night ( DATAFILES )
2) Restore of the archived logs of the day since 10:00 am
3) Restore of the ONLINE REDO LOGS saved at 10h10 am
4) Create crontrol files with command CREATE CONTROFILE with option
NORESETLOGS
5) RECOVER DATABASE
6) ALTER DATABASE OPEN
It works too without the lost of the transactions made between 10h00 and
10h10 !
I know you say that I MUST NOT save the online redo logs but can you just
explain me why in this case it is bad ?
Thanks
Christian.
"Howard J. Rogers" <d...@hjrdba.com> a écrit dans le message news:
3c3e0068$0$4216$afc3...@news.optusnet.com.au...
fdp
--------------------------------------------
Fred Pierce (DNRC) QSS Group Oracle DBA
Avialantic.com fpi...@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
-------------------------------------------
Your examples imply that a complete crash occurs immediately after the hot
backup, taking out all files utterly. If that happens, restoring the backed
up online redo log might recover completely if it was the last file backed up
before the crash (otherwise, it's worthless). However, the possibility that
this scenario will happen is probably extremely remote, and could easily be
made moot by switching logfiles before and after the hot backup. It's much
more likely that a crash will occur well after the "current" online log (at
the time of backup) has been archived off. Should that happen, and the
existing current log is mistakenly overwritten, you've lost data as well.
Since DBA's (juniors and seniors) are human, I'd think it much more likely
that whoever's doing the recovery will make that mistake at some point. Seems
to me that it's safer not to backup the current logfile (taking the precaution
to switch logfiles), than risk the latter, more likely scenario.
JMHO
"Dusan Bolek" <pages...@usa.net> wrote in message
news:1e8276d6.02011...@posting.google.com...
So these consultants may or may not know about a particular technical
product - however their ability to apply thought processes to that knowledge
and then apply them to business requirements looks suspect. This of course
bears no resemblance to any consultants I have ever come across.
--
Niall Litchfield
Oracle DBA
Audit Commission UK
*****************************************
Please include version and platform
and SQL where applicable
It makes life easier and increases the
likelihood of a good answer
******************************************
"Hemant K Chitale" <hkch...@singnet.com.sg> wrote in message
news:a1n29f$nqg$1...@dahlia.singnet.com.sg...
--
Posted via Mailgate.ORG Server - http://www.Mailgate.ORG
There is no need to protect the guilty, but I understand your not
giving their name. Perhaps you could have included a hint.
-- Mark D Powell --
> > Howard J. Rogers wrote:
> You do not backup online redo log, it is never necessary, even with a cold
> backup (cold backups should be done after a shutdown immediate or normal, redo
> logs are not necessary to recover).
>
> >> 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.
OK Guys, what do you make of the metalink Note 1034098.6 where they
explain you do want to if you want to restore from a cold backup
without applying archive logs?
---------------- Begin inclusion ------------------------
If a full restore of the database to the point in time of the backup
is
intended, both the control file and redo logs can be restored with the
datafiles. This will allow the database to open from the point it was
shutdown for the backups. This is the only time that recovery of the
redo
logs can assist recovery.
If the redo logs were not recovered, the recovery to a point back in
time
would require a resetlog to open the database.
Explanation
-----------
By default, the online redo logs are not backed up in the scripts.
This is
documented in the EBU manual. If roll forward is not performed, or in
noarchive mode, the backup and restore of online redo logs will
eliminate the
need for resetlogs when opening the database.
------------------ End inclusion
--------------------------------------
Personally, I think the idea of having a backup that can be restored
from with minimal DBA work is pretty attractive, especially in cases
of non-online-production instances like reporting or development or
pseudo-DW IAS where archive logging is just a waste of resources or
important-point-in-time breakpoints (like before a huge app upgrade)
where any restoration wouldn't be to something working. Maybe take
exports to help out klutzy developers.
That would make almost perfect sense if your redo's are so big (or
transactions are so small) they only switch once a day! :-)
More likely, they misinterpreted initial load instructions or what
their support department does for daily operational instructions. If
they had any brains, they should have said "we can sell you plenty of
consultant time to work with your DBA staff to come up with a
solution." But they probably have Microsoft experience.
> --
> _________________________________________
>
> Dusan Bolek, Ing.
> Oracle team leader
>
> Note: pages...@usa.net has been cancelled due to changes (maybe we
> can call it an overture to bankruptcy) on that server. I'm still using
> this email to prevent SPAM. Maybe one day I will change it and have a
> proper mail even for news, but right now I can be reached by this
> email.
jg
--
And of course, @home.com is becomming @cox.net. The email addressing
possibilities are astounding.
>1) Restore of my hot backup of the night ( DATAFILES )
Hold on, can you do that? I thought you needed cold backup files to start
with, then apply the archived log files??
That's of course quite a load of files.. Imagine applying seversal months of
redo logfiles..
Cheers,
Han.
+----------------------------------------------------------+
| Han Thomas 25/1 Moo 2 Pa Bong, Saraphee |
| H...@Royal.net Chiang Mai 50140 THAILAND |
+----------------------------------------------------------+
>you would roll forward through all of the archived redo logs you have.
>
>if the current online redo logs are available (the ones that were current as of
>the crash), you will apply them as well -- else you lose their work.
Question: Would you have to rename those to the same format as your archived
redo logs and apply them in the same fasion, or can you just leave them in the
original location after restoring the (cold) backups of the control and
datafiles, after which Oracle will take care of it?
I think i renamed them once as archived log files, and that worked.
you could but you could also just give us the file name as we recovered -- it'll
say something like "need to apply this suggested filename foo.log", you can just
put in the other name if you like...
along the lines of this:
...
As a log file is needed, Oracle suggests the name of the file. For example, if
you are using SQL*Plus, it returns the following lines and prompts:
ORA-00279: Change #### generated at DD/MM/YY HH:MM:SS needed for thread #
ORA-00289: Suggestion : logfile
ORA-00280: Change #### for thread # is in sequence #
Specify log: [<RET> for suggested | AUTO | FROM logsource | CANCEL ]
....
>Cheers,
>Han.
>
>+----------------------------------------------------------+
>| Han Thomas 25/1 Moo 2 Pa Bong, Saraphee |
>| H...@Royal.net Chiang Mai 50140 THAILAND |
>+----------------------------------------------------------+
--
Yes, you can. No, you dont. Why cant you restore from hot backup and
apply the archived logfiles ? What is the point of a hot backup if you
always need a cold backup to start with. You dont need to start with
your last cold backup to apply the archived logfiles.
> That's of course quite a load of files.. Imagine applying seversal months of
> redo logfiles..
You just need the archive logs from the time your last hot backup
started.
Murali
>> >1) Restore of my hot backup of the night ( DATAFILES )
>>
>> Hold on, can you do that? I thought you needed cold backup files to start
>> with, then apply the archived log files??
>
>Yes, you can. No, you dont. Why cant you restore from hot backup and
>apply the archived logfiles ? What is the point of a hot backup if you
>always need a cold backup to start with.
Well I thought you kept one cold set and just keep storing redo-logfiles
forever. Then if your server crashes a year later you'd have to apply one year
of redo logs. So that isn't the case I guess.. And you can use regular
operating system commands to do the hot backup? A regular copy of all the
datafiles?
Then I understand the control files only have to be backed up after structural
changes are made to the database, such as adding or removing tablespaces and
datafiles?
Thansk a lot!
Han.
Yes, you can use Hot Backups to recover from.
However, 'Hot Backup' means in the sense Oracle
allows you to take a hot backup --- not just copying
the file with an OS command.
You issue an
ALTER TABLESPACE <tablespacename> BEGIN BACKUP;
before you start copying the files _of_that_tablespace_!
with an OS command ("copy", "cp", "dd", "tar", ... whatever
and an
ALTER TABLESPACE <tablespacename> END BACKUP;
You had better read up on the Backup and Recovery
procedures well. You could also use Oracle RMAN
to do your Hot Backups.
Hemant K Chitale
"Han Thomas" <h...@royal.net> wrote in message
news:k7454uk11sh045cet...@4ax.com...
If you have backups/archivelogs, you may only lose data from the
current online redo log.
But if you have only exports, you'll lose everything since the last
export. And you'll have to recreate the database and do a full import.
So what they're saying is losing a day's worth of work and spending
countless hours on the import is better than MAYBE losing half an
hour's worth of data.
I'll bet it's a lot of fun importing 1TB of data. And the consultants
who will do the import are paid by the hour, of course.
Murali
http://www.dbaquest.com/
It sound like the sort of thing that is used to store object instances, with
an object identifier and a blob or set of fields to hold the data belonging
to the object. Given that, one table with an index on indentifier should
perform more than adequately.
Of course, you can't back it up and there doesn't seem much point in using a
relational database, but apart from that...
> Well I thought you kept one cold set and just keep storing
> redo-logfiles forever. Then if your server crashes a year later
> you'd have to apply one year of redo logs. So that isn't the
> case I guess.. And you can use regular operating system
> commands to do the hot backup? A regular copy of all the
> datafiles?
I got corrected on this awhile back. When you think about it, a
hot backup is still a cold backup for the tablespace which is
offline and being backed up (ie, you still perform an OS level
copy to some other destination), but there is block level redo
written for that tablespace during the offline state, so the
transactions for that tablespace are being written somewhere
else. You sort of do a round-robin backup of tablespaces, so in
essence, you have a cold backup for each tablespace but at
incrementally increasing time frames.
You could do one cold backup and then apply redo from the life of
the database as you say, or you can cut the time for recovery
down, as well as the need to store all of the archived redo logs
by doing hot backups.
--
Galen deForest Boyer
Sweet dreams and flying machines in pieces on the ground.