This chapter continues the discussion of files that are critical to running the Oracle9i database. The objectives important to this chapter are:
Redo log files contain information about changes to the database. Three kinds of information are listed:
Oracle 9i has several components that make redo log files work:
Eventually, redo log files become full. When this happens, the system switches to the next redo log group, and one of two things happens:
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:
The processes that take place around a COMMIT command are discussed.
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:
ALTER SYSTEM SWITCH LOGFILE;
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:
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:
ARCHIVE SYSTEM ARCHIVELOG CURRENT;
ARCHIVE SYSTEM ARCHIVELOG ALL;
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:
Three kinds of diagnostic files are listed. They are used for determining the cause of problems with the system: