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 the database are made up of the following:

• The datafiles. These are the actual files that are used to store tablespaces. [free spaces]
• The redo log files (sometimes just known as the redo log). This is where all redo information is kept. Without the redo log files you would not be able to recover from a system failure.
• 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.
• The parameter file. This file contains tuning information that is used by Oracle at startup. This is commonly referred to as the init.ora file.

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.

As you saw on Day 7, "Administering Tablespaces," a tablespace can hold four different types of segments:

• Data segment--Used to hold tables and clusters
• Index segment--Used to hold indexes
• Rollback segment--Special types of segments that are used to store undo information
• Temporary segment--Used for storing temporary data

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. 

www.newstips24.blogspot.com

Redo Log Files

The most crucial structure for recovery operations is the redo log, 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.

or

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.

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.

New Term: 

 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 rolled forward. All transactions that had modified data but had not been committed will be backed out; this is known as rolling back.

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.

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.

---

WARNING:

  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.

---

How Does the Redo Log Work?

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.

---

NOTE: 

 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 dirty buffers. 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.

---

When a COMMIT 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 COMMIT 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.

The redo log is made up of two or more redo log files or log file groups. A log file group 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.

New Term:

  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 archive log file. These archive log files are very important for the recoverability of the system in the event of a catastrophic failure.

Operations on the redo log files are done with the ALTER DATABASE command that was described on Day 6, "Administering Databases and Datafiles." Using the ALTER DATABASE command you can add redo log groups, add redo log files, rename redo log files, and so on.

Log Switches and Checkpoints

New Term:  
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 log switch, causes several automatic events to occur:

• 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.
• 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 archive log file. This archive log file is used in recovery if needed.
• Log Sequence Number--Each time a redo log file is reused, it is given a log sequence number. 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.

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.

Log Switch and Checkpoint Intervals

You can use the LOG_CHECKPOINT_INTERVAL and LOG_CHECKPOINT_TIMEOUT initialization parameters to control the checkpoint interval. LOG_CHECKPOINT_INTERVAL The LOG_CHECKPOINT_INTERVAL 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.

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 LOG_CHECKPOINT_INTERVAL:

LOG_CHECKPOINT_INTERVAL = 1MB / 512 (bytes/block) = 2,048 blocks

To accomplish this, set LOG_CHECKPOINT_INTERVAL = 2048 in the parameter file. To have the checkpoint occur only at log switches, set the value of LOG_CHECKPOINT_INTERVAL to be larger than the size of your redo log files. LOG_CHECKPOINT_TIMEOUT The parameter LOG_CHECKPOINT_TIMEOUT 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 LOG_CHECKPOINT_TIMEOUT = 600. 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.

Forcing a Checkpoint

A checkpoint can be forced by hand. If you want to force a checkpoint, you can do it with the following command:

ALTER SYSTEM CHECKPOINT;

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.

Forcing a Log Switch

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:

ALTER SYSTEM SWITCH LOGFILE;

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.

Sizing the Redo Log Files

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.

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.

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.

Archiving the Redo Logs

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:

• 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.
• 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.

Archiving is very important to maintain recoverability in the database. Archiving and backups are covered in more detail on Day 16. 

Adding Redo Log Files and Groups

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.

Using the Enterprise Manager

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.

---

NOTE: In this example I have closed all panes except for the Navigator pane.

---

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.

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.

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.

Using the ALTER DATABASE Command

Log files or log file groups can be added or modified with the ALTER DATABASE 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 ALTER DATABASE command to add a log file is shown here:

ALTER DATABASE database

ADD LOGFILE ( `log3a', `log3b' ) SIZE 10M;

To add a new log file to an already existing group, you can use this command:

ALTER DATABASE database

ADD LOGFILE MEMBER `log3c' TO GROUP 3;

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

ALTER DATABASE database

ADD LOGFILE MEMBER `log3c' TO GROUP ( `log3a', `log3b');

As I have said before, by using a SQL script and the ALTER DATABASE command, you can preserve a permanent record of the change and then use the file as a template for other, similar operations.

Modifying Redo Log Files and Groups

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.

Using the Enterprise Manager

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 ALTER DATABASE RENAME FILE 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 ALTER DATABASE command because of its flexibility and options.

Using the ALTER DATABASE Command

You can modify log files or log file groups with the ALTER DATABASE 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

ALTER DATABASE database

RENAME LOGFILE `log1' TO `log2';

Or you can delete a rollback segment with this command:

ALTER DATABASE database

DROP LOGFILE `log1';

You can drop an entire log file group with the following command:

ALTER DATABASE database

DROP LOGFILE GROUP 3;

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:

ALTER DATABASE database

DROP LOGFILE GROUP (`log3a', `log3b');

If necessary, you can drop just a log file group member with this syntax:

ALTER DATABASE database

DROP LOGFILE GROUP MEMBER `log3b';

Characteristics of the Log Files

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.

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.

---

NOTE: I use the term disk volume to refer to either a disk drive or set of disk drives in a RAID array.

---

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.

---

TIP: 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.

---

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.

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. 

Planning the Online Redo Log

Every instance of an Oracle database has an associated online redo log, 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.

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 log sequence number to identify the set of redo entries.

This section describes guidelines you should consider when configuring a database instance's online redo log, and includes the following topics:

• Multiplex the Online Redo Log
• Place Online Redo Log Members on Different Disks
• Set the Size of Online Redo Log Members
• Choose an Appropriate Number of Online Redo Log Files

Multiplex the Online Redo Log

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.

To avoid losing a database due to a single point of failure, Oracle can maintain multiple sets of on-line redo log files. A multiplex online redo log 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.

Warning: 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.

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).

Place Online Redo Log Members on Different Disks

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.

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).

Datafiles and online redo log files should also be on different disks to reduce contention in writing data blocks and redo entries. 

Set the Size of Online Redo Log Members

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.

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.

See Also: The default size of online redo log files is operating system -dependent; for more details see your operating system-specific Oracle documentation. 

Choose an Appropriate Number of Online Redo Log Files

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.

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.

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:

• 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 before creating a database. If MAXLOGFILES is not specified for the CREATE DATABASE statement, Oracle uses an operating system default value.
• 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.
• 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 before creating a database. If no MAXLOGMEMBERS parameter is specified for the CREATE DATABASE statement, Oracle uses an operating system default value.

See Also: 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.

Creating Online Redo Log Groups and Members

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.

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.

Creating Online Redo Log Groups

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.

The following statement adds a new group of redo logs to the database:

ALTER DATABASE

   ADD LOGFILE ('log1c', 'log2c') SIZE 500K;

Note: 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.

Using the ALTER DATABASE statement with the ADD LOGFILE option, you can specify the number that identifies the group with the GROUP option:

ALTER DATABASE

   ADD LOGFILE GROUP 10 ('log1c', 'log2c') SIZE 500K;

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. 

Creating Online Redo Log Members

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.

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.

The following statement adds a new redo log member to redo log group number 2:

ALTER DATABASE

   ADD LOGFILE MEMBER 'log2b' TO GROUP 2;

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.

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:

ALTER DATABASE

   ADD LOGFILE MEMBER 'log2c' TO ('log2a', 'log2b'); 

Note: 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.

Renaming and Relocating Online Redo Log Members

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.

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.

Before renaming any online redo log members, ensure that the new online redo log files already exist.

Warning: 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.

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.

1. Back up the database.

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.

2. Copy the online redo log files to the new location.

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.

Suggestion: You can execute an operating system command to copy a file without exiting Enterprise Manager. Use the Enterprise Manager HOST command.

3. Rename the online redo log members.

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.

4. Open the database for normal operation.

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.

5. Back up the control file.

As a precaution, after renaming or relocating a set of online redo log files, immediately back up the database's control file.

The following example renames the online redo log members. However, first assume that:

• The database is currently mounted by, but closed to, the instance.
• 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.
• 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.

The files LOG1A and LOG2A on Disk A must be copied to the new files LOG1C and LOG2C on Disk C.

ALTER DATABASE

   RENAME FILE 'log1a', 'log2a'

      TO 'log1c', 'log2c';

Dropping Online Redo Log Groups

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.

To drop an online redo log group, you must have the ALTER DATABASE system privilege.

Before dropping an online redo log group, consider the following restrictions and precautions:

• 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.)
• 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 "Forcing a Log Switch" on page 5-12.
• 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.

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.

The following statement drops redo log group number 3:

ALTER DATABASE DROP LOGFILE GROUP 3;

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. 
 

Dropping Online Redo Log Members

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.

To drop an online redo log member, you must have the ALTER DATABASE system privilege.

Consider the following restrictions and precautions before dropping individual online redo log members:

• 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.
• 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.
• 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.
• 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.

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.

The following statement drops the redo log LOG3C:

ALTER DATABASE   DROP LOGFILE MEMBER 'log3c';

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. 

Controlling Checkpoints and Log Switches

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.

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.

This section includes the following checkpoint and log switch topics:

• Setting Database Checkpoint Intervals
• Forcing a Log Switch
• Forcing a Fast Database Checkpoint Without a Log Switch

Setting Database Checkpoint Intervals

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.

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).

You can control the frequency of automatic database checkpoints via the values set in the LOG_CHECKPOINT_INTERVAL and LOG_CHECKPOINT_TIMEOUT parameters.

Setting LOG_CHECK-POINT_INTERVAL

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.

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.

As an example, assume the following conditions:

• All online redo log files of the database instance are 512K.
• The operating system block size is 512 bytes.
• Checkpoints should occur when an online redo log file is half full.

Using this information, you can compute the number of blocks per redo log file as follows:

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:

LOG_CHECKPOINT_INTERVAL=500

Setting LOG_CHECKPOINT_TIMEOUT

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.

Forcing a Log Switch

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.

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.

The following statement forces a log switch:

ALTER SYSTEM SWITCH LOGFILE;

Forcing a Fast Database Checkpoint Without a Log Switch

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.

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.

The following statement forces a checkpoint:

ALTER SYSTEM CHECKPOINT;

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.

Verifying Blocks in Redo Log Files

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.

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.

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.

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.

Clearing an Online Redo Log File

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.

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.

The following statement clears the log files in redo log group number 3:

ALTER DATABASE CLEAR UNARCHIVED LOGFILE

   GROUP 3;

Restrictions

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.

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.

Attention: If you clear an unarchived redo log file, you should take another backup of the database.

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.

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.

See Also: For a complete description of the ALTER DATABASE command, see the Oracle8 Server SQL Reference.

Listing Information about the Online Redo Log

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.

The following query returns information about the online redo log of a database used without the Parallel Server:

SELECT group#, bytes, memberswww.newstips24.blogspot.com

   FROM sys.v$log;

GROUP#        BYTES        MEMBERS

----------    ----------   ----------

         1      81920       2

         2      81920       2

To see the names of all of the member of a group, use a query similar to the following:

SELECT *

   FROM sys.v$logfile

   WHERE group# = 2;

GROUP#         BYTES       MEMBERS

----------    ----------  ---------

         2                  LOG2A

         2 STALE            LOG2B

         2                  LOG2C

If STATUS is blank for a member, the file is in use.

How to change the Redo Log File size in Oracle Database

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.
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.
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.
We have the following Log Groups and members:

SQL> select * from v$logfile;

2     ONLINE      /app01/oratest/oradata/BOTI/BOTI/redo02.log     NO

1     ONLINE      /app01/oratest/oradata/BOTI/BOTI/redo01.log     NO

And the status of the Log Groups is:

SQL> select * from v$log;

 GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARCHIVED  STATUS 

   1          1         12       52428800       1     NO      CURRENT

   2          1         11       52428800       1    YES       ACTIVE 

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:

SQL> alter system checkpoint;

System altered.

Now again check the status of the Redo Groups:

SQL> select * from v$log;

 GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARCHIVED  STATUS 

   1          1         12       52428800       1     NO      CURRENT

   2          1         11       52428800       1    YES      INACTIVE

The group 2 is now Inactive. Hence we can proceed with dropping this group:

SQL> alter database drop logfile group 2;

alter database drop logfile group 2

*

ERROR at line 1:

ORA-01567: dropping log 2 would leave less than 2 log files for instance BOTI (thread 1)

ORA-00312: online log 2 thread 1: '/app01/oratest/oradata/BOTI/BOTI/redo02.log'

What went wrong ???
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.
To overcome this issue, we need to add one more Redo group to the database.
Execute the following step:

SQL> alter database add logfile group 3 '/app01/oratest/oradata/BOTI/BOTI/redo03.log' size 100M;

Database altered.

Now check the logfiles:

SQL> select * from v$logfile;

3     ONLINE      /app01/oratest/oradata/BOTI/BOTI/redo03.log     NO

2     ONLINE      /app01/oratest/oradata/BOTI/BOTI/redo02.log     NO

1     ONLINE      /app01/oratest/oradata/BOTI/BOTI/redo01.log     NO

… and the status of the Groups:

SQL> select * from v$log;

 GROUP#    THREAD#  SEQUENCE#      BYTES     MEMBERS ARCHIVED   STATUS 

   1          1         12        52428800       1     NO       CURRENT

   2          1         11        52428800       1    YES       INACTIVE

   3          1          0        104857600      1    YES       UNUSED

The status of the new Group is UNUSED because it has never been used.
Now, we have 3 Redo Groups in our database. We can now proceed with Dropping Redo Group# 2.

SQL> alter database drop logfile group 2;

Database altered.

Also, delete the file ‘/app01/oratest/oradata/BOTI/BOTI/redo02.log’ from File system also.
Now add the Redo Group 2 back to the database with changed Redo size:

SQL> alter database add logfile group 2 '/app01/oratest/oradata/BOTI/BOTI/redo02.log' size 100M;

Database altered.

SQL> select * from v$logfile;

3     ONLINE      /app01/oratest/oradata/BOTI/BOTI/redo03.log     NO

2     ONLINE      /app01/oratest/oradata/BOTI/BOTI/redo02.log     NO

1     ONLINE      /app01/oratest/oradata/BOTI/BOTI/redo01.log     NO

SQL> select * from v$log;

 GROUP#    THREAD#  SEQUENCE#      BYTES     MEMBERS ARCHIVED   STATUS 

   1          1         12        52428800       1     NO       CURRENT  n DA

   2          1          0        104857600      1    YES       UNUSED   0

   3          1          0        104857600      1    YES       UNUSED   0

Now we have to drop the 1st Redo Log Group. Before that, we need to change the status of this group:

SQL> alter system switch logfile;

System altered.

SQL> select * from v$log;

 GROUP#    THREAD#  SEQUENCE#      BYTES     MEMBERS ARCHIVED   STATUS 

   1          1         12        52428800       1    YES       ACTIVE   n DA

   2          1          0        104857600      1     NO       CURRENT  n DA

   3          1          0        104857600      1    YES       UNUSED   0

Still the Grpoup is in Active status. Issue a checkpoint:

SQL> alter system checkpoint;

System altered.

SQL> select * from v$log;

GROUP#    THREAD#  SEQUENCE#      BYTES     MEMBERS ARCHIVED   STATUS 

   1          1         12        52428800       1    YES       ACTIVE   n DA

   2          1          0        104857600      1     NO       CURRENT  n DA

   3          1          0        104857600      1    YES       UNUSED   0

www.newstips24.blogspot.com

Control Files

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:

1. Shut down the Oracle instance.

2. Copy the control file to another location on another disk volume.

3. Edit the parameter file to include the new file name in the CONTROL_FILES parameter.

4. Restart the Oracle instance.

The control file can also be created using the CREATE CONTROLFILE 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.

The best way to save and protect your control files is to use the ALTER DATABASE database BACKUP CONTROLFILE command. The options to the ALTER DATABASE database BACKUP CONTROLFILE command are as follows:

• TO `filename'--Creates a new control file with the name specified as filename. If the file already exists, the optional REUSE qualifier must be used.
• TO TRACE--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 RESETLOGS or NORESETLOGS, 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.

---

TIP: Any time you make changes to the structure of the database by adding datafiles, redo log files, and so on, run the command ALTER DATABASE database BACKUP CONTROLFILE TO TRACE. 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.

---

Guidelines for Control Files

Name Control Files

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.

During database operation, Oracle Server writes to all necessary files listed for the CONTROL_FILES parameter. 

Multiplex Control Files on Different Disks

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.

Behavior of Multiplexed Control Files

The following list describes the behavior of multiplexed control files:

• Two or more filenames are listed for the initialization parameter CONTROL_FILES in the database's parameter file.
• The first file listed in the CONTROL_FILES parameter is the only file read by the Oracle Server during database operation.
• If any of the control files become unavailable during database operation, the instance becomes inoperable and should be aborted.

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.

Attention: Oracle strongly recommends that your database has a minimum of two control files on different disks.

Place Control Files Appropriately

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.

Manage the Size of Control Files

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.

Creating Control Files

Every Oracle database has a control file. A control files records the physical structure of the database and contains:

• the database name
• names and locations of associated databases and online redo log files
• the timestamp of the database creation
• the current log sequence number
• checkpoint information

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.

Creating Initial Control Files

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.

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.

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.

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.

Creating Additional Copies of the Control File, and Renaming and Relocating Control Files

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.

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.

