Online backup: Backup online redologs? 
Author Message
 Online backup: Backup online redologs?

Kevin Loney's Oracle 8 DBA handbook mentions the following 3-step
process for a online/hot backup:

1. Backup each tablespace by putting it in backup mode
2. Backup the archived redologs (and optionally delete them to save
space)
3. Backup the controlfile using ALTER DATABASE BACKUP CONTROLFILE

Why is there no mention of the online redologs? During BEGIN BACKUP,
arent the changed blocks written to the online redologs (in addition
to the datafiles themselves)? So, if my entire backup is over before
the online logs cycle, my online backup will be missing the changes
generated during the backup since they are present only in the online
redologs, right? What am I missing?

And if the online redologs should indeed be backed up, shouldnt there
be a BEGIN BACKUP for them as well since they are in a state of flux?

Thanks...



Sat, 13 Sep 2003 23:16:48 GMT
 Online backup: Backup online redologs?

On Tue, 27 Mar 2001 10:16:48 -0500, Vikas Agnihotri

Quote:
>1. Backup each tablespace by putting it in backup mode
>2. Backup the archived redologs (and optionally delete them to save
>space)
>3. Backup the controlfile using ALTER DATABASE BACKUP CONTROLFILE

>Why is there no mention of the online redologs? During BEGIN BACKUP,
>arent the changed blocks written to the online redologs (in addition
>to the datafiles themselves)? So, if my entire backup is over before
>the online logs cycle, my online backup will be missing the changes
>generated during the backup since they are present only in the online
>redologs, right? What am I missing?

>And if the online redologs should indeed be backed up, shouldnt there
>be a BEGIN BACKUP for them as well since they are in a state of flux?

Well just do a "switch logfile" after backuped the datafiles of any
tablespace. Thus, you don't need to backup the online redo-logs. Just
the archive redo-logs.

Bye,

Christian Hartmann



Sun, 14 Sep 2003 00:23:38 GMT
 Online backup: Backup online redologs?


Quote:
> Kevin Loney's Oracle 8 DBA handbook mentions the following 3-step
> process for a online/hot backup:

> 1. Backup each tablespace by putting it in backup mode
> 2. Backup the archived redologs (and optionally delete them to save
> space)
> 3. Backup the controlfile using ALTER DATABASE BACKUP CONTROLFILE

> Why is there no mention of the online redologs?

AAAAAAAAAAAAAAAAAAAAAAAAAAAAAARRRRRRRRRRRRRRRRRRRRRRRGGGGGGGGGGGGGGGGGGGHHHH
HHHHHHHHHHHHHHHHHHHHHH!
I don't beleieve this.
This was discussed at ENORMOUS length just last week.  Don't you ever scan
other posts before sending off your own??

Really.

OK.  <calm>.

The basic rule in Oracle is this: you can't copy anything hot unless you've
a mechanism for making it consistent subsequently, because all hot copies
will be internally one big mess.  Different bits copied at different times.

Datafiles copied hot will be inconsistent.  However, if you apply redo to
datafiles, they become consistent, and can then proceed to be rolled forward
to whatever time (read: SCN) you choose.

Controlfiles copied hot will be inconistent.  And you can't apply redo to
controlfiles.  So they can't be made consistent... but fortunately, the
'alter database backup controlfile to 'c:\blah\control01.bkp'" command makes
Oracle do the copying, and when Oracle is in charge it can use tricks and
techniques to guarantee the output will be read consistent.  (Woe betide
you, however, if you *ever* do a simple O/S copy of a hot controlfile,
because it will be unuseable).

Redo Logs copied hot will be inconsistent.  And you can't apply redo to redo
logs.  So they can't be made consistent.... and UNfortunately there is no
'alter database' command that will generate consistent copies.  So any hot
copies of redo logs will be inconsistent, will always be so, and will thus
be totally unuseable.

And then there's this: think about it.  If you are in archivelog mode, you
*do* have a consistent copy of the redo logs in the archive log (which is
'cold' the second it is written).  So by backing up the archivelogs, you are
in effect backing up consistent copies of the online redo logs.  The only
exception is the CURRENT redo log, which is hot, hasn't been archived, and
cannot be O/S copied.  This is the potential achilles' heel of the Oracle
database.  Loss of the current log will result in data loss -but that's why
redo log mirroring was invented, to make the potential total loss of the
current log extremely unlikely.

Quote:
>During BEGIN BACKUP,
> arent the changed blocks written to the online redologs (in addition
> to the datafiles themselves)? So, if my entire backup is over before
> the online logs cycle, my online backup will be missing the changes
> generated during the backup since they are present only in the online
> redologs, right? What am I missing?

You are missing the usual recommendation from Oracle, which is to issue an
'alter system switch log file' command at the end of each tablespace's spell
in hot backup mode.

 > And if the online redologs should indeed be backed up, shouldnt there

Quote:
> be a BEGIN BACKUP for them as well since they are in a state of flux?

See above.  It makes no sense to backup the online redo logs.  They've
already all been backed up to the archives, with the exception of the
current log.  If you are that concerned, then a switch log file will cause
that to be archived, and by backing up that last archive, you've got the
lot.

Regards
HJR

Quote:
> Thanks...



Sun, 14 Sep 2003 16:39:14 GMT
 Online backup: Backup online redologs?

Quote:



> > Kevin Loney's Oracle 8 DBA handbook mentions the following 3-step
> > process for a online/hot backup:

> > 1. Backup each tablespace by putting it in backup mode
> > 2. Backup the archived redologs (and optionally delete them to save
> > space)
> > 3. Backup the controlfile using ALTER DATABASE BACKUP CONTROLFILE

> > Why is there no mention of the online redologs?

> AAAAAAAAAAAAAAAAAAAAAAAAAAAAAARRRRRRRRRRRRRRRRRRRRRRRGGGGGGGGGGGGGGGGGGGHHHH
> HHHHHHHHHHHHHHHHHHHHHH!
> I don't beleieve this.
> This was discussed at ENORMOUS length just last week.  Don't you ever scan
> other posts before sending off your own??

> Really.

> OK.  <calm>.

> The basic rule in Oracle is this: you can't copy anything hot unless you've
> a mechanism for making it consistent subsequently, because all hot copies
> will be internally one big mess.  Different bits copied at different times.

> Datafiles copied hot will be inconsistent.  However, if you apply redo to
> datafiles, they become consistent, and can then proceed to be rolled forward
> to whatever time (read: SCN) you choose.

