Re: Why does Orcl generate REDO logs in NOARCHIVE mode?

I'm going to be brave and try and reply to your comments.

Firstly, I believe you have spotted an issue that I had not considered. However, currently I don't beleive it is a stopper. Please prove me wrong if you have the time.

The issue you highlight is only a problem if the data datafiles SCN is greater than (i.e. later) any of the rollback datafiles SCN. In this case, as the RDBMS reads through the redo log it will get to a point were the rollback segments need rebuilding. Because the data datafiles may be later than the rollback it may well not contain data required to rebuild the rollback segment and thus can't. This is obviously not acceptable.

In the example I gave that you comment on, given the above rule, the information has not been lost from the system because it will be in the rollback segments. The roll back segments will only need rebuilding from the point they reached at the time of the crash. If the rollback datafiles always have a later SCN than the data datafiles at the point the rollback needs to be rebuilt the data datafiles are inline and it can be guaranteed that the data datafiles will contain all the information required to rebuild the rollback segments (I think).

So in short I believe my suggestion works if, at the time of recovery, the rollback datafiles are not older than the data datafiles.

For the RDBMS to make sure this is the case during normal running it must ensure the rollback segment datafiles are always flushed at the same time (or more often) than the data datafiles (i.e. the same or later SCN).

The most obvious example I can think of were the rollback segment datafiles could be older than the data datafiles is a hot backup. Obviously each datafile can be backed up in any order. When restored the rollback segment datafiles might well be older than the data datafiles and hence the problem described above. My suggestion would require the user to make sure the rollback segment datafiles were always backed up after the data datafiles. This I believe would be the one compromise from the users perspective. It may be unacceptable, I don't know. I do understand some on the scenarios this may lead to.

The cold backup and roll forward is the simplest scenario and I believe there is no problem with this. This assumes all datafiles have the same SCN at the point of restore.

Of course this is just one of many scenarios, all of which make my brain ache. If anyone knows any other scenarios then please shoot.

If it is possible then the goal is a good reduction in redo log space usage. What I am not sure about is whether the saving is worth the effort. Of course there may be lots of small technical reason to do with the management of the rollback segments that make my suggestion a non starter.

I still think I've missed the obvious but just can't see it.

Thankyou for your time.

Thomas Kyte wrote:

> A copy of this was sent to Mike Burden <michael.burden_at_capgemini.co.uk>
> (if that email address didn't require changing)
> On Wed, 24 Mar 1999 15:58:56 +0000, you wrote:
>
> >I agree with all your points about switching off redo and rollback, but still think
> >there are some occasions when it might be useful. I agree you must know what you're
> >doing and accept you will have to restore to a cold backup, but it would be nice to
> >have the choice. Although perhaps we already have too many choices so I'm not
> >compalining.
> >
> >I also agree with not using a single area for roll back and redo because, as you say,
> >it does have advantages.
> >
> >However, are you sure that you need to log rollback. I say this again because
> >consider the following example:
> >
> >Table A column A is updated:
> >
> >XXXX to YYYY
> >XXXX gets written to the rollback.
>
> xxxx is written to rollback segments which are buffered in the buffer cache like
> all other segments (good thing they are -- consistent reads that use the
> rollback segment need this). So XXXX is not on disk generally its in the buffer
> cache.
>
> >YYYY gets written to the redo.
>
> yyyy gets written to redo on the commit.
>
> >and XXXX gets written to the redo. (This is the bit I think is not needed)
>
> this XXXX does in the same IO typically with the YYYY but the XXXX written to
> rollback is still buffered (see digression in previous post about why you want
> to write to redo on a commit but not do scattered IO all over the place to
> datafiles)
>
> >
> >Lets now restore the database and roll forward as we would expect.
> >
> >table contains
> >XXXX
> >
> >YYYY is read from the redo and applied to the datafile.
> >XXXX is read from the redo and is applied to the rollback.
> >
> >Lets say this is 2 reads and 2 writes
> >
> >However consider the alternative were XXXX was not written to redo log.
> >
> >table contains
> >
> >XXXX
> >
> >YYYY is read from the redo log. Before it is applied the previous value on the
> >datafile is read XXXX.
> >XXXX is applied to the rollback.
> >YYYY is then applied to the datafile.
> >
> >This too is two reads and two writes.
> >
>
> but your simple example falls apart in almost every case. The reason is
> checkpointing and log switches. Lets say you have 2-1 meg log files (2meg total
> log). You are updating 3meg of data. updating XXXX to YYYY is the FIRST row
> you updated. It was written to redo log #1. partway through the transaction we
> switched from log #1 to log #2 -- this fires a checkpoint (so that blocks that
> were modified and recorded in redo log#1 will be flushed to disk). We now start
> filling redo #2 with redo. Bam -- system crashes.
>
> We goto recover now. Lets say we did not record rollback to redo as you
> suggest. Unfortunately for us -- the checkpoint that had been in progress when
> we switched logfiles had a chance to flush to disk our first update from XXXX to
> YYYY. So, in your scenario:
>
> - yyyy is read from the redo log. before it is applied the 'previous' value on
> the datafile is read YYYY <<<<<------------- Herein lies the problem -- xxxx
> no long exists in the datafile. XXXX no longer exists anywhere in the system in
> fact.

Does it not exist in the rollback. Assuming the rollback was flushed at the same time as the datafiles.

>
>
> -- yyyy is applied to the rollback (since you are doing rollback from datafiles)
>
> You cannot rollback anymore-- your database is broken, this transaction cannot
> be rolled back anymore, nor can it be rolled forward. You *need* to have
> rollback in redo. Rollback is buffered and datafiles are fuzzy -- you cannot
> rely on the datafiles to have the before image.... Otherwise we would have to
> stop all activity during a checkpoint and get a consistent picture of the
> datafiles (and never have them be 'fuzzy' which they almost *always* are).
>
> >Hence we get to the same position but without rollback being written to the redo.
> >
>
> no we couldn't... checkpoints, wrapping logfiles (eg: you have 2meg of log and
> do 3meg of updates -- some of the redo disappears from the online logs)
>
> >You could also make more optimizations like buffering up rollback so if you get to
> >the commit in the redo log you can throw away the rollback updates as they are not
> >needed. They are only needed if you stop rolling forward at a point in time before
> >the commit and then need to rollback.
> >
>
> but they are needed for consistent reads -- ORA-1555 would happen in many many
> more cases if we did that.

