Adding,Dropping, Clearing and Relocating or Renaming Online Redo Log Files and Redo log File Members

Adding Online Redo Log File Groups: 

 

Generally, we all use the below command to add Redo Log File Groups. Suppose If you have 2 redo log groups in your Environment and Due to the heavy transaction, Database performance is affecting, So in this scenario, you can add an online redo log file groups to run Your database smoothly and Ensure your Database availability and reduce the burden of the oracle process.

Command Used for Online Redo log file Group Creation :

SQL> ALTER DATABSE ADD LOGFILE GROUP 3

('$HOME/ORADATA/u01/log3a.log',

'$HOME/OARADATA/u02/log3b.log') SIZE 1M; 

In some cases, you might need to create additional log file groups. For example, Adding groups can solve availability problems. To create a new group of online redo log files, Use the above SQL command and use the path as per your requirement. 

Redo Log File Group creation syntax is as below:

SQL> ALTER DATABASE ADD LOGFILE [GROUP  integer]  filespec

[, [GROUP  integer]  Filespec ] ... SIZE ];

You Specify the Name and Location of the members with the file specification. The Value of the GROUP parameter can be selected for each online redo log filegroup. If you omit this parameter, the Oracle Server Generates its Value Automatically. 

Adding Online Redo Log File Members :

We use below SQL command to add Online Redo Log File Group Members. You can add new members to existing Online Redo Log File Groups using the following Command. 

SQL> ALTER DATABASE ADD LOGFILE MEMBER 

'$HOME/ORADATA/u04/log1c.log'  To  GROUP 1,

'$HOME/ORADATA/u04/log2c.log ' To  GROUP 2,

'$HOME/ORADATA/u04/log3c.log'  To  GROUP 3 ;

Use the Fully specified name of the log file members, Otherwise, the files are created in a default directory of the database server.

If the file already exists, it must have the same size, and you must specify the REUSE  option. You can Identify the target group either by specifying one or more members of the group or by specifying the group numbers.

Dropping Online Redo Log File Groups :

Generally, We use the Below command to Drop an online redo log file group :

SQL> ALTER DATABASE DROP LOGILE GROUP 3;

To increase or decrease the size of the online redo log file groups, Add a new online redo log file group (With the new size) and then drop the old one.

An entire online redo log file group can be dropped with the following ALTER DATABASE DROP LOGFILE  command. Syntax of Command is as below:

ALTER DATABSE DROP LOGFILE {GROUP  interger | ('filename',filename')};

 

Restrictions

• An instance requires at least two groups of online redo log files.
• An active or Current group Can not be dropped.
• When an online redo log file Group is dropped, the operating system files are not deleted.

Dropping Online Redo Log File Members :

We use the below command to drop an online redo log file members:

SQL>ALTER DATABASE DROP LOGFILE MEMBER 

'$HOME/ORADATA/u04/log3c.log';

You may want to drop an online redo log file member because it is invalid. Use the following  ALTER DATABASE DROP LOGFILE MEMBER command if you want to drip one or more specific online redo log file members.

Command Syntax :

ALTER DATABASE DROP LOGFILE MEMBER 'filename' ;

Restrictions:

• If the member you want to drop is the last valid member of the group, you can not drop that member.
• If the group is current, you must force a log switch before you can drop the member.
• If the Database is running in ARCHIVELOG mode and the log file group to which the member belongs is not archived, then the member can not be dropped.
• When an online redo log file member is dropped, the operating system file is not deleted if you not using the OMF feature.

Relocating or Renaming the Online Redo Log Files :

Relocate or Rename online redo log files by one of the two following ways :

• ALTER DATABASE RENAME FILE command

-  Shut Down the Database.

-  Copy the online redo log files to the new Location.

-  Place the database in MOUNT mode. 

-  Execute the Command .

SQL> ALTER DATABSE RENAME FILE 

'$HOME/ORADATA/u01/log2a.log' To '$HOME/ORADATA/u02/log1c.log' ;

- Open Databae for normal operation.

• Add new members and drop old members.

The Location of Online redo log files can be changed by renaming the online redo log files. Before renaming the online redo log files, Ensure that the new online redo log file exists. The Oracle server changes only the pointers in the control files but does not physically rename or create any operating system files.

Steps for Relocating or Renaming Online Redo Log Members:

1. Shut down the database 

SQL>  SHUTDOWN IMMEDIATE

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

3. Startup the database in mount mode, but do not open it.

 SQL>  CONNECT / AS SYSDBA

 SQL> STARTUP MOUNT

4. Rename the online redo log members using ALTER DATABASE RENAME FILE command.

SQL> ALTER DATABASE RENAME FILE 

'$HOME/ORADATA/u01/log2a.log' To '$HOME/ORADATA/u02/log1c.log' ;

 5. Open the Database for normal operation.

SQL> ALTER DATABASE OPEN;

 

Clearing Online Redo Log Files:

For Clearing the online redo log files by using the following ways :

• ALTER DATABASE CLEAR LOGFILE command can be used to reinitialize an online redo log file.

SQL> ALTER DATABASE CLEAR LOGFILE GROUP 2 ;

• Use the UNARCHIVED keyword to avoid archiving the corrupted online redo log file.

SQL> ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 2 ;

 

An online redo log file might become corrupted while the database is open, and ultimately stop database activity because archiving cannot continue. In this Situation, the ALTER DATABASE CLEAR LOGFILE command can be used to reinitialize the online redo log file without shutting down the database.

This command can overcome two situations where dropping online redo log files is not possible :

• If there are only two log groups.
• The corrupt online redo log file belongs to the current group

If the corrupt online redo log file has not been archived, use the UNARCHIVED keyword in the command to clear the corrupted online redo log files and avoids archiving them. The cleared online redo log files are available for use even though they were not archived.

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 can not recover.  So we should have to perform a fresh backup of the Database if this kind of situation comes.

 

Online Redo Log File Configuration:

To determine the appropriate number of online redo log files for a database instance, you must test different configurations.

In some cases, a database instance may require only two groups. In another situation, a database instance may require additional groups to guarantee that the groups are always available to LGWR. 

For example: if the messages in the LGWR trace file or in the alert log file indicate the LGWR frequently must wait for a group because a checkpoint has not completed or a group has not been archived, you should add groups.

Location of Online Redo Log File:

When you multiplex the online redo log files, Place members of a group on different disks. By doing this, even if one member is not available but other members are available, the instance does not shut down.

Separate archive log files and online redo log files on different disks to reduce contention between the ARCn and LGWR background processes.

Datafiles and online redo log files should be placed on different disks to reduce LGWR and DBWn contention and reduce the risk of losing both data files and online redo log files in the event of media failure.     

Sizing Online Redo Log Files :

The minimum size of an online redo log file is 50 KB, and the maximum size is specific to the operating system. Members of the different groups can have different size, however, there is no benefit in having different-sized groups.

Different Sized groups should be required as a temporary result only if you want to change the size of the members of the online redo log file groups. In this case, you should create a new online redo log file  groups with different sizes, and then remove the old groups

The following situations might influence the configuration of the online redo log files: 

• Number of  log switches and checkpoints
• Number and amount of redo entries
• Amount of space on the storage medium. for example on tape if archiving is enabled. 

Thank you !!