> Controlfiles copied hot will be inconistent.  And you can't apply redo to
> controlfiles.  So they can't be made consistent... but fortunately, the
> 'alter database backup controlfile to 'c:\blah\control01.bkp'" command makes
> Oracle do the copying, and when Oracle is in charge it can use tricks and
> techniques to guarantee the output will be read consistent.  (Woe betide
> you, however, if you *ever* do a simple O/S copy of a hot controlfile,
> because it will be unuseable).

> Redo Logs copied hot will be inconsistent.  And you can't apply redo to redo
> logs.  So they can't be made consistent.... and UNfortunately there is no
> 'alter database' command that will generate consistent copies.  So any hot
> copies of redo logs will be inconsistent, will always be so, and will thus
> be totally unuseable.

My hot backup, working whit 8.0.6, makes, at the end, a hot copy of the online
redo logs, including the current. And, at recover time, I didn't see any
problem so far.
I know, if it's possible, I'll preserve the redo log files on disk (after a
failure).
What are the options for a total failure of a database with a hot backup and
some archived redos?
I tried to recreate the redo logs in mount mode, but 'he' is always asking me
about the lost redos.

Quote:

> And then there's this: think about it.  If you are in archivelog mode, you
> *do* have a consistent copy of the redo logs in the archive log (which is
> 'cold' the second it is written).  So by backing up the archivelogs, you are
> in effect backing up consistent copies of the online redo logs.  The only
> exception is the CURRENT redo log, which is hot, hasn't been archived, and
> cannot be O/S copied.  This is the potential achilles' heel of the Oracle
> database.  Loss of the current log will result in data loss -but that's why
> redo log mirroring was invented, to make the potential total loss of the
> current log extremely unlikely.

...but possible...

Quote:

> >During BEGIN BACKUP,
> > arent the changed blocks written to the online redologs (in addition
> > to the datafiles themselves)? So, if my entire backup is over before
> > the online logs cycle, my online backup will be missing the changes
> > generated during the backup since they are present only in the online
> > redologs, right? What am I missing?

> You are missing the usual recommendation from Oracle, which is to issue an
> 'alter system switch log file' command at the end of each tablespace's spell
> in hot backup mode.

I'm making a switch log at the begin and another switch at the end of all
tablespaces. Am I right?
Or are you talking about backing up only one tablespace?

Quote:

>  > And if the online redologs should indeed be backed up, shouldnt there
> > be a BEGIN BACKUP for them as well since they are in a state of flux?

> See above.  It makes no sense to backup the online redo logs.  They've
> already all been backed up to the archives, with the exception of the
> current log.  If you are that concerned, then a switch log file will cause
> that to be archived, and by backing up that last archive, you've got the
> lot.

> Regards
> HJR

> > Thanks...

Thanks in advance by your opinion HJR.

Jos Nicolau



Wed, 17 Sep 2003 22:04:08 GMT
 Online backup: Backup online redologs?


Quote:



> > > Kevin Loney's Oracle 8 DBA handbook mentions the following 3-step
> > > process for a online/hot backup:

> > > 1. Backup each tablespace by putting it in backup mode
> > > 2. Backup the archived redologs (and optionally delete them to save
> > > space)
> > > 3. Backup the controlfile using ALTER DATABASE BACKUP CONTROLFILE

> > > Why is there no mention of the online redologs?

AAAAAAAAAAAAAAAAAAAAAAAAAAAAAARRRRRRRRRRRRRRRRRRRRRRRGGGGGGGGGGGGGGGGGGGHHHH

Quote:
> > HHHHHHHHHHHHHHHHHHHHHH!
> > I don't beleieve this.
> > This was discussed at ENORMOUS length just last week.  Don't you ever
scan
> > other posts before sending off your own??

> > Really.

> > OK.  <calm>.

> > The basic rule in Oracle is this: you can't copy anything hot unless
you've
> > a mechanism for making it consistent subsequently, because all hot
copies
> > will be internally one big mess.  Different bits copied at different
times.

> > Datafiles copied hot will be inconsistent.  However, if you apply redo
to
> > datafiles, they become consistent, and can then proceed to be rolled
forward
> > to whatever time (read: SCN) you choose.

> > Controlfiles copied hot will be inconistent.  And you can't apply redo
to
> > controlfiles.  So they can't be made consistent... but fortunately, the
> > 'alter database backup controlfile to 'c:\blah\control01.bkp'" command
makes
> > Oracle do the copying, and when Oracle is in charge it can use tricks
and
> > techniques to guarantee the output will be read consistent.  (Woe betide
> > you, however, if you *ever* do a simple O/S copy of a hot controlfile,
> > because it will be unuseable).

> > Redo Logs copied hot will be inconsistent.  And you can't apply redo to
redo
> > logs.  So they can't be made consistent.... and UNfortunately there is
no
> > 'alter database' command that will generate consistent copies.  So any
hot
> > copies of redo logs will be inconsistent, will always be so, and will
thus
> > be totally unuseable.

> My hot backup, working whit 8.0.6, makes, at the end, a hot copy of the
online
> redo logs, including the current. And, at recover time, I didn't see any
> problem so far.

Well, you want to stop that immediately.  Oracle clearly and forcefully
tells you not to hot copy redo logs, for the reasons I explained.  The fact
that you've got away with it once or twice does not make a lousy technique a
good one.  Hot copying redo logs is idiotic, not to put too fine a point on
it.

I do a demo of cloning on the Backup and Recovery course, and re-naming the
database after it's cloned.  I knew that to rename the database there was a
requirement to do a clean shutdown.  But students get bored quickly whilst
waiting for a clean shutdown, so I've always done aborts.  And the database
has been renamed perfectly every time.  Except once, when I had just earlier
demo'd a transaction -which therefore needed to be recovered after the
shutdown abort... that conflicted with the need to re-create the
controlfile.. and bingo! One stuffed cloning demo.  The point of course is
that particular circumstances, and special considerations might let you get
away with a restore of hot copied redo logs (for example, if there are no
transactions happening on the database whilst you take the copy, you might
get away with it)... but that doesn't invalidate the principle.  You should
not take the backups in the first place, and you must not restore them.

Apart from anything else -by restoring a BACKUP of your redo logs, you've
just over-written the CURRENT log -and that means you've lost information
needed for complete recovery.

- Show quoted text -

Quote:
> I know, if it's possible, I'll preserve the redo log files on disk (after
a
> failure).
> What are the options for a total failure of a database with a hot backup
and
> some archived redos?
> I tried to recreate the redo logs in mount mode, but 'he' is always asking
me
> about the lost redos.

