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

Here are the reasons I think why object level checkpoint is needed for Truncate
>or Drop operation: 1) Datafiles on primary and standby need to be same bit by
>bit and block by block. This is fundamentally required for Oracle physical
>standby and physical
>backups. This will be broken if object level checkpoint wasn't done. But blocks
>don't get transferred, the redo does, and the redo will ensure that physical
>standby gets corrected. If you're thinking about creating a new physical standby
>- the truncated or dropped blocks are just garbage, apart from the segment
>header etc. (see reply to Tim Gorman) which would have been handled by local
>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.

2) Database level flashback can logically corrupt the data, when flashback database command is used, if thread level checkpoint wasn't done as part of Truncate or Drop operation. There may be a problem - but as a reason that doesn't really work: "I think we'll write truncated objects to disc because in 10 years time we're going to come up with database flashback and then things will break if we don't." ;)Again, though, flashback simply replaces blocks in the datafiles, then applies redo log to them, but the available redo would be appropriate for the block that had not been written to disc on truncate.

=> 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.

Thanks,
 Sai
http://sai-oracle.blogspot.com

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