Delete/Drop a Redo Log File or a Redo Log Group in Oracle

Deleting / Dropping a Redo Log File or a Redo Log Group

How to delete a Redo Log File or a Redo Log Group and Recreating a new one? What are the steps that need to be taken care during the whole Redo Log Deletion process?

Redo Log files are an important part of our Oracle Database as they collect all the entries for the changes made to the database. Usually we see 3 Redo Log Groups that are by default present in your system whenever you install the Oracle Database. Each log group contains a logfile which contains all the changed log information. If archive mode is enabled, the log from these Redo Log Files is copied to archive logs which are kept separated in Flash Recovery Area (By Default) or somewhere else ( Manually Defined Path), depending upon your need. 

Now sometimes our Redo Log files gets corrupted. These reason can be a hardware error or some other issue, but the point is that it becomes useless. It's status will go INVALID and it'll stop collecting information regarding the changed logs. There is no way to recover and restore this logfile as we usually copy it's content and store it in archivelog. We can't put an archivelog file in place of a redo log file. So we have to drop the logfile and recreate a new one (as simple as it is). This is a simple process and will take a few minutes and all of your redolog files will be up again.

What I am going to do here is simply drop my logfile and recreate a new one. So just let me check the status of my redolog files first.

As you can see I have 3 Redo Log Groups, each of them having their own logfile.

DROPPING A LOGFILE / LOG GROUP

Instead of dropping a logfile, I am going to drop the whole logfile group (as I am having only one logfile present in each of them) and recreate a new one. For that I'll use the command,

alter database drop logfile group 1;

If you have multiple log files present in your Redo Log Group and one of them is corrupted, you don't need to drop the whole log group. You can drop a log file alone and recreate it. To drop a logfile, give command (For example, I am using mine)

alter database drop logfile 'E:\APP\PAMMU\ORADATA\ORCL\REDO02.LOG';

Also learn - How to Multiplex Redo Log Files in Oracle to know how to add more redo log files in the redo log groups to keep the logs secure.

Important:- Before dropping log file or log group, please check for the status in V$LOG that whether that particular log file or log group is INACTIVE or not. If the status shows active that means the LGWr process is still waiting for the DBWr process. To make this process faster, issue command

alter database checkpoint;

which will tell the Oracle database to free the buffer by writing it from the memory to physical disks. After the above command, check for the status in the V$LOG and it will show INACTIVE for all the redo log groups except for the current one. 

Note:- Don't forget to delete the log file manually after giving the drop command otherwise whenever you'll try to create the same log file again, it will through error saying that the file already exists.

CREATING A LOGFILE / LOG GROUP

After you have physically removed the logfile from the disk, you can easily create a new log group or a log file for your Oracle Database. To create a new log group, please issue command,

alter database add logfile group 1 'E:\APP\PAMMU\ORADATA\ORCL\REDO01.LOG' size 50M;

or to know how to add log members (log files) into the log group, please go through my article - How to Multiplex Redo Log Files in Oracle

I hope this article helps !!