DomainKey-Signature:a=rsa-sha1; q=dns; c=nofws;  s=s1024; d=yahoo.com;  h=X-YMail-OSG:Received:X-Mailer:References:Message-ID:Date:From:Subject:To:In-Reply-To:MIME-Version:Content-Type;  b=DOLh9ogluaahR+BSG8M7BtE+6sMbpYohw76VYipOLcfpAAxb2bGkSPPFHnTktv4MH4q9K5JaTE7AV0sNAedAZs/JKCsq/T2M3LI86BBc5jcl36VkoDUP31FVv76/+icHxVLE7dypkkK/IN+zhnAVgkF6y67zTmW4WqwMeK1tKbk=;
References: <1309987985.44628.YahooMailRC@web65918.mail.ac4.yahoo.com> <A8EF9E974EE04D449D324F47994F9382@Primary>
Message-ID: <1310054846.10795.YahooMailRC@web65903.mail.ac4.yahoo.com>
Date: Thu, 7 Jul 2011 09:07:26 -0700 (PDT)
From: Saibabu Devabhaktuni <saibabu_d@yahoo.com>
Subject: Re: What is the purpose of segment level checkpoint before DROP/TRUNCATE of a table?
To: Jonathan Lewis <jonathan@jlcomp.demon.co.uk>,  free <oracle-l@freelists.org>
In-Reply-To: <A8EF9E974EE04D449D324F47994F9382@Primary>
MIME-Version: 1.0
Content-Type: multipart/alternative; boundary="0-2028870813-1310054846=:10795"
Sender: oracle-l-bounce@freelists.org
Errors-to: oracle-l-bounce@freelists.org
Precedence: normal
Reply-To: saibabu_d@yahoo.com
--0-2028870813-1310054846=:10795
Content-Type: text/plain; charset=us-ascii

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@jlcomp.demon.co.uk>
To: saibabu_d@yahoo.com; free <oracle-l@freelists.org>
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@yahoo.com>
To: "free" <oracle-l@freelists.org>
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.
> 
--0-2028870813-1310054846=:10795
Content-Type: text/html; charset=us-ascii

<html><head><style type="text/css"><!-- DIV {margin:0px;} --></style></head><body><div style="font-family:'Courier New', courier, monaco, monospace, sans-serif;font-size:10pt"><div>Hi Jonathan,</div><div><br></div><div>If we have a table with 100,000 dirty buffers on primary database.</div><div><br></div><div>1) Let's say 50,000 dirty buffers were already written to disk by DBWR on primary.</div><div>2) At time T1, truncate table command issued on primary.</div><div>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.</div><div>4) At time T3, truncate operation is fully completed on primary.</div><div>5) On the standby, configure very small buffer cache size (i.e. can only fit 10,000 buffers).</div><div>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.</div><div>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.</div><div><br></div><div><br></div><div>Data loss scenario:</div><div>1) If there was a system or datafile level checkpoint finished on primary between time T2 and T3.</div><div>2) If primary instance crashes between time T2 and T3, but after the above checkpoint was completed.</div><div>3) Above checkpoint would have skipped writing buffers marked as not to write and hence on disk image is not current.</div><div>4) When instance is starting up, crash recovery starts as of redo from the most recent checkpoint.</div><div>5) After the completion of crash recovery, truncate never really finished, but the data in the dirty blocks as of time T1 is missing.</div><div><br></div><div>Thanks,</div><div>&nbsp;Sai</div><div><span><a
 target="_blank" href="http://sai-oracle.blogspot.com">http://sai-oracle.blogspot.com</a></span></div><div style="font-family:Courier New, courier, monaco, monospace, sans-serif;font-size:10pt"><br><div style="font-family:arial, helvetica, sans-serif;font-size:13px"><font size="2" face="Tahoma"><hr size="1"><b><span style="font-weight: bold;">From:</span></b> Jonathan Lewis &lt;jonathan@jlcomp.demon.co.uk&gt;<br><b><span style="font-weight: bold;">To:</span></b> saibabu_d@yahoo.com; free &lt;oracle-l@freelists.org&gt;<br><b><span style="font-weight: bold;">Sent:</span></b> Wed, July 6, 2011 10:24:39 PM<br><b><span style="font-weight: bold;">Subject:</span></b> Re: What is the purpose of segment level checkpoint before DROP/TRUNCATE of a table?<br></font><br>
<br>Sai,<br><br>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.<br><br>Regards<br><br>Jonathan Lewis<br><span><a target="_blank" href="http://jonathanlewis.wordpress.com">http://jonathanlewis.wordpress.com</a></span><br><br><br>----- Original Message ----- From: "Saibabu Devabhaktuni" &lt;<a ymailto="mailto:saibabu_d@yahoo.com" href="mailto:saibabu_d@yahoo.com">saibabu_d@yahoo.com</a>&gt;<br>To: "free" &lt;<a ymailto="mailto:oracle-l@freelists.org" href="mailto:oracle-l@freelists.org">oracle-l@freelists.org</a>&gt;<br>Sent: Wednesday, July 06, 2011
 10:33 PM<br>Subject: Re: What is the purpose of segment level checkpoint before DROP/TRUNCATE of a table?<br>writes.<br>&gt; <br>&gt; =&gt; Yes redo gets applied on the physical standby, but the dirty blocks which<br>&gt; were marked as *not to write* on the primary can get flushed out to disk with<br>&gt; all the dirty changes on the standby before "*not to write* redo is applied on<br>&gt; the standby, causing blocks not matching with primary at binary level. If there<br>&gt; was higher level checkpoint (datafile or system level) happened at the same time<br>&gt; "TRUNCATE" operation is marking dirty blocks as *not to write* and instance<br>&gt; crashing before "TRUNCATE" operation is fully completed; it can introduce<br>&gt; logical corruption. Even though oracle can fix it if they wanted to, but why?<br>&gt; The whole concept of physical standby and primary not matching at binary level<br>&gt; can introduce code regression in other areas. I think it
 does not worth all this<br>&gt; for improving infrequent "TRUNCATE or DROP" operations to perform little faster.<br>&gt; <br>&gt; <br>&gt; =&gt; If there was a rollover of flashback logs (and higher level checkpoint)<br>&gt; happening at the same time dirty blocks being marked as *not to write* and<br>&gt; flashing back the database to right before the completion of "TRUNCATE"<br>&gt; operation, then there is a possibility of logical corruption.<br>&gt; <br><br></div></div><div style="position:fixed"></div>


</div></body></html>
--0-2028870813-1310054846=:10795--
--
http://www.freelists.org/webpage/oracle-l