In this post, I will demonstrate various scenarios in single instance where uncommitted data gets written to datafiles.

The database writer can write uncommitted info also to datafiles as a result of following activities:

Flush buffer cache
Before a logfile gets overwritten
Manual checkpoint
To free buffers
Tablespace offline
Tablespace read only
Tablespace begin backup

– SETUP –

– create tablespace test_dbwr

SQL>drop tablespace test_dbwr including contents and datafiles;
    create tablespace test_dbwr datafile '/u01/app/oracle/test_dbwr.dbf' size 100m;

– create table test_dbwr_tab

SQL>drop table test_dbwr_tab purge;
    create table test_dbwr_tab (txt char(100)) tablespace test_dbwr;

- insert a record with txt = test_init

SQL>insert into test_dbwr_tab values ('test_init');
    commit;
    alter system checkpoint;

– find out current checkpoint#

SQL>select checkpoint_change# from v$database;

CHECKPOINT_CHANGE#
——————
983019

– Switch logfile

SQL>alter system switch logfile;

Flush buffer cache

If buffer cache containing dirty blocks is flushed, dirty blocks containing uncomitted data are written to datafiles.

— Overview–

— find out current checkpoint#
— Update record in test_dbwr_tab to ‘test_dbwr’ and do not commit
— check that datafile does not contain string ‘test_dbwr’ as checkpoint has not taken place yet
— Flush buffer cache so that all the dirty blocks in the buffer cache are
flushed to disk by database writer
— Check that checkpoint# has been incremented
— check that datafile contains the string ‘test_dbwr’ as dirty blocks
have been flushed to disk

– Implementation –

– find out current checkpoint#

SQL>select checkpoint_change# from v$database;

CHECKPOINT_CHANGE#
------------------
            983019

– Update record in test_dbwr_tab to ‘test_dbwr’ and do not commit

SQL>update test_dbwr_tab set txt = 'test_dbwr';

-- check that datafile does not contain string ‘test_dbwr’ as checkpoint has not taken place yet

[oracle@node1 oracle]$ strings /u01/app/oracle/test_dbwr.dbf| grep test_dbwr

– Flush buffer cache so that all the dirty blocks in the buffer cache are flushed to disk by database writer

SQL>alter system flush buffer_cache;

– Check that checkpoint# has been incremented

SQL> select checkpoint_change# from v$database;

CHECKPOINT_CHANGE#
------------------
            983038

– check that datafile contains the string ‘test_dbwr’ as dirty blocks have been flushed to disk

[oracle@node1 oracle]$ strings /u01/app/oracle/test_dbwr.dbf| grep test_dbwr
dtest_dbwr

————————————————————————

Before a logfile gets overwritten

A redo log cannot be overwritten until changes recorded in it are written to disk by DBWR. Hence, before a redo log containing uncommitted data is overwritten, uncommitted changes recorded in it are written to datafiles by DBWR.

– Overview –
— check the no. of logfile groups and the current logfile group
— Check current checkpoint#
— update record to string ‘test_log_switch’ in table test_dbwr_tab
— check that datafile does not contain string ‘test_log_switch’
as dirty buffers have not been written to disk yet
— Switch logfile so that curent redo log gets overwritten
— Check that checkpoint# has incremented
— check that datafile contains the string ‘test_log_switch’ as dirty buffers
have been flushed to disk

– Implementation –
— check the no. of logfile groups and the current logfile group

SQL>select group#, status from v$log ;
   GROUP# STATUS
---------- ----------------
         4 INACTIVE
         5 INACTIVE
         6 CURRENT

– Check current checkpoint#

SQL>select checkpoint_change# from V$database;

CHECKPOINT_CHANGE#
------------------
            983038

– update record to string ‘test_log_switch’ in table test_dbwr_tab

SQL>update test_dbwr_tab set txt = 'test_log_switch';

– check that datafile does not contain string ‘test_log_switch’  as dirty buffers have not been written to disk yet

[oracle@node1 oracle]$ strings /u01/app/oracle/test_dbwr.dbf| grep test_log_switch

– Switch logfile so that curent redo log gets overwritten

SQL>alter system switch logfile;
    /
    /
    /
    select group#, status from v$log ;

