Online redo logs store a record of transactions that have occurred in your Oracle Database 12C. These logs serve the following purposes:
- Provide a mechanism for recording changes to the database so that in the event of a media failure, you have a method of recovering transactions.
- Ensure that in the event of total instance failure, committed transactions can be recovered (crash recovery) even if committed data changes have not yet been written to the data files.
- Allow administrators to inspect historical database transactions through the Oracle LogMiner utility.
- They are read by Oracle tools such as GoldenGate or Streams to replicate data.
You’re required to have at least two online redo log groups in your database. Each online redo log group must contain at least one online redo log member. The member is the physical file that exists on disk. You can create multiple members in each redo log group, which is known as multiplexing your online redo log group.
Tip I highly recommend that you multiplex your online redo log groups and, if possible, have each member on a separate physical device governed by a separate controller.
The log writer is the background process responsible for writing transaction information from the redo log buffer (in the SGA) to the online redo log files (on disk). Log writer flushes the contents of the redo log buffer when any of the following are true:
- A COMMIT or ROLLBACK issued.
- A log switch occurs.
- Three seconds go by.
- The redo log buffer is one-third full.
- The redo log buffer fills to one megabyte.
The online redo log group that the log writer is actively writing to is the current online redo log group. The log writer writes simultaneously to all members of a redo log group. The log writer needs to successfully write to only one member in order for the database to continue operating. The database ceases operating if the log writer can’t write successfully to at least one member of the current group.
When the current online redo log group fills up, a log switch occurs, and the log writer starts writing to the next online redo log group. The log writer writes to the online redo log groups in a round-robin fashion. Because you have a finite number of online redo log groups, eventually the contents of each online redo log group are overwritten. If you want to save a history of the transaction information, you must place your database in archivelog mode (see the article “Implementing Archivelog Mode” in my blog).
When your database is in archivelog mode, after every log switch the archiver background process copies the contents of the online redo log file to an archived redo log file. In the event of a failure the archived redo log files allow you to restore the complete history of transactions that have occurred since your last Oracle database 12C backup.
Figure 1 displays a typical setup for the online redo log files. This figure shows three online redo log groups, each containing two members. The database is in archivelog mode. In the figure, group 2 has recently been filled with transactions, a log switch has occurred, and the log writer is now writing to group 3. The archiver process is copying the contents of group 2 to an archived redo log file. When group 3 fills up, another log switch will occur, and the log writer will begin writing to group 1. At the same time, the archiver process will copy the contents of group 3 to archive log sequence 3 (and so forth).
The online redo log files aren’t intended to be backed up. These files contain only the most recent redo transaction information generated by the database. When you enable archiving, the archived redo log files are the mechanism for protecting your database transaction history.
Note In an Oracle Real Application Cluster (RAC) database, each instance has its own set of online redo logs. This is known as a thread of redo. Each RAC instance writes to its own online redo logs and generates its own thread of archive redo log files. Additionally, each instance must be able to read any other instance’s online redo logs. This is important because if one instance crashes, then the other surviving instances can initiate instance recovery via reading the crashed instance’s online redo logs.
The contents of the current online redo log files aren’t archived until a log switch occurs. This means that if you lose all members of the current online redo log file, you lose transactions. Listed next are several mechanisms you can implement to minimize the chance of failure with the online redo log files:
- Multiplex the groups.
- If possible, never allow two members of the same group to share the same controller.
- If possible, never put two members of the same group on the same physical disk.
- Ensure that OS file permissions are set appropriately (restrictive, that only the owner of the Oracle binaries has permissions to write and read).
- Use physical storage devices that are redundant (i.e., RAID [redundant array of inexpensive disks]).
- Appropriately size the log files, so that they switch and are archived at regular intervals.
- Consider setting the ARCHIVE_LAG_TARGET initialization parameter to ensure that the online redo logs are switched at regular intervals.
Note The only tool provided by Oracle that can protect you and preserve all committed transactions in the event you lose all members of the current online redo log group is Oracle Data Guard, implemented in maximum protection mode. See MOS note 239100.1 for more details regarding Oracle Data Guard protection modes.
The online redo log files are never backed up by an RMAN backup or by a user-managed hot backup. If you did back up the online redo log files, it would be meaningless to restore them. The online redo log files contain the latest redo generated by the database. You wouldn’t want to overwrite them from a backup with old redo information. For a database in archivelog mode the online redo log files contain the most recently generated transactions that are required to perform a complete recovery.
Displaying Online Redo Log Information
COL group# FORM 99999 COL thread# FORM 99999 COL grp_status FORM a10 COL member FORM a30 COL mem_status FORM a10 COL mbytes FORM 999999 -- SELECT a.group# ,a.thread# ,a.status grp_status ,b.member member ,b.status mem_status ,a.bytes/1024/1024 mbytes FROM v$log a, v$logfile b WHERE a.group# = b.group# ORDER BY a.group#, b.member; GROUP# THREAD# GRP_STATUS MEMBER MEM_STATUS MBYTES ------ ------- ---------- ------------------------------ ---------- ------- 1 1 CURRENT /u01/oraredo/O12C/redo01a.rdo 50 1 1 CURRENT /u02/oraredo/O12C/redo01b.rdo 50 2 1 INACTIVE /u01/oraredo/O12C/redo02a.rdo 50 2 1 INACTIVE /u02/oraredo/O12C/redo02b.rdo 50 3 1 INACTIVE /u01/oraredo/O12C/redo03a.rdo 50 3 1 INACTIVE /u02/oraredo/O12C/redo03b.rdo 50
When you’re diagnosing online redo log issues, the V$LOG and V$LOGFILE views are particularly helpful. You can query these views while the database is mounted or open.
Try to size the online redo logs so that they switch anywhere from two to six times per hour. The V$LOG_HISTORY contains a history of how frequently the online redo logs have switched. Execute this query to view the number of log switches per hour:
select count(*) ,to_char(first_time,'YYYY:MM:DD:HH24') from v$log_history group by to_char(first_time,'YYYY:MM:DD:HH24') order by 2; COUNT(*) TO_CHAR(FIRST ---------- ------------- 2 2014:09:24:04 80 2014:09:24:05 44 2014:09:24:06 10 2014:09:24:12
From the previous output, you can see that a great deal of log switch activity occurred from approximately 4:00 am to 6:00 am This could be due to a nightly batch job or users’ in different time zones updating data. For this database the size of the online redo logs should be increased. You should try to size the online redo logs to accommodate peak transaction loads on the Oracle database 12C.
The V$LOG_HISTORY derives its data from the control file. Each time there is a log switch, an entry is recorded in this view that details information such as the time of the switch and the system change number (SCN). As stated, a general rule of thumb is that you should size your online redo log files so that they switch approximately two to six times per hour. You don’t want them switching too often because there is overhead with the log switch. Oracle initiates a checkpoint as part of a log switch. During a checkpoint the database writer background process writes modified (also called dirty) blocks to disk, which is resource intensive.
Then again, you don’t want online redo log files never to switch, because the current online redo log contains transactions that you may need in the event of a recovery. If a disaster causes a media failure in your current online redo log, you can lose those transactions that haven’t been archived.
Tip Use the ARCHIVE_LAG_TARGET initialization parameter to set a maximum amount of time (in seconds) between log switches. A typical setting for this parameter is 1,800 seconds (30 minutes). A value of 0 (default) disables this feature. This parameter is commonly used in Oracle Data Guard environments to force log switches after the specified amount of time elapses.
You can also query the OPTIMAL_LOGFILE_SIZE column from the V$INSTANCE_RECOVERY view to determine if your online redo log files have been sized correctly:
SQL> select optimal_logfile_size from v$instance_recovery;
This column reports the redo log file size (in megabytes) that is considered optimal, based on the initialization parameter setting of FAST_START_MTTR_TARGET. Oracle recommends that you configure all online redo logs to be at least the value of OPTIMAL_LOGFILE_SIZE. However, when sizing your online redo logs, you must take into consideration information about your environment (such as the frequency of the switches).
Determining the Optimal Number of Redo Log Groups
Oracle requires at least two redo log groups in order to function. But, having just two groups sometimes isn’t enough. To understand why this is so, remember that every time a log switch occurs, it initiates a checkpoint. As part of a checkpoint the Oracle database 12C writer writes all modified (dirty) blocks from the SGA to the data files on disk. Also recall that the online redo logs are written to in a round-robin fashion and that eventually the information in a given log is overwritten. Before the log writer can begin to overwrite information in an online redo log, all modified blocks in the SGA associated with the redo log must first be written to a data file. If not all modified blocks have been written to the data files, you see this message in the alert.log file:
Thread 1 cannot allocate new log, sequence <sequence number> Checkpoint not complete
Another way to explain this issue is that Oracle needs to store in the online redo logs any information that would be required to perform a crash recovery. To help you visualize this, see Figure 2.
At time 1, Block A is read from Data File AA into the buffer cache and modified. At time 2 the redo-change vector information (how the block changed) is written to the log buffer. At time 3 the log-writer process writes the Block A change-vector information to online redo log 1. At time 4 a log switch occurs, and online redo log 2 becomes the current online redo log.
Now, suppose that online redo log 2 fills up quickly and another log switch occurs, at which point the log writer attempts to write to online redo log 1. The log writer isn’t allowed to overwrite information in online redo log 1 until the database block writer writes Block A to Data File AA. Until Block A is written to Data File AA, Oracle needs information in the online redo logs to recover this block in the event of a power failure or shutdown abort. Before Oracle overwrites information in the online redo logs, it ensures that blocks protected by redo have been written to disk. If these modified blocks haven’t been written to disk, Oracle temporarily suspends processing until this occurs. There are a few ways to resolve this issue:
- Add more redo log groups.
- Lower the value of FAST_START_MTTR_TARGET. Doing so causes the database writer process to write older modified blocks to disk in a shorter time frame.
- Tune the database-writer process (modify DB_WRITER_PROCESSES).
If you notice that the Checkpoint not complete message is occurring often (say, several times a day), I recommend that you add one or more log groups to resolve the issue. Adding an extra redo log gives the database writer more time to write modified blocks in the database buffer cache to the data files before the associated redo with a block is overwritten. There is little downside to adding more redo log groups. The main concern is that you could bump up against the MAXLOGFILES value that was used when you created the database. If you need to add more groups and have exceeded the value of MAXLOGFILES, then you must re-create your control file and specify a high value for this parameter.
If adding more redo log groups doesn’t resolve the issue, you should carefully consider lowering the value of FAST_START_MTTR_TARGET. When you lower this value, you can potentially see more I/O because the Oracle database 12C writer process is more actively writing modified blocks to data files. Ideally, it would be nice to verify the impact of modifying FAST_START_MTTR_TARGET in a test environment before making the change in production. You can modify this parameter while your instance is up; this means you can quickly modify it back to its original setting if there are unforeseen side effects.
Finally, consider increasing the value of the DB_WRITER_PROCESSES parameter. Carefully analyze the impact of modifying this parameter in a test environment before you apply it to production. This value requires that you stop and start your database; therefore, if there are adverse effects, downtime is required to change this value back to the original setting.
If you determine that you need to add an online redo log group, use the ADD LOGFILE GROUP statement. In this example the database already contains two online redo log groups that are sized at 50M each. An additional log group is added that has two members and is sized at 50MB:
alter database add logfile group 3 ('/u01/oraredo/O12C/redo03a.rdo', '/u02/oraredo/O12C/redo03b.rdo') SIZE 50M;
In this scenario I highly recommend that the log group you add be the same size and have the same number of members as the existing online redo logs. If the newly added group doesn’t have the same physical characteristics as the existing groups, it’s harder to accurately determine performance issues.
For example, if you have two log groups sized at 50MB, and you add a new log group sized at 500MB, this is very likely to produce the Checkpoint not complete issue described in the previous section. This is because flushing all modified blocks from the SGA that are protected by the redo in a 500MB log file can potentially take much longer than flushing modified blocks from the SGA that are protected by a 50MB log file.
You may need to change the size of your online redo logs (see the section “Determining the Optimal Size of Online Redo Log Groups,” earlier in this article). You can’t directly modify the size of an existing online redo log. To resize an online redo log, you have to first add online redo log groups that are the size you want, and then drop the online redo logs that are the old size.
Say you want to resize the online redo logs to be 200MB each. First, you add new groups that are 200MB, using the ADD LOGFILE GROUP statement. The following example adds log group 4, with two members sized at 200MB:
alter database add logfile group 4 ('/u01/oraredo/O12C/redo04a.rdo', '/u02/oraredo/O12C/redo04b.rdo') SIZE 200M;
Note You can specify the size of the log file in bytes, kilobytes, megabytes, or gigabytes.
After you’ve added the log files with the new size, you can drop the old online redo logs. A log group must have an INACTIVE status before you can drop it. You can check the status of the log group, as shown here:
SQL> select group#, status, archived, thread#, sequence# from v$log;
You can drop an inactive log group with the ALTER DATABASE DROP LOGFILE GROUP statement:
SQL> alter database drop logfile group <group #>;
If you attempt to drop the current online log group, Oracle returns an ORA-01623 error, stating that you can’t drop the current group. Use the ALTER SYSTEM SWITCH LOGFILE statement to switch the logs and make the next group the current group:
SQL> alter system switch logfile;
After a log switch the log group that was previously the current group retains an active status as long as it contains redo that Oracle requires to perform crash recovery. If you attempt to drop a log group with an active status, Oracle throws an ORA-01624 error, indicating that the log group is required for crash recovery. Issue an ALTER SYSTEM CHECKPOINT command to make the log group inactive:
SQL> alter system checkpoint;
Additionally, you can’t drop an online redo log group if doing so leaves your Oracle database 12C with only one log group. If you attempt to do this, Oracle throws an ORA-01567 error and informs you that dropping the log group isn’t permitted because it would leave you with fewer than two log groups for your database (as mentioned earlier, Oracle requires at least two redo log groups in order to function).
Dropping an online redo log group doesn’t remove the log files from the OS. You have to use an OS command to do this (such as the rm Linux/Unix command). Before you remove a file from the OS, ensure that it isn’t in use and that you don’t remove a live online redo log file. For every database on the server, issue this query to view which online redo log files are in use:
SQL> select member from v$logfile;
Before you physically remove a log file, first switch the online redo logs enough times that all online redo log groups have recently been switched; doing so causes the OS to write to the file and thus give it a new timestamp. For example, if you have three groups, make sure you perform at least three log switches:
SQL> alter system switch logfile; SQL> / SQL> /
Now, verify at the OS prompt that the log file you intend to remove doesn’t have a new timestamp. First, go to the directory containing the online redo log files:
$ cd /u01/oraredo/O12C
Then, list the files to view the latest modification date:
$ ls -altr
When you’re absolutely sure the file isn’t in use, you can remove it. The danger in removing a file is that if it happens to be an in-use online redo log, and the only member of a group, you can cause serious damage to your database. Ensure that you have a good backup of your database and that the file you’re removing isn’t used by any databases on the server.
Adding Online Redo Log Files to a Group
You may occasionally need to add a log file to an existing group. For example, if you have an online redo log group that contains only one member, you should consider adding a log file (to provide a higher level of protection against a single–log file member failure). Use the ALTER DATABASE ADD LOGFILE MEMBER statement to add a member file to an existing online redo log group. You need to specify the new member file location, name, and group to which you want to add the file:
SQL> alter database add logfile member '/u02/oraredo/O12C/redo01b.rdo' to group 1;
Make certain you follow standards with regard to the location and names of any newly added redo log files.
Removing Online Redo Log Files from a Group
Occasionally, you may need to remove an online redo log file from a group. For example, your Oracle database 12C may have experienced a failure with one member of a multiplexed group, and you want to remove the apostate member. First, make sure the log file you want to drop isn’t in the current group:
SELECT a.group#, a.member, b.status, b.archived, SUM(b.bytes)/1024/1024 mbytes FROM v$logfile a, v$log b WHERE a.group# = b.group# GROUP BY a.group#, a.member, b.status, b.archived ORDER BY 1, 2;
If you attempt to drop a log file that is in the group with the CURRENT status, you receive the following error:
ORA-01623: log 2 is current log for instance O12C (thread 1) - cannot drop
If you’re attempting to drop a member from the current online redo log group, then force a switch, as follows:
SQL> alter system switch logfile;
Use the ALTER DATABASE DROP LOGFILE MEMBER statement to remove a member file from an existing online redo log group. You don’t need to specify the group number because you’re removing a specific file:
SQL> alter database drop logfile member '/u01/oraredo/O12C/redo04a.rdo';
You also can’t drop the last remaining log file of a group. A group must contain at least one log file. If you attempt to drop the last remaining log file of a group, you receive the following error:
ORA-00361: cannot remove last log member ...
Sometimes, you need to move or rename online redo log files. For example, you may have added some new mount points to the system, and you want to move the online redo logs to the new storage. You can use two methods to accomplish this task:
- Add the new log files in the new location, and drop the old log files.
- Physically rename the files from the OS.
If you can’t afford any downtime, consider adding new log files in the new location and then dropping the old log files. See the section “Adding Online Redo Log Groups”, for details on how to add a log group. See also the section “Resizing and Dropping Online Redo Log Groups” for details on how to drop a log group.
Alternatively, you can physically move the files from the OS. You can do this with the database open or closed. If your Oracle database 12C is open, ensure that the files you move aren’t part of the current online redo log group (because those are actively written to by the log writer background process). It’s dangerous to try to do this task while your database is open because on an active system, the online redo logs may be switching at a rapid rate, which creates the possibility of attempting to move a file while it’s being switched to be the current online redo log. Therefore, I strongly recommend that you only try to do this while your database is closed.
The next example shows how to move the online redo log files with the database shut down. Here are the steps:
- Shut down your database:
SQL> shutdown immediate;
- From the OS prompt, move the files. This example uses the mv command to accomplish this task:
$ mv /u02/oraredo/O12C/redo02b.rdo /u01/oraredo/O12C/redo02b.rdo
- Start up your database in mount mode:
SQL> startup mount;
- Update the control file with the new file locations and names:
SQL> alter database rename file '/u02/oraredo/O12C/redo02b.rdo' to '/u01/oraredo/O12C/redo02b.rdo';
- Open your database:
SQL> alter database open;
You can verify that your online redo logs are in the new locations by querying the V$LOGFILE view. I recommend as well that you switch your online redo logs several times and then verify from the OS that the files have recent timestamps. Also check the alert.log file for any pertinent errors.