In both cases, to guarantee that control files do not change during the procedure, shut down the instance before copying the control file.

To Multiplex or Move Additional Copies of the Current Control Files

1. Shut down the database.

2. Exit Enterprise Manager.

3. Copy an existing control file to a different location, using operating system commands.

4. 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.

5. Restart Enterprise Manager.

6. Restart the database.

New Control Files

You can create a new control file for a database using the CREATE CONTROLFILE command. This is recommended in the following situations:

• All control files for the database have been permanently damaged and you do not have a control file backup.
• 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.

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.

The following statement creates a new control file for the PROD database (formerly a database that used a different database name):

CREATE CONTROLFILE

        SET DATABASE prod

        LOGFILE GROUP 1 ('logfile1A', 'logfile1B') SIZE 50K,

           GROUP 2 ('logfile2A', 'logfile2B') SIZE 50K

        NORESETLOGS

        DATAFILE 'datafile1' SIZE 3M, 'datafile2' SIZE 5M

        MAXLOGFILES 50

        MAXLOGMEMBERS 3

        MAXDATAFILES 200

        MAXINSTANCES 6

        ARCHIVELOG;

Warning: 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.

Creating New Control Files

This section provides step-by-step instructions for creating new control files.

To Create New Control Files

1. Make a list of all datafiles and online redo log files of the database.

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.

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 Step 5 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.