– Check that checkpoint# has incremented

SQL>select checkpoint_change# from V$database;

CHECKPOINT_CHANGE#
------------------
            983337

– check that datafile contains the string ‘test_log_switch’ as dirty buffers
have been flushed to disk

[oracle@node1 oracle]$ strings /u01/app/oracle/test_dbwr.dbf| grep test_log_switch
dtest_log_switch

————————————————————————–

Manual checkpoint

Whenever checkpoint is initiated manually, all the dirty blocks containing committed/uncommitted data in buffer cache are flushed to disk .

– Overview –
— Find out current checkpoint#
— Update the record to string ‘test_checkpoint’ in table test_dbwr_tab
— check that datafile does not contain string ‘test_checkpoint’
— Perform manual checkpoint so that all the dirty buffers get written
to disk
— Check that checkpoint# has incremented
— check that datafile contains the string ‘test_checkpoint’ as dirty buffers
have been flushed to disk

– Implementation –

– Find out current checkpoint#

SQL>select checkpoint_change# from V$database;

CHECKPOINT_CHANGE#
------------------
            983337

– Update the record to string ‘test_checkpoint’ in table test_dbwr_tab

SQL>update test_dbwr_tab set txt = 'test_checkpoint';

– check that datafile does not contain string ‘test_checkpoint’

[oracle@node1 oracle]$ strings /u01/app/oracle/test_dbwr.dbf| grep test_checkpoint

– Perform manual checkpoint so that all the dirty buffers get written to disk

SQL>alter system  checkpoint;

– Check that checkpoint# has incremented

SQL>select checkpoint_change# from V$database;

CHECKPOINT_CHANGE#
------------------
            983842

– check that datafile contains the string ‘test_checkpoint’ as dirty buffers have been flushed to disk

[oracle@node1 oracle]$ strings /u01/app/oracle/test_dbwr.dbf| grep test_checkpoint
dtest_checkpoint

——————————————————————

Tablespace offline

When a tablespace is taken offline, the  dirty  buffers containinig committed/uncommitted data belonging to it are flushed to disk.

– Overview –
— Find out current checkpoint#
— Find out current checkpoint# for datafile of test_dbwr tablespace
— Update the record to string ‘test_offline’ in table test_dbwr_tab
— check that datafile does not contain string ‘test_offline’
— Take tablespace test_dbwr offline
— Note that database checkpoint# does not change
— Note that checkpoint# for the datafile for test_dbwr tablespace has been incremented
— check that datafile contains the string ‘test_offline’ as dirty buffers
— have been flushed to disk
— Bring tablespace online —

– Implementation –

– Find out current checkpoint#

SQL>select checkpoint_change# from V$database;

CHECKPOINT_CHANGE#
------------------
            983842

– Find out current checkpoint# for datafile of test_dbwr tablespace

SQL>select checkpoint_change# from V$datafile
    where upper(name) like '%TEST_DBWR%';

CHECKPOINT_CHANGE#
------------------
            983842

– Update the record to string ‘test_offline’ in table test_dbwr_tab

SQL>update test_dbwr_tab set txt = 'test_offline';

– check that datafile does not contain string ‘test_offline’

[oracle@node1 oracle]$ strings /u01/app/oracle/test_dbwr.dbf| grep test_offline

– Take tablespace test_dbwr offline –

SQL>alter tablespace test_dbwr offline;

– Note that database checkpoint# does not change

SQL>select checkpoint_change# from V$database;
CHECKPOINT_CHANGE#
------------------
            983842

– Note that checkpoint# for the datafile for test_dbwr tablespace has been incremented

SQL>select checkpoint_change# from V$datafile
    where upper(name) like '%TEST_DBWR%';

CHECKPOINT_CHANGE#
------------------
            984338

– check that datafile contains the string ‘test_offline’ as dirty buffers – have been flushed to disk

[oracle@node1 oracle]$ strings /u01/app/oracle/test_dbwr.dbf| grep test_offline
dtest_offline

– Bring tablespace online –

SQL>alter tablespace test_dbwr online;

————————————————————————–

Similarly, it can be demonstrated that uncommitted data gets written to datafiles whenever
– a tablespace is put in read only mode
– a tablespace is put in backup mode

In these cases also, database checkpoint# is not incremented but checkpoint# for the datafile of the tablespace gets incremented

To free buffers in buffer cache

When a server process is looking for free buffers and they are not available, dirty buffers containing uncommitted/committed data from cold end of LRU list  will be flushed to disk. The flushed buffers may not be the ones with oldest SCN.  Hence, in this case checkpoint# is not incremented.

– Overview –

— Find out current size of database buffer cache
— Decrease size of buffer cache to small value 4M
— check that buffer cache has been shrunk to 4M
— check checkpoint# for the database —
— Update the record to string ‘test_buffer’ in table test_dbwr_tab
— Issue a query on a large table so that dirty buffers in buffer cache have to be flushed to disk in order to free buffers
— check that datafile now contain the strings ‘test_buffer’ as dirty buffers have been written to disk
— Note that checkpoint # has not changed in this case although dirty buffers have been written to disk(LRU/TCH algorithm)
— Reset buffer cache to earlier size

– Implementation –

– Find out current size of database buffer cache

SQL> col component for a20
     select component, current_size/1024/1024 current_mb, min_size/1024/1024 MIN_MB
     from v$sga_dynamic_components
     where component = 'DEFAULT buffer cache';

COMPONENT            CURRENT_MB     MIN_MB
-------------------- ---------- ----------
DEFAULT buffer cache         28         28

– Decrease size of buffer cache to minimum value i.e. 4M

SQL>alter system set db_cache_size=4m;

– check that buffer cache has been shrunk to 4M

SQL> select component, current_size/1024/1024 current_mb, min_size/1024/1024 MIN_MB
     from v$sga_dynamic_components
     where component = 'DEFAULT buffer cache';

OMPONENT            CURRENT_MB     MIN_MB
-------------------- ---------- ----------
DEFAULT buffer cache          4          4

– check checkpoint# for the database –

SQL>select checkpoint_change# from V$database;

CHECKPOINT_CHANGE#
------------------
            983842

– Update the record to string ‘test_buffer’ in table test_dbwr_tab

SQL>update test_dbwr_tab set txt = 'test_buffer';

– check that datafile does not contain string ‘test_buffer”

[oracle@node1 oracle]$ strings /u01/app/oracle/test_dbwr.dbf| grep test_buffer

– Issue a query on a large table so that dirty buffers in buffer cache have to be flushed to disk in order to free buffers

SQL>select * from sh.sales;

– check that datafile now contain the strings ‘test_buffer’ as dirty buffers have been written to disk

[oracle@node1 oracle]$ strings /u01/app/oracle/test_dbwr.dbf| grep test_buffer
dtest_buffer

– Note that checkpoint # has not changed in this case although dirty buffers have been written to disk(LRU/TCH algorithm)

SQL>select checkpoint_change# from V$database;

CHECKPOINT_CHANGE#
------------------
            983842

– Reset buffer cache to earlier size

SQL>alter system set db_cache_size=44m;
    select component, current_size/1024/1024 current_mb, min_size/1024/1024 MIN_MB
     from v$sga_dynamic_components
     where component = 'DEFAULT buffer cache';

– cleanup –

SQL>drop tablespace test_dbwr including contents and datafiles;

Summary:

– When uncommitted data is flushed to disk as a result of

•      . flush buffer cache,
•      . manual checkpoint or
•      . to write changes in a redo log to be overwritten,

checkpoint# for the database is incremented.

– When uncommitted data belonging to a tablespace  is flushed to disk as a result of

•        . tablespace offline,
•        . tablespace read only or
•        . tablespace begin backup

checkpoint# for the database is not incremented but checkpoint# for the datafile(s) belonging to the tablespace is incremented.

– When uncommitted data   is flushed to disk to free buffers in the buffer cache,    checkpoint# for the database is not incremented .

References:

Oracle Documentation

https://community.oracle.com/thread/886580
———————————————————————–

Related links:

Home

Database  Index

Consistent Reads In Oracle : Part-I
Consistent Reads In Oracle : Part-II
Flush Buffer Cache
Oracle Checkpoints
Undo And Redo In Oracle