> > And then there's this: think about it.  If you are in archivelog mode,
you
> > *do* have a consistent copy of the redo logs in the archive log (which
is
> > 'cold' the second it is written).  So by backing up the archivelogs, you
are
> > in effect backing up consistent copies of the online redo logs.  The
only
> > exception is the CURRENT redo log, which is hot, hasn't been archived,
and
> > cannot be O/S copied.  This is the potential achilles' heel of the
Oracle
> > database.  Loss of the current log will result in data loss -but that's
why
> > redo log mirroring was invented, to make the potential total loss of the
> > current log extremely unlikely.

> ...but possible...

Yes.  It's *possible* there'll be a nuclear war tomorrrow, as well.  You
can't really ever guarantee never losing all copies of a log group.
Mirroring is just a sensible insurance policy.

- Show quoted text -

Quote:

> > >During BEGIN BACKUP,
> > > arent the changed blocks written to the online redologs (in addition
> > > to the datafiles themselves)? So, if my entire backup is over before
> > > the online logs cycle, my online backup will be missing the changes
> > > generated during the backup since they are present only in the online
> > > redologs, right? What am I missing?

> > You are missing the usual recommendation from Oracle, which is to issue
an
> > 'alter system switch log file' command at the end of each tablespace's
spell
> > in hot backup mode.

> I'm making a switch log at the begin and another switch at the end of all
> tablespaces. Am I right?

No.  Not really.  It's better than nothing.  But you might have a failure
during the hot backup that takes out your redo logs entirely.  At which
point, you've lost the block-level redo, since no archive log has been
produced.  A switch every tablespace at least minimises the amount of data
you'd lose in that scenario.

Regards
HJR

- Show quoted text -

Quote:
> Or are you talking about backing up only one tablespace?

> >  > And if the online redologs should indeed be backed up, shouldnt there
> > > be a BEGIN BACKUP for them as well since they are in a state of flux?

> > See above.  It makes no sense to backup the online redo logs.  They've
> > already all been backed up to the archives, with the exception of the
> > current log.  If you are that concerned, then a switch log file will
cause
> > that to be archived, and by backing up that last archive, you've got the
> > lot.

> > Regards
> > HJR

> > > Thanks...

> Thanks in advance by your opinion HJR.

> Jos Nicolau



Wed, 17 Sep 2003 18:42:12 GMT
 Online backup: Backup online redologs?

Quote:

>>> See above.  It makes no sense to backup the online redo logs.  They've
>>> already all been backed up to the archives, with the exception of the
>>> current log.  If you are that concerned, then a switch log file will
>>> cause
>>> that to be archived, and by backing up that last archive, you've got the
>>> lot.

EXCEPT that you cannot execute the output of 'ALTER DATABASE
BACKUP CONTROLFILE TO TRACE' without the presence of the
redolog files, one of which must be marked as active.

What is really the difference between a restore with a hot
copy of the redo and a recovery of a crashed instance which
corrupts a number of tablespaces? Wouldn't each situation
introduce the same sort of corruption?

It would make much more sense if there were an option to the
CREATE CONTROLFILE statement to create blank redologs. Oracle
should struggle to make this easy; this ambiguity is not useful.

I don't understand why people are so evasive on this issue.



Sat, 18 Oct 2003 03:04:39 GMT
 Online backup: Backup online redologs?


Quote:

> >>> See above.  It makes no sense to backup the online redo logs.  They've
> >>> already all been backed up to the archives, with the exception of the
> >>> current log.  If you are that concerned, then a switch log file will
> >>> cause
> >>> that to be archived, and by backing up that last archive, you've got
the
> >>> lot.

> EXCEPT that you cannot execute the output of 'ALTER DATABASE
> BACKUP CONTROLFILE TO TRACE' without the presence of the
> redolog files, one of which must be marked as active.

You shouldn't take a reply to one person's query out of context.  That reply
was referring to a worry about not having the very last transactions in a
given hot backup, and had nothing to do with cloning a database.

Quote:
> What is really the difference between a restore with a hot
> copy of the redo and a recovery of a crashed instance which
> corrupts a number of tablespaces?

The difference is that corrupt tablespaces can be replaced with inconsistent
versions, and brought into a state of consistency by the application of
redo.  Restoring hot copies of redo logs means you are restoring
inconsistent files which can NOT be mad consistent by any means.

Quote:
>Wouldn't each situation
> introduce the same sort of corruption?

Yes, sort of.  Both sorts of files will be internally inconsistent (corrupt,
if you prefer).  Corruption is corruption is corruption.  It's what you can
*do* about the corruption that makes the difference, and there's sod-all you
can do about corruption in redo logs.

Quote:
> It would make much more sense if there were an option to the
> CREATE CONTROLFILE statement to create blank redologs. Oracle
> should struggle to make this easy; this ambiguity is not useful.

Where's the ambiguity?  "You cannot take hot copies of redo logs" is pretty
unambiguous.  And "mirroring (or multiplexing iof you prefer) was invented
precisely so that you would never be in a position of losing all members of
a redo log group" is likewise fairly clear-cut.

Quote:

> I don't understand why people are so evasive on this issue.

I don't understand why certain people can't simply approach this issue with
logic and clarity, instead of seeking to do the impossible by utterly
illogical means.

Regards
HJR



Sat, 18 Oct 2003 08:47:36 GMT
 Online backup: Backup online redologs?

Quote:

>>>>> See above.  It makes no sense to backup the online redo logs.  They've
>>>>> already all been backed up to the archives, with the exception of the
>>>>> current log.  If you are that concerned, then a switch log file will
>>>>> cause
>>>>> that to be archived, and by backing up that last archive, you've got
>>>>> the
>>>>> lot.
>> EXCEPT that you cannot execute the output of 'ALTER DATABASE
>> BACKUP CONTROLFILE TO TRACE' without the presence of the
>> redolog files, one of which must be marked as active.
> You shouldn't take a reply to one person's query out of context.  That reply
> was referring to a worry about not having the very last transactions in a
> given hot backup, and had nothing to do with cloning a database.

Then why was it part of your reply?

I also shouldn't take hot backups of the redo logs,
but that seems to work pretty well, doesn't it?
The emphasis here stressed the discontinuity of your reply.

Quote:
>> What is really the difference between a restore with a hot
>> copy of the redo and a recovery of a crashed instance which
>> corrupts a number of tablespaces?
>> Wouldn't each situation
>> introduce the same sort of corruption?
> Yes, sort of.  Both sorts of files will be internally inconsistent (corrupt,
> if you prefer).  Corruption is corruption is corruption.  It's what you can
> *do* about the corruption that makes the difference, and there's sod-all you
> can do about corruption in redo logs.

Either it is the same or it is different; you must choose.

Quote:
>> It would make much more sense if there were an option to the
>> CREATE CONTROLFILE statement to create blank redologs. Oracle
>> should struggle to make this easy; this ambiguity is not useful.
> Where's the ambiguity?  "You cannot take hot copies of redo logs" is pretty
> unambiguous.  And "mirroring (or multiplexing iof you prefer) was invented
> precisely so that you would never be in a position of losing all members of
> a redo log group" is likewise fairly clear-cut.

In a cloning situation, *I only want to apply the redo
that is in the archived logs.* I could care less about
the online redo - really, absolutely, I don't want it,
and I don't want to have to worry about it.

Actually, I feel mostly the same about backing up to tape.
I just don't care about unarchived redo.

But, as I said, *YOU CANNOT CREATE A NEW SET OF CONTROLFILES
WITHOUT THE REDOLOGS*, which implies to me that cloning from
a set of hotbackups is not supported with a controlfile
trace. If this is so, then what is the point of hotbackups?
It might as well be Microsoft Notepad with a SQL interface.
Oracle's tremendous flexibility with datafile manipulation is
negated by the lack of proper management of this single component.

Whether I am cloning or backing up to tape, I want to hold
in my hand all required components to COMPLETELY restore
the database. 99% is not satisfactory.

Quote:
>> I don't understand why people are so evasive on this issue.
> I don't understand why certain people can't simply approach this issue with
> logic and clarity, instead of seeking to do the impossible by utterly
> illogical means.

Yes, at this point, it appears that cloning from hot backups
using a controlfile trace is impossible FROM AN ORACLE
SUPPORT PERSPECTIVE, nevermind that it is demonstrated to
work in practice in at least certain situations (actually, in
every situation that I've seen).

The third Oracle support technician that I spoke with slyly
implied that this method would never cause trouble, so I've
heard answers all over the map on this question, most of them
wrong (as I previously demonstrated). Pardon me for my scepticisim.

Honestly, I don't understand why this process isn't better
addressed in the documentation and better understood by
support personnel. What is the purpose of a hotbackup if you
can't restore it?

In any case, I don't want to argue; let's try a lateral approach.
Assuming that I have a backup controlfile produced by
"ALTER DATABASE BACKUP CONTROLFILE TO '/CONTROL.BAK';", what
is the procedure to recreate a set of online redologs (including
an active thread)? Of course, I would rather not do it this way -
the controlfile trace will allow me to make many changes to the
positioning of the datafiles in one go, including the system
tablespace. But assuming that I am willing to endure the lesser
quality, how are the redologs recreated?



Sat, 18 Oct 2003 22:18:16 GMT
 Online backup: Backup online redologs?


Quote:

> >>>>> See above.  It makes no sense to backup the online redo logs.
They've
> >>>>> already all been backed up to the archives, with the exception of
the
> >>>>> current log.  If you are that concerned, then a switch log file will
> >>>>> cause
> >>>>> that to be archived, and by backing up that last archive, you've got
> >>>>> the
> >>>>> lot.

> >> EXCEPT that you cannot execute the output of 'ALTER DATABASE
> >> BACKUP CONTROLFILE TO TRACE' without the presence of the
> >> redolog files, one of which must be marked as active.

> > You shouldn't take a reply to one person's query out of context.  That
reply
> > was referring to a worry about not having the very last transactions in
a
> > given hot backup, and had nothing to do with cloning a database.

> Then why was it part of your reply?

Because the *original* poster wanted to know whether he should be backing up
online redo logs for the purposes of performing complete recovery.  As such,
that reply makes perfect sense.

Quote:

> I also shouldn't take hot backups of the redo logs,
> but that seems to work pretty well, doesn't it?

Er, no.  We may be at cross purposes here, in which case I haven't a clue
what you are on about.  But hot backups of redo logs are invariably useless
for any purpose you care to mention.  They are internally inconsistent, and
there is nothing that can make them consistent.

Quote:
> The emphasis here stressed the discontinuity of your reply.

Like I say, I haven't got a clue what you are on about.

- Show quoted text -

Quote:
> >> What is really the difference between a restore with a hot
> >> copy of the redo and a recovery of a crashed instance which
> >> corrupts a number of tablespaces?
> >> Wouldn't each situation
> >> introduce the same sort of corruption?

> > Yes, sort of.  Both sorts of files will be internally inconsistent
(corrupt,
> > if you prefer).  Corruption is corruption is corruption.  It's what you
can
> > *do* about the corruption that makes the difference, and there's sod-all
you
> > can do about corruption in redo logs.

> Either it is the same or it is different; you must choose.

I don't have to do anything.  My reply is accurate.  Both sorts of files
will be internally inconsistent.  In your terms, that means they both have
been introduced to 'the same sort of corruption' (inconsistency should be
distinguished from true corruption, but practically they result in the same
grief).  But one you can fix, and the other you can't.

- Show quoted text -

Quote:
> >> It would make much more sense if there were an option to the
> >> CREATE CONTROLFILE statement to create blank redologs. Oracle
> >> should struggle to make this easy; this ambiguity is not useful.

> > Where's the ambiguity?  "You cannot take hot copies of redo logs" is
pretty
> > unambiguous.  And "mirroring (or multiplexing iof you prefer) was
invented
> > precisely so that you would never be in a position of losing all members
of
> > a redo log group" is likewise fairly clear-cut.

> In a cloning situation, *I only want to apply the redo
> that is in the archived logs.* I could care less about
> the online redo - really, absolutely, I don't want it,
> and I don't want to have to worry about it.

In a cloning situation, you are supposed to shut the thing down first.

Quote:
> Actually, I feel mostly the same about backing up to tape.
> I just don't care about unarchived redo.

> But, as I said, *YOU CANNOT CREATE A NEW SET OF CONTROLFILES
> WITHOUT THE REDOLOGS*, which implies to me that cloning from
> a set of hotbackups is not supported with a controlfile
> trace. If this is so, then what is the point of hotbackups?

Er, because they permit total or incomplete data recovery without having to
shut down the database first.  Mirroring is the protection for online redo
logs, not backing up.

Quote:
> It might as well be Microsoft Notepad with a SQL interface.
> Oracle's tremendous flexibility with datafile manipulation is
> negated by the lack of proper management of this single component.

I rather think the problem is your expectations, not Oracle, at least in
this particular regard.  You are trying to conflate the issues of hot backup
and cloning.  They are two separate issues, and the techniques that apply in
the one don't apply in the other.

Quote:
> Whether I am cloning or backing up to tape, I want to hold
> in my hand all required components to COMPLETELY restore
> the database. 99% is not satisfactory.

Define your terms.  You start out wanting to clone a database, and now
you're talking about wanting to do a complete restore.  You can't have it
both ways (or rather, you can, provided you do two different sorts of
things).

If you really want to clone a database without the iffyness of having to
shut it down first, why not investigate transportable tablespaces?

Quote:
> >> I don't understand why people are so evasive on this issue.

> > I don't understand why certain people can't simply approach this issue
with
> > logic and clarity, instead of seeking to do the impossible by utterly
> > illogical means.

> Yes, at this point, it appears that cloning from hot backups
> using a controlfile trace is impossible FROM AN ORACLE
> SUPPORT PERSPECTIVE, nevermind that it is demonstrated to
> work in practice in at least certain situations (actually, in
> every situation that I've seen).

Well, you beat me there.  I've never seen it done hot in practice, and
theory suggests that it isn't possible.  There are, however, hidden
parameters that will allow you to open any database with inconsistent redo
logs.  The particular one escapes me for now, but even if I could remember
it, I wouldn't vouch for the results afterwards.  And the end result would
most definitely NOT be a "clone" in the strict sense of the word.

Quote:
> The third Oracle support technician that I spoke with slyly
> implied that this method would never cause trouble, so I've
> heard answers all over the map on this question, most of them
> wrong (as I previously demonstrated). Pardon me for my scepticisim.

> Honestly, I don't understand why this process isn't better
> addressed in the documentation and better understood by
> support personnel. What is the purpose of a hotbackup if you
> can't restore it?

I really don't understand your problem!  Hot backups are taken to ensure
complete database recovery in the event of media failure, or incomplete
recovery in the event of user error, without the need to shutdown the entire
database whilst taking the backup.  The resulting backup set is internally
inconsistent, but the application of redo will make it consistent.  Since
you cannot apply redo to redo logs, hot copied redo logs will remain
internally inconsistent.  That's perfectly straightforward, and easy to live
with, I should have thought.

You want to clone a database?  Then follow the well-documented cloning
procedures (which involve a cold copy of all datafiles and redo logs).

Quote:
> In any case, I don't want to argue; let's try a lateral approach.
> Assuming that I have a backup controlfile produced by
> "ALTER DATABASE BACKUP CONTROLFILE TO '/CONTROL.BAK';", what
> is the procedure to recreate a set of online redologs (including
> an active thread)?

I may be wrong, and I don't have a database to hand on which to test this
out, but since the commands to create or drop redo logs are all variations
on a theme of 'alter database', I would have thought that they can be issued
in the mount stage -which means you can take your binary version of the
control file, created as you just described, get to the mount stage, add two
new redo log groups, drop all reference to the existing groups, and alter
database open resetlogs.  The trace file method of course relies on the
(consistent) presence of the existing logs, because the controlfile has to
gets its SCN from somewhere.  Using the binary file, you're just performing
boring old incomplete recovery because of a gap in your redo stream.

I wouldn't consider the resulting database a clone, though, because of that
resetlogs.  But your view might differ.

Quote:
>Of course, I would rather not do it this way -
> the controlfile trace will allow me to make many changes to the
> positioning of the datafiles in one go, including the system
> tablespace. But assuming that I am willing to endure the lesser
> quality, how are the redologs recreated?

Alter database add logfile group 3 "blah/blah.rdo' size 1m (repeat for group
4)
Alter database drop logfile group 1 (repeat for group 2)

Regards
HJR



Sat, 18 Oct 2003 23:59:41 GMT
 Online backup: Backup online redologs?

Quote:

> > I also shouldn't take hot backups of the redo logs, > but that seems
> > to work pretty well, doesn't it?
> Er, no.  We may be at cross purposes here, in which case I haven't a
> clue what you are on about.  But hot backups of redo logs are
> invariably useless for any purpose you care to mention.  They are
> internally inconsistent, and there is nothing that can make them
> consistent.

In theory, you are right. However, in practice, you have essentially
admitted that you are wrong.

The Oracle documentation does indeed claim that you should never take hot
backups of the online redologs because of the danger of running over the
'end of backup' marker in a log replay.

However, in my original post, I did a successful 'RECOVER AUTOMATIC
DATABASE' on a restore of a hot backup with hot copies of the redologs.
You admit in your original reply that the poster might have "gotten away"
with it, even if it was a "lousy technique." The fact that it worked at
all demonstrates that it can work in practice.

As I see it, the 'end of backup' marker has been archived if an "ALTER
SYSTEM ARCHIVE LOG CURRENT" has been run (after taking the last tablespace
out of backup mode, but before using OS utilities to copy). Hopefully, the
risk of this circumstance is minimal.

Quote:
> > Either it is the same or it is different; you must choose.
> I don't have to do anything.  My reply is accurate.  Both sorts of files
> will be internally inconsistent.  In your terms, that means they both have
> been introduced to 'the same sort of corruption' (inconsistency should be
> distinguished from true corruption, but practically they result in the same
> grief).  But one you can fix, and the other you can't.

I'm not so sure.

As I understand it, redologs are written sequentially. Transactions with
SCNs are written in a linear manner to the redologs, then applied to the
datafiles.

If LGWR were to die for any reason, leaving an incomplete SCN recorded in
the redologs, then I assume that upon the next instance startup, RECO (or
whatever process is doing the recovery) is smart enough to recognize the
incomplete record in the redologs, check that the datafiles do not record
that SCN, then essentially do an fseek() in the active redolog thread and
write over that space.

I would assume that this sort of thing happens quite regularly in
"SHUTDOWN ABORT" situations.

This may be a rather simplistic view, but the same sort of conditions
would exist with a hot copy of the redologs. If a transaction was
in-flight, LGWR might have only partially recorded it, in which case it
would not have been written to the tablespaces and could be removed with
impunity.

