Note: Oracle recommends that you multiplex your redo log files. The loss of the log file data can be catastrophic if recovery is required. Depending on your configuration, this may impact overall database performance. LGWR writes to the available members of a group and ignores the unavailable members. LGWR cannot access the next group at a log switch because the group needs to be archived Database operation temporarily halts until the group becomes available or until the group is archived.
All members of the next group are inaccessible to LGWR at a log switch because of media failure Oracle Database returns an error, and the database instance shuts down. In this case, you may need to perform media recovery on the database from the loss of a redo log file.
All members of a group suddenly become inaccessible to LGWR while it is writing to them Oracle Database returns an error and the database instance immediately shuts down.
In this case, you may need to perform media recovery. If the media containing the log is not actually lost--for example, if the drive for the log was inadvertently turned off--media recovery may not be needed. In this case, you need only turn the drive back on and let the database perform automatic instance recovery.
Legal and Illegal Configurations In most cases, a multiplexed redo log should be symmetrical: all groups of the redo log should have the same number of members. Placing Redo Log Members on Different Disks When setting up a multiplexed redo log, place members of a group on different physical disks.
Planning the Size of Redo Log Files When setting the size of redo log files, consider whether you will be archiving the redo log. The minimum size permitted for a redo log file is 4 MB. See Also: Your operating system—specific Oracle documentation.
The default size of redo log files is operating system dependent. Planning the Block Size of Redo Log Files Unlike the database block size, which can be between 2K and 32K, redo log files always default to a block size that is equal to the physical sector size of the disk. Choosing the Number of Redo Log Files The best way to determine the appropriate number of redo log files for a database instance is to test different configurations.
Controlling Archive Lag You can force all enabled redo log threads to switch their current logs at regular time intervals. The current log contains redo records. This is the default setting. Failing to do so results in unpredictable behavior. All rights reserved. Book List. Master Index. This is the reason why it is recommended to multiplex the log members i.
The Redo Log file should be cleared when it is found to be corrupt and unusable. It does not matter if it was archived or not, it is required to clear the log file before you can use it. The commands to clear the Redo Log are given in a later section of this article, which does not require a database reboot.
By multiplexing, Oracle will maintain two or more identical copies grouped together of the Redo Log in separate locations. When multiplexing Redo Logs, make sure that the different members in the same group go to different disks for better recoverability. In this case, even if one disk fails, you will have the member on the other disk for recovery of the database instance. You can fetch the complete details about the existing groups, their members and the count using the following command:.
To start multiplexing a Redo Log file, you will first need to determine the number of existing groups and members. You can do this using the following command:. Once you have the information about the groups and its member, you can use the alter table command to another a log file to a group of your choice as follows. You can verify whether the switch was successful or not by monitoring the list of groups.
The current group will now have a new status of active, whereas the next group will now become the new current group. You can move your online Redo Logs by shutting down your database by using the alter database command as follows:.
In case you want to create a new Redo Log file-based group for your Oracle database, you can do this, using the alter database command, along with the add logfile parameter as follows:. To drop an existing member of your Redo Logs, you can use the alter database command, along with the drop logfile member clause as follows:. In case you want to clear out your Redo Log files in a situation of an unexpected failure or corruption, you can use the alter table clear logfile command as follows:.
To avoid archiving a log file that you want to clear, you can use the unarchive parameter in your command as follows:. The following data dictionary views give information about Redo Logs and groups.
By listing the online redo logs with their last update date and time at the operating system level, you can determine how often they are switching.
A better solution may be to increase the number of online redo logs so the additional space is provided while also having a frequent log switch smaller but more online redo logs.
The second potential problem concerns long-running jobs that are spending a large amount of time switching online redo logs. Long-running jobs are often much faster when the entire job fits into a single online redo log. For the online transaction processing OLTP type of environment, smaller online redo logs are usually better.
My rule of thumb is for online redo logs to switch every half hour not counting the long-running batch jobs that shorten this time. Here is a query that shows you the time between log switches. It can be handy in determining if you have a problem:. 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. The example used to illustrate these steps assumes:. The redo log files located on diska must be relocated to diskc. 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:.
In some cases, you may want to drop an entire group of redo log members. For example, you want to reduce the number of groups in an instance redo log. In a different case, you may want to drop one or more specific redo log members. For example, if a disk failure occurs, you may need to drop all the redo log files on the failed disk so that the database does not try to write to the inaccessible files.
In other situations, particular redo log files become unnecessary. For example, a file might be stored in an inappropriate location.
Before dropping a redo log group, consider the following restrictions and precautions:. An instance requires at least two groups of redo log files, regardless of the number of members in the groups.
A group comprises one or more members. You can drop a redo log group only if it is inactive. If you need to drop the current group, first force a log switch to occur. Make sure a redo log group is archived if archiving is enabled before dropping it. When a redo log group is dropped from the database, and you are not using the Oracle-managed files feature, the operating system files are not deleted from disk.
Rather, the control files of the associated database are updated to drop the members of the group from the database structure. After dropping a redo log group, make sure that the drop completed successfully, and then use the appropriate operating system command to delete the dropped redo log files. When using Oracle-managed files, the cleanup of operating systems files is done automatically for you.
Consider the following restrictions and precautions before dropping individual redo log members:. It is permissible to drop redo log files so that a multiplexed redo log becomes temporarily asymmetric. For example, if you use duplexed groups of redo log files, you can drop one member of one group, even though all other groups have two members each. However, you should rectify this situation immediately so that all groups have at least two members, and thereby eliminate the single point of failure possible for the redo log.
An instance always requires at least two valid groups of redo log files, regardless of the number of members in the groups. If the member you want to drop is the last valid member of the group, you cannot drop the member until the other members become valid.
A stale log file becomes valid again the next time its group is made the active group. You can drop a redo log member only if it is not part of an active or current group. If you want to drop a member of an active group, first force a log switch to occur. Make sure the group to which a redo log member belongs is archived if archiving is enabled before dropping the member. When a redo log member is dropped from the database, the operating system file is not deleted from disk.
Rather, the control files of the associated database are updated to drop the member from the database structure. After dropping a redo log file, make sure that the drop completed successfully, and then use the appropriate operating system command to delete the dropped redo log file. A log switch occurs when LGWR stops writing to one redo log group and starts writing to another. By default, a log switch occurs automatically when the current redo log file group fills.
You can force a log switch to make the currently active group inactive and available for redo log maintenance operations. For example, you want to drop the currently active group, but are not able to do so until the group is inactive. You may also wish to force a log switch if the currently active group needs to be archived at a specific time before the members of the group are completely filled.
0コメント