If a media failure damages all members of an online redo log group, then different scenarios can occur depending on the type of online redo log group affected by the failure and the archiving mode of the database.
Your first task is to determine whether the damaged group is active or inactive. For example, enter:. If the affected group is active as in the preceding example , then follow the procedure in "Losing an Active Online Redo Log Group". If all members of an online redo log group with INACTIVE status are damaged, then the procedure depends on whether you can fix the media problem that damaged the inactive redo log group.
You can clear an inactive redo log group when the database is open or closed. The procedure depends on whether the damaged group has been archived. To clear an inactive, online redo log group that has been archived, use the following procedure:. Reinitialize the damaged log group. For example, to clear redo log group 2 , issue the following statement:.
Clearing a not-yet-archived redo log allows it to be reused without archiving it. This action makes backups unusable if they were started before the last change in the log, unless the file was taken offline prior to the first change in the log.
Hence, if you need the cleared log file for recovery of a backup, then you cannot recover that backup. Also, it prevents complete recovery from backups due to the missing log.
To clear an inactive, online redo log group that has not been archived, use the following procedure:. For example, to clear log group 2 , issue:.
The datafile and its entire tablespace have to be dropped because the redo necessary to bring it online is being cleared, and there is no copy of it. Immediately back up the whole database with an operating system utility, so that you have a backup you can use for complete recovery without relying on the cleared log group.
Relocate the redo log file onto alternative media by re-creating it under the currently configured redo log filename. Reuse the currently configured log filename to re-create the redo log file because the name itself is invalid or unusable for example, due to media failure.
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. 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. Search instead for. Did you mean:. Article Number Checkpoint cannot complete messages are generated in the Oracle alert log when Oracle redo logs are switching so fast, but a checkpoint associated with the log switch isn't completed fast enough for logs to be available when the next switch is needed.
As per Oracle documentation, redo logs are:. The most crucial structure for recovery operations, which consists of two or more preallocated files that store all changes made to the database as they occur. Every instance of an Oracle Database has an associated redo log to protect the database in case of an instance failure. Determine if your current configuration should be modified to avoid this waits.
As a general rule of thumb, Oracle recommends that you size your online redo logs not to switch more then 5 times per hour during peak DML times. This script display log switches in a linear format, useful for redo log sizing:. The following SQL will give advice on the optimal size of the redo logs, but manual inspection of redo log switch frequency is always the best approach:. Oracle 10g introduced an advisory utility that allows you to specify your optimal mean time to recovery MTTR recovery interval and uses this to suggest the optimal redo log size.
With 10 g , the Oracle database can now self-tune check-pointing to achieve good recovery times with low impact on normal throughput. You no longer have to set any checkpoint-related parameters.
0コメント