Home » Oracle » Oracle Database » ADD /Drop redo log files in Oracle database

ADD /Drop redo log files in Oracle database

We often get situation where we need to increase the size of the redo log files as log switches are very frequent.Increasing the redo log files sizes decreases the log switches and  improves the performance of the database.There is no resize command for redo log files, we have to recreate redo log i,e dropped and added in order to increase or decrease the size. So need to drop/add redo log in order to change the size.

Here in this article ,I am presenting the steps on How to drop and add redo log files i,e how to resize the redo log files

(1) Make sure database is not in hot backup mode and get the current status of the redo logs

How to check the status of Hot backup

select distinct status from v$backup;

How to check the status of redo log files

select THREAD#, INSTANCE, GROUPS, STATUS, ENABLED from v$thread;

select group#, status, member from v$logfile order by 1;

(2) It is better to drop/create redo when redo is not too busy.So look for the time where there is less activity


(3) take a backup of control file to trace

alter database backup controlfile to trace;
exit

(4) Login again
Run the following sql to check the status of Redo logs

select l.thread#, l.group#, l.archived, l.status, bytes/1024/1024 MB
from v$log l,
v$instance i
where l.thread# = i.thread#
order by 1, 2
THREAD# GROUP# ARC STATUS MB
---------- ---------- --- ---------------- -----
1 1 NO CURRENT 200
1 2 YES INACTIVE 200
1 3 YES INACTIVE 200
1 4 YES ACTIVE 200

(5) Pick the highest number log

See also  How to check Package version in EBS R12


(6) If the group is something other than Archive=Yes and Status=Inactive, then you need to rotate it.So run

alter system switch log file;

check again,if it is not Archive=Yes and Status=Inactive then run again

7) Then again run the above command

select l.thread#, l.group#, l.archived, l.status, bytes/1024/1024 MB
from v$log l,
v$instance i
where l.thread# = i.thread#
order by 1, 2

(8) When the group is Archive=Yes and Status=Inactive, then you can drop it in your command session using alter database drop logfile group

alter database drop logfile group 1;

(9) Create the new redo group using alter database add logfile group

alter database add logfile group 1 (
'/f01/oracle/app/testdata/redo01a.dbf',
'/f02/oracle/app/testdata/red01b.dbf') size 200M;
With ASM
alter database add logfile group 1 (
'+DATA',
'+FLASH') size 200M;

(10) Repeat the same for all redo group and get all the redo recreated
(11) The same is true for redo log file member.

You should be following the each steps for error free recreation of redo logs. It is advisable to test the steps on Test environment first before moving to Production environment.

I hope the steps are clear on how to recreate redo log in Oracle database using alter database drop logfile group and alter database add logfile group

Related Articles

Undo and redo in Oracle database
how to find session generating lots of redo
alter system switch logfile v/s archive log current
Local Undo in Oracle Database 12c R2(12.2)
https://docs.oracle.com/cd/B28359_01/server.111/b28310/onlineredo005.htm#ADMIN11324

Leave a Comment

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

Scroll to Top