But nowadays, DBWR also does continuous checkpointing and
flush the dirty buffers to disk (i.e., writes both committed and uncommitted data
to datafiles) because of the following
reasons.
a. When we do transactions greater than the
available memory, we need some free buffers for transactions to complete.
b. When DBWR flush dirty blocks to disk, redo logs
will have enough space and can be reused.
c. Limits the time to recovery after a crash using
the parameter FAST_START_MTTR_TARGET.
Consider, we are doing a bulk update.
Then commit.
Next, the system suddenly crashes ,
If we have all data left in the cache by DBWR,
then while recovery, all the data needs to be reapplied and would take long
time.
DBWR flush dirty buffers to datafiles when one of the
following occurs.
a.
Checkpoint occurs
b.
Dirty buffers reach threshold / Flush buffer
cache
c.
No free buffers
d.
Before logfile gets overwritten/Before log
switch
e.
Tablespace offline
f.
Tablespace read only
g.
Table drop or truncate
h.
Tablespace begin backup
In this post, let us see a demonstration that datafiles
contains even the uncommitted data. This is carried out in single instance.
1. Create a new tablespace TEST_UNCOMMITTBS
SQL> create tablespace test_uncommittbs
datafile '/data1/noasmdb/datafile/test_uncommit01.dbf' size 1M autoextend on
next 512K;
Tablespace created.
2. Create a table TEST_UNCOMMIT_TBL under the
tablespace created above.
Create table test_uncommit_tbl (sampletext
varchar2(30)) tablespace test_uncommittbs
3. Insert a record in to the table and do not commit.
3. Insert a record in to the table and do not commit.
SQL> insert into test_uncommit_tbl
values ('testdata_uncommit');
1 row created.
4. C heck for the data in the datafile and we do not file as we have not performed any checkpoint/flush of dirty buffer/tablespace offline/tablespace read only/tablespace begin backup.
[oracle@vm1 datafile]$ strings
test_uncommit01.dbf |grep testdata
5. Now commit and do a checkpoint
SQL>
commit;
Commit
complete.
SQL> alter
system checkpoint;
System
altered
6. Note down the checkpoint change number
6. Note down the checkpoint change number
SQL> select
checkpoint_change# from v$database;
CHECKPOINT_CHANGE#
------------------
4708155
7.
We have the data in group 1 (CURRENT) redolog files too. So, let us do a
log file switch now to have fresh CURRENT redo log file with no data above.
[oracle@vm1 redofiles]$ strings
redo_g01a.log |grep testdata
testdata_uncommit
[oracle@vm1 redofiles]$ strings
redo_g01b.log |grep testdata
testdata_uncommit
SQL> select group#,status
from v$log;
GROUP# STATUS
---------- ----------------
1 CURRENT
2 INACTIVE
3 INACTIVE
SQL> alter system switch
logfile;
System altered.
SQL> select group#,status
from v$log;
GROUP# STATUS
---------- ----------------
1 ACTIVE
2 CURRENT
3 INACTIVE
[oracle@vm1 redofiles]$ strings
redo_g02a.log |grep testdata
[oracle@vm1 redofiles]$ strings
redo_g02b.log |grep testdata
Scenario 1 : Manual
Checkpoint
In this scenario, we will do a
manual checkpoint which would initiate uncommitted and committed data (dirty
buffers) to write to disk.
1. Find the current checkpoint number.
SQL> select
checkpoint_change# from v$database;
CHECKPOINT_CHANGE#
------------------
4708155
2. Update
the record we inserted before and do not commit
SQL> update test_uncommit_tbl
set sampletext='testdata_uncommit_ckpt' where sampletext='testdata_uncommit';
1 row updated.
3. Check
for the data in the datafile and it should not be available.
[oracle@vm1 datafile]$ strings
test_uncommit01.dbf |grep testdata
testdata_uncommit
Only old data is available.
4. Now,
perform a manual checkpoint in the database.
SQL> alter system
checkpoint;
System altered.
5. Check
for the data available in datafiles.
[oracle@vm1 datafile]$ strings
test_uncommit01.dbf |grep testdata
testdata_uncommit_ckpt,
testdata_uncommit
[oracle@vm1 datafile]$
6. Find
the current checkpoint number
SQL> select
checkpoint_change# from v$database;
CHECKPOINT_CHANGE#
------------------
4709165
Conclusion
a. Uncommitted
data is present in datafiles when a checkpoint occurs.
b. Checkpoint
of the database is incremented.
Scenario 2 :
Flush buffer cache
In this scenario, we will see that buffer cache
containing the dirty buffers getting flushed to disk without checkpoint.
1.
Find the current checkpoint
SQL> select
checkpoint_change# from v$database;
CHECKPOINT_CHANGE#
------------------
4709314
2.
Update the record as test_uncommit_flush and do
not commit
SQL> update test_uncommit_tbl
set sampletext='testdata_uncommit_flush' where sampletext='testdata_uncommit';
1 row updated.
3.
Note that the updated record is still not
available in the datafile
[oracle@vm1 datafile]$ strings
test_uncommit01.dbf |grep testdata
testdata_uncommit,
testdata_uncommit_ckpt,
testdata_uncommit
4. Now, flush the buffer cache .
SQL> alter system flush buffer_cache;
System altered.
5. Note that the uncommitted record is available in
the datafile.
[oracle@vm1 datafile]$ strings
test_uncommit01.dbf |grep testdata
testdata_uncommit_flush,
testdata_uncommit,
testdata_uncommit_ckpt,
testdata_uncommit
6. Also, check that checkpoint number is the same as before.
SQL> select checkpoint_change# from
v$database;
CHECKPOINT_CHANGE#
------------------
4709314
Scenario 3: Taking
tablespace offline/Tablespace Read only/Tablespace online backup
1. Check the current checkpoint number of the database.
SQL> select checkpoint_change# from
v$database;
CHECKPOINT_CHANGE#
------------------
4709314
2. Check the current checkpoint value of the datafile of the tablespace TEST_UNCOMMITTBS
SQL>
select name,checkpoint_change# from v$datafile where name like
'%test_uncommit%';
NAME
CHECKPOINT_CHANGE#
------------------------------------------------------------
------------------
/data1/noasmdb/datafile/test_uncommit01.dbf 4709314
3. Update the record of the table and do not
commit.
SQL>
update test_uncommit_tbl set sampletext='testdata_uncommit_tbsoff' where sampletext='testdata_uncommit';
1 row updated
4. Check the datafile does not contain the data testdata_uncommit_tbsoff
[oracle@vm1 datafile]$ strings
test_uncommit01.dbf |grep testdata
testdata_uncommit_flush,
testdata_uncommit,
testdata_uncommit_ckpt,
testdata_uncommit
5. Now, take the tablespace ‘test_uncommittbs ‘ offline
SQL> alter tablespace test_uncommittbs
offline;
Tablespace altered.
6. Check for the data in the datafile, it will be available.
[oracle@vm1 datafile]$ strings
test_uncommit01.dbf |grep testdata
testdata_uncommit_tbsoff,
testdata_uncommit,
testdata_uncommit_flush,
testdata_uncommit,
testdata_uncommit_ckpt,
testdata_uncommit
7. Now the checkpoint number of the datafile is
changed/incremented but the database checkpoint is same.
SQL> select name,checkpoint_change# from
v$datafile where name like '%test_uncommit%';
NAME
CHECKPOINT_CHANGE#
------------------------------------------------------------
------------------
/data1/noasmdb/datafile/test_uncommit01.dbf 4712883
SQL> select checkpoint_change# from
v$database;
CHECKPOINT_CHANGE#
------------------
4709314
8. Now bring the tablespace online
SQL> alter tablespace test_uncommittbs
online;
Tablespace altered.
9. Check for the checkpoint of the database and
tablespace. We will notice only the tablespace checkpoint would have got incremented.
SQL> select checkpoint_change# from
v$database;
CHECKPOINT_CHANGE#
------------------
4709314
SQL> select name,checkpoint_change# from
v$datafile where name like '%test_uncommit%';
NAME
CHECKPOINT_CHANGE#
------------------------------------------------------------
------------------
/data1/noasmdb/datafile/test_uncommit01.dbf 4712952
10. Check that data will still be the ‘Updated value’ as the transaction is not yet committed.
SQL> select * from test_uncommit_tbl;
SAMPLETEXT
------------------------------
testdata_uncommit_tbsoff
Scenario 4: Before
log switch/ before log files are overwrriten
1. We have seen previously that we have 3 red log groups each with 2 members. However, just displaying the result again. The CURRENT redo log group is Group 3.
1. We have seen previously that we have 3 red log groups each with 2 members. However, just displaying the result again. The CURRENT redo log group is Group 3.
SQL>
select group#,members,status from v$log;
GROUP# MEMBERS STATUS
---------- ---------- ----------------
1 2 INACTIVE
2 2 INACTIVE
3 2 CURRENT
2. Note down the checkpoint number of the database.
SQL> select checkpoint_change# from
v$database;
CHECKPOINT_CHANGE#
------------------
4709314
3. Update the record in the table.
SQL> update test_uncommit_tbl set
sampletext='testdata_uncommit_logswitch'
where sampletext='testdata_uncommit';
1 row updated.
4. Check in the datafile and the updated record should not be available.
[oracle@vm1 datafile]$ strings
test_uncommit01.dbf |grep testdata
testdata_uncommit_tbsoff,
testdata_uncommit,
testdata_uncommit_flush,
testdata_uncommit,
testdata_uncommit_ckpt,
testdata_uncommit
5. Now , perform log switch such that group 3 gets overwritten .
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered
6. Now, we can noticed that data is existing in the datafile before the log switch to overwritten the log.
[oracle@vm1 datafile]$ strings
test_uncommit01.dbf |grep testdata
testdata_uncommit_logswitch,
testdata_uncommit,
testdata_uncommit_tbsoff,
testdata_uncommit,
testdata_uncommit_flush,
testdata_uncommit,
testdata_uncommit_ckpt,
testdata_uncommit
7. Note the checkpoint number incremented.
SQL> select checkpoint_change# from
v$database;
CHECKPOINT_CHANGE#
------------------
4713768
Scenario 5 : Buffers reaching threshold
In this scenario, we will see how the committed/uncommitted
data (dirty buffers) are flushed to disk when the server processes do not find
free buffers.
1. Note the current checkpoint of the database.
SQL> select checkpoint_change# from
v$database;
CHECKPOINT_CHANGE#
------------------
4713768
2. Find the current size of the database buffer cache.
2. Find the current size of the database buffer cache.
SQL> select
component,current_size/1024/1024,min_Size/1024/1024,max_size/1024/1024 from
v$sga_dynamic_components where component='DEFAULT buffer cache';
COMPONENT CURRENT_SIZE/1024/1024
MIN_SIZE/1024/1024 MAX_SIZE/1024/1024
------------------------
---------------------- ------------------ ------------------
DEFAULT buffer cache 44 44 44
3. Let us update the record in the table and do not commit.
SQL> update test_uncommit_tbl set
sampletext='testdata_uncommit_threshold' where sampletext='testdata_uncommit';
1 row updated.
4. Now check for the data in datafile and you will not find the data.
[oracle@vm1 datafile]$ strings
test_uncommit01.dbf |grep testdata
testdata_uncommit_logswitch,
testdata_uncommit,
testdata_uncommit_tbsoff,
testdata_uncommit,
testdata_uncommit_flush,
testdata_uncommit,
5. Let us query another table without committing the last update statement.
I just
ran the statement.
Select * from dba_objects;
6. Once I did this, I could see the uncommitted data ‘testdata_uncommit_threshold’ is available in the datafile.
6. Once I did this, I could see the uncommitted data ‘testdata_uncommit_threshold’ is available in the datafile.
[oracle@vm1 datafile]$ strings
test_uncommit01.dbf |grep testdata
testdata_uncommit_threshold,
testdata_uncommit,
testdata_uncommit_logswitch,
7. Checkpoint is not incremented .
SQL>
select checkpoint_change# from v$database;
CHECKPOINT_CHANGE#
------------------
4713768
Conclusion on this scenario :
Checkpoint is not changing when there is
just a flush of dirty buffers to disk. This is due to LRU/TCH algorithm which
only flushes from the cold end of LRUlist which may not necessary to have the
recent SCN i.e.,, they may contain the oldest SCN.
References
No comments:
Post a Comment