I meant to say this optimization is only done during the recovery phase so no selects are taking place if I understand your point correctly. It may well do optimizations like this currently. My point is there is no reason to write rollback rebuild information during a roll forward for a given transaction if the commit is reached. In most cases I presume the information for transactions is committed. It could simply store it in memory and throw it away when the commit was reached. If it gets to big it simply writes it to the rollback.

>
>
> don't forget rollback isn't just for rolling back -- its an integral part of our
> consistent read mechanism -- people read rollback (prevents LOCKs from blocking
> reads so readers are not blocked by writers -- try to do a select count(*) from
> T in another database when there is an outstanding UPDATE on the table -- one
> thats not committed or rolledback -- it'll block).
>
> >I can so no real technical reason why this wouldn't work however, I may have missed
> >something really obvious in which case please say, anyone.
> >
> >I know the change would not be trivial but it may save a good percentage of the redo.
> >Would this not be beneficial.
> >
> >When I have run a test to update a 2K columns it does seem to write 4K to the redo
> >log. However, it is not easy to confirm this one way or the other.
> >
>
> redo of the data (2k) + redo of the rollback (2k) = 4k done in one large
> sequential write as opposed to randon seeks and writes. Multiply your single
> session by dozens or hundreds and the payoff is there big time.
>
> >Thomas Kyte wrote:
> >
> >> A copy of this was sent to Mike Burden <michael.burden_at_capgemini.co.uk>
> >> (if that email address didn't require changing)
> >> On Wed, 24 Mar 1999 11:33:27 +0000, you wrote:
> >>
> >> >Agreed that redo logs aid recovery, but surely they are not essential. If you
> >> >accept that in the event of a failure, you will restore to a cold backup, why do
> >>
> >> The redo logs are used to recover from things like a POWER failure, software
> >> failure, database crash, etc. They are only "essential" if you don't want to
> >> always have to go back to your last full cold backup after a power failure,
> >> system failure, software failure of some sort (eg: they are pretty essential).
> [snip]
>
> Thomas Kyte
> tkyte_at_us.oracle.com
> Oracle Service Industries
> Reston, VA USA
>
> --
> http://govt.us.oracle.com/ -- downloadable utilities
>
> ----------------------------------------------------------------------------
> Opinions are mine and do not necessarily reflect those of Oracle Corporation
Received on Fri Mar 26 1999 - 03:19:15 CST