Oracle and DB2, Comparison and Compatibility/Process Model/Logging/Oracle

Oracle

In Oracle, transactions are logged by writing them to the undo log buffer in memory. These buffers are then written to log files on disk, and optionally the disk log files are written to an archive. All changes to the database including structural changes done with DDL (Create, Alter and Drop statements) are logged. In an active database, change is constant so the volume of change, plus the fact that the undo logs are duplicated for security means that there is a lot of data being generated by the DBMS itself as part of its operation. Changes made in shared memory are written to disk log files that are mirrored, so that each change is recorded twice. This is to protect the database in the event that one set of disk log files is unusable, a belt and braces approach. Because the mirrored disk log files are backups for each other, they should be on separate disk drives and separate controllers. The mirrored disk log files are called groups, and as one group fills up the DBMS switches to the next log file group. This process continues through the log file groups until the last group in the series is filled and then the process starts again with the first group. Log files are cyclically overwritten. The minimum number of groups needed to do this is two, but usually there are at least three. Since log files are cyclically overwritten they are not permanent, at some point in the future they will get written to again. You can take disk log files that are full and write them to an archive and theoretically protect every change ever to the database. The Archive process (ARCH) handles this, and once ARCH is complete it flags that the undo logs that it just archived are available to be overwritten. If you use ARCH it’s important to allocate enough space to the archive, since if this directory fills up, the database will stop.

The process that writes to the undo logs is Log Writer (LGWR). LGWR periodically copies the contents of the undo log segments in memory to the undo log files on disk. Additionally LGWR will signal a checkpoint when the redo log files are full. Checkpoint writes all committed transactions to the log files on disk and signals the Background Writer process(es) DBWR to write dirty buffers to disk.

Every log switch is flagged with a log sequence number that is written to the data file headers and control files so that the appropriate log files are used in a recovery.

In Oracle, transactions are written to the redo log buffer in memory. The LGWR process will periodically write the redo log buffer to the redo log files, and the ARCn process will write the redo log files to off-line storage if the database is running in ARCHIVE mode.

Logging in Oracle

The figure above describes the memory, disk and processes involved in logging.

The logging process (LGWR) writes information from the redo log buffer into the Log Files. The log files are a record of all the changes made to the database. These files are critical in the event of a recovery, so they are duplicated, and redo log information is written to both members (multiplexing). The information stored in the log files is all DML that change Tables and Indexes, all DDL (i.e. Create, Drop and Alter), and all commits.

The multiplexed log files are then organized in groups. As one set of log files fill up, Oracle will checkpoint and switch and the next set of files in the group are written to (Called a Log Switch). There will be at least two sets of log file groups for a database, typically there are three, and the maximum can be 255. The log files are written to cyclically so that when one group of log files is filled, the next group in the series is written to. When the last group in a series is written to, the process begins again with the first group in the series.

An additional level of security can be obtained by archiving the log files as they fill up, so that when they are written to again, not data is overwritten – this is called archive mode. You need to ensure that there is enough space to hold the archived log files since the database will hang if it tries to archive to a directory that is full. The archiver process (ARCn) is responsible for archiving the log files, and it copies redo log files to offline storage on each log switch. The archiver process(es) only exist on instances that have log archiving turned on, and are signalled by LGWR depending on system load.

Every time Oracle switches to a new set of redo log files, it generates a log sequence number that is written in the control files. This log sequence number is used to determine which log files to use in the event of a recovery. Control Files contain the structure of the database and there should be at least 3 for every database you have. They contain such things as the database name, create date, the entire pathname of all the data files and check point information for each database. They are used to mount the database, open it and access it. They store synchronization data in the event that the database needs to be recovered. Because the control files and log files store such critical information about the database, they should be on separate drives and controllers.

The Log Writer (LGWR) writes data from the redo log buffer pool to the current group of online undo logs. The log writer writes a redo log entry commit record when transactions commit, and then writes the commit and all changes associated with that commit to disk. When redo log entries are written to disk, only those entries added to the buffer since the last LGWR write are written to disk (so that duplicates are not written). LGWR will write to disk every three seconds and additionally when a redo log becomes one third full. LGWR will also write to disk if the database writer process(es) (DBWn) write dirty buffers to disk. Before DBWn writes to disk, all the redo entries associated with the changed data are written by LGWR. LGWR does this by signaling checkpoint (CKPT). CKPT signals a background write (DBWn) of updated committed blocks to on disk data files, and ensures that committed transactions in the log file are written to disk.