Does Log writer writes both uncommitted and committed data to redo log files?
Answer is yes
Log writer writes the information from log buffer to redo log files when log buffer is 1/3rd full or 1 MB of redo is generated or every 3 seconds or at every commit.
Does DBWR writes both uncommitted and committed data to data files?
Answer is yes
Redo for dirty blocks can’t be overwritten. If the redo logs fill up (all active), then changes in the database stop until all of the dirty blocks corresponding to the oldest redo log have been written to disk. At this point we get the message in alert log saying "checkpoint not complete".
Dirty buffers or blocks are the one that has changed after it had been read from disk (datafiles). Even if the commit has not occurred and because of enough redo generated by the transactions there are frequent log switches and now the oldest logfile has to be overwritten in order to accommodate more redo. Checkpoint occurs and this invokes DBWR to write all dirty buffers corresponding to that logfile to be written to disk. Let's see the below test that I performed on one of the test database.
SQL> create tablespace test11 datafile '/opt/oracle/data/dwh01/test101.dbf' size
Tablespace created.
SQL> create table test_table (name varchar2(20)) tablespace test11;
Table created.
SQL> insert into test_table values ('Akshay');
1 row created.
Controlling Checkpoints and Log Switches
A checkpoint is the event during which the Database Writer process (DBWR) writes all modified database buffers in the SGA to the appropriate datafiles. A log switch is the event during which LGWR stops writing to one online redo log group and starts writing to another. The two events are often connected: an instance takes a checkpoint at each log switch by default. A log switch, by default, takes place automatically when the current online redo log file group fills.
However, you can designate that checkpoints are taken more often than when you have log switches, or you can have a checkpoint take place ahead of schedule, without a log switch. You can also have a log switch and checkpoint occur ahead of schedule, or without an accompanying checkpoint.
Answer is yes
Log writer writes the information from log buffer to redo log files when log buffer is 1/3rd full or 1 MB of redo is generated or every 3 seconds or at every commit.
- Let's take an example of a user "A" who is executing a huge transaction which generated a lot of redo records and log buffer went 1/3rd full or 1 MB full. Even if the user has not performed a commit, log writer has to write all the contents of log buffer to log files.
- Let's take another example where one user performed a commit on his transaction and as the rule says LGWR has to write at every commit. At this point with the committed data of the particular session there can also be uncommitted data of other sessions or transactions which will also be written to redo log files.
Does DBWR writes both uncommitted and committed data to data files?
Answer is yes
Redo for dirty blocks can’t be overwritten. If the redo logs fill up (all active), then changes in the database stop until all of the dirty blocks corresponding to the oldest redo log have been written to disk. At this point we get the message in alert log saying "checkpoint not complete".
Dirty buffers or blocks are the one that has changed after it had been read from disk (datafiles). Even if the commit has not occurred and because of enough redo generated by the transactions there are frequent log switches and now the oldest logfile has to be overwritten in order to accommodate more redo. Checkpoint occurs and this invokes DBWR to write all dirty buffers corresponding to that logfile to be written to disk. Let's see the below test that I performed on one of the test database.
SQL> create tablespace test11 datafile '/opt/oracle/data/dwh01/test101.dbf' size
Tablespace created.
SQL> create table test_table (name varchar2(20)) tablespace test11;
Table created.
SQL> insert into test_table values ('Akshay');
1 row created.
Issue below command at unix level
[oracle@host1]$ strings test101.dbf | grep Akshay
No result displayed.
SQL> alter system checkpoint;
System altered.
[oracle@host1]$ strings test101.dbf | grep Akshay
Akshay
In the above case even if the commit has not occurred but checkpoint resulted in dbwr writing dirty buffers to disk.
Let us update the record and without committing do some log switches.
SQL> update test_table set name='Narang';
1 row updated.
[oracle@host1]$ strings test101.dbf | grep Akshay
Akshay
[oracle@host1]$ strings test101.dbf | grep Narang
No result displayed.
SQL> alter system switch logfile;
System altered.
SQL> select group#,status from v$log;
GROUP# STATUS
---------- ----------------
1 CURRENT
2 INACTIVE
3 ACTIVE
[oracle@host1]$ strings test101.dbf | grep Narang
No result displayed.
SQL> alter system switch logfile;
System altered.
SQL> /
GROUP# STATUS
---------- ----------------
1 ACTIVE
2 CURRENT
3 ACTIVE
[oracle@host1]$ strings test101.dbf | grep Narang
No result displayed.
SQL> alter system switch logfile;
System altered.
SQL> /
GROUP# STATUS
---------- ----------------
1 INACTIVE
2 INACTIVE
3 CURRENT
[oracle@host1]$ strings test101.dbf | grep Narang
Narang
[oracle@host1]$ strings test101.dbf | grep Akshay
No result displayed.
Due to log switches Oracle has to reuse the first redo log file but the update to the corresponding block was not written to disk. This caused a checkpoint which directed DWBR to write the block to corresponding datafile even if the changes were not committed.
Controlling Checkpoints and Log Switches
A checkpoint is the event during which the Database Writer process (DBWR) writes all modified database buffers in the SGA to the appropriate datafiles. A log switch is the event during which LGWR stops writing to one online redo log group and starts writing to another. The two events are often connected: an instance takes a checkpoint at each log switch by default. A log switch, by default, takes place automatically when the current online redo log file group fills.
However, you can designate that checkpoints are taken more often than when you have log switches, or you can have a checkpoint take place ahead of schedule, without a log switch. You can also have a log switch and checkpoint occur ahead of schedule, or without an accompanying checkpoint.
Comments
Post a Comment