How to recreate redo log files in Oracle database

Last updated on January 13th, 2019 at 10:47 am

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, it has to be drop and recreated in order to increase or decrease the size.

Here in this article ,I am presenting the steps on

How to drop and recreate redo log files

Steps to recreate redo log

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

select distinct status from v$backup;
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

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
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

alter database drop logfile group 4;

9) Create the new redo group using the appropriate sql script

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.

Related Articles

What is 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)

Leave a Reply