Re: Split Blocks on Instance Crash

No problem,

When modify a table with normal insert/update/delete, then

1. redo for corresponding UNDO block change for this update is placed in redolog buffer
2. corresponding UNDO block is changed
3. redo for DATA block change for this update is placed in redolog buffer
4. DATA block itself is changed

Note that during buffer cache block changes we record the RBA (redo byte address in redo stream) of last change in buffer headers (can be seen from X$BH). RBA is an address in redo stream, it can uniquely identify a redo record/change vector in redologs.

As long as we don't commit (with sync option) here, no redo has to be written onto disk.

1. If instance crashes now, we don't have neither the redo nor datablock changes persisting, thus no inconsistencies
2. If instance crashes after LGWR has written the redo to disk, but DBWR hasn't written the datablocks, then during instance recovery we:
3. read old versions of datablocks from disk
4. apply redo to the old versions - note that Oracle guarantees every change vector between old version on disk and current version until commit to be in online redologs - e.g. it doesn't allow any redolog to be overwritten before all corresponding datablocks are checkpointed to disk.
5. this above is called cache recover - we make sure that all datafiles are physically consistent. After this point if we still have inconsistent/fractured blocks, we have encountered a hardware fault or software bug
6. database can be opened now, if any server processes see uncommitted datablocks, they can roll these back on their own - leaving the blocks they don't need for SMON (this is called fast start rollback if memory serves correctly)
7. SMON goes through undo segments to see whether there are uncommitted transactions (the committed transactions have a commit record at their end in redologs) and rolls those back (or hands those over to parallel slaves to roll back depending on transaction sizes)
8. It is not possible to have a situation where DBWR writes modified buffers back to disk before their redo is written. This is called write-ahead logging protocol and is mentioned in documentation. It is achieved by marking RBA of latest change to buffer header (as mentioned above) and DBWR checks whether redo up to max(RBA) in write block batch is already on disk (from memory structure underlying X$KCCCP). If it's not, DBWR will signal LGWR and won't write those buffers to disk until LGWR acknowledges that redo is on disk.

So, as long as the foundations on what Oracle relies (OS, hardware) works properly and we don't hit any bugs (and unless I'm wrong) Oracle ALWAYS guarantees the blocks to be consistent after cache recovery in case of instance failure. It doesn't matter whether only half of DBWR's block write succeeded, or 99% or 1%, Oracle's recovery mechanisms are conceptually reliable.

All of above was a simplification, especially from 10g where we can have some redo log buffers in shared pool (private strands), some undo data cached in shared pool (in-memory undo), out-of order physical redo (multiple redolog strands and redo allocation latches), but again, I'm sure Oracle has based their recovery mechanisms on solid theoretical foundations here - no vulnerabilities on race conditions nor dependencies on which order you execute your stuff.

Tanel.

• Original Message ----- From: "Charlotte Hammond" To: Sent: Thursday, December 22, 2005 11:02 AM Subject: Re: Split Blocks on Instance Crash

> Hi Tanel,
>
> Very sorry - I'm being slow today - I still don't get
> it! Why can you always read the previous image of the
> block from disk? The hypothesis was that DBW was in
> the middle of writing when the instance crashed,
> leaving only a current copy of the block on disk - a
> split one since DBW did not complete the write.
>
> Thanks
> Charlotte
>
>>Hi,
>>
>>Because in case of instance crash, you can always
> read the previous image of
>>block from disk and all subsequent changes to this
> block can be read from
>>online logfiles, in media failure case you might not
> be able to do so.
>>
>>Tanel.
>>
>>----- Original Message -----
>>From: "Charlotte Hammond"
>
>>To:
>>Sent: Thursday, December 22, 2005 9:31 AM
>>Subject: Re: Split Blocks on Instance Crash
>>
>>
>>>
>>> Thanks David - but why is this different from the
>>> backup situation? You would have the (archived)
> redo
>>> available then too, but you must still use
> BEGIN/END
>>> BACKUP to avoid split blocks.
>>>
>>> Charlotte
>>>
>>>
>>>
>>> --- David Sharples wrote:
>>>
>>>> because the information is available from redo /
>>>> undo and can easily be
>>>> replayed in case of a failure
>>>>
>>>> On 12/22/05, Charlotte Hammond
>>>> wrote:
>>>> >
>>>> > Hi All,
>>>> >
>>>> > Why does instance crash recovery not encounter
> the
>>>> > split blocks that you might get doing a backup
>>>> without
>>>> > BEGIN/END BACKUP?
>>>> >
>>>>
>>>
>>>
>>> __________________________________________________
>>> Do You Yahoo!?
>>> Tired of spam? Yahoo! Mail has the best spam
> protection around
>>> http://mail.yahoo.com
>>> --
>>> http://www.freelists.org/webpage/oracle-l
>>>
>>>
>>
>>--
>>http://www.freelists.org/webpage/oracle-l
>>
>>
>>
>
> __________________________________________________
> Do You Yahoo!?
> Tired of spam? Yahoo! Mail has the best spam protection around
> http://mail.yahoo.com
> --
> http://www.freelists.org/webpage/oracle-l
>
>

--
http://www.freelists.org/webpage/oracle-l