Oracle Database Tips by Donald Burleson

Addition of Log Groups and Log Member Files  

The number of redo logs is directly related to the number, size, and length of transactions that are performed in the database. Each transaction that alters the database is recorded in the redo log files. The size of redo logs is governed by the amount of data a database can afford to lose. If a database supports noncritical data, where loss of a few hours' worth of data is not important, then very large redo logs can be used. In a database where each piece of data is critical and loss of even minuscule portions of data could be catastrophic, a very small redo log is in order. If you have larger redo logs, fewer are needed; if you have small redo logs, many may be needed. Under Oracle7, Oracle8-8i and Oracle9i, two groups of at least one redo log each are required; again, three are suggested. Having multiple group members allows the shadowing of log files on multiple drives, thus making redo-log-loss-type failures almost impossible.   

Under Oracle7, Oracle8-8i, and Oracle9i, redo logs are members of groups, and each group should be located on a separate drive and can be associated with a single thread of the multithread server. In addition, Oracle allows redo log mirroring, where a redo log can be simultaneously copied to multiple disks at the same time by the LGWR process. This ensures that the loss of a group of log files will not affect operation. Groups are archived together. The MAXLOGMEMBERS parameter in the CREATE DATABASE statement determines the maximum number of redo logs in a group. The MAXLOGFILES parameter in the CREATE DATABASE statement determines the maximum number of groups. The reason that the MAXLOGFILES parameter is MAXLOGFILES and not MAXLOGGROUPS is that it is a carryover from the days before you could have multiple log members, as well as multiple log groups. In those days, a single parameter value controlled the number of files. Rather than change the parameter everywhere it is used, Oracle simply added a second parameter.     

Another factor is whether or not you are using archive logging. While a redo log (or log group) is being archived, it cannot be used. If a log switch goes to a redo log (or log group) that is being archived, the database stops. This is why three is the minimum number of logs or log groups recommended for an archive situation: one in use, one waiting to be used, and one archiving. Generally, it is suggested that several be available for use. In several installations where the logs were archived to disk, during heavy-use periods the disk filled, causing archiving to be suspended. Once the available logs filled, the database stopped. In Oracle8i and Oracle9i, multiplexing of archive logs is supported. Multiplexing of archive logs allows multiple copies to be written to several locations, thus enhancing recoverability.      

With multiple logs or log groups, you can have time to respond to this type of situation before the database has to be stopped. This also points out that you should keep a close eye on disk space usage for your archive destination(s). If the redo logs or groups are archived to tape, ensure that the log sizes are such that an equal number will fit on a standard tape to avoid wasting space and time. For example, if you have redo logs that are 1 MB in size on a version 7 database, and your tape has 90-MB capacity, then 90 will fit on the tape (approximately) with little waste. The entire group is archived as a unit with a size equal to that of one of the members.

See Code Depot

www.dba-oracle.com/oracle_scripts.htm