Re: What is the purpose of segment level checkpoint before DROP/TRUNCATE of a table?
From: Saibabu Devabhaktuni <saibabu_d_at_yahoo.com>
Date: Sun, 10 Jul 2011 23:46:07 -0700 (PDT)
Message-ID: <1310366767.14153.YahooMailNeo_at_web65908.mail.ac4.yahoo.com>
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
Date: Sun, 10 Jul 2011 23:46:07 -0700 (PDT)
Message-ID: <1310366767.14153.YahooMailNeo_at_web65908.mail.ac4.yahoo.com>
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
-- http://www.freelists.org/webpage/oracle-lReceived on Mon Jul 11 2011 - 01:46:07 CDT