Log Files

Redo log files are often referred to as the online redo log files to distinguish them from the archive log files (covered in the next section). You can control the number and size of the redo log files for your system. Basically, having a small number of redo log files that are larger can be beneficial on log switches when you are archiving the log files. This spreads out the times when the redo logs are copied to archive logs. This is important when input/output transfer rates to the disks on which the archive log files are being created is limited. (You have to complete the copy operation before the online redo log can be overwritten.)

You have a fixed number of online redo log files that are used in a cyclical manner (see Figure 11.2). When you finish writing to the first file, further transactions are written to the second file. This process continues until you finish writing to the last file in the sequence, at which time you begin to write to the first file again. You have to be careful when you are operating in archive log mode, because you cannot overwrite the contents of an online redo log file until that file has been completely copied to an archive log file. This is important. If Oracle cannot write a redo log file to an archive log file because, for example, the file system for the archive log files is full or offline, Oracle cannot accept transactions. It sticks, giving error messages to users who try to perform updates, deletes, or insertions. Worse still, it sometimes gets so confused in this mode that it is very difficult to get things moving again.

Figure 11.2.  Online redo log file recycling.

---

Caution:  
Carefully monitor the file system where the archive log files are being written to prevent locking up Oracle’s capability of accepting transactions.

---

This setup, where one redo log file at a time records every transaction made to the system, can turn into an input/output bottleneck. You have the option of creating groups of redo log files. If one of the members of the group is on a disk that is busy, the transaction is written to an available redo log file in that group and the busy log file is synched-up at a later time. You can have several members of each of several groups of log files.

As with most of the topics in this book, there is more information to cover than space available. The following is an example of the command you use to determine what redo log files are associated with in your instance:

SQL> select * from v$logfile;



   GROUP# STATUS

--------- ------

MEMBER

----------------------------------------------------------------------

        2

C:\ORAWIN\DBS\wdblog2.ora



        1

C:\ORAWIN\DBS\wdblog1.ora



SQL>

Here are a few maintenance activities that you rarely might want to do with online redo logs:

•  You might want to add additional redo log files. If you use individual log files as opposed to groups, the command would be alter database add logfile ‘filename’ size ###K. If you are using log file groups, the command would be alter database add logfile group # (‘filename1’,’filename2’,…) size ###K.

•  You might want to delete existing redo log files. The command here would be alter database drop logfile ‘filename’ or alter database drop logfile group #.

•  You might want to manually switch which redo log file is currently being written to so that you can perform some maintenance. Oracle will not let you do any maintenance on a log file or log file group that is currently in use. Therefore, to switch log file or log file groups, you would use the command alter system switch logfile.

From the previous section, you should understand that archive log files are merely sequential copies of the online redo log files. Their contents is a series of transactions that have been applied to your database. What else do you need to know about archive log files? Two key points: the options that are available for storing archive log files and how to set up the database to use archive log files.

Archive log files have two basic storage options. The first option is to use space on one of your disk drives. The other option is to write these log files directly to magnetic tape. There are pros and cons to either approach:

•  Writing archive logs to tape saves the much more expensive disk storage space for other purposes.

•  Archive logs written to tape have to be recovered from the slower tape medium in the event you need to do a recovery.

•  Writing archive logs to tape requires a dedicated tape drive, which is not available on all systems.