when log switch occurs....

[reydp]

Does this mean that there is no situation when DBWn writes uncommitted data to disk..coz if during checkpoint, if only bufferes related to redo log files are return to disk...then since redo log files always hold committed data(LGWR writes data from redo log buffer to redo log file on commit)-- so there is no case of writing uncommitted data to disk or what?????????

-------------------------------------------------

Does this mean that there is no situation
when DBWn writes uncommitted data to disk..
****wrong assumption
coz if during checkpoint, if only bufferes related
to redo log files are return to disk...
*****wrong assumption
then since redo log files always hold committed data
*****wrong assumption
(LGWR writes data from redo log buffer to redo log file on commit)
-- so there is no case of writing uncommitted data to disk or what?????????
*****wrong conclusion
-----------------------------------------------

First, Nice explaination ixion,

Janki_mehta,
Just to clarify your understanding about redo logs switch, checkpoints, DBWn, LGWR. I am open to any opinion, ideas.

I think Tamil also miss 1 point about the reason why a REDO LOG SWITCH OCCURS.
And that is when an ONLINE REDO LOG is full and it has to advance to the next LOG sequence.
When this happens a SLOW CHECKPOINT(others call it SOFT CHECKPOINT) happens implicitly.
The other type of SLOW CHECKPOINT happens as what TAMIL have explained earlier,
which I believe will only happened if you have not configured your db buffer cache properly,
or you have a very huge REDO LOG size that a SLOW CHECKPOINT must happen even before a REDO LOG becomes full.
So at the normal operation where in you have a good db buffer cache configuration and an adequate REDO LOG size,
the SLOW CHECKPOINT that Tamil have discussed is less likely to happen.


I'll show you why a DBWn also writes uncommitted data to disk/datafile.
And it also follows that during checkpoint not only buffers related
to redo log files are written to disk/redo log files but also the dirty blocks are also be disk/datafile.
I'll show you also why uncommitted data are also written to redo logs.


Example.
In a DB where in it is newly opened and no other users used, we are going to do a series of inserts into a single table.
Leaves it as uncommited, then issue a shutdown abort to the database. And let's see what will happen when we start the database.
Walkthrough yourself for explanation through item# INFO.

item#1 - a newly opened db without transaction - ONLINE REDO LOG info
SQL> l
1 SELECT A.GROUP#, A.MEMBER, B.SEQUENCE#, B.BYTES, B.STATUS
2 FROM V$LOGFILE A, V$LOG B
3* WHERE A.GROUP# = B.GROUP#
SQL> /

GROUP# MEMBER SEQUENCE# BYTES STATUS
---------- ------------------------------------ ---------- ---------- ----------------
1 C:\ORACLE\ORADATA\HRISESM\REDO01.LOG 152 1048576 INACTIVE
2 C:\ORACLE\ORADATA\HRISESM\REDO02.LOG 153 1048576 INACTIVE
3 C:\ORACLE\ORADATA\HRISESM\REDO03.LOG 154 1048576 CURRENT

--- here let's an INSERTs happens to a table--- uncommitted-----

item#2 - a db with transaction(uncommited) before SHUTDOWN ABORT - ONLINE REDO LOG info
SQL> /

GROUP# MEMBER SEQUENCE# BYTES STATUS
---------- ------------------------------------ ---------- ---------- ----------------
1 C:\ORACLE\ORADATA\HRISESM\REDO01.LOG 155 1048576 INACTIVE
2 C:\ORACLE\ORADATA\HRISESM\REDO02.LOG 156 1048576 CURRENT
3 C:\ORACLE\ORADATA\HRISESM\REDO03.LOG 154 1048576 INACTIVE


item#3 - a buffer busy waits - just to confirm that there was at least
- a wait events happened when the uncommitted transaction was made.
SQL> select class, count
2 from v$waitstat
3 where count > 0
4 order by count desc;

CLASS COUNT
------------------ ----------
data block 6

----------shutdown abort was issued --------

----------the db was restarted---------------

item#4 - an alertSID.log shows that during instance recovery
- a transaction recovery(roll forward) occured before database was completely open.
- See Thread recovery: finish rolling forward thread 1
- Thread recovery: 67 data blocks read, 65 data blocks written, 1821 redo blocks read
- As you can see it reads both from the data blocks(datafile) and the redo blocks(redo log files) during instance recovery.
ALTER DATABASE OPEN
Beginning crash recovery of 1 threads
Mon Apr 02 12:38:02 2007
Thread recovery: start rolling forward thread 1
Recovery of Online Redo Log: Thread 1 Group 2 Seq 156 Reading mem 0
Mem# 0 errs 0: C:\ORACLE\ORADATA\HRISESM\REDO02.LOG
Mon Apr 02 12:38:02 2007
Thread recovery: finish rolling forward thread 1
Thread recovery: 67 data blocks read, 65 data blocks written, 1821 redo blocks read
Crash recovery completed successfully

item#5 - After the DB was successfully reopen, it shows that redo log Group 2 SEQUENCE# 156
- was also archived immediately instead of putting it in ACTIVE status
- because it was suppose to be ARCHIVE if the DB was shutdown properly.
SQL> SELECT A.GROUP#, A.MEMBER, B.SEQUENCE#, B.BYTES, B.STATUS
2 FROM V$LOGFILE A, V$LOG B
3 WHERE A.GROUP# = B.GROUP#
4 /

GROUP# MEMBER SEQUENCE# BYTES STATUS
---------- ------------------------------------ ---------- ---------- ----------------
1 C:\ORACLE\ORADATA\HRISESM\REDO01.LOG 155 1048576 INACTIVE
2 C:\ORACLE\ORADATA\HRISESM\REDO02.LOG 156 1048576 INACTIVE
3 C:\ORACLE\ORADATA\HRISESM\REDO03.LOG 157 1048576 CURRENT