Featured Post

The great debacle of healthcare.gov

This is the first time in history when the president of the United States of America, or probably for any head of state around the world,...

Sunday, February 23, 2014

How LGWR and the ARCH processes are inter-related?

Both the LGWR and ARCH processes are background processes and are used by Oracle instance to recover transaction and lost data. LGWR process ensures the recovery from an Oracle instance failure whereas ARCH process ensures the recovery from a media failure where the Online redo log files are corrupted or unavailable.

LGWR process writes the redo entries from the redo log buffer to the online redo log file in a sequential manner. There’s at least two or more online redo log files available in the online redo log. But Oracle uses only one online redo log file at a time to store the redo records and when one is filled up or configured to switch at a pre-specified period of time, the log switch occurs and the LGWR process writes to the next available online redo log file.

This scenario holds true in both configuration regardless of the archiving mode or no-archiving mode. But if there’s no more unused log files in the online redo log, then the LGWR proceeds with writing redo log depending on the value of the archiving mode. As in this case, the database is running in ARCHIVELOG mode, a filled online redo log file is available to log writer (LGWR) process only after the changes in that online redo log file have been written to the data files and the file has been archived to the archived red log file by the ARCH process(es).

So the number of ARCH background processes (maximum it can be set to 10) plays an important role in Oracle database performance. LGWR process triggers a new ARCH process when it sees the load is high and continues to do so until it reaches the maximum allowable ARCn process number. So if ARCH processes are slowed down for some reason, it may impact the LGWR activities and eventually overall database performance and recovery in a database running in ARCHIVELOG mode.

No comments: