Log Writer (LGWR)

Describe how the Log Writer writes out redo log records.
Every Oracle instance will have a Log Writer process (LGWR) that is responsible for writing out redo log entries to the redo log files. Unlike the database writer, an instance may have only one log writer. The Log Writer writes redo log entries from an area in the SGA known as the redo log buffer. The redo log buffer is a circular buffer, and conceptually looks like this:

In the architecture of an Oracle Database instance, the Log Writer process, abbreviated as LGWR, stands as a cornerstone component, indispensable for the assurance of both data integrity and transactional durability. The process is automatically initialized during the instantiation of the Oracle Database and runs throughout the lifecycle of the database instance. Operating in the background, LGWR executes critical tasks that directly impact the ACID (Atomicity, Consistency, Isolation, Durability) properties of the database system.

1. Redo Log Buffer Flushing: The primary responsibility of LGWR is to write the contents of the redo log buffer, found in the System Global Area (SGA), to the online redo log files on disk. This action captures all changes made to the database, thereby enabling recovery procedures in the event of a failure.
2. Synchronization: LGWR plays an integral role in the commit operations of transactions. When a user session commits a transaction, LGWR writes all the redo entries generated by that transaction from the redo log buffer to the online redo log files. Only upon the successful completion of this write operation is the transaction considered committed.
3. Asynchronous Operations: LGWR is designed to work asynchronously, which allows it to write multiple redo entries in a single I/O operation. This enhances the efficiency of the database by reducing the I/O wait time for end-users.
4. Log Switching: LGWR is responsible for switching to a new redo log file when the current one becomes full. This is crucial for the continuous operation of the database and also facilitates the archiving of filled redo log files if the database is operating in ARCHIVELOG mode.
5. Checkpoint Coordination: Although not directly responsible for checkpoints, LGWR triggers the checkpoint process by signaling the Checkpoint Process (CKPT). This action ensures that all data files are updated with the changes stored in the redo log files, thereby achieving a state of data consistency.
6. Batch Processing: LGWR employs batch processing techniques to optimize I/O operations. By grouping multiple redo entries, it minimizes the number of disk writes, thereby improving overall system performance.
7. Network Transmission for Standby Databases: In configurations involving standby databases, LGWR can also transmit redo data over the network to the standby instance. This capability is crucial for data protection and disaster recovery solutions like Oracle Data Guard.
8. Optimized Writes for Flashback Technology: By consistently writing redo records, LGWR also supports Oracle’s Flashback technology, enabling database administrators to revert to a previous state of the database, which is invaluable for data recovery and auditing purposes.

In summary, the Log Writer (LGWR) process is a foundational component within the Oracle Database ecosystem, integral for ensuring data durability, facilitating recovery, and optimizing performance. Understanding the pivotal role played by LGWR is crucial for database administrators who aim for high reliability, availability, and integrity of an Oracle Database instance.

The background process responsible for redo log buffer management is the LGWR, which writes the redo log buffer to the online redo log. LGWR writes all redo entries that have been copied into the buffer since the last time it wrote. The log writer process (LGWR) writes redo log entries in the SGA's redo log buffers to the redo log file. It also writes the transaction's SCN to the redo log file. This atomic event constitutes the commit of the transaction.
Note: Sometimes, if more buffer space is needed, LGWR writes redo log entries before a transaction is committed. These entries become permanent only if the transaction is later committed.

When a user commits a transaction, the transaction is assigned a system change number (SCN), which Oracle records along with the transaction's redo entries in the redo log. SCNs are recorded in the redo log so that recovery operations can be synchronized in Real Application Clusters and distributed databases.
In times of high activity, LGWR can write to the redo log file using group commits. For example, assume that a user commits a transaction. LGWR must write the transaction's redo entries to disk, and as this happens, other users issue COMMIT statements. However, LGWR cannot write to the redo log file to commit these transactions until it has completed its previous write operation. After the first transaction's entries are written to the redo log file, the entire list of redo entries of waiting transactions (not yet committed) can be written to disk in one operation, requiring less I/O than do transaction entries handled individually. Therefore, Oracle minimizes disk I/O and maximizes performance of LGWR. If requests to commit continue at a high rate, then every write (by LGWR) from the redo log buffer can contain multiple commit records.

An atomic transaction is a possibly complex series of actions that is considered as a single operation by those not involved directly in performing the transaction. If you transfer $100 from Alica's account to Bob's account, no one else can see the database while it is in an intermediate state where the money has been removed from Alice's account and not yet added to Bob's. The transaction either happens completely or none of its pieces happen and cannot happen partially. Atomic transactions are important for maintaining consistency and validity, and are thus important for the R and U parts of CRUD. Physical data containers such as notebooks support atomic transactions because typically only one person at a time can use them. You can finish a series of operations before you let someone else have a turn. Some of the most primitive kinds of databases, such as flat files and XML files do not inherently support atomic transactions, but the more advanced relational database products do. Those databases allow you to start a transaction and perform a series of operations. You can then either commit the transaction to make the changes permanent or rollback the transaction to undo them all and restore the database to the state it previously had, before you started the transaction. These databases also automatically rollback any transaction that is open if the database halts unexpectedly.

As changes are made to the database, redo log entries are added to the head of the log, and the head pointer is advanced. The LGWR process is constantly working from the other end of the log, the tail, and constantly writes redo log entries to disk. The next lesson explains this in more detail.

The log writer process (LGWR) is responsible for redo log buffer management.writing the redo log buffer to a redo log file on disk. LGWR writes all redo entries that have been copied into the buffer since the last time it wrote. The redo log buffer is a circular buffer. When LGWR writes redo entries from the redo log buffer to a redo log file, server processes can then copy new entries over the entries in the redo log buffer that have been written to disk. LGWR normally writes fast enough to ensure that space is always available in the buffer for new entries, even when access to the redo log is heavy. LGWR writes one contiguous portion of the buffer to disk. LGWR writes:

1. A commit record when a user process commits a transaction
2. Redo log buffers
   1. Every three seconds
   2. When the redo log buffer is one-third full
   3. When a DBWn process writes modified buffers to disk, if necessary

LGWR writes synchronously to the active mirrored group of redo log files. If one of the files in the group is damaged or unavailable, LGWR continues writing to other files in the group and logs an error in the LGWR trace file and in the system alert log. If all files in a group are damaged, or the group is unavailable because it has not been archived, LGWR cannot continue to function. When a user issues a COMMIT statement, LGWR puts a commit record in the redo log buffer and writes it to disk immediately, along with the transaction's redo entries.
The corresponding changes to data blocks are deferred until it is more efficient to write them. This is called a fast commit mechanism. The atomic write of the redo entry containing the transaction's commit record is the single event that determines the transaction has committed. Oracle returns a success code to the committing transaction, although the data buffers have not yet been written to disk.