Re: What is the purpose of segment level checkpoint before DROP/TRUNCATE of a table?

From: Saibabu Devabhaktuni <saibabu_d_at_yahoo.com>
Date: Thu, 7 Jul 2011 09:07:26 -0700 (PDT)
Message-ID: <1310054846.10795.YahooMailRC_at_web65903.mail.ac4.yahoo.com>

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_at_jlcomp.demon.co.uk> To: saibabu_d_at_yahoo.com; free <oracle-l_at_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_at_yahoo.com> To: "free" <oracle-l_at_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.
  >
  -- http://www.freelists.org/webpage/oracle-l

Received on Thu Jul 07 2011 - 11:07:26 CDT