CIS 421b: Database Administration I

Chapter 5: The Redo Log Files and Diagnostic Files


This chapter continues the discussion of files that are critical to running the Oracle9i database. The objectives important to this chapter are:

  1. Understanding redo log files, groups of log files, and members of groups
  2. Managing redo log file groups and members
  3. Using diagnostic files

Redo log files contain information about changes to the database. Three kinds of information are listed:

  • checkpoints - markers in the database, like restore points in Windows; like a snapshot of the state of the database; checkpoints are set when a CHECKPOINT command is given, when the database is shut down, and when the log writer switches to a new redo log group
  • commands to make changes - Data Definition Language and Data Manipulation Language command are saved to undo and redo changes to the database
  • data changes - the redo log file holds records of each time a file is added, deleted, or changed in the database

Oracle 9i has several components that make redo log files work:

  • redo log group - each redo log file is a member of a group; the multiple files in a group are redundant copies of each other. A database is required to have at least two redo log groups, with at least one file in each group.
  • redo log member - as noted above, a redo log file
  • archived redo log files - offline copies of redo log files (created by ARC processes)
  • redo log buffer - memory block holding database changes that have not been written to files yet
  • LGWR process - process that writes to redo log files
  • ARC process - process that makes archive copies of redo log files
  • CKPT process - process that flushes buffers, which triggers the LGWR process

Eventually, redo log files become full. When this happens, the system switches to the next redo log group, and one of two things happens:

  • If the ARCHIVELOG mode is set, the ARC process makes an archive copy of the redo log that is now full. When the current redo log file becomes full, the system switches back to the first group of files and overwrites the old data.
  • If the NOARCHIVELOG mode is set, the system still switches back and forth between redo log groups, overwriting data, but no archive files are created.

Redo log files are meant to act as automatic recovery devices when there is a short term power loss. The text recommends operating in ARCHIVELOG mode to maintain an archive of redo log files, as well as performing standard backups of data. These precautions will provide more protection than redo log files alone.

The text offers a list of four events that will cause the LGWR process to write to a redo log file:

  • a COMMIT command is generated by any process
  • the redo log buffer becomes at least one third full
  • the buffer contains over 1 MB of updated records
  • a checkpoint is generated

The processes that take place around a COMMIT command are discussed.

  1. Recall that changes to data are first placed in a redo log buffer.
  2. A COMMIT command is given by the data user.
  3. The LGWR process writes the changed data to a redo log group. It also writes that a COMMIT command was given.
  4. The redo log buffer is flushed (emptied).
  5. The changed data is not yet written to the data files, but is kept in memory.
  6. The Oracle system reports that the COMMIT was successful.

Note that these processes report success, even though the database files have NOT been updated.

Sometimes you may need to manually command the system to switch log files. The text suggests that if you want to perform maintenance on a log file group, you need to switch to another group before you can work on the first group. The process is to get to an SQL prompt as a user with SYSDBA rights, and enter this command:


The next concept in the text addresses checkpoints. We are told that a checkpoint not only tells the LGWR process to write to log files, it also tells the DBWR process to write to data files all the data in dirty (used) buffers, and it increments the System Change Number, which is stored in the control file.

More information is given about redo log files. Remember that you have to have at least two redo log groups. Each group must have at least one file in it, but more files will provide multiplexing protection. Important facts:

  • All files in a given redo log group should be identical
  • The LGWR process writes to all files in a group at the same time, as long as they are undamaged
  • The LGWR process writes to only one group at a time
  • If a file in a group is damaged, it is ignored, and the LGWR process writes to the other files in that group
  • If all files in a group are damaged, the DBA must manually cause a log file switch. All database operation are halted until the switch occurs.
  • You can't switch to a group that is pending an archive action. If an archive is pending, it will take place, then the switch will take place.
  • If all redo log files in the group you are switching to are damaged, the system halts, and must be recovered by a DBA.

To examine the current redo log groups, start Enterprise Manager Console, open the Storage icon, and open Redo Log Groups. If you wish to add members (files) to a group, use this SQL command:

ALTER DATABASE ADD LOGFILE MEMBER '<drive_letter:\path_to_new_file>\new_logfile_name.log' TO GROUP x;

It may be necessary to add a new redo log group to a database. To do so, you need to determine the next logfile group number, how many logfiles you want in the group, the names of the log files, their locations, and the size to make them at the time of creation. An example is given:

ALTER DATABASE ADD LOGFILE GROUP x ( '<drive_letter:\path_to_new_file>\new_logfile_name.log', '<drive_letter:\path_to_new_file>\new_logfile_name.log' ) SIZE xxM ;

If you add hard drives to your system, you may want to move redo log files to them, or add new files there. If you need to do this, you must shut down the database first, then copy and rename files using the operating system on your server. Then you start the database in MOUNT mode, and use the ALTER command to point it to the new files.

Dropping a redo log file or redo log group is discussed. We are told that you cannot do either if the group is active, or if it is pending an archive.

Having made reference to archives for several pages, the text turns to commands to turn on archiving. A new argument is made for archives: they can be used to keep a standby database relatively up to date. Two commands are given that will force an archive, if the system is in ARCHIVELOG mode already:

This will cause the current redo log files to be archived.

This will cause all redo log files be to archived.

You can avoid some the work associated with copying and activating files if you use Oracle Managed Files. Of course, this means that you lose some control over those files as well, depending on the Oracle system to create, name, and manage the files.

As shown in a previous chapter, you can see information about log files with the V$LOG, V$LOGFILE, and other views of the database. In these views, it may be helpful to know the six states a redo log group may be in:

  • UNUSED - a group will be in this state if it has never been used
  • CURRENT - LGWR is using this group
  • ACTIVE - this group is not current, but holds information needed for recovery
  • CLEARING - this group is being purged of corrupted data
  • CLEARING_CURRENT - this group was being cleared of corrupt data, but an error was encountered
  • INACTIVE - this group does not hold data needed for recovery

Three kinds of diagnostic files are listed. They are used for determining the cause of problems with the system:

  • alert log file - lists major actions in the database, such as start and stop
  • background trace file - log file of errors from background processes
  • user trace file - log file of errors from user commands