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