Oracle redo log file location

A redo log file that is cycled back for use is given the next available log sequence number. Each online or archived redo log file is uniquely identified by its log sequence number.

During crash, instance, or media recovery, the database properly applies redo log files in ascending order by using the log sequence number of the necessary archived and redo log files. This section provides guidelines you should consider when configuring a database instance redo log and contains the following topics:.

To protect against a failure involving the redo log itself, Oracle Database allows a multiplexed redo log, meaning that two or more identical copies of the redo log can be automatically maintained in separate locations.

For the most benefit, these locations should be on separate disks. When redo log files are multiplexed, LGWR concurrently writes the same redo log information to multiple identical redo log files, thereby eliminating a single point of redo log failure. Multiplexing is implemented by creating groups of redo log files. A group consists of a redo log file and its multiplexed copies. Each identical copy is said to be a member of the group.

Each redo log group is defined by a number, such as group 1, group 2, and so on. Figure Multiplexed Redo Log Files. Each member in a group must be exactly the same size. Each member of a log file group is concurrently active—that is, concurrently written to by LGWR—as indicated by the identical log sequence numbers assigned by LGWR.

The specific reaction of LGWR when a redo log member is unavailable depends on the reason for the lack of availability, as summarized in the table that follows. If the database checkpoint has moved beyond the lost redo log, media recovery is not necessary, because the database has saved the data recorded in the redo log to the datafiles. You need only drop the inaccessible redo log group.

In most cases, a multiplexed redo log should be symmetrical: all groups of the redo log should have the same number of members. However, the database does not require that a multiplexed redo log be symmetrical. For example, one group can have only one member, and other groups can have two members. This configuration protects against disk failures that temporarily affect some redo log members but leave others intact. The only requirement for an instance redo log is that it have at least two groups.

Figure shows legal and illegal multiplexed redo log configurations. The second configuration is illegal because it has only one group. When setting up a multiplexed redo log, place members of a group on different physical disks. If a single disk fails, then only one member of a group becomes unavailable to LGWR and other members remain accessible to LGWR, so the instance can continue to function.

If you archive the redo log, spread redo log members across disks to eliminate contention between the LGWR and ARC n background processes. For example, if you have two groups of multiplexed redo log members a duplexed redo log , place each member on a different disk and set your archiving destination to a fifth disk. Datafiles should also be placed on different disks from redo log files to reduce contention in writing data blocks and redo records.

When setting the size of redo log files, consider whether you will be archiving the redo log. Redo log files should be sized so that a filled group can be archived to a single unit of offline storage media such as a tape or disk , with the least amount of space on the medium left unused. In this case, it is better to decrease the size of the redo log files slightly, so that two log groups could be archived on each tape.

All members of the same multiplexed redo log group must be the same size. Members of different groups can have different sizes. However, there is no advantage in varying file size between groups.

If checkpoints are not set to occur between log switches, make all groups the same size to guarantee that checkpoints occur at regular intervals. The best way to determine the appropriate number of redo log files for a database instance is to test different configurations.

The optimum configuration has the fewest groups possible without hampering LGWR from writing redo log information. In some cases, a database instance may require only two groups. In other situations, a database instance may require additional groups to guarantee that a recycled group is always available to LGWR.

During testing, the easiest way to determine whether the current redo log configuration is satisfactory is to examine the contents of the LGWR trace file and the database alert log. If messages indicate that LGWR frequently has to wait for a group because a checkpoint has not completed or a group has not been archived, add groups. Consider the parameters that can limit the number of redo log files before setting up or altering the configuration of an instance redo log.

The following parameters limit the number of redo log files that you can add to a database:. When the compatibility level is set earlier than Therefore, it is important to consider this limit before creating a database.

When compatibility is set to Oracle Database Backup and Recovery Advanced User's Guide to learn how checkpoints and the redo log impact instance recovery. You can force all enabled redo log threads to switch their current logs at regular time intervals.

The changes that are being applied by the standby database can lag behind the changes that are occurring on the primary database, because the standby database must wait for the changes in the primary database redo log to be archived into the archived redo log and then shipped to it. Setting this parameter lets you specify in seconds how long that lag can be.

If the following conditions are met, then the instance will switch the log:. In an Oracle Real Application Clusters environment, the instance also causes other threads to switch and archive their logs if they are falling behind.

It also provides an upper limit of how long in seconds the current log of the primary database can span. Because the estimated archival time is also considered, this is not the exact log switch time. The following initialization parameter setting sets the log switch interval to 30 minutes a typical value. However, in the case of irregularities of redo generation speed, the interval does provide an upper limit for the time range each current log covers. This can force frequent log switches.

Set the parameter to a reasonable value so as not to degrade the performance of the primary database. Plan the redo log of a database and create all required groups and members of redo log files during database creation. However, there are situations where you might want to create additional groups or members. For example, adding groups to a redo log can correct redo log group availability problems. Using group numbers can make administering redo log groups easier. Do not skip redo log file group numbers that is, do not number your groups 10, 20, 30, and so on , or you will consume unnecessary space in the control files of the database.

In some cases, it might not be necessary to create a complete group of redo log files. A group could already exist, but not be complete because one or more members of the group were dropped for example, because of a disk failure. In this case, you can add new members to an existing group. The following statement adds a new redo log member to redo log group number Notice that filenames must be specified, but sizes need not be. The size of the new members is determined from the size of the existing members of the group.

This procedure is necessary, for example, if the disk currently used for some redo log files is going to be removed, or if datafiles and a number of redo log files are stored on the same disk and should be separated to reduce contention.

Additionally, you might also need operating system privileges to copy files to the desired location and privileges to open and back up the database. Before relocating your redo logs, or making any other structural changes to the database, completely back up the database in case you experience problems while performing the operation. As a precaution, after renaming or relocating a set of redo log files, immediately back up the database control file.

Use the following steps for relocating redo logs. Our tactic is to add a new member for each group to the new location, then drop all log files in the old location. Since direct copying redo log files in ASM disk groups has some side effects for a RAC database, so we focus only on replacing old redo logs with new ones.

Furthermore, there're 6 redo log groups in the RAC database, 3 groups per thread. Which is, group 1, 3, 5 are for node 1, group 2, 4, 6 are for node 2. Making a log switch could be somewhat different between. Your email address will not be published. Save my name, email, and website in this browser for the next time I comment. Redo Log File. Single-Instance Database There're two approaches that can change the location of redo log files of a single-instance database.

Operating system files, such as redo log members, must be copied using the appropriate operating system commands. See your operating system specific documentation for more information about copying files. The following example uses operating system commands UNIX to move the redo log members to a new location:.

Skip Headers. The example used to illustrate these steps assumes: The log files are located on two disks: diska and diskb. Some operating systems allow you to use a character in place of the word HOST.