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: Sun, 10 Jul 2011 23:46:07 -0700 (PDT)
Here is a test case to simulate the behavior of Oracle not doing object level
checkpoint during truncate table operation (and hence not guaranteeing dirty
buffers write to disk) and Oracle reading stale data (logical corruption).
Run t1.sql from one session, and run t2.sql from another session while first
session is waiting on dbms_lock.sleep (Test was done on 10.2.0.4 with 8K block
size)
T1.SQL:
create tablespace tr1 datafile '?/dbs/tr1.dbf' size 10M
extent management local
segment space management auto;
create table tr1(a number, b char(1000)) tablespace tr1;
create table tr2(a number) tablespace sysaux;
insert into tr1 select rownum, 'A' from dual connect by level <= 1000;
commit;
alter tablespace tr1 begin backup;
host cp $ORACLE_HOME/dbs/tr1.dbf $ORACLE_HOME/dbs/tr1.dbf.org
alter tablespace tr1 end backup;
update tr1 set a=0;
commit;
REM Run t2.sql from another session
exec dbms_lock.sleep(15);
truncate table tr1;
alter system flush buffer_cache;
host cp $ORACLE_HOME/dbs/tr1.dbf.org $ORACLE_HOME/dbs/tr1.dbf
exec dbms_lock.sleep(60);
select count(*), count(distinct a) from tr2;
T2.SQL:
alter session set db_file_multiblock_read_count=2;
declare
j number := 1;
begin
for i in (select a from tr1) loop
insert into tr2 values (i.a);
if (j <= 60) then
dbms_lock.sleep(1);
end if;
commit;
j := j+1;
end loop;
end;
/
Even though TR1 table was updated to set all values of column "A" to "0",
Oracle reported some records from latest update operation and the rest from
before update operation. I think this can be classified as logical corruption,
although re-running the same query will fix the problem.
This is by far the biggest reason I think Oracle should do object level
checkpoint as part of truncate.
Another reason which I'm still very convinced is that if Oracle generated redo
for a change that is supposed to be persistent across database bounces, then
the data block buffers which had this redo applied in the buffer cache should
be written to disk regardless of any state change happening to the underlying
object. This not only keeps code base simple but leave the door open for new
features (think of anybody predicting flashback database feature in 8i days)
which may very well rely on this fact.
I'll continue to test flashback feature if it can be rigged for data loss
scenario when object level checkpoint is missing during truncate operation.
Thanks,
Sai
http://sai-oracle.blogspot.com
Other related posts:
