After completion of any DML,DDL or TCL operation like insert, drop or commit, oracle writes the blocks (user blocks and undo blocks) and the original sql statement to the redo log buffer. This process repeats for every sql statement execution.
The redo log buffer is a small memory area thus it would fill up with data very rapidly, when ever the redo log buffer becomes 1/3rd filled up with data then oracle calls the log writer process.
The log writer process will move or write out the content of the log buffer to a redo log group's, log files.
Log groups are selected for writing by the lgwr based on the log group status.
Log groups have 3 status values
1) inactive
2) current
3) active
Inactive status:
It indicates that the log group is empty and can be used by the lgwr for writing data.
When ever an oracle database is started up then all its log groups would be in the inactive status. The lgwr usually picks up the first log group to begin writing transactions.
Current status:
It indicates that the log group is currently being written to it.
Once a log group is filled up with data then oracle can no longer write data to the log files of that group, at this point in time the lgwr stops writing to that group and begins writing to the next group who's status is inactive. This is known as a log switch.
Once the log group is filled up the status is changed to "active".
When ever a log switch occurs then the database writer process is called and its job is to write out the content of the filled up log group to the respective datafiles, it does this by reading the same data from the data buffer cache and writing that data to the datafiles.
After dbwr has finished writing the data to the datafiles from the data buffer then it signals the logwrite process to clear the active log group of its contents.
The data that is written to the datafiles by the database writer may be commited or uncommited data.
One oracle database will by default have 1 dbwr process but if needed the dba can enable upto 20 dbwr's.
Increasing the number of dbwr's will increase the speed of writing data buffer cache data to the datafiles.
The reason why oracle delays a write to the datafile and instead writes all data to the log groups is that, log files are small in size and thus for every small transaction oracle need not open the large datafiles and instead write to the smaller log files to gain performance.
The lgwr process is also called in the following scenarios:
1) When the redo log buffer is 1/3 rd full.
2) The lgwr has a 3 sec timeout value, where in every 3 seconds it is called to check the redo log buffer for any transactions.
3) The lgwr is also called when ever a user issues a commit statement.
A commit when written to the redo log buffer will cause the lgwr to wake up and write out all the content of the redo log buffer to the current log group.
Once the commit has been written to the log file then the user will get an acknowledgement of "commit complete"
The dbwr process is called in the following scenarios:
1) Every 3 seconds (dbwr time out value).
2) The dbcache has a threshold value, i.e. If 80% of the dbcache is filled up, then the dbwr process is called to empty the dbcache.
3) The dbwr is called when ever a user queries a very huge table, to make space for that table to be accomodated into the dbcache.
4) The dbwr is called before making a tablespace offline to write out the content of that tablespace to its datafiles.
5) The dbwr is called to write the dirty buffers to their respective datafiles before the database is going to shutdown.
Archiver process:
This is an optional process, if enabled by the dba then its job is to write or copy a filled up online redo log file to a location known as the archive log destination, before the log switch occurs.
The logfile will be copied with a unique name and the archiver process will continue to copy the logfiles as long as the database is running.
Archiver process will copy the logfile with a .arc file extention. These files can be used by the dba to perform media recovery incase of a media failure and loss of data due to deletion of the physical files.
The redo log buffer is a small memory area thus it would fill up with data very rapidly, when ever the redo log buffer becomes 1/3rd filled up with data then oracle calls the log writer process.
The log writer process will move or write out the content of the log buffer to a redo log group's, log files.
Log groups are selected for writing by the lgwr based on the log group status.
Log groups have 3 status values
1) inactive
2) current
3) active
Inactive status:
It indicates that the log group is empty and can be used by the lgwr for writing data.
When ever an oracle database is started up then all its log groups would be in the inactive status. The lgwr usually picks up the first log group to begin writing transactions.
Current status:
It indicates that the log group is currently being written to it.
Once a log group is filled up with data then oracle can no longer write data to the log files of that group, at this point in time the lgwr stops writing to that group and begins writing to the next group who's status is inactive. This is known as a log switch.
Once the log group is filled up the status is changed to "active".
When ever a log switch occurs then the database writer process is called and its job is to write out the content of the filled up log group to the respective datafiles, it does this by reading the same data from the data buffer cache and writing that data to the datafiles.
After dbwr has finished writing the data to the datafiles from the data buffer then it signals the logwrite process to clear the active log group of its contents.
The data that is written to the datafiles by the database writer may be commited or uncommited data.
One oracle database will by default have 1 dbwr process but if needed the dba can enable upto 20 dbwr's.
Increasing the number of dbwr's will increase the speed of writing data buffer cache data to the datafiles.
The reason why oracle delays a write to the datafile and instead writes all data to the log groups is that, log files are small in size and thus for every small transaction oracle need not open the large datafiles and instead write to the smaller log files to gain performance.
The lgwr process is also called in the following scenarios:
1) When the redo log buffer is 1/3 rd full.
2) The lgwr has a 3 sec timeout value, where in every 3 seconds it is called to check the redo log buffer for any transactions.
3) The lgwr is also called when ever a user issues a commit statement.
A commit when written to the redo log buffer will cause the lgwr to wake up and write out all the content of the redo log buffer to the current log group.
Once the commit has been written to the log file then the user will get an acknowledgement of "commit complete"
The dbwr process is called in the following scenarios:
1) Every 3 seconds (dbwr time out value).
2) The dbcache has a threshold value, i.e. If 80% of the dbcache is filled up, then the dbwr process is called to empty the dbcache.
3) The dbwr is called when ever a user queries a very huge table, to make space for that table to be accomodated into the dbcache.
4) The dbwr is called before making a tablespace offline to write out the content of that tablespace to its datafiles.
5) The dbwr is called to write the dirty buffers to their respective datafiles before the database is going to shutdown.
Archiver process:
This is an optional process, if enabled by the dba then its job is to write or copy a filled up online redo log file to a location known as the archive log destination, before the log switch occurs.
The logfile will be copied with a unique name and the archiver process will continue to copy the logfiles as long as the database is running.
Archiver process will copy the logfile with a .arc file extention. These files can be used by the dba to perform media recovery incase of a media failure and loss of data due to deletion of the physical files.
No comments:
Post a Comment