p.s. My books do say that the RECO process is responsible for recovery,
     but when I run "top" during a "RECOVER AUTOMATIC DATABASE", I just
     see the DBWR/DBW0 (I'm still using some Oracle 7) consuming the cpu.
     I wish I knew what process was truly responsible for recovery
     operations.

Quote:
> > In a cloning situation, *I only want to apply the redo
> > that is in the archived logs.* I could care less about
> > the online redo - really, absolutely, I don't want it,
> > and I don't want to have to worry about it.
> In a cloning situation, you are supposed to shut the thing down first.

That is tremendously inconvenient for me. Let me explain.

The real reason that I got interested in backups is that our 3rd party
backup software failed (BMC obacktrack - awful stuff, at least the
versions that ran with Oracle 7). I wrote a script to do the backup the
"old-fashioned" way; I've attached it.

Another of my Oracle 7 systems needs a production-to-development copy
every couple of months or so. I can only do cold backups on the weekend. I
hate coming in on the weekends. I've found and demonstrated a cloning
method using hot backups. It works every time I've tried. This was where I
first learned that you must have a hot backup of the redologs when
recovering an instance from hot backups when rebuilding the controlfile.

And then one of my Oracle 8.0.5 users on yet another system heard that I
had a generic hotbackup script that could do instance recovery and
complained about their twice-weekly cold backups, so the script gets used
yet again. It's rather generic now, and quite polished if I do say so
myself (rather spiffy, actually). I've tested recoveries with the hot
redologs about 20 times so far; I've yet to have a problem.

I need to clone from time to time. I like to do it with hot backups. Here
is a way that it can be done. If you have a better way, I'm all ears.

And yes, I am also including a standard controlfile backup in the orthodox
and endorsed manner, just in case things go awry.

Quote:
> > But, as I said, *YOU CANNOT CREATE A NEW SET OF CONTROLFILES
> > WITHOUT THE REDOLOGS*, which implies to me that cloning from
> > a set of hotbackups is not supported with a controlfile
> > trace. If this is so, then what is the point of hotbackups?
> Er, because they permit total or incomplete data recovery without having to
> shut down the database first.  Mirroring is the protection for online redo
> logs, not backing up.

That's just daft. You should be able to back up everything.

Quote:
> > It might as well be Microsoft Notepad with a SQL interface.
> > Oracle's tremendous flexibility with datafile manipulation is
> > negated by the lack of proper management of this single component.
> I rather think the problem is your expectations, not Oracle, at least in
> this particular regard.  You are trying to conflate the issues of hot backup
> and cloning.  They are two separate issues, and the techniques that apply in
> the one don't apply in the other.

You should be able to clone without shutting down. Practically, you can.
Oracle should endorse a method for doing so. It is a GREAT flaw.

Quote:
> > Whether I am cloning or backing up to tape, I want to hold
> > in my hand all required components to COMPLETELY restore
> > the database. 99% is not satisfactory.
> Define your terms.  You start out wanting to clone a database, and now
> you're talking about wanting to do a complete restore.  You can't have it
> both ways (or rather, you can, provided you do two different sorts of
> things).

I don't agree, but now you can see why. I have it both ways. I just want a
few voices saying that it will work in general. Voices from Oracle support
will be best, but hey, I'm not picky.

Quote:
> If you really want to clone a database without the iffyness of having to
> shut it down first, why not investigate transportable tablespaces?

Oracle 7, dude.

Quote:
> > > I don't understand why people are so evasive on this issue.
> > > I don't understand why certain people can't simply approach this issue
> > > with
> > > logic and clarity, instead of seeking to do the impossible by utterly
> > > illogical means.
> > Yes, at this point, it appears that cloning from hot backups
> > using a controlfile trace is impossible FROM AN ORACLE
> > SUPPORT PERSPECTIVE, nevermind that it is demonstrated to
> > work in practice in at least certain situations (actually, in
> > every situation that I've seen).
> Well, you beat me there.  I've never seen it done hot in practice, and
> theory suggests that it isn't possible.  There are, however, hidden
> parameters that will allow you to open any database with inconsistent redo
> logs.  The particular one escapes me for now, but even if I could remember
> it, I wouldn't vouch for the results afterwards.  And the end result would
> most definitely NOT be a "clone" in the strict sense of the word.

I didn't use any hidden parameters, incantations, {*filter*} sacrifices,
secret compacts or covenants, or any other form of divine intervention.

Do you want the screen captures?

- Show quoted text -

Quote:
> > The third Oracle support technician that I spoke with slyly
> > implied that this method would never cause trouble, so I've
> > heard answers all over the map on this question, most of them
> > wrong (as I previously demonstrated). Pardon me for my scepticisim.
> > Honestly, I don't understand why this process isn't better
> > addressed in the documentation and better understood by
> > support personnel. What is the purpose of a hotbackup if you
> > can't restore it?
> I really don't understand your problem!  Hot backups are taken to ensure
> complete database recovery in the event of media failure, or incomplete
> recovery in the event of user error, without the need to shutdown the entire
> database whilst taking the backup.  The resulting backup set is internally
> inconsistent, but the application of redo will make it consistent.  Since
> you cannot apply redo to redo logs, hot copied redo logs will remain
> internally inconsistent.  That's perfectly straightforward, and easy to live
> with, I should have thought.

As far as I know, redo has a more cohesive structure and linear order
which makes it somewhat less delicate than the standard datafiles. More
can be assumed about a redolog in the recovery process than about the
other types of Oracle data.

But all of this verbage is just hand-waving; the proof is in the pudding.

Quote:
> You want to clone a database?  Then follow the well-documented cloning
> procedures (which involve a cold copy of all datafiles and redo logs).

Umm, I like my weekends, thanks.

- Show quoted text -

Quote:
> I may be wrong, and I don't have a database to hand on which to test this
> out, but since the commands to create or drop redo logs are all variations
> on a theme of 'alter database', I would have thought that they can be issued
> in the mount stage -which means you can take your binary version of the
> control file, created as you just described, get to the mount stage, add two
> new redo log groups, drop all reference to the existing groups, and alter
> database open resetlogs.  The trace file method of course relies on the
> (consistent) presence of the existing logs, because the controlfile has to
> gets its SCN from somewhere.  Using the binary file, you're just performing
> boring old

...

read more »



Mon, 20 Oct 2003 05:09:13 GMT
 Online backup: Backup online redologs?


Quote:

> > > I also shouldn't take hot backups of the redo logs, > but that seems
> > > to work pretty well, doesn't it?

> > Er, no.  We may be at cross purposes here, in which case I haven't a
> > clue what you are on about.  But hot backups of redo logs are
> > invariably useless for any purpose you care to mention.  They are
> > internally inconsistent, and there is nothing that can make them
> > consistent.

> In theory, you are right. However, in practice, you have essentially
> admitted that you are wrong.

> The Oracle documentation does indeed claim that you should never take hot
> backups of the online redologs because of the danger of running over the
> 'end of backup' marker in a log replay.

That's really the least of your worries.  If you are doing O/S backups, you
have no control over what bit of the redo log is being copied at what time.
If you happen to be writing to the bit as it is being read, it will be
useless for subsequent use.  Accordingly, if you are not writing to the logs
at all, or if the level of writes is low, and you happen to get lucky, I'm
sure you'll get away with it.  Not exactly a strategy I'd be relying on,
anyway.  And *that's* why Oracle support won't endorse the backing up of hot
redo logs... it's purely a matter of chance whether it works or not.

Quote:
> However, in my original post, I did a successful 'RECOVER AUTOMATIC
> DATABASE' on a restore of a hot backup with hot copies of the redologs.
> You admit in your original reply that the poster might have "gotten away"
> with it, even if it was a "lousy technique." The fact that it worked at
> all demonstrates that it can work in practice.

That's rather like saying "Gee, I walked across the lanes of a freeway with
a blindfold and didn't get hit once!... this must be a perfectly respectable
way of crossing a road".  It isn't.

 > As I see it, the 'end of backup' marker has been archived if an "ALTER

Quote:
> SYSTEM ARCHIVE LOG CURRENT" has been run (after taking the last tablespace
> out of backup mode, but before using OS utilities to copy). Hopefully, the
> risk of this circumstance is minimal.

Again, I may be wrong, (and now I know you working on 7, I won't press the
point), but there's a log switch induced by that command, and that therefore
makes the 'current' log just archived INactive -and a hot copy of an
inactive log is, effectively, a cold copy.  No problems of inconsistency,
therefore.

- Show quoted text -

Quote:
> > > Either it is the same or it is different; you must choose.

> > I don't have to do anything.  My reply is accurate.  Both sorts of files
> > will be internally inconsistent.  In your terms, that means they both
have
> > been introduced to 'the same sort of corruption' (inconsistency should
be
> > distinguished from true corruption, but practically they result in the
same
> > grief).  But one you can fix, and the other you can't.

> I'm not so sure.

> As I understand it, redologs are written sequentially. Transactions with
> SCNs are written in a linear manner to the redologs, then applied to the
> datafiles.

> If LGWR were to die for any reason, leaving an incomplete SCN recorded in
> the redologs, then I assume that upon the next instance startup, RECO (or
> whatever process is doing the recovery) is smart enough to recognize the
> incomplete record in the redologs, check that the datafiles do not record
> that SCN, then essentially do an fseek() in the active redolog thread and
> write over that space.

You are assuming that the thing can be read.  If the O/S decided to grab a
chunk of the current log as it was being written to, the chances of it being
able to interpret the thing at all are slim.

Quote:
> I would assume that this sort of thing happens quite regularly in
> "SHUTDOWN ABORT" situations.

> This may be a rather simplistic view, but the same sort of conditions
> would exist with a hot copy of the redologs. If a transaction was
> in-flight, LGWR might have only partially recorded it, in which case it
> would not have been written to the tablespaces and could be removed with
> impunity.

As I say, the issue is not whether the contents of the log can be dealt
with, but whether you can read the contents in the first place.

Anyway: I'll leave you happily backing up hot redo logs and getting away
with it.  This doesn't seem the appropriate forum to debate the merits of
such an approach.

Regards
HJR

[Snip]



Mon, 20 Oct 2003 14:49:48 GMT
 Online backup: Backup online redologs?
i have to chime in here.  maybe i missed this important
point in your conversation, and at the risk of repeating
what has already been said...

the reason that Oracle recommends NOT copying the
online redo logs as part of a hot backup procedure is
simply put...

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.

the point is, you should never need to restore a copy of
an online redo log prior to a recovery operation.

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!

since the control files and online redo logs are so critical in
the recovery of an Oracle database, we protect from data
loss using disk mirroring, multiple control files (on separate
disk drives) and multiple members (on separate drives) in
each logfile group.

the ONLY time that i have found a need for a backup copy
of the online redo log files was specifically in setting up an
initial "clone" database as a replication target using Quest
SharePlex Overdrive.  note that this was NOT for recovery
of an existing database, but for setting up a new database.

'nuf said.



Quote:




> > > > I also shouldn't take hot backups of the redo logs, > but that seems
> > > > to work pretty well, doesn't it?

> > > Er, no.  We may be at cross purposes here, in which case I haven't a
> > > clue what you are on about.  But hot backups of redo logs are
> > > invariably useless for any purpose you care to mention.  They are
> > > internally inconsistent, and there is nothing that can make them
> > > consistent.

> > In theory, you are right. However, in practice, you have essentially
> > admitted that you are wrong.

> > The Oracle documentation does indeed claim that you should never take
hot
> > backups of the online redologs because of the danger of running over the
> > 'end of backup' marker in a log replay.

> That's really the least of your worries.  If you are doing O/S backups,
you
> have no control over what bit of the redo log is being copied at what
time.
> If you happen to be writing to the bit as it is being read, it will be
> useless for subsequent use.  Accordingly, if you are not writing to the
logs
> at all, or if the level of writes is low, and you happen to get lucky, I'm
> sure you'll get away with it.  Not exactly a strategy I'd be relying on,
> anyway.  And *that's* why Oracle support won't endorse the backing up of
hot
> redo logs... it's purely a matter of chance whether it works or not.

> > However, in my original post, I did a successful 'RECOVER AUTOMATIC
> > DATABASE' on a restore of a hot backup with hot copies of the redologs.
> > You admit in your original reply that the poster might have "gotten
away"
> > with it, even if it was a "lousy technique." The fact that it worked at
> > all demonstrates that it can work in practice.

> That's rather like saying "Gee, I walked across the lanes of a freeway
with
> a blindfold and didn't get hit once!... this must be a perfectly
respectable
> way of crossing a road".  It isn't.

>  > As I see it, the 'end of backup' marker has been archived if an "ALTER
> > SYSTEM ARCHIVE LOG CURRENT" has been run (after taking the last
tablespace
> > out of backup mode, but before using OS utilities to copy). Hopefully,
the
> > risk of this circumstance is minimal.

> Again, I may be wrong, (and now I know you working on 7, I won't press the
> point), but there's a log switch induced by that command, and that
therefore
> makes the 'current' log just archived INactive -and a hot copy of an
> inactive log is, effectively, a cold copy.  No problems of inconsistency,
> therefore.

> > > > Either it is the same or it is different; you must choose.

> > > I don't have to do anything.  My reply is accurate.  Both sorts of
files
> > > will be internally inconsistent.  In your terms, that means they both
> have
> > > been introduced to 'the same sort of corruption' (inconsistency should
> be
> > > distinguished from true corruption, but practically they result in the
> same
> > > grief).  But one you can fix, and the other you can't.

> > I'm not so sure.

> > As I understand it, redologs are written sequentially. Transactions with
> > SCNs are written in a linear manner to the redologs, then applied to the
> > datafiles.

> > If LGWR were to die for any reason, leaving an incomplete SCN recorded
in
> > the redologs, then I assume that upon the next instance startup, RECO
(or
> > whatever process is doing the recovery) is smart enough to recognize the
> > incomplete record in the redologs, check that the datafiles do not
record
> > that SCN, then essentially do an fseek() in the active redolog thread
and
> > write over that space.

> You are assuming that the thing can be read.  If the O/S decided to grab a
> chunk of the current log as it was being written to, the chances of it
being
> able to interpret the thing at all are slim.

