RE: What is the purpose of segment level checkpoint before DROP/TRUNCATE of a table?
- From: "Mark W. Farnham" <mwf@xxxxxxxx>
- To: <saibabu_d@xxxxxxxxx>, "'Jonathan Lewis'" <jonathan@xxxxxxxxxxxxxxxxxx>, "'free'" <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 7 Jul 2011 12:28:01 -0400
You seem to think that buffers from the primary system are written on the
standby system. Only the block changes recorded in the redo log are written
and whether a buffer was dirty or clean or ever written to the database file
on the primary is of no concern at all the the recovery model of physical
standby.
The truncate DDL is guaranteed to occur in the correct order, since it
contains an implicit commit. No redo regarding the object that is already
gone on the primary can be present in a valid redo stream after the
truncate.
Regards,
mwf
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Saibabu Devabhaktuni
Sent: Thursday, July 07, 2011 12:07 PM
To: Jonathan Lewis; free
Subject: Re: What is the purpose of segment level checkpoint before
DROP/TRUNCATE of a table?
Hi Jonathan,
If we have a table with 100,000 dirty buffers on primary database.
1) Let's say 50,000 dirty buffers were already written to disk by DBWR on
primary.
2) At time T1, truncate table command issued on primary.
3) If Oracle had this feature to not write buffers as part of truncate, then
at time T2 Oracle finished marking buffers not to write.
4) At time T3, truncate operation is fully completed on primary.
5) On the standby, configure very small buffer cache size (i.e. can only fit
10,000 buffers).
6) As the redo up to time T1 applied on the standby, only 10,000 dirty
buffers can stay in the cache and rest of the blocks will be written to disk
as soon as redo is applied.
7) Redo as of time T2, will not really mark buffers as not to write, as most
of them are already on disk. This is when blocks on the standby is going to
be different from the primary as of same checkpoint time.
Data loss scenario:
1) If there was a system or datafile level checkpoint finished on primary
between time T2 and T3.
2) If primary instance crashes between time T2 and T3, but after the above
checkpoint was completed.
3) Above checkpoint would have skipped writing buffers marked as not to
write and hence on disk image is not current.
4) When instance is starting up, crash recovery starts as of redo from the
most recent checkpoint.
5) After the completion of crash recovery, truncate never really finished,
but the data in the dirty blocks as of time T1 is missing.
Thanks,
Sai
http://sai-oracle.blogspot.com
_____
From: Jonathan Lewis <jonathan@xxxxxxxxxxxxxxxxxx>
To: saibabu_d@xxxxxxxxx; free <oracle-l@xxxxxxxxxxxxx>
Sent: Wed, July 6, 2011 10:24:39 PM
Subject: Re: What is the purpose of segment level checkpoint before
DROP/TRUNCATE of a table?
Sai,
I started writing a long complicated note to see if I could show why there
ought to be no problems with the standby and primary being out of synch at
this point - but it got too complicated because it was trying to cover too
many options. So I'd like to do this the other way round, since you may
already have worked this out. Can you supply the detailed sequence of events
where it matters - I can't but my model may be missing something that you
know about.
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
----- Original Message ----- From: "Saibabu Devabhaktuni"
<saibabu_d@xxxxxxxxx>
To: "free" <oracle-l@xxxxxxxxxxxxx>
Sent: Wednesday, July 06, 2011 10:33 PM
Subject: Re: What is the purpose of segment level checkpoint before
DROP/TRUNCATE of a table?
writes.
>
> => Yes redo gets applied on the physical standby, but the dirty blocks
which
> were marked as *not to write* on the primary can get flushed out to disk
with
> all the dirty changes on the standby before "*not to write* redo is
applied on
> the standby, causing blocks not matching with primary at binary level. If
there
> was higher level checkpoint (datafile or system level) happened at the
same time
> "TRUNCATE" operation is marking dirty blocks as *not to write* and
instance
> crashing before "TRUNCATE" operation is fully completed; it can introduce
> logical corruption. Even though oracle can fix it if they wanted to, but
why?
> The whole concept of physical standby and primary not matching at binary
level
> can introduce code regression in other areas. I think it does not worth
all this
> for improving infrequent "TRUNCATE or DROP" operations to perform little
faster.
>
>
> => If there was a rollover of flashback logs (and higher level checkpoint)
> happening at the same time dirty blocks being marked as *not to write* and
> flashing back the database to right before the completion of "TRUNCATE"
> operation, then there is a possibility of logical corruption.
>
Other related posts:
