ADD-DROP Redo log files in Oracle Database

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 required to taken 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**$$$$$$$


This document is only for learning purpose and 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.

 826 total views,  1 views today

How useful was this post?

Click on a star to rate it!

Average rating 5 / 5. Vote count: 4

No votes so far! Be the first to rate this post.

As you found this post useful...

Follow us on social media!

We are sorry that this post was not useful for you!

Let us improve this post!

Tell us how we can improve this post?

Spread the love

4 thoughts on “<a href="https://dbsguru.com/add-drop-redo-log-files-in-oracle-database/">ADD-DROP Redo log files in Oracle Database<a>”

    1. Hi Krishna,
      Thanks a ton !!
      Kindly visit our site for regular updates and do share your valuable feedback.
      Thanks,
      Team DBsGuru.

  1. Hi Shruti.

    I am so thankful to you for your contribution in sharing your knowledge and experience in oracle. I cannot even say how useful this has been as I have initially began my work in this. I wish this knowledge spreads until every beginner DBA like me becomes confident while working. Cheers and wish you great success for your initiative 🥳

  2. Thanks Tejaswini for the kind words!
    All of your positive feedbacks motivates us to work even harder! 🙂
    Keep following our posts and sharing your feedbacks with us.

    Thanks and regards,
    Team DBsGuru

Leave a Reply

Your email address will not be published. Required fields are marked *

five × two =

Follow us on Social Media
Open chat
1
Contact Us:
Hi,

Greetings of the day! How can we help you?

Thanks.
Team DBsGuru