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

  • From: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
  • To: <saibabu_d@xxxxxxxxx>, "free" <oracle-l@xxxxxxxxxxxxx>
  • Date: Wed, 6 Jul 2011 19:26:45 +0100

Notes in line:


Jonathan Lewis

----- Original Message ----- From: "Saibabu Devabhaktuni" <saibabu_d@xxxxxxxxx>
To: "free" <oracle-l@xxxxxxxxxxxxx>
Sent: Wednesday, July 06, 2011 6:04 PM
Subject: 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.

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.

Marking buffers as invalid for writes is only done in Active DataGuard
environment on physical standby databases in some situations.

Hope it helps.


Version: 10.0.1388 / Virus Database: 1516/3746 - Release Date: 07/05/11


Other related posts: