Managing Redo log   «Prev  Next»
Lesson 2 Protecting redo log files
Objective Properly place and multiplex redo log files.

Properly place and multiplex redo log files in Oracle 12c

Here is the procedure to properly place and multiplex redo log files in Oracle 12c:
  1. Determine the number of redo log files: The number of redo log files needed depends on the size of the database and the amount of redo activity. A good starting point is to use two or three redo log files. You can always add more redo log files later if needed.
  2. Determine the size of the redo log files: The size of the redo log files should be large enough to hold the redo data generated during a typical database workload. A good starting point is to use redo log files that are 256MB or 512MB in size.
  3. Place the redo log files on separate disks: This will help to improve the performance of the redo log system. If the redo log files are all on the same disk, they will compete for I/O resources.
  4. Multiplex the redo log files: This means that you should create multiple redo log groups, each of which contains two or more redo log files. Multiplexing the redo log files will help to improve the performance of the redo log system by distributing the I/O load across multiple disks.
  5. Set the appropriate redo log parameters: There are a number of redo log parameters that you can set to control the behavior of the redo log system. These parameters include:
    • `LOG_BUFFER_SIZE`: This parameter determines the size of the redo log buffer. The redo log buffer is used to hold redo data before it is written to the redo log files.
    • `LGWR_PROCESS_COUNT`: This parameter determines the number of LGWR processes to run. The LGWR processes are responsible for writing redo data to the redo log files.
    • `LGWR_DELAY_TIME`: This parameter determines the amount of time that the LGWR processes will delay before writing redo data to the redo log files. This can help to improve the performance of the redo log system by reducing the amount of I/O.
  6. Monitor the redo log system:
    You should monitor the redo log system to make sure that it is functioning properly. You can use the following tools to monitor the redo log system:
    • `V\$LOGFILE`: This view provides information about the redo log files, such as the group ID, member number, and status.
    • `V\$LGWR_STATISTICS`: This view provides information about the LGWR processes, such as the number of redo entries written, the amount of time spent writing redo entries, and the amount of time spent waiting for I/O.

By following these steps, you can properly place and multiplex redo log files in Oracle 12c. This will help to improve the performance and reliability of your database.
Here are some additional tips for properly placing and multiplexing redo log files in Oracle 12c:
  • If you are using Oracle Real Application Clusters (RAC), you should place the redo log files on shared storage.** This will ensure that all of the RAC nodes have access to the redo log files.
  • You should regularly back up your redo log files. This will protect you from data loss in the event of a disk failure or other disaster.
  • You should use Oracle Enterprise Manager to monitor your redo log system. This will help you to identify and resolve any problems with the redo log system.

Purpose of redo logs

The purpose of a redo log file is to preserve a record of committed transactions for a database. Oracle uses this record for two purposes:
  1. crash recovery and
  2. media recovery.
The record preserved in the redo log allows Oracle to recover committed changes that would otherwise be lost in the event of a system crash. The combination of archived and online redo log files allows you to:
  1. Restore a data file from a backup, and
  2. Replay all the subsequent changes to bring the data file up to date.

Protecting redo logs

If you lose your redo log files, you lose your ability to recover fully from a crash or a drive failure. How then do you protect the redo log files? The answer is that you mirror them. Redo log files need to be mirrored just as control files need to be mirrored. The following multiplex illustrates one possible mirroring scenario:

The DBA has created three redo log groups. Each group consists of three member files, with each member on a drive separate from the others.
1) The DBA has created three redo log groups. Each group consists of three member files, with each member on a drive separate from the others.
As Oracle records changes in the database redo log, it writes the same information to all members of a group
2) As Oracle records changes in the database redo log, it writes the same information to all members of a group
Because the members are on different drives, the loss of any one drive does not put the database at risk. Here, one drive has been lost, but Oracle continues to record changes in the redo log members on surviving drives.
3) Because the members are on different drives, the loss of any one drive does not put the database at risk. Here, one drive has been lost, but Oracle continues to record changes in the redo log members on surviving drives.
Even if two drives were to be lost, an extreme case to be sure, Oracle would continue to function. That is because each group has three members. In this case, with the redo log dependant on the one surviving disk, you would want to quickly create new redo log members on a second disk.
4) Even if two drives were to be lost, an extreme case to be sure, Oracle would continue to function. That is because each group has three members. In this case, with the redo log dependant on the one surviving disk, you would want to quickly create new redo log members on a second disk.

Mirroring: hardware versus software

On some systems, particularly UNIX systems, you may have the choice of using either hardware or software mirroring. The question then becomes which to use? I once posed that question to an Oracle newsgroup, and it induced some amount of debate. There's certainly a performance advantage to mirroring in hardware, but there appears to be a slight theoretical risk of hardware-induced corruption as well. Personally, I'm a bit paranoid, and use both hardware and software mirroring, preferring not to place all my eggs in one basket.
With hardware mirroring, you can have just one redo log member in each group as far as Oracle is concerned, and leave the job of writing a duplicate copy to the hardware RAID controller. When software mirroring is used, it is the Oracle software that writes to each redo log member. In the next lesson, you will learn how to list the redo log files in your database to see how many groups you have and how many members you have in each group.