Re: What is the purpose of segment level checkpoint before DROP/TRUNCATE of a table?
- From: Saibabu Devabhaktuni <saibabu_d@xxxxxxxxx>
- To: free <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 6 Jul 2011 14:33:05 -0700 (PDT)
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:
