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

Thanks Jonathan for your valuable comments. Inline responses below (lines 
starting with =>)


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

Other related posts: