Log File Tukiyooo 0 Log file Administration 23:27 <div dir="ltr" style="text-align: left;" trbidi="on"><a href="http://www.blogger.com/www.ipv4-ipv6.blogspot.com"></a><br /><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";"></span><div dir="ltr" style="text-align: left;" trbidi="on"><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><span style="font-size: 12pt;"> Remember, the instance is made up of the files used by Oracle, the processes or threads, and the memory used by Oracle. The files used by the database are made up of the following: </span></div><ul type="disc"><li class="MsoNormal" style="line-height: normal; mso-list: l8 level1 lfo1; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto; tab-stops: list .5in;"><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">The datafiles. These are the actual files that are used to store tablespaces. [free spaces]</span></li><li class="MsoNormal" style="line-height: normal; mso-list: l8 level1 lfo1; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto; tab-stops: list .5in;"><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">The redo log files (sometimes just known as the<b> <i>redo log</i></b>). This is where all redo information is kept. Without the redo log files you would not be able to recover from a system failure. </span></li><li class="MsoNormal" style="line-height: normal; mso-list: l8 level1 lfo1; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto; tab-stops: list .5in;"><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">The control files. These files contain information vital to the Oracle database. The information that is used to tell the instance where the datafiles and log files reside is stored in the control file. </span></li><li class="MsoNormal" style="line-height: normal; mso-list: l8 level1 lfo1; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto; tab-stops: list .5in;"><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">The parameter file. This file contains tuning information that is used by Oracle at startup. This is commonly referred to as the </span><span style="font-size: 10.0pt; mso-bidi-font-size: 11.0pt; mso-fareast-font-family: "Times New Roman";">init.ora</span><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";"> file. </span></li></ul><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">There are also the RDBMS binaries and other files such as backup files, archive log files, and so on, but they are not really part of the database itself, even though they are essential to the RDBMS.</span></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">As you saw on Day 7, "Administering Tablespaces," a tablespace can hold four different types of segments: </span></div><ul type="disc"><li class="MsoNormal" style="line-height: normal; mso-list: l24 level1 lfo2; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto; tab-stops: list .5in;"><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">Data segment--Used to hold tables and clusters </span></li><li class="MsoNormal" style="line-height: normal; mso-list: l24 level1 lfo2; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto; tab-stops: list .5in;"><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">Index segment--Used to hold indexes </span></li><li class="MsoNormal" style="line-height: normal; mso-list: l24 level1 lfo2; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto; tab-stops: list .5in;"><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">Rollback segment--Special types of segments that are used to store undo information </span></li><li class="MsoNormal" style="line-height: normal; mso-list: l24 level1 lfo2; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto; tab-stops: list .5in;"><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">Temporary segment--Used for storing temporary data </span></li></ul><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">Today you will look at the rollback segments. Data segments, index segments, and temp-orary segments are covered on Days 12-15. The rollback segment is not only important to the basic function of the Oracle RDBMS, but it has performance implications as well. </span></div><div class="MsoNormal" style="line-height: normal;"><a href="http://www.ipv4-ipv6.blogspot.com/" target="_blank">www.ipv4-ipv6.blogspot.com</a><br /><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto; mso-outline-level: 2;"><b style="color: lime;"><span style="font-size: 18pt;">Redo Log Files</span></b></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;">The most crucial structure for recovery operations is the <span class="bold">redo log</span>, 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.</div><div class="MsoNormal" style="line-height: normal;"><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";"><span style="font-size: small;"><b>or</b></span></span></div><div class="MsoNormal" style="line-height: normal;"><span style="font-size: 12pt;">The redo log files are used to store redo information. Each time data is changed in the database, a log record is written describing the change(s). With this information the database can be recovered in the event of a system failure.</span></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">If a catastrophic system failure occurs, such as a power failure, component failure, or similar occurrence, the Oracle instance will be aborted. The instance will be cut off immediately or, in the event of a disk failure, the instance might crash. If this occurs, all changed data in the buffer cache will be lost; only changes that have been written out to disk will be saved.</span></div><div class="MsoNormal" style="color: lime; line-height: normal;"><b><span style="font-size: large;">New Term: </span></b></div><div class="MsoNormal" style="line-height: normal;"><b><span style="font-size: 12pt;"> </span></b><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">When Oracle is restarted, the information in the redo log file will be used to reproduce changes that have been made to the database, thus saving as much work as possible. All previously committed transactions will be recovered; this is also known as being <i>rolled forward</i>. All transactions that had modified data but had not been committed will be backed out; this is known as <i>rolling back</i>.</span></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">The redo log file is necessary for proper recovery. If this file is lost due to a disk failure, you will not be able to recover in the event of a system failure; therefore, you must protect the redo log file against this kind of failure. I recommend you use disk mirroring or RAID-1 on all redo log files.</span></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">Because the redo log files are so critical to the recoverability of the system, it is recommended that you do not use a caching disk controller with write-caching unless that cache is backed up with a battery. In the event of a power failure, you must make sure that no redo information is lost. It is often recommended that write-caching not be used at all on the redo log, but I feel that if you have a battery back-up your risk is reduced. </span></div><div align="center" class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; text-align: center;"><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";"> </span><br /><hr align="center" size="2" width="100%" /><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";"> </span></div><div class="MsoNormal" style="color: lime; line-height: normal; margin-bottom: 0.0001pt;"><b><span style="font-size: 12pt;">WARNING:</span></b></div><div class="MsoNormal" style="line-height: normal; margin-bottom: 0.0001pt;"><b><span style="color: #000077; font-size: 12pt;"> </span></b><b><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";"> </span></b><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">If you use a write-caching on the controller that has the redo log files and it is not backed up with a battery, you are in danger of losing data. In the event of a power failure, you will lose redo information and might not be able to recover. </span></div><div align="center" class="MsoNormal" style="line-height: normal; margin-bottom: 5.0pt; text-align: center;"><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";"> </span><br /><hr align="center" size="2" width="100%" /><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";"> </span></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto; mso-outline-level: 3;"><b style="color: lime;"><span style="font-size: 13.5pt;">How Does the Redo Log Work?</span></b></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">Each change to the database is logged into the redo log. Because of this, in the event of a failure all changes made since the last backup can be recovered with the use of these redo log files. If the instance should fail due to a power failure or other system failure, the redo log files can recover all changes done since the last checkpoint. </span></div><div align="center" class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; text-align: center;"><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";"> </span><br /><hr align="center" size="2" width="100%" /><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";"> </span></div><div class="MsoNormal" style="color: lime; line-height: normal; margin-bottom: 0.0001pt;"><b><span style="font-size: 12pt;">NOTE:</span></b><b><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";"> </span></b></div><div class="MsoNormal" style="line-height: normal; margin-bottom: 0.0001pt;"><b><span style="font-size: 12pt;"> </span></b><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">A checkpoint causes all in-cache data blocks that have not been written out to disk to be written out to disk. These unwritten, changed buffers are called <i>dirty buffers</i>. These dirty buffers are what cause the system to need to be recovered. If there are no dirty buffers when the system fails, recovery time will be instantaneous. </span></div><div align="center" class="MsoNormal" style="line-height: normal; margin-bottom: 5.0pt; text-align: center;"><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";"> </span><br /><hr align="center" size="2" width="100%" /><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";"> </span></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">When a </span><span style="font-size: 10.0pt; mso-bidi-font-size: 11.0pt; mso-fareast-font-family: "Times New Roman";">COMMIT</span><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">operation is performed, the redo information is written into the redo log buffers. The LGWR process writes the redo log files with the information in the redo log buffer. The </span><span style="font-size: 10.0pt; mso-bidi-font-size: 11.0pt; mso-fareast-font-family: "Times New Roman";">COMMIT</span><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";"> operation is not completed until the redo log has been written. After that has occurred, that transaction is irrevocable and will be recovered in the event of a system failure. You can see how important the redo log file really is.</span></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">The redo log is made up of two or more redo log files or log file groups. A <i>log file group</i> is a set of files that Oracle automatically mirrors. In this manner the redo log is protected against disk failure. A redo log group is made up of one or more redo log files and must be protected against disk failure. If you are using disk mirroring to protect the redo log, it is not necessary to use log file groups; because the disk is protected, single log files are sufficient.</span></div><div class="MsoNormal" style="color: lime; line-height: normal;"><b><span style="font-size: 12pt;">New Term:</span></b></div><div class="MsoNormal" style="line-height: normal;"><b><span style="color: #000077; font-size: 12pt;"> </span></b><b><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";"> </span></b><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">The redo log has two or more log files or log file groups that are used in an alternating fashion. When the first log file has filled up, the logging operation moves to the next redo log file in the chain. If archiving is enabled, when it fills up and the log switch occurs, this file is copied to an <i>archive log file</i>. These archive log files are very important for the recoverability of the system in the event of a catastrophic failure.</span></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">Operations on the redo log files are done with the </span><span style="font-size: 10.0pt; mso-bidi-font-size: 11.0pt; mso-fareast-font-family: "Times New Roman";">ALTER DATABASE</span><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";"> command that was described on Day 6, "Administering Databases and Datafiles." Using the </span><span style="font-size: 10.0pt; mso-bidi-font-size: 11.0pt; mso-fareast-font-family: "Times New Roman";">ALTER DATABASE</span><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";"> command you can add redo log groups, add redo log files, rename redo log files, and so on. </span></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto; mso-outline-level: 3;"><br /></div><div class="MsoNormal" style="line-height: normal;"><br /></div><div class="MsoNormal" style="color: lime; line-height: normal;"><b><span style="font-size: 13.5pt;">Log Switches and Checkpoints</span></b></div><div class="MsoNormal" style="line-height: normal;"><br /></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><b style="color: lime;"><span style="font-size: 12pt;">New Term:</span></b><b><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";"> </span></b><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";"> </span><br /><span style="font-size: 12pt;">Each time a redo log file or log file group fills up, it switches to the next redo log file in the sequence. This switch, called the <i>log switch</i>, causes several automatic events to occur: </span></div><ul type="disc"><li class="MsoNormal" style="line-height: normal; mso-list: l3 level1 lfo3; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto; tab-stops: list .5in;"><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">Checkpointing--A log switch always causes a checkpoint to occur. The checkpoint flushes all dirty buffers from the Oracle buffer cache. This reduces the amount of time a recovery will take, if needed. </span></li><li class="MsoNormal" style="line-height: normal; mso-list: l3 level1 lfo3; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto; tab-stops: list .5in;"><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">Archiving--If archiving is turned on (and it should be), the log switch causes the redo log file that was just active to copy its contents to an <i>archive log file</i>. This archive log file is used in recovery if needed. </span></li><li class="MsoNormal" style="line-height: normal; mso-list: l3 level1 lfo3; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto; tab-stops: list .5in;"><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">Log Sequence Number--Each time a redo log file is reused, it is given a <i>log sequence number</i>. This log sequence number is also given to the associated archive log file. By having this number, the RDBMS can keep track of which log file and archive log files have been used. </span></li></ul><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">Archiving and checkpointing are covered on Days 16 and 17, "Understanding Effective Backup Techniques" and "Recovering the Database," where backup and recovery are covered in detail. </span></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto; mso-outline-level: 4;"><br /><b><span style="color: #000077; font-size: 12pt;">Log Switch and Checkpoint Intervals</span></b></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">You can use the </span><span style="font-size: 10.0pt; mso-bidi-font-size: 11.0pt; mso-fareast-font-family: "Times New Roman";">LOG_CHECKPOINT_INTERVAL</span><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";"> and </span><span style="font-size: 10.0pt; mso-bidi-font-size: 11.0pt; mso-fareast-font-family: "Times New Roman";">LOG_CHECKPOINT_TIMEOUT</span><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";"> initialization parameters to control the checkpoint interval. LOG_CHECKPOINT_INTERVAL The </span><span style="font-size: 10.0pt; mso-bidi-font-size: 11.0pt; mso-fareast-font-family: "Times New Roman";">LOG_CHECKPOINT_INTERVAL</span><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">parameter is set by the administrator to a number of operating system blocks that are used before the log switch occurs. For most operating systems, the size of the operating system block size is 512 bytes, so this parameter will define the number of 512-byte blocks that are used in the redo log before a checkpoint occurs.</span></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">If your redo log files are 10MB in size and you want the checkpoint interval to be one tenth of the redo log file or 1MB, use the following formula to determine the value of </span><span style="font-size: 10.0pt; mso-bidi-font-size: 11.0pt; mso-fareast-font-family: "Times New Roman";">LOG_CHECKPOINT_INTERVAL</span><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">:</span></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><span style="color: #0066ff; font-size: 10.0pt; mso-bidi-font-size: 11.0pt; mso-fareast-font-family: "Times New Roman";">LOG_CHECKPOINT_INTERVAL</span><span style="color: #0066ff; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";"> = 1MB / 512 (bytes/block) = 2,048 blocks</span></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">To accomplish this, set </span><span style="font-size: 10.0pt; mso-bidi-font-size: 11.0pt; mso-fareast-font-family: "Times New Roman";">LOG_CHECKPOINT_INTERVAL = 2048</span><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";"> in the parameter file. To have the checkpoint occur only at log switches, set the value of </span><span style="font-size: 10.0pt; mso-bidi-font-size: 11.0pt; mso-fareast-font-family: "Times New Roman";">LOG_CHECKPOINT_INTERVAL</span><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";"> to be larger than the size of your redo log files. LOG_CHECKPOINT_TIMEOUT The parameter </span><span style="font-size: 10.0pt; mso-bidi-font-size: 11.0pt; mso-fareast-font-family: "Times New Roman";">LOG_CHECKPOINT_TIMEOUT</span><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">specifies a time interval, in seconds, at which the checkpoint will occur. This will automatically run the checkpoint process at this interval. To set the checkpoint to occur every 10 minutes, for example, set </span><span style="font-size: 10.0pt; mso-bidi-font-size: 11.0pt; mso-fareast-font-family: "Times New Roman";">LOG_CHECKPOINT_TIMEOUT = 600</span><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">. By setting the checkpoint interval on a timer, you can be assured that checkpoints will happen regularly, even if there is not much activity at the time.</span></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><br /><b><span style="color: #000077; font-size: 12pt;">Forcing a Checkpoint</span></b></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">A checkpoint can be forced by hand. If you want to force a checkpoint, you can do it with the following command:</span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; tab-stops: 45.8pt 91.6pt 137.4pt 183.2pt 229.0pt 274.8pt 320.6pt 366.4pt 412.2pt 458.0pt 503.8pt 549.6pt 595.4pt 641.2pt 687.0pt 732.8pt;"><span style="color: #0066ff; font-size: 10.0pt; mso-fareast-font-family: "Times New Roman";">ALTER SYSTEM CHECKPOINT;</span></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">You might want to do this if you think that your system is at risk of some sort of failure, such as from a thunderstorm or other phenomenon that might cause a power outage or similar situation.</span></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><b><span style="color: #000077; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">Forcing a Log Switch</span></b></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">As with the checkpoint, a log switch can be forced by hand. If you want to force a log switch, you can do it with the following command:</span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; tab-stops: 45.8pt 91.6pt 137.4pt 183.2pt 229.0pt 274.8pt 320.6pt 366.4pt 412.2pt 458.0pt 503.8pt 549.6pt 595.4pt 641.2pt 687.0pt 732.8pt;"><span style="color: #0066ff; font-size: 10.0pt; mso-fareast-font-family: "Times New Roman";">ALTER SYSTEM SWITCH LOGFILE;</span></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">It is only in rare circumstances where you will need to switch log files. This may happen when you want to force an archive before some system maintenance or other occurrence where the system may be at risk, or you may want to do this before your regular backup of archive log files. </span></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto; mso-outline-level: 3;"><br /><b style="color: lime;"><span style="font-size: 13.5pt;">Sizing the Redo Log Files</span></b></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">Typically the size of the redo log file is based on the capability of the medium that will contain the archive log files. If the archive log files will be written out to cartridge tape that can hold 525MB, you should consider making the redo log files 520MB. This will allow you to copy one archive log file to tape and have a little space left over for a margin of error.</span></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">If you do not have a particular medium in mind for archiving, or if the space is unlimited, you should make the redo log file a manageable size. A very large redo log file, say 2GB in size, might be a little unmanageable. Copying such a file can take quite some time.</span></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">There is no rule of thumb for the size of the redo log files. Your own preference should help you decide on it. Remember, if you make them too big you could potentially go all day without performing a checkpoint. This can be dangerous because the longer you go without checkpointing, the longer the recovery interval would be in the event of a system failure. </span></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto; mso-outline-level: 3;"><br /><b style="color: lime;"><span style="font-size: 13.5pt;">Archiving the Redo Logs</span></b></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">When a log switch occurs, the log records in the filled redo log file are copied to an archive log file if archiving is enabled. This archiving is usually done automatically. Because the redo log file cannot be reused until the archive process has completed, you should make sure that you will not try to reuse that log file before the operation is complete. There are several ways to make sure that the archiving process happens quickly: </span></div><ul type="disc"><li class="MsoNormal" style="line-height: normal; mso-list: l2 level1 lfo4; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto; tab-stops: list .5in;"><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">Archive to disk. You can archive to disk and then copy those archive log files to tape later. This will prevent the archiving process from waiting for a tape drive or other, slower medium to complete. </span></li><li class="MsoNormal" style="line-height: normal; mso-list: l2 level1 lfo4; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto; tab-stops: list .5in;"><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">Use multiple log files. By having more than two redo log files, you can simultaneously archive two or more log files while a third is being used for logging. </span></li></ul><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">Archiving is very important to maintain recoverability in the database. Archiving and backups are covered in more detail on Day 16. </span></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto; mso-outline-level: 3;"><b><span style="color: #000077; font-size: 13.5pt; mso-fareast-font-family: "Times New Roman";">Adding Redo Log Files and Groups</span></b></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">As with many of the functions that you have seen in this book, there are several ways to add to the redo log. Most of these utilities have the option of using either a graphical or a command-line utility. </span></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto; mso-outline-level: 4;"><b><span style="color: #000077; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">Using the Enterprise Manager</span></b></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">If you choose, you can add log files to the redo log via the Enterprise Manager. To do so, drill down into the database that you want to modify using the Navigator pane and right-click the Redo Log Groups icon. You will see the Create option. Select the Create option and you will see the Create Redo Log Group screen (see Figure 8.1). From here you fill in the size of the new redo log file that you want to create, specify a name under the New Members label, and click Add to add the redo log member. After you have clicked Add, that member will appear in the Current Members list, as shown in Figure 8.2. </span></div><div align="center" class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; text-align: center;"><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";"> </span><br /><hr align="center" size="2" width="100%" /><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";"> </span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in;"><b><span style="color: #000077; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">NOTE:</span></b><b><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";"> </span></b><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">In this example I have closed all panes except for the Navigator pane. </span></div><div align="center" class="MsoNormal" style="line-height: normal; margin-bottom: 5.0pt; text-align: center;"><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";"> </span><br /><hr align="center" size="2" width="100%" /><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";"> </span></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">After you have entered all the filenames for the redo log group members, click OK to create the log group. To add a member to an already existing group, right-click on the Redo Log Group icon. This will bring up the Create Redo Log Member screen, as shown in Figure 8.3.</span></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="01"></a><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">Here you type the name of the redo log group member and click OK to create that member. To create a redo log group member with the same characteristics as an existing redo log group member, right-click the redo log group member that you want to duplicate and select the Create Like option. You will then see the Create Log Group Member screen, as shown in Figure 8.4. Simply type the new log group member's name and click the OK button to create the redo log group member.</span></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="04"></a><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">The options for the redo log group creation is somewhat limited in the Enterprise Manager. It is not possible to add redo log group members and groups from the Storage Manager. </span></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto; mso-outline-level: 4;"><br /><b><span style="color: #000077; font-size: 12pt;">Using the ALTER DATABASE Command</span></b></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">Log files or log file groups can be added or modified with the </span><span style="font-size: 10.0pt; mso-bidi-font-size: 11.0pt; mso-fareast-font-family: "Times New Roman";">ALTER DATABASE</span><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";"> command, as shown on Day 6. I prefer the command-line utilities because they can be scripted, and as such are a permanent record that can be used over and over again. An example of how to use the </span><span style="font-size: 10.0pt; mso-bidi-font-size: 11.0pt; mso-fareast-font-family: "Times New Roman";">ALTER DATABASE </span><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">command to add a log file is shown here:</span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; tab-stops: 45.8pt 91.6pt 137.4pt 183.2pt 229.0pt 274.8pt 320.6pt 366.4pt 412.2pt 458.0pt 503.8pt 549.6pt 595.4pt 641.2pt 687.0pt 732.8pt;"><span style="color: #0066ff; font-size: 10.0pt; mso-fareast-font-family: "Times New Roman";">ALTER DATABASE database</span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; tab-stops: 45.8pt 91.6pt 137.4pt 183.2pt 229.0pt 274.8pt 320.6pt 366.4pt 412.2pt 458.0pt 503.8pt 549.6pt 595.4pt 641.2pt 687.0pt 732.8pt;"><span style="color: #0066ff; font-size: 10.0pt; mso-fareast-font-family: "Times New Roman";">ADD LOGFILE ( `log3a', `log3b' ) SIZE 10M;</span></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">To add a new log file to an already existing group, you can use this command:</span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; tab-stops: 45.8pt 91.6pt 137.4pt 183.2pt 229.0pt 274.8pt 320.6pt 366.4pt 412.2pt 458.0pt 503.8pt 549.6pt 595.4pt 641.2pt 687.0pt 732.8pt;"><span style="color: #0066ff; font-size: 10.0pt; mso-fareast-font-family: "Times New Roman";">ALTER DATABASE database</span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; tab-stops: 45.8pt 91.6pt 137.4pt 183.2pt 229.0pt 274.8pt 320.6pt 366.4pt 412.2pt 458.0pt 503.8pt 549.6pt 595.4pt 641.2pt 687.0pt 732.8pt;"><span style="color: #0066ff; font-size: 10.0pt; mso-fareast-font-family: "Times New Roman";">ADD LOGFILE MEMBER `log3c' TO GROUP 3;</span></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">If you don't know the group name, you can use the same command and specify the other members of the log file group, as in</span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; tab-stops: 45.8pt 91.6pt 137.4pt 183.2pt 229.0pt 274.8pt 320.6pt 366.4pt 412.2pt 458.0pt 503.8pt 549.6pt 595.4pt 641.2pt 687.0pt 732.8pt;"><span style="color: #0066ff; font-size: 10.0pt; mso-fareast-font-family: "Times New Roman";">ALTER DATABASE database</span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; tab-stops: 45.8pt 91.6pt 137.4pt 183.2pt 229.0pt 274.8pt 320.6pt 366.4pt 412.2pt 458.0pt 503.8pt 549.6pt 595.4pt 641.2pt 687.0pt 732.8pt;"><span style="color: #0066ff; font-size: 10.0pt; mso-fareast-font-family: "Times New Roman";">ADD LOGFILE MEMBER `log3c' TO GROUP ( `log3a', `log3b');</span></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">As I have said before, by using a SQL script and the </span><span style="font-size: 10.0pt; mso-bidi-font-size: 11.0pt; mso-fareast-font-family: "Times New Roman";">ALTER DATABASE</span><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";"> command, you can preserve a permanent record of the change and then use the file as a template for other, similar operations. </span></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto; mso-outline-level: 3;"><br /><b><span style="color: #000077; font-size: 13.5pt;">Modifying Redo Log Files and Groups</span></b></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">As with many functions you have seen in this book, there are several ways to add to the redo log. Most of these utilities have an option of using either a graphical or a command-line utility. </span></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto; mso-outline-level: 4;"><b style="color: lime;"><span style="font-size: 12pt;">Using the Enterprise Manager</span></b></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">To modify a redo log group member, right-click that member via the Enterprise Manager's Navigator pane and choose the Quick Edit option. From here you will see the Quick Edit Redo Log Member screen, as shown in Figure 8.5. In this screen you can change the name of the redo log group member, thus causing the </span><span style="font-size: 10.0pt; mso-bidi-font-size: 11.0pt; mso-fareast-font-family: "Times New Roman";">ALTER DATABASE RENAME FILE</span><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">command to be run. As you can see here, the options for modifying the redo log via the Enterprise Manager are very limited. I prefer to use the </span><span style="font-size: 10.0pt; mso-bidi-font-size: 11.0pt; mso-fareast-font-family: "Times New Roman";">ALTER DATABASE</span><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";"> command because of its flexibility and options.</span></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto; mso-outline-level: 4;"><br /></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto; mso-outline-level: 4;"><b style="color: lime;"><span style="font-size: 12pt;">Using the ALTER DATABASE Command</span></b></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">You can modify log files or log file groups with the </span><span style="font-size: 10.0pt; mso-bidi-font-size: 11.0pt; mso-fareast-font-family: "Times New Roman";">ALTER DATABASE</span><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";"> command, as shown earlier today and on Day 6. Again, I really prefer the command-line utilities because they can be scripted, and as such are a permanent record that can be used over and over again. For example, a redo log file can be renamed with the command</span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; tab-stops: 45.8pt 91.6pt 137.4pt 183.2pt 229.0pt 274.8pt 320.6pt 366.4pt 412.2pt 458.0pt 503.8pt 549.6pt 595.4pt 641.2pt 687.0pt 732.8pt;"><span style="color: #0066ff; font-size: 10.0pt; mso-fareast-font-family: "Times New Roman";">ALTER DATABASE database</span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; tab-stops: 45.8pt 91.6pt 137.4pt 183.2pt 229.0pt 274.8pt 320.6pt 366.4pt 412.2pt 458.0pt 503.8pt 549.6pt 595.4pt 641.2pt 687.0pt 732.8pt;"><span style="color: #0066ff; font-size: 10.0pt; mso-fareast-font-family: "Times New Roman";">RENAME LOGFILE `log1' TO `log2';</span></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">Or you can delete a rollback segment with this command:</span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; tab-stops: 45.8pt 91.6pt 137.4pt 183.2pt 229.0pt 274.8pt 320.6pt 366.4pt 412.2pt 458.0pt 503.8pt 549.6pt 595.4pt 641.2pt 687.0pt 732.8pt;"><span style="color: #0066ff; font-size: 10.0pt; mso-fareast-font-family: "Times New Roman";">ALTER DATABASE database</span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; tab-stops: 45.8pt 91.6pt 137.4pt 183.2pt 229.0pt 274.8pt 320.6pt 366.4pt 412.2pt 458.0pt 503.8pt 549.6pt 595.4pt 641.2pt 687.0pt 732.8pt;"><span style="color: #0066ff; font-size: 10.0pt; mso-fareast-font-family: "Times New Roman";">DROP LOGFILE `log1';</span></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">You can drop an entire log file group with the following command:</span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; tab-stops: 45.8pt 91.6pt 137.4pt 183.2pt 229.0pt 274.8pt 320.6pt 366.4pt 412.2pt 458.0pt 503.8pt 549.6pt 595.4pt 641.2pt 687.0pt 732.8pt;"><span style="color: #0066ff; font-size: 10.0pt; mso-fareast-font-family: "Times New Roman";">ALTER DATABASE database</span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; tab-stops: 45.8pt 91.6pt 137.4pt 183.2pt 229.0pt 274.8pt 320.6pt 366.4pt 412.2pt 458.0pt 503.8pt 549.6pt 595.4pt 641.2pt 687.0pt 732.8pt;"><span style="color: #0066ff; font-size: 10.0pt; mso-fareast-font-family: "Times New Roman";">DROP LOGFILE GROUP 3;</span></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">If you don't know the number of the log file group, you can drop it by specifying the names of the log file group members:</span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; tab-stops: 45.8pt 91.6pt 137.4pt 183.2pt 229.0pt 274.8pt 320.6pt 366.4pt 412.2pt 458.0pt 503.8pt 549.6pt 595.4pt 641.2pt 687.0pt 732.8pt;"><span style="color: #0066ff; font-size: 10.0pt; mso-fareast-font-family: "Times New Roman";">ALTER DATABASE database</span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; tab-stops: 45.8pt 91.6pt 137.4pt 183.2pt 229.0pt 274.8pt 320.6pt 366.4pt 412.2pt 458.0pt 503.8pt 549.6pt 595.4pt 641.2pt 687.0pt 732.8pt;"><span style="color: #0066ff; font-size: 10.0pt; mso-fareast-font-family: "Times New Roman";">DROP LOGFILE GROUP (`log3a', `log3b');</span></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">If necessary, you can drop just a log file group member with this syntax:</span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; tab-stops: 45.8pt 91.6pt 137.4pt 183.2pt 229.0pt 274.8pt 320.6pt 366.4pt 412.2pt 458.0pt 503.8pt 549.6pt 595.4pt 641.2pt 687.0pt 732.8pt;"><span style="color: #0066ff; font-size: 10.0pt; mso-fareast-font-family: "Times New Roman";">ALTER DATABASE database</span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; tab-stops: 45.8pt 91.6pt 137.4pt 183.2pt 229.0pt 274.8pt 320.6pt 366.4pt 412.2pt 458.0pt 503.8pt 549.6pt 595.4pt 641.2pt 687.0pt 732.8pt;"><span style="color: #0066ff; font-size: 10.0pt; mso-fareast-font-family: "Times New Roman";">DROP LOGFILE GROUP MEMBER `log3b';</span></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto; mso-outline-level: 3;"><b><span style="color: #000077; font-size: 13.5pt; mso-fareast-font-family: "Times New Roman";">Characteristics of the Log Files</span></b></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">The redo log files are one of the few files in the Oracle database that are always written to in a sequential manner. Because redo records are only read during recovery, they are write-only files during normal operations.</span></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">Because of the sequential nature of the redo log files, by isolating these files onto separate disk volumes you can take advantage of the fact that sequential I/O is much faster than random I/O. Keep in mind that the archival operation reads from the redo log file, so if you have two redo log files on the same disk volume, the archive process in conjunction with the redo log operation will cause random I/O. </span></div><div align="center" class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; text-align: center;"><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";"> </span><br /><hr align="center" size="2" width="100%" /><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";"> </span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in;"><b style="color: lime;"><span style="font-size: 12pt;">NOTE:</span></b><b><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";"> </span></b><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">I use the term <i>disk volume</i> to refer to either a disk drive or set of disk drives in a RAID array. </span></div><div align="center" class="MsoNormal" style="line-height: normal; margin-bottom: 5.0pt; text-align: center;"><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";"> </span><br /><hr align="center" size="2" width="100%" /><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";"> </span></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">In most cases, the performance of the redo log operation is not usually a problem. If you are running in a high transaction rate environment, you might need to separate each redo log file on its own disk volume. </span></div><div align="center" class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; text-align: center;"><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";"> </span><br /><hr align="center" size="2" width="100%" /><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";"> </span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in;"><b><span style="color: #000077; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";"><span style="color: lime;">TIP</span>:</span></b><b><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";"> </span></b><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">The redo log files should be protected, either by using log file groups or with a RAID array. When using a RAID array (either hardware or software), use RAID-1 for the redo log files. RAID-1 offers the most protection and the fastest write performance. </span></div><div align="center" class="MsoNormal" style="line-height: normal; margin-bottom: 5.0pt; text-align: center;"><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";"> </span><br /><hr align="center" size="2" width="100%" /><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";"> </span></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">The performance of the archive log volume is not as important as that of the redo log volume, but it is still fairly important. It is necessary that the archival operation be completed before you need to reuse the redo log file.</span></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">In many cases, archival information can be kept on another system and restored when necessary. If you are doing this, or are keeping your archive log files on tape, you might want to archive to disk first and then copy to tape or to the network so you can restore the data more quickly. If you are copying your data to a backup system, you can use RAID-5, which is slower but less costly. In any case, by archiving to a temporary area first, you free up the redo log file in the fastest possible time. This is covered in more detail on Day 16. </span></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto; mso-outline-level: 2;"><b><span style="color: #330099; font-size: 18.0pt; mso-fareast-font-family: "Times New Roman";">Planning the Online Redo Log</span></b></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="316"></a><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">Every instance of an Oracle database has an associated <i>online redo log</i>, which is a set of two or more online log files that record all committed changes made to the database. Online redo logs serve to protect the database in the event of an instance failure. Whenever a transaction is committed, the corresponding redo entries temporarily stored in redo log buffers of the system global area are written to an online redo log file by the background process LGWR. </span></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="324"></a><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">Online redo log files are used in a cyclical fashion; for example, if two files constitute the online redo log, the first file is filled, the second file is filled, the first file is reused and filled, the second file is reused and filled, and so on. Each time a file is filled, it is assigned a <i>log sequence number </i>to identify the set of redo entries. </span></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="328"></a><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">This section describes guidelines you should consider when configuring a database instance's online redo log, and includes the following topics: </span></div><ul type="disc"><li class="MsoNormal" style="line-height: normal; mso-list: l5 level1 lfo6; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto; tab-stops: list .5in;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="330"></a><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";"><a href="http://www.csee.umbc.edu/portal/help/oracle8.bak/server803/A54641_01/ch5.htm#338"><span style="color: blue;">Multiplex the Online Redo Log</span></a> </span></li><li class="MsoNormal" style="line-height: normal; mso-list: l5 level1 lfo6; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto; tab-stops: list .5in;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="332"></a><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";"><a href="http://www.csee.umbc.edu/portal/help/oracle8.bak/server803/A54641_01/ch5.htm#367"><span style="color: blue;">Place Online Redo Log Members on Different Disks</span></a> </span></li><li class="MsoNormal" style="line-height: normal; mso-list: l5 level1 lfo6; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto; tab-stops: list .5in;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="334"></a><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";"><a href="http://www.csee.umbc.edu/portal/help/oracle8.bak/server803/A54641_01/ch5.htm#379"><span style="color: blue;">Set the Size of Online Redo Log Members</span></a> </span></li><li class="MsoNormal" style="line-height: normal; mso-list: l5 level1 lfo6; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto; tab-stops: list .5in;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="336"></a><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";"><a href="http://www.csee.umbc.edu/portal/help/oracle8.bak/server803/A54641_01/ch5.htm#395"><span style="color: blue;">Choose an Appropriate Number of Online Redo Log Files</span></a> </span></li></ul><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto; mso-outline-level: 3;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="338"></a><b><span style="color: #330099; font-size: 13.5pt; mso-fareast-font-family: "Times New Roman";">Multiplex the Online Redo Log</span></b></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="348"></a><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">The online redo log of a database instance should consist of multiplexed groups of online redo log files. Furthermore, members in the same group should be stored on separate disks so that no single disk failure can cause LGWR and the database instance to fail. </span></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="1272"></a><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">To avoid losing a database due to a single point of failure, Oracle can maintain multiple sets of on-line redo log files. A <i>multiplex online redo log</i>consists of copies of online redo log files physically located on separate disks; changes made to one member of the group are made to all members. If a disk that contains an online redo log file fails, other copies are still intact and available to Oracle. System operation is not interrupted and the lost online redo log files can be easily recovered. </span></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="1275"></a><b><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">Warning: </span></b><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">Although the Oracle Server allows multiplexed groups to contain different numbers of members, this state should only be the temporary result of an abnormal situation such as a disk failure damaging a member of a group. If any group contains only one member, the failure of the disk containing that member could cause Oracle to halt. </span></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="365"></a><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">While multiplexed groups require extra storage space, the cost of this space is usually insignificant compared to the potential cost of lost data (if a disk failure destroys a non-multiplexed online redo log). </span></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto; mso-outline-level: 3;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="367"></a><b><span style="color: #330099; font-size: 13.5pt; mso-fareast-font-family: "Times New Roman";">Place Online Redo Log Members on Different Disks</span></b></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="373"></a><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">When setting up a multiplex online redo log, place members of a group on different disks. This way, if a single disk fails, only one member of a group becomes unavailable to LGWR and other members remain accessible to LGWR, so the instance can continue to function. </span></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="375"></a><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">If you archive the redo log, spread online redo log members across disks to eliminate contention between the LGWR and ARCH background processes. For example, if you have two groups of duplexed online redo log members, place each member on a different disk and set your archiving destination to a fifth disk. This way, there is never contention between LGWR (writing to the members) and ARCH (reading the members). </span></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="377"></a><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">Datafiles and online redo log files should also be on different disks to reduce contention in writing data blocks and redo entries. </span></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto; mso-outline-level: 3;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="379"></a><b><span style="color: #330099; font-size: 13.5pt; mso-fareast-font-family: "Times New Roman";">Set the Size of Online Redo Log Members</span></b></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="385"></a><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">When setting the size of online redo log files, consider whether you will be archiving the redo log. Online 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. For example, suppose only one filled online redo log group can fit on a tape and 49% of the tape's storage capacity remains unused. In this case, it would be better to decrease the size of the online redo log files slightly, so that two log groups could be archived per tape. </span></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="389"></a><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">With multiplex groups of online redo logs, all members of the same 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. </span></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="393"></a><b><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">See Also:</span></b><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";"> The default size of online redo log files is operating system -dependent; for more details see your operating system-specific Oracle documentation. </span><br /><br /></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto; mso-outline-level: 3;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="395"></a><b><span style="color: #330099; font-size: 13.5pt; mso-fareast-font-family: "Times New Roman";">Choose an Appropriate Number of Online Redo Log Files</span></b></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="399"></a><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">The best way to determine the appropriate number of online redo log files for a database instance is to test different configurations. The optimum configuration has the fewest groups possible without hampering LGWR's writing redo log information. </span></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="401"></a><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">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 if the current online redo log configuration is satisfactory is to examine the contents of the LGWR trace file and the database's ALERT file. 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. </span></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="403"></a><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">Consider the parameters that can limit the number of online redo log files before setting up or altering the configuration of an instance's online redo log. The following three parameters limit the number of online redo log files that you can add to a database: </span></div><ul type="disc"><li class="MsoNormal" style="line-height: normal; mso-list: l16 level1 lfo7; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto; tab-stops: list .5in;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="409"></a><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">The MAXLOGFILES parameter used in the CREATE DATABASE statement determines the maximum number of groups of online redo log files per database; group values can range from 1 to MAXLOGFILES. The only way to override this upper limit is to re-create the database or its control file; thus, it is important to consider this limit <i>before</i> creating a database. If MAXLOGFILES is not specified for the CREATE DATABASE statement, Oracle uses an operating system default value. </span></li><li class="MsoNormal" style="line-height: normal; mso-list: l16 level1 lfo7; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto; tab-stops: list .5in;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="415"></a><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">The LOG_FILES parameter (in the parameter file) can temporarily decrease the maximum number of groups of online redo log files for the duration of the current instance. However, LOG_FILES cannot override MAXLOGFILES to increase the limit. If LOG_FILES is not set in the database's parameter file, Oracle uses an operating system-specific default value. </span></li><li class="MsoNormal" style="line-height: normal; mso-list: l16 level1 lfo7; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto; tab-stops: list .5in;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="421"></a><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">The MAXLOGMEMBERS parameter used in the CREATE DATABASE statement determines the maximum number of members per group. As with MAXLOGFILES, the only way to override this upper limit is to re-create the database or control file; thus, it is important to consider this limit <i>before</i> creating a database. If no MAXLOGMEMBERS parameter is specified for the CREATE DATABASE statement, Oracle uses an operating system default value. </span></li></ul><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="425"></a><b><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">See Also:</span></b><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";"> For the default and legal values of the MAXLOGFILES and MAXLOGMEMBERS parameters, and the LOG_FILES initialization parameter, see your operating system-specific Oracle documentation. </span></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto; mso-outline-level: 2;"><br /></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto; mso-outline-level: 2;"><b><span style="color: #330099; font-size: 18.0pt; mso-fareast-font-family: "Times New Roman";">Creating Online Redo Log Groups and Members</span></b></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="439"></a><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">You can create groups and members of online redo log files during or after database creation. If you can, plan the online redo log of a database and create all required groups and members of online redo log files during database creation. To create new online redo log groups and members, you must have the ALTER DATABASE system privilege. </span></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="441"></a><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">In some cases, you might need to create additional groups or members of online redo log files. For example, adding groups to an online redo log can correct redo log group availability problems. A database can have up to MAXLOGFILES groups. </span></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto; mso-outline-level: 4;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="443"></a><b><span style="color: #330099; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">Creating Online Redo Log Groups</span></b></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="447"></a><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">To create a new group of online redo log files, use either the Add Logfile Group property sheet of Enterprise Manager, or the SQL command ALTER DATABASE with the ADD LOGFILE parameter. </span></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="451"></a><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">The following statement adds a new group of redo logs to the database: </span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; tab-stops: 45.8pt 91.6pt 137.4pt 183.2pt 229.0pt 274.8pt 320.6pt 366.4pt 412.2pt 458.0pt 503.8pt 549.6pt 595.4pt 641.2pt 687.0pt 732.8pt;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="453"></a><span style="font-size: 10.0pt; mso-fareast-font-family: "Times New Roman";">ALTER DATABASE</span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; tab-stops: 45.8pt 91.6pt 137.4pt 183.2pt 229.0pt 274.8pt 320.6pt 366.4pt 412.2pt 458.0pt 503.8pt 549.6pt 595.4pt 641.2pt 687.0pt 732.8pt;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="455"></a><span style="font-size: 10.0pt; mso-fareast-font-family: "Times New Roman";"><span style="mso-spacerun: yes;"> </span>ADD LOGFILE ('log1c', 'log2c') SIZE 500K;</span></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="461"></a><b><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">Note:</span></b><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";"> Fully specify filenames of new log members to indicate where the operating system file should be created; otherwise, the file is created in the default directory of the database server, which is operating system-dependent. If you want to reuse an existing operating system file, you do not have to indicate the file size. </span></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="463"></a><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">Using the ALTER DATABASE statement with the ADD LOGFILE option, you can specify the number that identifies the group with the GROUP option: </span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; tab-stops: 45.8pt 91.6pt 137.4pt 183.2pt 229.0pt 274.8pt 320.6pt 366.4pt 412.2pt 458.0pt 503.8pt 549.6pt 595.4pt 641.2pt 687.0pt 732.8pt;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="465"></a><span style="font-size: 10.0pt; mso-fareast-font-family: "Times New Roman";">ALTER DATABASE</span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; tab-stops: 45.8pt 91.6pt 137.4pt 183.2pt 229.0pt 274.8pt 320.6pt 366.4pt 412.2pt 458.0pt 503.8pt 549.6pt 595.4pt 641.2pt 687.0pt 732.8pt;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="467"></a><span style="font-size: 10.0pt; mso-fareast-font-family: "Times New Roman";"><span style="mso-spacerun: yes;"> </span>ADD LOGFILE GROUP 10 ('log1c', 'log2c') SIZE 500K;</span></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="469"></a><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">Using group numbers can make administering redo log groups easier. However, the group number must be between 1 and MAXLOGFILES; 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. </span></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto; mso-outline-level: 4;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="471"></a><b><span style="color: #330099; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">Creating Online Redo Log Members</span></b></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="477"></a><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">In some cases, you might not need to create a complete group of online redo log files; the group may 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. </span></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="485"></a><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">To create new online redo log members for an existing group, use the Add Logfile Member property sheet of Enterprise Manager, or the SQL command ALTER DATABASE with the ADD LOG MEMBER parameter. </span></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="487"></a><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">The following statement adds a new redo log member to redo log group number 2: </span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; tab-stops: 45.8pt 91.6pt 137.4pt 183.2pt 229.0pt 274.8pt 320.6pt 366.4pt 412.2pt 458.0pt 503.8pt 549.6pt 595.4pt 641.2pt 687.0pt 732.8pt;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="489"></a><span style="font-size: 10.0pt; mso-fareast-font-family: "Times New Roman";">ALTER DATABASE</span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; tab-stops: 45.8pt 91.6pt 137.4pt 183.2pt 229.0pt 274.8pt 320.6pt 366.4pt 412.2pt 458.0pt 503.8pt 549.6pt 595.4pt 641.2pt 687.0pt 732.8pt;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="491"></a><span style="font-size: 10.0pt; mso-fareast-font-family: "Times New Roman";"><span style="mso-spacerun: yes;"> </span>ADD LOGFILE MEMBER 'log2b' TO GROUP 2;</span></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="493"></a><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">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. </span></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="495"></a><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">When using the ALTER DATABASE command, you can alternatively identify the target group by specifying all of the other members of the group in the TO parameter, as shown in the following example: </span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; tab-stops: 45.8pt 91.6pt 137.4pt 183.2pt 229.0pt 274.8pt 320.6pt 366.4pt 412.2pt 458.0pt 503.8pt 549.6pt 595.4pt 641.2pt 687.0pt 732.8pt;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="497"></a><span style="font-size: 10.0pt; mso-fareast-font-family: "Times New Roman";">ALTER DATABASE</span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; tab-stops: 45.8pt 91.6pt 137.4pt 183.2pt 229.0pt 274.8pt 320.6pt 366.4pt 412.2pt 458.0pt 503.8pt 549.6pt 595.4pt 641.2pt 687.0pt 732.8pt;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="499"></a><span style="font-size: 10.0pt; mso-fareast-font-family: "Times New Roman";"><span style="mso-spacerun: yes;"> </span>ADD LOGFILE MEMBER 'log2c' TO ('log2a', 'log2b');<i><span style="mso-spacerun: yes;"> </span></i></span></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="505"></a><b><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">Note: </span></b><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">Fully specify the filenames of new log members to indicate where the operating system files should be created; otherwise, the files will be created in the default directory of the database server.</span></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto; mso-outline-level: 2;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="512"></a><b><span style="color: #330099; font-size: 18.0pt; mso-fareast-font-family: "Times New Roman";">Renaming and Relocating Online Redo Log Members </span></b></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="518"></a><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">You can rename online redo log members to change their locations. This procedure is necessary, for example, if the disk currently used for some online redo log files is going to be removed, or if datafiles and a number of online redo log files are stored on the same disk and should be separated to reduce contention. </span></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="522"></a><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">To rename online redo log members, you must have the ALTER DATABASE system privilege. Additionally, you might also need operating system privileges to copy files to the desired location and privileges to open and back up the database. </span></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="524"></a><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">Before renaming any online redo log members, ensure that the new online redo log files already exist. </span></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="1282"></a><b><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">Warning:</span></b><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";"> The following steps only modify the internal file pointers in a database's control files; they do not physically rename or create any operating system files. Use your computer's operating system to copy the existing online redo log files to the new location. </span></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="533"></a><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">Rename online redo log members with the Rename Logfile Member property sheet of Enterprise Manager, or the SQL command ALTER DATABASE with the RENAME FILE parameter. </span></div><ol start="1" type="1"><li class="MsoNormal" style="line-height: normal; mso-list: l23 level1 lfo8; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto; tab-stops: list .5in;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="535"></a><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">Back up the database. </span></li></ol><div class="MsoNormal" style="line-height: normal; margin-bottom: 5.0pt;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="537"></a><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">Before making any structural changes to a database, such as renaming or relocating online redo log members, completely back up the database (including the control file) in case you experience any problems while performing this operation. </span></div><ol start="2" type="1"><li class="MsoNormal" style="line-height: normal; mso-list: l10 level1 lfo9; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto; tab-stops: list .5in;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="539"></a><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">Copy the online redo log files to the new location. </span></li></ol><div class="MsoNormal" style="line-height: normal; margin-bottom: 5.0pt;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="541"></a><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">Operating system files, such as online redo log members, must be copied using the appropriate operating system commands. See your operating system manual for more information about copying files. </span></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="550"></a><b><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">Suggestion:</span></b><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";"> You can execute an operating system command to copy a file without exiting Enterprise Manager. Use the Enterprise Manager HOST command. </span></div><ol start="3" type="1"><li class="MsoNormal" style="line-height: normal; mso-list: l1 level1 lfo10; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto; tab-stops: list .5in;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="552"></a><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">Rename the online redo log members. </span></li></ol><div class="MsoNormal" style="line-height: normal; margin-bottom: 5.0pt;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="554"></a><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">Use the Rename Online Redo Log Member dialog box, or the ALTER DATABASE command with the RENAME FILE clause to rename the database's online redo log files. </span></div><ol start="4" type="1"><li class="MsoNormal" style="line-height: normal; mso-list: l20 level1 lfo11; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto; tab-stops: list .5in;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="556"></a><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">Open the database for normal operation. </span></li></ol><div class="MsoNormal" style="line-height: normal; margin-bottom: 5.0pt;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="558"></a><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">The online redo log alterations take effect the next time that the database is opened. Opening the database may require shutting down the current instance (if the database was previously opened by the current instance) or just opening the database using the current instance. </span></div><ol start="5" type="1"><li class="MsoNormal" style="line-height: normal; mso-list: l4 level1 lfo12; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto; tab-stops: list .5in;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="560"></a><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">Back up the control file. </span></li></ol><div class="MsoNormal" style="line-height: normal; margin-bottom: 5.0pt;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="562"></a><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">As a precaution, after renaming or relocating a set of online redo log files, immediately back up the database's control file. </span></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="568"></a><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">The following example renames the online redo log members. However, first assume that: </span></div><ul type="disc"><li class="MsoNormal" style="line-height: normal; mso-list: l6 level1 lfo13; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto; tab-stops: list .5in;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="570"></a><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">The database is currently mounted by, but closed to, the instance. </span></li><li class="MsoNormal" style="line-height: normal; mso-list: l6 level1 lfo13; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto; tab-stops: list .5in;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="572"></a><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">The online redo log is duplexed: one group consists of the members LOG1A and LOG1B, and the second group consists of the members LOG2A and LOG2B. The files LOG1A and LOG2A are stored on Disk A, while LOG1B and LOG2B are stored on Disk B. </span></li><li class="MsoNormal" style="line-height: normal; mso-list: l6 level1 lfo13; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto; tab-stops: list .5in;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="574"></a><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">The online redo log files located on Disk A must be relocated to Disk C. The new filenames will reflect the new location: LOG1C and LOG2C. </span></li></ul><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="576"></a><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">The files LOG1A and LOG2A on Disk A must be copied to the new files LOG1C and LOG2C on Disk C. </span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; tab-stops: 45.8pt 91.6pt 137.4pt 183.2pt 229.0pt 274.8pt 320.6pt 366.4pt 412.2pt 458.0pt 503.8pt 549.6pt 595.4pt 641.2pt 687.0pt 732.8pt;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="578"></a><span style="font-size: 10.0pt; mso-fareast-font-family: "Times New Roman";">ALTER DATABASE</span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; tab-stops: 45.8pt 91.6pt 137.4pt 183.2pt 229.0pt 274.8pt 320.6pt 366.4pt 412.2pt 458.0pt 503.8pt 549.6pt 595.4pt 641.2pt 687.0pt 732.8pt;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="580"></a><span style="font-size: 10.0pt; mso-fareast-font-family: "Times New Roman";"><span style="mso-spacerun: yes;"> </span>RENAME FILE 'log1a', 'log2a'</span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; tab-stops: 45.8pt 91.6pt 137.4pt 183.2pt 229.0pt 274.8pt 320.6pt 366.4pt 412.2pt 458.0pt 503.8pt 549.6pt 595.4pt 641.2pt 687.0pt 732.8pt;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="582"></a><span style="font-size: 10.0pt; mso-fareast-font-family: "Times New Roman";"><span style="mso-spacerun: yes;"> </span>TO 'log1c', 'log2c';</span></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto; mso-outline-level: 2;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="586"></a><b><span style="color: #330099; font-size: 18.0pt; mso-fareast-font-family: "Times New Roman";">Dropping Online Redo Log Groups </span></b></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="592"></a><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">In some cases, you might want to drop an entire group of online redo log members. For example, you might want to reduce the number of groups in an instance's online redo log. </span></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="598"></a><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">To drop an online redo log group, you must have the ALTER DATABASE system privilege. </span></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="600"></a><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">Before dropping an online redo log group, consider the following restrictions and precautions: </span></div><ul type="disc"><li class="MsoNormal" style="line-height: normal; mso-list: l9 level1 lfo14; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto; tab-stops: list .5in;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="602"></a><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">An instance requires at least two groups of online redo log files, regardless of the number of members in the groups. (A group is one or more members.) </span></li><li class="MsoNormal" style="line-height: normal; mso-list: l9 level1 lfo14; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto; tab-stops: list .5in;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="604"></a><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">You can drop an online redo log group only if it is not the active group. If you need to drop the active group, first force a log switch to occur; see </span><a href="http://www.csee.umbc.edu/portal/help/oracle8.bak/server803/A54641_01/ch5.htm#2056"><span style="color: blue; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">"Forcing a Log Switch" on page 5-12</span></a><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">. </span></li><li class="MsoNormal" style="line-height: normal; mso-list: l9 level1 lfo14; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto; tab-stops: list .5in;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="608"></a><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">Make sure an online redo log group is archived (if archiving is enabled) before dropping it. To see whether this has happened, use the Enterprise Manager ARCHIVE LOG command with the LIST parameter. </span></li></ul><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="614"></a><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">Drop an online redo log group with either the Drop Logfile Group menu item of Enterprise Manager, or the SQL command ALTER DATABASE with the DROP LOGFILE clause. </span></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="616"></a><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">The following statement drops redo log group number 3: </span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; tab-stops: 45.8pt 91.6pt 137.4pt 183.2pt 229.0pt 274.8pt 320.6pt 366.4pt 412.2pt 458.0pt 503.8pt 549.6pt 595.4pt 641.2pt 687.0pt 732.8pt;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="618"></a><span style="font-size: 10.0pt; mso-fareast-font-family: "Times New Roman";">ALTER DATABASE DROP LOGFILE GROUP 3;</span></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="620"></a><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">When an online redo log group is dropped from the database, 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 an online redo log group, make sure that the drop completed successfully, and then use the appropriate operating system command to delete the dropped online redo log files. </span><br /><span style="font-size: 12pt;"> </span></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto; mso-outline-level: 2;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="624"></a><b><span style="color: #330099; font-size: 18.0pt; mso-fareast-font-family: "Times New Roman";">Dropping Online Redo Log Members</span></b></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="630"></a><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">In some cases, you might want to drop one or more specific online redo log members. For example, if a disk failure occurs, you might need to drop all the online redo log files on the failed disk so that Oracle does not try to write to the inaccessible files. In other situations, particular online redo log files become unnecessary; for example, a file might be stored in an inappropriate location. </span></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="636"></a><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">To drop an online redo log member, you must have the ALTER DATABASE system privilege. </span></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="638"></a><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">Consider the following restrictions and precautions before dropping individual online redo log members: </span></div><ul type="disc"><li class="MsoNormal" style="line-height: normal; mso-list: l27 level1 lfo15; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto; tab-stops: list .5in;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="640"></a><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">It is all right to drop online redo log files so that a multiplexed online redo log becomes temporarily asymmetric. For example, if you use duplexed groups of online 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 online redo log. </span></li><li class="MsoNormal" style="line-height: normal; mso-list: l27 level1 lfo15; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto; tab-stops: list .5in;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="648"></a><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">An instance always requires at least two valid groups of online redo log files, regardless of the number of members in the groups. (A group is one or more members.) 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; to see a redo log file's status, use the V$LOGFILE view. A redo log file becomes INVALID if Oracle cannot access it. It becomes STALE if Oracle suspects that it is not complete or correct; a stale log file becomes valid again the next time its group is made the active group. </span></li><li class="MsoNormal" style="line-height: normal; mso-list: l27 level1 lfo15; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto; tab-stops: list .5in;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="650"></a><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">You can drop an online redo log member only if it is not part of an active group. If you want to drop a member of an active group, first force a log switch to occur. </span></li><li class="MsoNormal" style="line-height: normal; mso-list: l27 level1 lfo15; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto; tab-stops: list .5in;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="652"></a><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">Make sure the group to which an online redo log member belongs is archived (if archiving is enabled) before dropping the member. To see whether this has happened, use the Enterprise Manager ARCHIVE LOG command with the LIST parameter. </span></li></ul><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="656"></a><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">To drop specific inactive online redo log members, use either the Drop Logfile Member menu item of Enterprise Manager, or the SQL command ALTER DATABASE command with the DROP LOGFILE MEMBER clause. </span></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="660"></a><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">The following statement drops the redo log LOG3C: </span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; tab-stops: 45.8pt 91.6pt 137.4pt 183.2pt 229.0pt 274.8pt 320.6pt 366.4pt 412.2pt 458.0pt 503.8pt 549.6pt 595.4pt 641.2pt 687.0pt 732.8pt;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="662"></a><span style="font-size: 10.0pt; mso-fareast-font-family: "Times New Roman";">ALTER DATABASE<span style="mso-spacerun: yes;"> </span>DROP LOGFILE MEMBER 'log3c';</span></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="664"></a><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">When an online 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 an online redo log file, make sure that the drop completed successfully, and then use the appropriate operating system command to delete the dropped online redo log file. </span></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="666"></a></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto; mso-outline-level: 2;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="670"></a><b><span style="color: #330099; font-size: 18.0pt; mso-fareast-font-family: "Times New Roman";">Controlling Checkpoints and Log Switches</span></b></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="678"></a><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">A checkpoint is the event during which the Database Writer process (DBWR) writes all modified database buffers in the SGA to the appropriate datafiles. A log switch is the event during which LGWR stops writing to one online redo log group and starts writing to another. The two events are often connected: an instance takes a checkpoint at each log switch by default. A log switch, by default, takes place automatically when the current online redo log file group fills. </span></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="680"></a><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">However, you can designate that checkpoints are taken more often than when you have log switches, or you can have a checkpoint take place ahead of schedule, without a log switch. You can also have a log switch and checkpoint occur ahead of schedule, or without an accompanying checkpoint. </span></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="682"></a><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">This section includes the following checkpoint and log switch topics: </span></div><ul type="disc"><li class="MsoNormal" style="line-height: normal; mso-list: l0 level1 lfo16; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto; tab-stops: list .5in;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="684"></a><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";"><a href="http://www.csee.umbc.edu/portal/help/oracle8.bak/server803/A54641_01/ch5.htm#690"><span style="color: blue;">Setting Database Checkpoint Intervals</span></a> </span></li><li class="MsoNormal" style="line-height: normal; mso-list: l0 level1 lfo16; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto; tab-stops: list .5in;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="686"></a><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";"><a href="http://www.csee.umbc.edu/portal/help/oracle8.bak/server803/A54641_01/ch5.htm#2056"><span style="color: blue;">Forcing a Log Switch</span></a> </span></li><li class="MsoNormal" style="line-height: normal; mso-list: l0 level1 lfo16; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto; tab-stops: list .5in;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="688"></a><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";"><a href="http://www.csee.umbc.edu/portal/help/oracle8.bak/server803/A54641_01/ch5.htm#778"><span style="color: blue;">Forcing a Fast Database Checkpoint Without a Log Switch</span></a> </span></li></ul><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto; mso-outline-level: 3;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="690"></a><b><span style="color: #330099; font-size: 13.5pt; mso-fareast-font-family: "Times New Roman";">Setting Database Checkpoint Intervals</span></b></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="694"></a><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">When your database uses large online redo log files, you can set additional database checkpoints to take place automatically at predetermined intervals, between the checkpoints that automatically occur at log switches. The time necessary to recover from an instance failure decreases when more database checkpoints are set. However, there may be a performance impact on the Oracle Server due to the extra I/O necessary for the checkpoint to complete. </span></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="696"></a><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">Generally, unless your database consistently requires instance recovery on startup, set database checkpoint intervals so that checkpoints occur only at log switches. If you use small online redo log files, checkpoints already occur at frequent intervals (at each log switch). </span></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="700"></a><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">You can control the frequency of automatic database checkpoints via the values set in the LOG_CHECKPOINT_INTERVAL and LOG_CHECKPOINT_TIMEOUT parameters. </span></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto; mso-outline-level: 4;"><br /><b><span style="color: #330099; font-size: 12pt;">Setting LOG_CHECK-POINT_INTERVAL</span></b></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="704"></a><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">To have database checkpoints only occur at log switches (the default), set the value for the LOG_CHECKPOINT_INTERVAL parameter higher than the size of the online redo log files in use. Alternatively, to force additional checkpoints to occur at intervals between two log switches, set the value for the LOG_CHECKPOINT_INTERVAL parameter lower than the size of the online redo log files in use. </span></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="706"></a><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">The value of the LOG_CHECKPOINT_INTERVAL is a number of operating system blocks, not Oracle data blocks. Therefore, you must know the size, in bytes, of your operating system's blocks. Once you know this, calculate the number of operating system blocks per online redo log file. </span></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="708"></a><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">As an example, assume the following conditions: </span></div><ul type="disc"><li class="MsoNormal" style="line-height: normal; mso-list: l25 level1 lfo17; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto; tab-stops: list .5in;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="710"></a><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">All online redo log files of the database instance are 512K. </span></li><li class="MsoNormal" style="line-height: normal; mso-list: l25 level1 lfo17; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto; tab-stops: list .5in;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="712"></a><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">The operating system block size is 512 bytes. </span></li><li class="MsoNormal" style="line-height: normal; mso-list: l25 level1 lfo17; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto; tab-stops: list .5in;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="714"></a><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">Checkpoints should occur when an online redo log file is half full. </span></li></ul><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><span style="height: 66px; margin-left: -82px; margin-top: 26px; mso-ignore: vglayout; position: absolute; width: 796px; z-index: 1;"> </span></div><table cellpadding="0" cellspacing="0"> <tbody><tr> <td bgcolor="white" height="66" style="background: white; border: .75pt solid black; vertical-align: top;" width="796"><span style="mso-ignore: vglayout; position: absolute; z-index: 1;"> </span><br /><table cellpadding="0" cellspacing="0" style="width: 100%px;"> <tbody><tr> <td><div class="shape" style="padding: 4.35pt 7.95pt 4.35pt 7.95pt;"><div class="MsoNormal"><span style="mso-no-proof: yes;"><img alt="http://www.csee.umbc.edu/portal/help/oracle8.bak/server803/A54641_01/ch5a.gif" height="38" src="file:///C:/Users/avik/AppData/Local/Temp/1/msohtmlclip1/01/clip_image002.gif" width="775" /></span></div></div></td> </tr></tbody></table><span style="mso-ignore: vglayout; position: absolute; z-index: 1;"></span> </td> </tr></tbody></table><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="716"></a><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">Using this information, you can compute the number of blocks per redo log file as follows: </span> <br /><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><br /></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><br /></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="726"></a><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">Now that the approximate number of blocks per online redo log file (1000) is known, the LOG_CHECKPOINT_INTERVAL parameter can be set accordingly in the instance's parameter file: </span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; tab-stops: 45.8pt 91.6pt 137.4pt 183.2pt 229.0pt 274.8pt 320.6pt 366.4pt 412.2pt 458.0pt 503.8pt 549.6pt 595.4pt 641.2pt 687.0pt 732.8pt;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="728"></a><span style="font-size: 10.0pt; mso-fareast-font-family: "Times New Roman";">LOG_CHECKPOINT_INTERVAL=500</span></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto; mso-outline-level: 4;"><br /><b><span style="color: #330099; font-size: 12pt;">Setting LOG_CHECKPOINT_TIMEOUT</span></b></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="734"></a><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">To have database checkpoints only occur at log switches (the default), set the value for the LOG_CHECKPOINT_TIMEOUT parameter to zero. Alternatively, to force additional checkpoints to occur at intervals between two log switches, set the value for the LOG_CHECKPOINT_TIMEOUT parameter to a time interval (in seconds) less than the average time it takes to fill an online redo log file. To determine the average time it takes to fill online redo log files, examine the LGWR trace file for messages that indicate the times of log switches. </span></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto; mso-outline-level: 3;"><br /><b><span style="color: #330099; font-size: 13.5pt;">Forcing a Log Switch</span></b></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="758"></a><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">You can force a log switch to make the currently active group inactive and available for online 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; this option is often useful in configurations with large online redo log files that take a long time to fill. </span></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="760"></a><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="772"></a><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">To force a log switch, you must have the Alter System privilege.To force a log switch, use either the Switch Logfile menu item of Enterprise Manager or the SQL command ALTER SYSTEM with the SWITCH LOGFILE option. </span></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="774"></a><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">The following statement forces a log switch: </span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; tab-stops: 45.8pt 91.6pt 137.4pt 183.2pt 229.0pt 274.8pt 320.6pt 366.4pt 412.2pt 458.0pt 503.8pt 549.6pt 595.4pt 641.2pt 687.0pt 732.8pt;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="776"></a><span style="font-size: 10.0pt; mso-fareast-font-family: "Times New Roman";">ALTER SYSTEM SWITCH LOGFILE;</span></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto; mso-outline-level: 3;"><br /><b><span style="color: #330099; font-size: 13.5pt;">Forcing a Fast Database Checkpoint Without a Log Switch</span></b></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="786"></a><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">In some cases, you might want to force a fast database checkpoint. A fast checkpoint is one which does not involve a log switch; LGWR continues to write to the current online redo log file. A fast checkpoint allows DBWR to write more modified database buffers to disk per I/O on behalf of a checkpoint. Therefore, you need fewer I/Os (thus less time) to complete a fast checkpoint. </span></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="794"></a><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">To force a database checkpoint, you must have the ALTER SYSTEM system privilege. Force a fast database checkpoint with either the Force Checkpoint menu item of Enterprise Manager, or the SQL command ALTER SYSTEM with the CHECKPOINT option. </span></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="796"></a><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">The following statement forces a checkpoint: </span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; tab-stops: 45.8pt 91.6pt 137.4pt 183.2pt 229.0pt 274.8pt 320.6pt 366.4pt 412.2pt 458.0pt 503.8pt 549.6pt 595.4pt 641.2pt 687.0pt 732.8pt;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="798"></a><span style="font-size: 10.0pt; mso-fareast-font-family: "Times New Roman";">ALTER SYSTEM CHECKPOINT;</span></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="802"></a><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">Omitting the GLOBAL option allows you to force a checkpoint for only the connected instance, while including it forces a checkpoint for all instances of the database. Forcing a checkpoint for only the local instance is useful only with the Oracle Parallel Server. In a non-parallel server configuration, global and local checkpoints are identical. </span></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto; mso-outline-level: 2;"><br /><b><span style="color: #330099; font-size: 18pt;">Verifying Blocks in Redo Log Files</span></b></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="815"></a><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">You can configure Oracle to use checksums to verify blocks in the redo log files. Set the initialization parameter LOG_BLOCK_CHECKSUM to TRUE to enable redo log block checking. The default value of LOG_BLOCK_CHECKSUM is FALSE. </span></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="817"></a><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">If you enable redo log block checking, Oracle computes a checksum for each redo log block written to the current log. The checksums are written in the header of the block. </span></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="819"></a><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">Oracle uses the checksum to detect corruption in a redo log block. Oracle tries to verify the redo log block when it writes the block to an archive log file and when the block is read from an archived log during recovery. </span></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="821"></a><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">If Oracle detects a corruption in a redo log block while trying to archive it, Oracle tries to read the block from another member in the group. If the block is corrupted in all members the redo log group, then archiving cannot proceed. </span></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto; mso-outline-level: 2;"><br /><b><span style="color: #330099; font-size: 18pt;">Clearing an Online Redo Log File</span></b></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="1261"></a><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">If you have enabled redo log block checking, Oracle verifies each block before archiving it. If a particular redo log block is corrupted in all members of a group, archiving stops. Eventually all the redo logs become filled and database activity is halted, until archiving can resume. </span></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="837"></a><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">In this situation, you can use the SQL command ALTER DATABASE... CLEAR LOGFILE to clear the corrupted redo logs and avoid archiving them. The cleared redo logs are available for use even though they were not archived. </span></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="839"></a><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">The following statement clears the log files in redo log group number 3: </span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; tab-stops: 45.8pt 91.6pt 137.4pt 183.2pt 229.0pt 274.8pt 320.6pt 366.4pt 412.2pt 458.0pt 503.8pt 549.6pt 595.4pt 641.2pt 687.0pt 732.8pt;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="841"></a><span style="font-size: 10.0pt; mso-fareast-font-family: "Times New Roman";">ALTER DATABASE CLEAR UNARCHIVED LOGFILE<br style="mso-special-character: line-break;" /><br style="mso-special-character: line-break;" /></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; tab-stops: 45.8pt 91.6pt 137.4pt 183.2pt 229.0pt 274.8pt 320.6pt 366.4pt 412.2pt 458.0pt 503.8pt 549.6pt 595.4pt 641.2pt 687.0pt 732.8pt;"><span style="font-size: 10.0pt; mso-fareast-font-family: "Times New Roman";"><span style="mso-spacerun: yes;"> </span>GROUP 3;</span></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto; mso-outline-level: 3;"><br /></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto; mso-outline-level: 3;"><br /></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto; mso-outline-level: 3;"><b><span style="color: #330099; font-size: 13.5pt; mso-fareast-font-family: "Times New Roman";">Restrictions</span></b></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="845"></a><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">You can clear a redo log file whether it is archived or not. However, when it is not archived, you must include the keyword UNARCHIVED. </span></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="847"></a><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">If you clear a log file that is needed for recovery of a backup, then you can no longer recover from that backup. Oracle writes a message in the alert log describing the backups from which you cannot recover. </span></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="852"></a><b><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">Attention: </span></b><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">If you clear an unarchived redo log file, you should take another backup of the database. </span></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="854"></a><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">If you want to clear an unarchived redo log that is needed to bring an offline tablespace online, you must use the clause UNRECOVERABLE DATAFILE in the ALTER DATABASE command. </span></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="856"></a><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">If you clear a redo log needed to bring an offline tablespace online, you will not be able to bring the tablespace online again. You will have to drop the tablespace or perform an incomplete recovery. </span></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="858"></a><b><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">See Also:</span></b><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";"> For a complete description of the ALTER DATABASE command, see the Oracle8 Server SQL Reference. </span></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto; mso-outline-level: 2;"><br /><b><span style="color: #330099; font-size: 18pt;">Listing Information about the Online Redo Log</span></b></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="872"></a><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">Use the V$LOG, V$LOGFILE, and V$THREAD views to see information about the online redo log of a database; the V$THREAD view is of particular interest for Parallel Server administrators. </span></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="874"></a><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">The following query returns information about the online redo log of a database used without the Parallel Server: </span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; tab-stops: 45.8pt 91.6pt 137.4pt 183.2pt 229.0pt 274.8pt 320.6pt 366.4pt 412.2pt 458.0pt 503.8pt 549.6pt 595.4pt 641.2pt 687.0pt 732.8pt;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="876"></a><span style="font-size: 10.0pt; mso-fareast-font-family: "Times New Roman";">SELECT group#, bytes, members<a href="http://www.ipv4-ipv6.blogspot.com/" target="_blank">www.ipv4-ipv6.blogspot.com</a></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; tab-stops: 45.8pt 91.6pt 137.4pt 183.2pt 229.0pt 274.8pt 320.6pt 366.4pt 412.2pt 458.0pt 503.8pt 549.6pt 595.4pt 641.2pt 687.0pt 732.8pt;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="878"></a><span style="font-size: 10.0pt; mso-fareast-font-family: "Times New Roman";"><span style="mso-spacerun: yes;"> </span>FROM sys.v$log;</span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; tab-stops: 45.8pt 91.6pt 137.4pt 183.2pt 229.0pt 274.8pt 320.6pt 366.4pt 412.2pt 458.0pt 503.8pt 549.6pt 595.4pt 641.2pt 687.0pt 732.8pt;"><br /></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; tab-stops: 45.8pt 91.6pt 137.4pt 183.2pt 229.0pt 274.8pt 320.6pt 366.4pt 412.2pt 458.0pt 503.8pt 549.6pt 595.4pt 641.2pt 687.0pt 732.8pt;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="1306"></a><span style="font-size: 10.0pt; mso-fareast-font-family: "Times New Roman";">GROUP#<span style="mso-spacerun: yes;"> </span>BYTES<span style="mso-spacerun: yes;"> </span>MEMBERS</span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; tab-stops: 45.8pt 91.6pt 137.4pt 183.2pt 229.0pt 274.8pt 320.6pt 366.4pt 412.2pt 458.0pt 503.8pt 549.6pt 595.4pt 641.2pt 687.0pt 732.8pt;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="1307"></a><span style="font-size: 10.0pt; mso-fareast-font-family: "Times New Roman";">----------<span style="mso-spacerun: yes;"> </span>----------<span style="mso-spacerun: yes;"> </span>----------</span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; tab-stops: 45.8pt 91.6pt 137.4pt 183.2pt 229.0pt 274.8pt 320.6pt 366.4pt 412.2pt 458.0pt 503.8pt 549.6pt 595.4pt 641.2pt 687.0pt 732.8pt;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="886"></a><span style="font-size: 10.0pt; mso-fareast-font-family: "Times New Roman";"><span style="mso-spacerun: yes;"> </span>1<span style="mso-spacerun: yes;"> </span>81920<span style="mso-spacerun: yes;"> </span>2</span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; tab-stops: 45.8pt 91.6pt 137.4pt 183.2pt 229.0pt 274.8pt 320.6pt 366.4pt 412.2pt 458.0pt 503.8pt 549.6pt 595.4pt 641.2pt 687.0pt 732.8pt;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="888"></a><span style="font-size: 10.0pt; mso-fareast-font-family: "Times New Roman";"><span style="mso-spacerun: yes;"> </span>2<span style="mso-spacerun: yes;"> </span>81920<span style="mso-spacerun: yes;"> </span>2</span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; tab-stops: 45.8pt 91.6pt 137.4pt 183.2pt 229.0pt 274.8pt 320.6pt 366.4pt 412.2pt 458.0pt 503.8pt 549.6pt 595.4pt 641.2pt 687.0pt 732.8pt;"><br /></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="890"></a><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">To see the names of all of the member of a group, use a query similar to the following: </span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; tab-stops: 45.8pt 91.6pt 137.4pt 183.2pt 229.0pt 274.8pt 320.6pt 366.4pt 412.2pt 458.0pt 503.8pt 549.6pt 595.4pt 641.2pt 687.0pt 732.8pt;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="892"></a><span style="font-size: 10.0pt; mso-fareast-font-family: "Times New Roman";">SELECT *</span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; tab-stops: 45.8pt 91.6pt 137.4pt 183.2pt 229.0pt 274.8pt 320.6pt 366.4pt 412.2pt 458.0pt 503.8pt 549.6pt 595.4pt 641.2pt 687.0pt 732.8pt;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="894"></a><span style="font-size: 10.0pt; mso-fareast-font-family: "Times New Roman";"><span style="mso-spacerun: yes;"> </span>FROM sys.v$logfile</span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; tab-stops: 45.8pt 91.6pt 137.4pt 183.2pt 229.0pt 274.8pt 320.6pt 366.4pt 412.2pt 458.0pt 503.8pt 549.6pt 595.4pt 641.2pt 687.0pt 732.8pt;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="896"></a><span style="font-size: 10.0pt; mso-fareast-font-family: "Times New Roman";"><span style="mso-spacerun: yes;"> </span>WHERE group# = 2;</span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; tab-stops: 45.8pt 91.6pt 137.4pt 183.2pt 229.0pt 274.8pt 320.6pt 366.4pt 412.2pt 458.0pt 503.8pt 549.6pt 595.4pt 641.2pt 687.0pt 732.8pt;"><br /></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; tab-stops: 45.8pt 91.6pt 137.4pt 183.2pt 229.0pt 274.8pt 320.6pt 366.4pt 412.2pt 458.0pt 503.8pt 549.6pt 595.4pt 641.2pt 687.0pt 732.8pt;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="1446"></a><span style="font-size: 10.0pt; mso-fareast-font-family: "Times New Roman";">GROUP#<span style="mso-spacerun: yes;"> </span>BYTES<span style="mso-spacerun: yes;"> </span>MEMBERS</span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; tab-stops: 45.8pt 91.6pt 137.4pt 183.2pt 229.0pt 274.8pt 320.6pt 366.4pt 412.2pt 458.0pt 503.8pt 549.6pt 595.4pt 641.2pt 687.0pt 732.8pt;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="2262"></a><span style="font-size: 10.0pt; mso-fareast-font-family: "Times New Roman";">----------<span style="mso-spacerun: yes;"> </span>----------<span style="mso-spacerun: yes;"> </span>---------</span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; tab-stops: 45.8pt 91.6pt 137.4pt 183.2pt 229.0pt 274.8pt 320.6pt 366.4pt 412.2pt 458.0pt 503.8pt 549.6pt 595.4pt 641.2pt 687.0pt 732.8pt;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="2263"></a><span style="font-size: 10.0pt; mso-fareast-font-family: "Times New Roman";"><span style="mso-spacerun: yes;"> </span>2<span style="mso-spacerun: yes;"> </span>LOG2A</span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; tab-stops: 45.8pt 91.6pt 137.4pt 183.2pt 229.0pt 274.8pt 320.6pt 366.4pt 412.2pt 458.0pt 503.8pt 549.6pt 595.4pt 641.2pt 687.0pt 732.8pt;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="2264"></a><span style="font-size: 10.0pt; mso-fareast-font-family: "Times New Roman";"><span style="mso-spacerun: yes;"> </span>2 STALE<span style="mso-spacerun: yes;"> </span>LOG2B</span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; tab-stops: 45.8pt 91.6pt 137.4pt 183.2pt 229.0pt 274.8pt 320.6pt 366.4pt 412.2pt 458.0pt 503.8pt 549.6pt 595.4pt 641.2pt 687.0pt 732.8pt;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="908"></a><span style="font-size: 10.0pt; mso-fareast-font-family: "Times New Roman";"><span style="mso-spacerun: yes;"> </span>2<span style="mso-spacerun: yes;"> </span>LOG2C</span></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="910"></a><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">If STATUS is blank for a member, the file is in use. </span></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><br /></div><h2><span style="color: #0070c0;">How to change the Redo Log File size in Oracle Database</span></h2>Yesterday we found and realized that there has been too much log switching in one of our databases and it was impacting the database performance. This excessive log switching was happening because our Redo Log files were not enough large as per the the database activity.<br />We had 50 MB Redo Log Files, 1 Redo Thread, 2 Redo Log Groups and One member in each group.We decided to increase the Redo Log size to 100MB.<br />The Redo Logs must be dropped and recreated for changing the redo log size. It can be done online without shutting down the database. However, you need to make sure that the Redo Group being dropped should be INACTIVE when you do this.<br />We have the following Log Groups and members:<br /><pre>SQL> select * from v$logfile;</pre><pre>2 ONLINE /app01/oratest/oradata/BOTI/BOTI/redo02.log NO</pre><pre>1 ONLINE /app01/oratest/oradata/BOTI/BOTI/redo01.log NO</pre>And the status of the Log Groups is:<br /><pre>SQL> select * from v$log;</pre><pre> </pre><pre> GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARCHIVED STATUS </pre><pre><span style="mso-spacerun: yes;"> </span>1 1 12 <span style="mso-spacerun: yes;"> </span>52428800 1<span style="mso-spacerun: yes;"> </span>NO CURRENT</pre><pre><span style="mso-spacerun: yes;"> </span>2 1 11 <span style="mso-spacerun: yes;"> </span>52428800 1<span style="mso-spacerun: yes;"> </span>YES ACTIVE </pre>Here we see that the Group# 1 is being used Currently and the Group# 2 though not being used Currently, however is ACTIVE (means if the Database crashes now, you will need this Group for recovery.) We need to make this group Inactive before proceeding ahead:For this, execute a checkpoint:<br /><pre>SQL> alter system checkpoint;</pre><pre> </pre><pre>System altered.</pre><pre> </pre><pre>Now again check the status of the Redo Groups:</pre><pre>SQL> select * from v$log;</pre><pre> </pre><pre> GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARCHIVED STATUS </pre><pre><span style="mso-spacerun: yes;"> </span>1 1 12 <span style="mso-spacerun: yes;"> </span>52428800 1<span style="mso-spacerun: yes;"> </span>NO CURRENT</pre><pre><span style="mso-spacerun: yes;"> </span>2 1 11 <span style="mso-spacerun: yes;"> </span>52428800 1<span style="mso-spacerun: yes;"> </span>YES INACTIVE</pre><pre> </pre><pre>The group 2 is now Inactive. Hence we can proceed with dropping this group:</pre><pre> </pre><pre>SQL> alter database drop logfile group 2;</pre><pre>alter database drop logfile group 2</pre><pre>*</pre><pre>ERROR at line 1:</pre><pre>ORA-01567: dropping log 2 would leave less than 2 log files for instance BOTI (thread 1)</pre><pre>ORA-00312: online log 2 thread 1: '/app01/oratest/oradata/BOTI/BOTI/redo02.log'</pre><pre> </pre>What went wrong ???<br />It’s a basic requirement in Oracle Database that there should always be a minimum 2 Redo Log Groups available with the Database. Hence we can not drop any Redo Group if there are only 2 Groups.<br />To overcome this issue, we need to add one more Redo group to the database.<br />Execute the following step:<br /><br /><pre>SQL> alter database add logfile group 3 '/app01/oratest/oradata/BOTI/BOTI/redo03.log' size 100M;</pre><pre> </pre><pre>Database altered.</pre><pre>Now check the logfiles:</pre><pre>SQL> select * from v$logfile;</pre><pre>3 ONLINE /app01/oratest/oradata/BOTI/BOTI/redo03.log<span style="mso-spacerun: yes;"> </span>NO</pre><pre>2 ONLINE /app01/oratest/oradata/BOTI/BOTI/redo02.log NO</pre><pre>1 ONLINE /app01/oratest/oradata/BOTI/BOTI/redo01.log NO</pre><pre> </pre><pre>… and the status of the Groups:</pre><pre>SQL> select * from v$log;</pre><pre> </pre><pre> GROUP# THREAD# SEQUENCE# BYTES <span style="mso-spacerun: yes;"> </span>MEMBERS ARCHIVED <span style="mso-spacerun: yes;"> </span>STATUS </pre><pre><span style="mso-spacerun: yes;"> </span>1 1 12 <span style="mso-spacerun: yes;"> </span>52428800 1<span style="mso-spacerun: yes;"> </span>NO CURRENT</pre><pre><span style="mso-spacerun: yes;"> </span>2 1 11 <span style="mso-spacerun: yes;"> </span>52428800 1<span style="mso-spacerun: yes;"> </span>YES INACTIVE</pre><pre><span style="mso-spacerun: yes;"> </span>3 1 0 <span style="mso-spacerun: yes;"> </span>104857600 1<span style="mso-spacerun: yes;"> </span>YES UNUSED</pre><pre> </pre>The status of the new Group is UNUSED because it has never been used.<br />Now, we have 3 Redo Groups in our database. We can now proceed with Dropping Redo Group# 2.<br /><pre>SQL> alter database drop logfile group 2;</pre><pre> </pre><pre>Database altered.</pre>Also, delete the file ‘/app01/oratest/oradata/BOTI/BOTI/redo02.log’ from File system also.<br />Now add the Redo Group 2 back to the database with changed Redo size:<br /><pre>SQL> alter database add logfile group 2 '/app01/oratest/oradata/BOTI/BOTI/redo02.log' size 100M;</pre><pre> </pre><pre>Database altered.</pre><pre> </pre><pre> </pre><pre> </pre><pre>SQL> select * from v$logfile;</pre><pre>3 ONLINE /app01/oratest/oradata/BOTI/BOTI/redo03.log<span style="mso-spacerun: yes;"> </span>NO</pre><pre>2 ONLINE /app01/oratest/oradata/BOTI/BOTI/redo02.log NO</pre><pre>1 ONLINE /app01/oratest/oradata/BOTI/BOTI/redo01.log NO</pre><pre> </pre><pre>SQL> select * from v$log;</pre><pre> </pre><pre> GROUP# THREAD# SEQUENCE# BYTES <span style="mso-spacerun: yes;"> </span>MEMBERS ARCHIVED <span style="mso-spacerun: yes;"> </span>STATUS </pre><pre><span style="mso-spacerun: yes;"> </span>1 1 12 <span style="mso-spacerun: yes;"> </span>52428800 1<span style="mso-spacerun: yes;"> </span>NO CURRENT<span style="mso-spacerun: yes;"> </span>n DA</pre><pre><span style="mso-spacerun: yes;"> </span>2 1 0 <span style="mso-spacerun: yes;"> </span>104857600 1<span style="mso-spacerun: yes;"> </span>YES UNUSED<span style="mso-spacerun: yes;"> </span>0</pre><pre><span style="mso-spacerun: yes;"> </span>3 1 0 <span style="mso-spacerun: yes;"> </span>104857600 1<span style="mso-spacerun: yes;"> </span>YES UNUSED<span style="mso-spacerun: yes;"> </span>0</pre><pre> </pre><pre>Now we have to drop the 1st Redo Log Group. Before that, we need to change the status of this group:</pre><pre> </pre><pre>SQL> alter system switch logfile;</pre><pre> </pre><pre>System altered.</pre><pre>SQL> select * from v$log;</pre><pre> </pre><pre> GROUP# THREAD# SEQUENCE# BYTES <span style="mso-spacerun: yes;"> </span>MEMBERS ARCHIVED <span style="mso-spacerun: yes;"> </span>STATUS </pre><pre><span style="mso-spacerun: yes;"> </span>1 1 12 <span style="mso-spacerun: yes;"> </span>52428800 1<span style="mso-spacerun: yes;"> </span>YES ACTIVE<span style="mso-spacerun: yes;"> </span>n DA</pre><pre><span style="mso-spacerun: yes;"> </span>2 1 0 <span style="mso-spacerun: yes;"> </span>104857600 1<span style="mso-spacerun: yes;"> </span>NO CURRENT<span style="mso-spacerun: yes;"> </span>n DA</pre><pre><span style="mso-spacerun: yes;"> </span>3 1 0 <span style="mso-spacerun: yes;"> </span>104857600 1<span style="mso-spacerun: yes;"> </span>YES UNUSED<span style="mso-spacerun: yes;"> </span>0</pre><pre> </pre><pre>Still the Grpoup is in Active status. Issue a checkpoint:</pre><pre> </pre><pre>SQL> alter system checkpoint;</pre><pre> </pre><pre>System altered.</pre><pre> </pre><pre>SQL> select * from v$log;</pre><pre> </pre><pre>GROUP# THREAD# SEQUENCE# BYTES <span style="mso-spacerun: yes;"> </span>MEMBERS ARCHIVED <span style="mso-spacerun: yes;"> </span>STATUS </pre><pre><span style="mso-spacerun: yes;"> </span>1 1 12 <span style="mso-spacerun: yes;"> </span>52428800 1<span style="mso-spacerun: yes;"> </span>YES ACTIVE<span style="mso-spacerun: yes;"> </span>n DA</pre><pre><span style="mso-spacerun: yes;"> </span>2 1 0 <span style="mso-spacerun: yes;"> </span>104857600 1<span style="mso-spacerun: yes;"> </span>NO CURRENT<span style="mso-spacerun: yes;"> </span>n DA</pre><pre><span style="mso-spacerun: yes;"> </span>3 1 0 <span style="mso-spacerun: yes;"> </span>104857600 1<span style="mso-spacerun: yes;"> </span>YES UNUSED<span style="mso-spacerun: yes;"> </span>0</pre><pre> </pre><pre> </pre><pre><a href="http://www.ipv4-ipv6.blogspot.com/" target="_blank">www.ipv4-ipv6.blogspot.com</a></pre><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto; mso-outline-level: 2;"><b><span style="color: #000077; font-size: 18.0pt; mso-fareast-font-family: "Times New Roman";">Control Files</span></b></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">Control files are used to keep information critical to the operation of the RDBMS. The control file (or files) resides on the operating system file system. These files are used in the startup of the instance to identify where the datafiles and redo log files are in the system. The loss of a control file can be devastating to the operation of the RDBMS. It is always a good idea to have multiple control files on different disk volumes so that a failure does not cause the loss of all the control files. You can add an additional control file after the database has been created by following these steps: </span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; margin-left: .5in; margin-right: 0in; margin-top: 0in;"><b><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">1. </span></b><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">Shut down the Oracle instance. </span></div><div class="MsoNormal" style="line-height: normal; margin-left: .5in; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><b><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">2.</span></b><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";"> Copy the control file to another location on another disk volume.</span></div><div class="MsoNormal" style="line-height: normal; margin-left: .5in; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><b><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">3.</span></b><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";"> Edit the parameter file to include the new file name in the </span><span style="font-size: 10.0pt; mso-bidi-font-size: 11.0pt; mso-fareast-font-family: "Times New Roman";">CONTROL_FILES</span><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";"> parameter.</span></div><div class="MsoNormal" style="line-height: normal; margin-left: .5in; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><b><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">4.</span></b><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";"> Restart the Oracle instance. </span></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">The control file can also be created using the </span><span style="font-size: 10.0pt; mso-bidi-font-size: 11.0pt; mso-fareast-font-family: "Times New Roman";">CREATE CONTROLFILE</span><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";"> command. This creation of the control file should be done only in extreme situations, such as when you need to rename a database or reproduce a control file because all control files are damaged and you don't have backups.</span></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">The best way to save and protect your control files is to use the </span><span style="font-size: 10.0pt; mso-bidi-font-size: 11.0pt; mso-fareast-font-family: "Times New Roman";">ALTER DATABASE </span><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">database</span><span style="font-size: 10.0pt; mso-bidi-font-size: 11.0pt; mso-fareast-font-family: "Times New Roman";"> BACKUP CONTROLFILE</span><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";"> command. The options to the </span><span style="font-size: 10.0pt; mso-bidi-font-size: 11.0pt; mso-fareast-font-family: "Times New Roman";">ALTER DATABASE </span><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">database</span><span style="font-size: 10.0pt; mso-bidi-font-size: 11.0pt; mso-fareast-font-family: "Times New Roman";"> BACKUP CONTROLFILE</span><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";"> command are as follows: </span></div><ul type="disc"><li class="MsoNormal" style="line-height: normal; mso-list: l7 level1 lfo5; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto; tab-stops: list .5in;"><span style="font-size: 10.0pt; mso-bidi-font-size: 11.0pt; mso-fareast-font-family: "Times New Roman";">TO `</span><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">filename</span><span style="font-size: 10.0pt; mso-bidi-font-size: 11.0pt; mso-fareast-font-family: "Times New Roman";">'</span><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">--Creates a new control file with the name specified as filename. If the file already exists, the optional </span><span style="font-size: 10.0pt; mso-bidi-font-size: 11.0pt; mso-fareast-font-family: "Times New Roman";">REUSE</span><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";"> qualifier must be used. </span></li><li class="MsoNormal" style="line-height: normal; mso-list: l7 level1 lfo5; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto; tab-stops: list .5in;"><span style="font-size: 10.0pt; mso-bidi-font-size: 11.0pt; mso-fareast-font-family: "Times New Roman";">TO TRACE</span><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">--This optional parameter writes SQL to a trace file that can be used to re-create the control files. Optionally you can specify the qualifiers </span><span style="font-size: 10.0pt; mso-bidi-font-size: 11.0pt; mso-fareast-font-family: "Times New Roman";">RESETLOGS</span><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";"> or </span><span style="font-size: 10.0pt; mso-bidi-font-size: 11.0pt; mso-fareast-font-family: "Times New Roman";">NORESETLOGS</span><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">, which will add additional SQL to open the database with these options. The SQL statements are complete enough to start the database, re-create the control files, and recover and open the database appropriately. </span></li></ul><div align="center" class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; text-align: center;"><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";"> </span><br /><hr align="center" size="2" width="100%" /><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";"> </span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in;"><b><span style="color: #000077; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">TIP:</span></b><b><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";"> </span></b><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">Any time you make changes to the structure of the database by adding datafiles, redo log files, and so on, run the command </span><span style="font-size: 10.0pt; mso-bidi-font-size: 11.0pt; mso-fareast-font-family: "Times New Roman";">ALTER DATABASE </span><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">database</span><span style="font-size: 10.0pt; mso-bidi-font-size: 11.0pt; mso-fareast-font-family: "Times New Roman";"> BACKUP CONTROLFILE TO TRACE</span><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">. By doing this you will have a method of re-creating the control files if necessary. This will save you a lot of work if you have to recover the entire system. </span></div><div align="center" class="MsoNormal" style="line-height: normal; margin-bottom: 5.0pt; text-align: center;"><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";"> </span><br /><hr align="center" size="2" width="100%" /><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";"> </span></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto; mso-outline-level: 2;"><br /></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto; mso-outline-level: 2;"><br /></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto; mso-outline-level: 2;"><b><span style="color: #330099; font-size: 18.0pt; mso-fareast-font-family: "Times New Roman";">Guidelines for Control Files</span></b></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto; mso-outline-level: 3;"><b><span style="color: #330099; font-size: 13.5pt; mso-fareast-font-family: "Times New Roman";">Name Control Files</span></b></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">Assign control file names via the CONTROL_FILES initialization parameter in the database's parameter file. CONTROL_FILES indicates one or more names of control files separated by commas. The instance startup procedure recognizes and opens all the listed files. The instance maintains all listed control files during database operation. </span></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">During database operation, Oracle Server writes to all necessary files listed for the CONTROL_FILES parameter. </span></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto; mso-outline-level: 3;"><b><span style="color: #330099; font-size: 13.5pt; mso-fareast-font-family: "Times New Roman";">Multiplex Control Files on Different Disks</span></b></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="340"></a><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">Every Oracle database should have at least two control files, each stored on a different disk. If a control file is damaged due to a disk failure, the associated instance must be shut down. Once the disk drive is repaired, the damaged control file can be restored using an intact copy of the control file and the instance can be restarted; no media recovery is required. </span></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto; mso-outline-level: 4;"><br /><b><span style="color: #330099; font-size: 12pt;">Behavior of Multiplexed Control Files</span></b></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="344"></a><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">The following list describes the behavior of multiplexed control files: </span></div><ul type="disc"><li class="MsoNormal" style="line-height: normal; mso-list: l15 level1 lfo18; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto; tab-stops: list .5in;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="346"></a><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">Two or more filenames are listed for the initialization parameter CONTROL_FILES in the database's parameter file. </span></li><li class="MsoNormal" style="line-height: normal; mso-list: l15 level1 lfo18; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto; tab-stops: list .5in;"><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">The first file listed in the CONTROL_FILES parameter is the only file read by the Oracle Server during database operation. </span></li><li class="MsoNormal" style="line-height: normal; mso-list: l15 level1 lfo18; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto; tab-stops: list .5in;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="350"></a><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">If any of the control files become unavailable during database operation, the instance becomes inoperable and should be aborted. </span></li></ul><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="352"></a><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">The only disadvantage of having multiple control files is that all operations that update the control files (such as adding a datafile or checkpointing the database) can take slightly longer. However, this difference is usually insignificant (especially for operating systems that can perform multiple, concurrent writes) and does not justify using only a single control file. </span></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="361"></a><b><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">Attention:</span></b><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";"> Oracle strongly recommends that your database has a minimum of two control files on different disks<b><i>.</i></b> </span></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto; mso-outline-level: 3;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="363"></a><b><span style="color: #330099; font-size: 13.5pt; mso-fareast-font-family: "Times New Roman";">Place Control Files Appropriately</span></b></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">Each copy of a control file should be stored on a different disk drive. Furthermore, a control file copy should be stored on every disk drive that stores members of online redo log groups, if the online redo log is multiplexed. By storing control files in these locations, you minimize the risk that all control files and all groups of the online redo log will be lost in a single disk failure. </span></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto; mso-outline-level: 3;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="369"></a><b><span style="color: #330099; font-size: 13.5pt; mso-fareast-font-family: "Times New Roman";">Manage the Size of Control Files</span></b></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="1211"></a><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">The main determinants of a control file's size are the values set for the MAXDATAFILES, MAXLOGFILES, MAXLOGMEMBERS, MAXLOGHISTORY, and MAXINSTANCES parameters in the CREATE DATABASE statement that created the associated database. Increasing the values of these parameters increases the size of a control file of the associated database. </span></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto; mso-outline-level: 2;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="381"></a><b><span style="color: #330099; font-size: 18.0pt; mso-fareast-font-family: "Times New Roman";">Creating Control Files</span></b></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">Every Oracle database has a <i>control file</i>. A control files records the physical structure of the database and contains: </span></div><ul type="disc"><li class="MsoNormal" style="line-height: normal; mso-list: l14 level1 lfo19; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto; tab-stops: list .5in;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="391"></a><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">the database name </span></li><li class="MsoNormal" style="line-height: normal; mso-list: l14 level1 lfo19; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto; tab-stops: list .5in;"><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">names and locations of associated databases and online redo log files </span></li><li class="MsoNormal" style="line-height: normal; mso-list: l14 level1 lfo19; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto; tab-stops: list .5in;"><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">the timestamp of the database creation </span></li><li class="MsoNormal" style="line-height: normal; mso-list: l14 level1 lfo19; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto; tab-stops: list .5in;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="397"></a><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">the current log sequence number </span></li><li class="MsoNormal" style="line-height: normal; mso-list: l14 level1 lfo19; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto; tab-stops: list .5in;"><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">checkpoint information </span></li></ul><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">The control file of an Oracle database is created at the same time as the database. By default, at least one copy of the control file must be created during database creation. On some operating systems, Oracle creates multiple copies. You should create two or more copies of the control file during database creation. You might also need to create control files later, if you lose control files or want to change particular settings in the control files. </span></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto; mso-outline-level: 3;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="413"></a><b><span style="color: #330099; font-size: 13.5pt; mso-fareast-font-family: "Times New Roman";">Creating Initial Control Files</span></b></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="419"></a><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">You create the initial control files of an Oracle database by specifying one or more control filenames in the CONTROL_FILES parameter in the parameter file used during database creation. The filenames specified in CONTROL_FILES should be fully specified. Filename specification is operating system-specific. </span></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="427"></a><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">If files with the specified names currently exist at the time of database creation, you must specify the CONTROLFILE REUSE parameter in the CREATE DATABASE command, or else an error occurs. Also, if the size of the old control file differs from that of the new one, you cannot use the REUSE option. The size of the control file changes between some release of new version of Oracle, as well as when the number of files specified in the control file changes; configuration parameters such as MAXLOGFILES, MAXLOGMEMBERS, MAXLOGHISTORY, MAXDATAFILES, and MAXINSTANCES affect control file size. </span></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">If you do not specify files for CONTROL_FILES before database creation, Oracle uses a default filename. The default name is also operating system-specific. </span></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="433"></a><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">You can subsequently change the value of the CONTROL_FILES parameter to add more control files or to change the names or locations of existing control files. </span></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto; mso-outline-level: 3;"><b><span style="color: #330099; font-size: 13.5pt; mso-fareast-font-family: "Times New Roman";">Creating Additional Copies of the Control File, and Renaming and Relocating Control Files </span></b></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="457"></a><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">You add a new control file by copying an existing file to a new location and adding the file's name to the list of control files. </span></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="459"></a><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">Similarly, you rename an existing control file by copying the file to its new name or location, and changing the file's name in the control file list. </span></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">In both cases, to guarantee that control files do not change during the procedure, shut down the instance before copying the control file. </span></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="1023"></a><b><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">To Multiplex or Move Additional Copies of the Current Control Files </span></b></div><ol start="1" type="1"><li class="MsoNormal" style="line-height: normal; mso-list: l22 level1 lfo20; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto; tab-stops: list .5in;"><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">Shut down the database. </span></li></ol><ol start="2" type="1"><li class="MsoNormal" style="line-height: normal; mso-list: l22 level1 lfo21; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto; tab-stops: list .5in;"><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">Exit Enterprise Manager. </span></li></ol><ol start="3" type="1"><li class="MsoNormal" style="line-height: normal; mso-list: l22 level1 lfo22; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto; tab-stops: list .5in;"><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">Copy an existing control file to a different location, using operating system commands. </span></li></ol><ol start="4" type="1"><li class="MsoNormal" style="line-height: normal; mso-list: l22 level1 lfo23; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto; tab-stops: list .5in;"><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">Edit the CONTROL_FILES parameter in the database's parameter file to add the new control file's name, or to change the existing control filename. </span></li></ol><ol start="5" type="1"><li class="MsoNormal" style="line-height: normal; mso-list: l22 level1 lfo24; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto; tab-stops: list .5in;"><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">Restart Enterprise Manager. </span></li></ol><ol start="6" type="1"><li class="MsoNormal" style="line-height: normal; mso-list: l22 level1 lfo25; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto; tab-stops: list .5in;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="473"></a><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">Restart the database. </span></li></ol><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto; mso-outline-level: 3;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="475"></a><b><span style="color: #330099; font-size: 13.5pt; mso-fareast-font-family: "Times New Roman";">New Control Files</span></b></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">You can create a new control file for a database using the CREATE CONTROLFILE command. This is recommended in the following situations: </span></div><ul type="disc"><li class="MsoNormal" style="line-height: normal; mso-list: l13 level1 lfo26; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto; tab-stops: list .5in;"><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">All control files for the database have been permanently damaged and you do not have a control file backup. </span></li><li class="MsoNormal" style="line-height: normal; mso-list: l13 level1 lfo26; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto; tab-stops: list .5in;"><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">You want to change one of the permanent database settings originally specified in the CREATE DATABASE statement, including the database's name, MAXLOGFILES, MAXLOGMEMBERS, MAXLOGHISTORY, MAXDATAFILES, and MAXINSTANCES. </span></li></ul><div class="MsoNormal" style="line-height: normal; margin-bottom: 5.0pt;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="501"></a><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">For example, you might need to change a database's name if it conflicts with another database's name in a distributed environment. As another example, you might need to change one of the previously mentioned parameters if the original setting is too low. </span></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><br /></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">The following statement creates a new control file for the PROD database (formerly a database that used a different database name): </span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; tab-stops: 45.8pt 91.6pt 137.4pt 183.2pt 229.0pt 274.8pt 320.6pt 366.4pt 412.2pt 458.0pt 503.8pt 549.6pt 595.4pt 641.2pt 687.0pt 732.8pt;"><span style="font-size: 10.0pt; mso-fareast-font-family: "Times New Roman";">CREATE CONTROLFILE</span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; tab-stops: 45.8pt 91.6pt 137.4pt 183.2pt 229.0pt 274.8pt 320.6pt 366.4pt 412.2pt 458.0pt 503.8pt 549.6pt 595.4pt 641.2pt 687.0pt 732.8pt;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="507"></a><span style="font-size: 10.0pt; mso-fareast-font-family: "Times New Roman";"><span style="mso-tab-count: 1;"> </span>SET DATABASE prod</span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; tab-stops: 45.8pt 91.6pt 137.4pt 183.2pt 229.0pt 274.8pt 320.6pt 366.4pt 412.2pt 458.0pt 503.8pt 549.6pt 595.4pt 641.2pt 687.0pt 732.8pt;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="509"></a><span style="font-size: 10.0pt; mso-fareast-font-family: "Times New Roman";"><span style="mso-tab-count: 1;"> </span>LOGFILE GROUP 1 ('logfile1A', 'logfile1B') SIZE 50K,</span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; tab-stops: 45.8pt 91.6pt 137.4pt 183.2pt 229.0pt 274.8pt 320.6pt 366.4pt 412.2pt 458.0pt 503.8pt 549.6pt 595.4pt 641.2pt 687.0pt 732.8pt;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="511"></a><span style="font-size: 10.0pt; mso-fareast-font-family: "Times New Roman";"><span style="mso-tab-count: 1;"> </span><span style="mso-spacerun: yes;"> </span>GROUP 2 ('logfile2A', 'logfile2B') SIZE 50K</span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; tab-stops: 45.8pt 91.6pt 137.4pt 183.2pt 229.0pt 274.8pt 320.6pt 366.4pt 412.2pt 458.0pt 503.8pt 549.6pt 595.4pt 641.2pt 687.0pt 732.8pt;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="513"></a><span style="font-size: 10.0pt; mso-fareast-font-family: "Times New Roman";"><span style="mso-tab-count: 1;"> </span>NORESETLOGS</span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; tab-stops: 45.8pt 91.6pt 137.4pt 183.2pt 229.0pt 274.8pt 320.6pt 366.4pt 412.2pt 458.0pt 503.8pt 549.6pt 595.4pt 641.2pt 687.0pt 732.8pt;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="515"></a><span style="font-size: 10.0pt; mso-fareast-font-family: "Times New Roman";"><span style="mso-tab-count: 1;"> </span>DATAFILE 'datafile1' SIZE 3M, 'datafile2' SIZE 5M</span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; tab-stops: 45.8pt 91.6pt 137.4pt 183.2pt 229.0pt 274.8pt 320.6pt 366.4pt 412.2pt 458.0pt 503.8pt 549.6pt 595.4pt 641.2pt 687.0pt 732.8pt;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="517"></a><span style="font-size: 10.0pt; mso-fareast-font-family: "Times New Roman";"><span style="mso-tab-count: 1;"> </span>MAXLOGFILES 50</span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; tab-stops: 45.8pt 91.6pt 137.4pt 183.2pt 229.0pt 274.8pt 320.6pt 366.4pt 412.2pt 458.0pt 503.8pt 549.6pt 595.4pt 641.2pt 687.0pt 732.8pt;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="519"></a><span style="font-size: 10.0pt; mso-fareast-font-family: "Times New Roman";"><span style="mso-tab-count: 1;"> </span>MAXLOGMEMBERS 3</span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; tab-stops: 45.8pt 91.6pt 137.4pt 183.2pt 229.0pt 274.8pt 320.6pt 366.4pt 412.2pt 458.0pt 503.8pt 549.6pt 595.4pt 641.2pt 687.0pt 732.8pt;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="521"></a><span style="font-size: 10.0pt; mso-fareast-font-family: "Times New Roman";"><span style="mso-tab-count: 1;"> </span>MAXDATAFILES 200</span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; tab-stops: 45.8pt 91.6pt 137.4pt 183.2pt 229.0pt 274.8pt 320.6pt 366.4pt 412.2pt 458.0pt 503.8pt 549.6pt 595.4pt 641.2pt 687.0pt 732.8pt;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="523"></a><span style="font-size: 10.0pt; mso-fareast-font-family: "Times New Roman";"><span style="mso-tab-count: 1;"> </span>MAXINSTANCES 6</span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; tab-stops: 45.8pt 91.6pt 137.4pt 183.2pt 229.0pt 274.8pt 320.6pt 366.4pt 412.2pt 458.0pt 503.8pt 549.6pt 595.4pt 641.2pt 687.0pt 732.8pt;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="525"></a><span style="font-size: 10.0pt; mso-fareast-font-family: "Times New Roman";"><span style="mso-tab-count: 1;"> </span>ARCHIVELOG;</span></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="1486"></a><b><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">Warning: </span></b><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">The CREATE CONTROLFILE command can potentially damage specified datafiles and online redo log files; omitting a filename can cause loss of the data in that file, or loss of access to the entire database. Employ caution when using this command and be sure to follow the steps in the next section. </span></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto; mso-outline-level: 3;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="1431"></a><b><span style="color: #330099; font-size: 13.5pt; mso-fareast-font-family: "Times New Roman";">Creating New Control Files</span></b></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="1432"></a><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">This section provides step-by-step instructions for creating new control files. </span></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="1433"></a><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">To Create New Control Files </span></div><ol start="1" type="1"><li class="MsoNormal" style="line-height: normal; mso-list: l19 level1 lfo27; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto; tab-stops: list .5in;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="538"></a><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">Make a list of all datafiles and online redo log files of the database. </span></li></ol><div class="MsoNormal" style="line-height: normal; margin-bottom: 5.0pt;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="540"></a><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">If you followed the recommendations for database backups, you should already have a list of datafiles and online redo log files that reflect the current structure of the database. </span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: 5.0pt;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="542"></a><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">If you have no such lists and your control file has been damaged so that the database cannot be opened, try to locate all of the datafiles and online redo log files that constitute the database. Any files not specified in </span><a href="http://www.csee.umbc.edu/portal/help/oracle8.bak/server803/A54641_01/ch6.htm#552"><span style="color: blue; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">Step 5</span></a><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">are not recoverable once a new control file has been created. Moreover, if you omit any of the files that make up the SYSTEM tablespace, you might not be able to recover the database. </span></div><ol start="2" type="1"><li class="MsoNormal" style="line-height: normal; mso-list: l21 level1 lfo28; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto; tab-stops: list .5in;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="544"></a><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">Shut down the database. </span></li></ol><div class="MsoNormal" style="line-height: normal; margin-bottom: 5.0pt;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="546"></a><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">If the database is open, shut down the database with normal priority, if possible. Use the IMMEDIATE or ABORT options only as a last resort. </span></div><ol start="3" type="1"><li class="MsoNormal" style="line-height: normal; mso-list: l28 level1 lfo29; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto; tab-stops: list .5in;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="548"></a><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">Back up all datafiles and online redo log files of the database. </span></li></ol><ol start="4" type="1"><li class="MsoNormal" style="line-height: normal; mso-list: l28 level1 lfo30; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto; tab-stops: list .5in;"><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">Start up an new instance, but do not mount or open the database. </span></li></ol><ol start="5" type="1"><li class="MsoNormal" style="line-height: normal; mso-list: l28 level1 lfo31; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto; tab-stops: list .5in;"><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">Create a new control file for the database using the CREATE CONTROLFILE command. </span></li></ol><div class="MsoNormal" style="line-height: normal; margin-bottom: 5.0pt;"><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">When creating the new control file, select the RESETLOGS option if you have lost any online redo log groups in addition to the control files. In this case, you will need to recover from the loss of the redo logs (</span><a href="http://www.csee.umbc.edu/portal/help/oracle8.bak/server803/A54641_01/ch6.htm#566"><span style="color: blue; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">Step 8</span></a><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">). You must also specify the RESETLOGS option if you have renamed the database. Otherwise, select the NORESETLOGS option. </span></div><ol start="6" type="1"><li class="MsoNormal" style="line-height: normal; mso-list: l18 level1 lfo32; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto; tab-stops: list .5in;"><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">Store a backup of the new control file on an offline storage device. </span></li></ol><ol start="7" type="1"><li class="MsoNormal" style="line-height: normal; mso-list: l18 level1 lfo33; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto; tab-stops: list .5in;"><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">Edit the parameter files of the database. </span></li></ol><div class="MsoNormal" style="line-height: normal; margin-bottom: 5.0pt;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="564"></a><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">Edit the parameter files of the database to indicate all of the control files created in </span><a href="http://www.csee.umbc.edu/portal/help/oracle8.bak/server803/A54641_01/ch6.htm#552"><span style="color: blue; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">Step 5</span></a><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">and </span><a href="http://www.csee.umbc.edu/portal/help/oracle8.bak/server803/A54641_01/ch6.htm#560"><span style="color: blue; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">Step 6</span></a><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">(not including the backup control file) in the CONTROL_FILES parameter. </span></div><ol start="8" type="1"><li class="MsoNormal" style="line-height: normal; mso-list: l17 level1 lfo34; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto; tab-stops: list .5in;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="566"></a><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">Recover the database if necessary. </span></li></ol><div class="MsoNormal" style="line-height: normal; margin-bottom: 5.0pt;"><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">If you are creating the control file as part of recovery, recover the database. If the new control file was created using the NORESETLOGS option (</span><a href="http://www.csee.umbc.edu/portal/help/oracle8.bak/server803/A54641_01/ch6.htm#552"><span style="color: blue; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">Step 5</span></a><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">), you can recover the database with complete, closed database recovery. </span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: 5.0pt;"><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">If the new control file was created using the RESETLOGS option, you must specify USING BACKUP CONTROL FILE. If you have lost online or archived redo logs or datafiles, use the procedures for recovering those files. </span></div><ol start="9" type="1"><li class="MsoNormal" style="line-height: normal; mso-list: l29 level1 lfo35; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto; tab-stops: list .5in;"><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">Open the database. </span></li></ol><div class="MsoNormal" style="line-height: normal; margin-bottom: 5.0pt;"><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">Open the database using one of the following methods: </span></div><ul type="disc"><li class="MsoNormal" style="line-height: normal; mso-list: l11 level1 lfo36; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto; tab-stops: list .5in;"><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">If you did not perform recovery, open the database normally. </span></li><li class="MsoNormal" style="line-height: normal; mso-list: l11 level1 lfo36; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto; tab-stops: list .5in;"><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">If you performed complete, closed database recovery in </span><a href="http://www.csee.umbc.edu/portal/help/oracle8.bak/server803/A54641_01/ch6.htm#566"><span style="color: blue; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">Step 8</span></a><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">, use the Startup Open radio button of the Startup Database dialog box of Enterprise Manager. </span></li><li class="MsoNormal" style="line-height: normal; mso-list: l11 level1 lfo36; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto; tab-stops: list .5in;"><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">If you specified RESETLOGS when creating the control file, use the ALTER DATABASE command, indicating RESETLOGS. </span></li></ul><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">The database is now open and available for use. <a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="584"></a><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="1526"></a></span></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto; mso-outline-level: 2;"><b><span style="color: #330099; font-size: 18.0pt; mso-fareast-font-family: "Times New Roman";">Troubleshooting After Creating Control Files</span></b></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">After issuing the CREATE CONTROLFILE statement, you may encounter some common errors. This section describes the most common control file usage errors, and includes the following topics: </span></div><ul type="disc"><li class="MsoNormal" style="line-height: normal; mso-list: l12 level1 lfo37; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto; tab-stops: list .5in;"><a href="http://www.csee.umbc.edu/portal/help/oracle8.bak/server803/A54641_01/ch6.htm#606"><span style="color: blue; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">Checking for Missing or Extra Files</span></a><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";"> </span></li><li class="MsoNormal" style="line-height: normal; mso-list: l12 level1 lfo37; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto; tab-stops: list .5in;"><a href="http://www.csee.umbc.edu/portal/help/oracle8.bak/server803/A54641_01/ch6.htm#630"><span style="color: blue; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">Handling Errors During CREATE CONTROLFILE</span></a><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";"> </span></li></ul><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto; mso-outline-level: 3;"><br /></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto; mso-outline-level: 3;"><br /></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto; mso-outline-level: 3;"><b><span style="color: #330099; font-size: 13.5pt; mso-fareast-font-family: "Times New Roman";">Checking for Missing or Extra Files</span></b></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">After creating a new control file and using it to open the database, check the ALERT log to see if Oracle has detected inconsistencies between the data dictionary and the control file, such as a datafile that the data dictionary includes but the control file does not list. </span></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">If a datafile exists in the data dictionary but not in the new control file, Oracle creates a placeholder entry in the control file under the name MISSING<i>nnnn</i> (where <i>nnnn </i>is the file number in decimal). MISSING<i>nnnn</i> is flagged in the control file as being offline and requiring media recovery. </span></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">In the following two cases only, the actual datafile corresponding to MISSING<i>nnnn</i> can be made accessible by renaming MISSING<i>nnnn</i> to point to it. </span></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="622"></a><b><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">Case 1</span></b><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">: The new control file was created using the CREATE CONTROLFILE command with the NORESETLOGS option, thus allowing the database to be opened without using the RESETLOGS option. This would be possible only if all online redo logs are available. </span></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><b><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">Case 2</span></b><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">: It was necessary to use the RESETLOGS option on the CREATE CONTROLFILE command, thus forcing the database to be opened using the RESETLOGS option, but the actual datafile corresponding to MISSING<i>nnnn</i> was read-only or offline normal. </span></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="626"></a><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">If, on the other hand, it was necessary to open the database using the RESETLOGS option, and MISSING<i>nnnn</i> corresponds to a datafile that was not read-only or offline normal, then the rename operation cannot be used to make the datafile accessible (since the datafile requires media recovery that is precluded by the results of RESETLOGS). In this case, the tablespace containing the datafile must be dropped. </span></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="628"></a><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">In contrast, if a datafile indicated in the control file is not present in the data dictionary, Oracle removes references to it from the new control file. In both cases, Oracle includes an explanatory message in the ALERT file to let you know what it found. </span></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto; mso-outline-level: 3;"><b><span style="color: #330099; font-size: 13.5pt; mso-fareast-font-family: "Times New Roman";">Handling Errors During CREATE CONTROLFILE</span></b></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="646"></a><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">If Oracle sends you an error (usually error ORA-01173, ORA-01176, ORA-01177, ORA-01215, or ORA-01216) when you attempt to mount and open the database after creating a new control file, the most likely cause is that you omitted a file from the CREATE CONTROLFILE statement or included one that should not have been listed. In this case, you should restore the files you backed up in </span><a href="http://www.csee.umbc.edu/portal/help/oracle8.bak/server803/A54641_01/ch6.htm#548"><span style="color: blue; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">Step 3</span></a><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">and repeat the procedure from </span><a href="http://www.csee.umbc.edu/portal/help/oracle8.bak/server803/A54641_01/ch6.htm#550"><span style="color: blue; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">Step 4</span></a><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">, using the correct filenames. </span></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto; mso-outline-level: 2;"><br /></div><div class="MsoNormal" style="line-height: normal;"><b><span style="color: #330099; font-size: 18pt;">Dropping Control Files</span></b></div><h2 class="sect1"> Control File?</h2><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" id="sthref770" name="sthref770"></a><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" id="sthref771" name="sthref771"></a>Every Oracle Database has a <span class="bold">control file</span>, which is a small binary file that records the physical structure of the database. The control file includes:<br /><ul><li>The database name<br /> </li><li>Names and locations of associated datafiles and redo log files<br /> </li><li>The timestamp of the database creation<br /> </li><li>The current log sequence number<br /> </li><li>Checkpoint information<br /> </li></ul>The control file must be available for writing by the Oracle Database server whenever the database is open. Without the control file, the database cannot be mounted and recovery is difficult.<br />The control file of an Oracle Database is created at the same time as the database. By default, at least one copy of the control file is created during database creation. On some operating systems the default is to create multiple copies. You should create two or more copies of the control file during database creation. You can also create control files later, if you lose control files or want to change particular settings in the control files.<br /><div class="MsoNormal" style="line-height: normal;"><br /></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">You can drop control files from the database. For example, you might want to do so if the location of a control file is inappropriate. Remember that the database must have at least two control files at all times. </span></div><ol start="1" type="1"><li class="MsoNormal" style="line-height: normal; mso-list: l26 level1 lfo38; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto; tab-stops: list .5in;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="658"></a><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">Shut down the database. </span></li></ol><ol start="2" type="1"><li class="MsoNormal" style="line-height: normal; mso-list: l26 level1 lfo39; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto; tab-stops: list .5in;"><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">Exit Enterprise Manager. </span></li></ol><ol start="3" type="1"><li class="MsoNormal" style="line-height: normal; mso-list: l26 level1 lfo40; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto; tab-stops: list .5in;"><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">Edit the CONTROL_FILES parameter in the database's parameter file to delete the old control file's name. </span></li></ol><ol start="4" type="1"><li class="MsoNormal" style="line-height: normal; mso-list: l26 level1 lfo41; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto; tab-stops: list .5in;"><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">Restart Enterprise Manager. </span></li></ol><ol start="5" type="1"><li class="MsoNormal" style="line-height: normal;"><span style="font-size: 12pt;">Restart the database. </span></li></ol><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><a href="http://www.blogger.com/blogger.g?blogID=1517218626477434974" name="1528"></a><b><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">Warning:</span></b><span style="font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";"> This operation does not physically delete the unwanted control file from the disk. Use operating system commands to delete the unnecessary file after you have dropped the control file from the database. </span><br /><span style="font-size: 12pt;">www.itasraful@gmail.com</span><br /><span style="font-size: 12pt;">www.ipv4-ipv6.blogspot.com </span></div><div class="MsoNormal"><br /></div></div></div></div> Log File Remember, the instance is made up of the files used by Oracle, the processes or threads, and the memory used by Oracle. The files used by t... Read more »