> > I would assume that this sort of thing happens quite regularly in
> > "SHUTDOWN ABORT" situations.

> > This may be a rather simplistic view, but the same sort of conditions
> > would exist with a hot copy of the redologs. If a transaction was
> > in-flight, LGWR might have only partially recorded it, in which case it
> > would not have been written to the tablespaces and could be removed with
> > impunity.

> As I say, the issue is not whether the contents of the log can be dealt
> with, but whether you can read the contents in the first place.

> Anyway: I'll leave you happily backing up hot redo logs and getting away
> with it.  This doesn't seem the appropriate forum to debate the merits of
> such an approach.

> Regards
> HJR

> [Snip]



Fri, 24 Oct 2003 13:50:33 GMT
 Online backup: Backup online redologs?

Quote:

> 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.

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.

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. 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.

Number three:

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

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, but
maybe I ought to look into forcing the first log group to go active before
I take the hot backups.

-------------------------------------------------------------------------------
Charles J. Fisher - Consultant
Alcoa Davenport Works
(319) 459-2512



Sun, 26 Oct 2003 04:05:46 GMT
 Online backup: Backup online redologs?


Quote:

> > 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?

Quote:
> 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.

Quote:
> 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.

Quote:
>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.

Quote:

> 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.

Quote:

> 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.

Quote:
>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

- Show quoted text -

Quote:
> --------------------------------------------------------------------------
-----
> Charles J. Fisher - Consultant
> Alcoa Davenport Works
> (319) 459-2512



Sun, 26 Oct 2003 05:28:17 GMT
 Online backup: Backup online redologs?

Quote:

> > ...but assuming that I am willing to endure the lesser
> > quality, how are the redologs recreated?
> I may be wrong, and I don't have a database to hand on which to test this
> out, but since the commands to create or drop redo logs are all variations
> on a theme of 'alter database', I would have thought that they can be issued
> in the mount stage -which means you can take your binary version of the
> control file, created as you just described, get to the mount stage, add two
> new redo log groups, drop all reference to the existing groups, and alter
> database open resetlogs.  The trace file method of course relies on the
> (consistent) presence of the existing logs, because the controlfile has to
> gets its SCN from somewhere.  Using the binary file, you're just performing
> boring old incomplete recovery because of a gap in your redo stream.
> I wouldn't consider the resulting database a clone, though, because of that
> resetlogs.  But your view might differ.
> Alter database add logfile group 3 "blah/blah.rdo' size 1m (repeat for group
> 4)
> Alter database drop logfile group 1 (repeat for group 2)

Hey, this doesn't work.

I have restored everything, but I have no redologs.

I can mount the database, but here is what happens:

$ svrmgrl

Oracle Server Manager Release 3.0.5.0.0 - Production

(c) Copyright 1997, Oracle Corporation.  All Rights Reserved.

Oracle8 Enterprise Edition Release 8.0.5.0.0 - Production
With the Partitioning and Objects options
PL/SQL Release 8.0.5.0.0 - Production

SVRMGR> connect internal
Connected.
SVRMGR> startup mount
ORACLE instance started.
Total System Global Area                         26148952 bytes
Fixed Size                                          49240 bytes
Variable Size                                     5349376 bytes
Database Buffers                                 20480000 bytes
Redo Buffers                                       270336 bytes
Database mounted.
SVRMGR> alter database drop logfile group 1;
Statement processed.
SVRMGR> alter database drop logfile group 2;
Statement processed.
SVRMGR> alter database drop logfile group 3;
Statement processed.
SVRMGR> alter database drop logfile group 4;
Statement processed.
SVRMGR> alter database drop logfile group 5;
alter database drop logfile group 5
*
ORA-01567: dropping log 5 would leave less than 2 log files in thread 1
ORA-00312: online log 5 thread 1:
'/pkg/prdcrm/oradata/prdcrm/redoprdcrm05.log'
SVRMGR> alter database add logfile group 1
        '/pkg/prdcrm/oradata/prdcrm/redoprdcrm01.log' size 10M;
Statement processed.
SVRMGR> alter database drop logfile group 5;
Statement processed.
SVRMGR> alter database add logfile group 2
        '/pkg/prdcrm/oradata/prdcrm/redoprdcrm02.log' size 10M;
Statement processed.
SVRMGR> alter database drop logfile group 6;
alter database drop logfile group 6
*
ORA-01623: log 6 is current log for thread 1 - cannot drop
ORA-00312: online log 6 thread 1:
'/pkg/prdcrm/oradata/prdcrm/redoprdcrm06.log'

Of course, I cannot force a log switch in this state.

What do I do? Using the hotbackup online redologs seems to be the ONLY way
to do a complete recovery. If I couldn't get the online redologs for
whatever reason, I couldn't recover the database!

-------------------------------------------------------------------------------
Charles J. Fisher - Consultant
Alcoa Davenport Works
(319) 459-2512



Sun, 26 Oct 2003 05:38:45 GMT
 
 [ 25 post ]  Go to page: [1] [2]

 Relevant Pages 

1. online backup of the redologs

2. ebu backup and online redo log backup

3. Online, Online, Online

4. Online Backups?

5. Problem in Online database backup

6. Full Database Backup During Online Possible!

7. SQL 7 active online backup

8. Backups with Users online (SQL Server 6.5)

9. Online backups and replication..

10. Online backups corrupt or inconsistent?

11. Problems with Veritas Online Backup of SQL 7 Databases

12. Urgent: Online Full Database Backup in SQL 2000!


 
Powered by phpBB® Forum Software