2. Shut down the database.

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.

3. Back up all datafiles and online redo log files of the database.

4. Start up an new instance, but do not mount or open the database.

5. Create a new control file for the database using the CREATE CONTROLFILE command.

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 (Step 8). You must also specify the RESETLOGS option if you have renamed the database. Otherwise, select the NORESETLOGS option.

6. Store a backup of the new control file on an offline storage device.

7. Edit the parameter files of the database.

Edit the parameter files of the database to indicate all of the control files created in Step 5 and Step 6 (not including the backup control file) in the CONTROL_FILES parameter.

8. Recover the database if necessary.

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 (Step 5), you can recover the database with complete, closed database recovery.

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.

9. Open the database.

Open the database using one of the following methods:

• If you did not perform recovery, open the database normally.
• If you performed complete, closed database recovery in Step 8, use the Startup Open radio button of the Startup Database dialog box of Enterprise Manager.
• If you specified RESETLOGS when creating the control file, use the ALTER DATABASE command, indicating RESETLOGS.

The database is now open and available for use.

Troubleshooting After Creating Control Files

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:

• Checking for Missing or Extra Files
• Handling Errors During CREATE CONTROLFILE

Checking for Missing or Extra Files

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.

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 MISSINGnnnn (where nnnn is the file number in decimal). MISSINGnnnn is flagged in the control file as being offline and requiring media recovery.

In the following two cases only, the actual datafile corresponding to MISSINGnnnn can be made accessible by renaming MISSINGnnnn to point to it.

Case 1: 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.

Case 2: 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 MISSINGnnnn was read-only or offline normal.

If, on the other hand, it was necessary to open the database using the RESETLOGS option, and MISSINGnnnn 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.

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.

Handling Errors During CREATE CONTROLFILE

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 Step 3 and repeat the procedure from Step 4, using the correct filenames.

Dropping Control Files

Control File?

Every Oracle Database has a control file, which is a small binary file that records the physical structure of the database. The control file includes:

• The database name
  
• Names and locations of associated datafiles and redo log files
  
• The timestamp of the database creation
  
• The current log sequence number
  
• Checkpoint information
  

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.
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.

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.

1. Shut down the database.

2. Exit Enterprise Manager.

3. Edit the CONTROL_FILES parameter in the database's parameter file to delete the old control file's name.

4. Restart Enterprise Manager.

5. Restart the database. 

Warning: 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. 
www.itasraful@gmail.com
www.newstips24.blogspot.com