How to recreate redo log files



Last updated on September 15th, 2016 at 05:30 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 recreated inorder to increase or decrease the size.

Here in this article ,I am presenting the steps on How to recreate redo log files

Steps to recreate redo log

1) Make sure database is not in hot backup mode.

select distinct status from v$backup;

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
10) Repeat the same for all redo group and get all the redo recreated
11) The same is true for redo log file member.


Leave a Reply