ADD-DROP Redo log files in Oracle Database

November 26, 2020

ADD-DROP Redo Log Files In Oracle Database

In the real world, we may encounter scenarios where we need to or asked to increase/decrease the size of redo log files as log switches creating contentions at the database. For example, by increasing the redo log file size, the number of log switches can be decreased which in turn will improve the database performance. Since there is no provision for resizing the redo log files(members), we need to recreate the redo log groups either by adding groups or dropping groups in order to increase or decrease the size. In this article, we will be discussing how to create and drop redo log groups along with add member to group:

1.Check the location of redo log groups and members. Click here for sample output.

set lines 333
col GROUP# for 9999
col TYPE for a30
col MEMBER for a45
select GROUP#,TYPE,MEMBER from v$logfile;
prompt$$$$$$$**Welcome to DBsGuru!**Share Learn Grow**$$$$$$$

2. Check the status of redo log files.Click here for sample output.

set lines 222
col GROUP# for 99
col THREAD# for 99
col SEQUENCE# for 9999
col MEMBERS for 99
col ARCHIVED for a15
col STATUS for a15
col BLOCKSIZE for 999
col FIRST_CHANGE# for 99999999
col CON_ID for 9
col BYTES  for 9999999999
set numwidth 20
select * from v$log;
prompt$$$$$$$**Welcome to DBsGuru!**Share Learn Grow**$$$$$$$

3. Addition and Deletion of Redo log Group

a) To add a new redo log file group

ALTER DATABASE ADD LOGFILE THREAD 1
GROUP 4 ('/oradata/lab03/redo04a.log') size 100M;  

Database altered.

ALTER DATABASE ADD LOGFILE THREAD 1
GROUP 5 ('/oradata/lab03/redo05a.log') size 100M;  

Database altered.

b) Check the status of redo log files again after addition, refer SQL code from step# 2. Click here for sample output

The status can be described as :

UNUSED: This is often the status of a redo log that’s newly added or immediately after RESETLOGS. Online redo has not been written ever.

CURRENT: This can be the status of the redo log where it’s active and this might be either open or closed.

ACTIVE: This can be the status of active and requires for crash recovery also for block recovery. This status may or might not be archived.

CLEARING: Once we execute the SQL command “ALTER DATABASE CLEAR LOGFILE” log will be re-created as an empty log. Once the log is cleared, the status will be changed to UNUSED.

CLEARING_CURRENT: Current log will be cleared of a closed thread. The log can remain in this status due to some failure within the switch like an I/O error while writing the new log header.

INACTIVE: Log isn’t any longer needed for example instance recovery. It’s going to be in use for media recovery and it may or may not be archived.

INVALIDATED: Archived this current redo log without a log switch.

c) To add a member to the newly created redo log file-group i.e. group 5 in the below example:

ALTER DATABASE ADD LOGFILE MEMBER '/u01/lab03/redo05b.log' TO GROUP 5;

Database altered.

select GROUP#,TYPE,MEMBER from v$logfile;
prompt$$$$$$$**Welcome to DBsGuru!**Share Learn Grow**$$$$$$$

GROUP# TYPE                           MEMBER
------ ------------------------------ ---------------------------------------------
     1 ONLINE                         /oradata/lab03/redo01a.log
     1 ONLINE                         /u01/lab03/redo01b.log
     2 ONLINE                         /oradata/lab03/redo02a.log
     2 ONLINE                         /u01/lab03/redo02b.log
     3 ONLINE                         /oradata/lab03/redo03a.log
     3 ONLINE                         /u01/lab03/redo03b.log
     4 ONLINE                         /oradata/lab03/redo04a.log
     5 ONLINE                         /oradata/lab03/redo05a.log
     5 ONLINE                         /u01/lab03/redo05b.log

9 rows selected.
SQL>
$$$$$$$**Welcome to DBsGuru!**Share Learn Grow**$$$$$$$

d) To delete a redo log file group, 2 points are required to take into consideration from v$log output:

1. ARCHIVED= YES
2.STATUS=INACTIVE

If these conditions satisfy, we are good to go, else it requires to keep rotating (alter system switch logfile) until the above-mentioned conditions are satisfied i.e. redo group is archived and no active transactions are being written to this group, refer SQL code from step# 2. Click here for sample output.

alter system switch logfile;

System altered.


select * from v$log;

e) Although, this is an online activity, however, it is advisable to drop/create a redo when the redo is not too busy. So look for the time when there is less activity, to drop the redo log file, use the below command. Click here for sample output.

ALTER DATABASE DROP LOGFILE GROUP 4;

Database altered.

set lines 333
col GROUP# for 9999
col TYPE for a30
col MEMBER for a45
select GROUP#,TYPE,MEMBER from v$logfile;
prompt$$$$$$$**Welcome to DBsGuru!**Share Learn Grow**$$$$$$$

Click here to know more about Add & Drop Standby Redolog Files on Standby (Dataguard) in Oracle.

This document is only for learning purposes and is always validate in the LAB environment first before applying in the LIVE environment.

Hope so you like this article!
Please share your valuable feedback/comments/subscribe and follow us below and don’t forget to click on the bell icon to get the latest update. Click here to know more about our pursuit.

ADD-DROP Redo Log Files In Oracle Database

Related Articles