Resize Redo logs in Oracle Database

In RAC, each instance has a redo thread; when you start the instance, it mounts one and only one thread. Therefore if you want to add logs or increase the size, you must do it for each thread. The goal is normally to have the same number and size of logs for each thread.

First, determine what log members each thread has

Here is a sample of a script to show what log members you currently have and their sizes:
-- Script begins here -
spool log.lst
set echo off
set feedback off
set linesize 120
set pagesize 35
set trim on
set trims on
set lines 120
col group# format 999
col thread# format 999
col member format a70 wrap
col status format a10
col archived format a10
col fsize format 999 heading "Size (MB)"

select l.group#, l.thread#,
f.member,
l.archived,
l.status,
(bytes/1024/1024) fsize
from
v$log l, v$logfile f
where f.group# = l.group#
order by 1,2
/
spool off
-- End of script --
Sample output:
GROUP# THREAD# MEMBER                             ARCHIVED STATUS    MB
------ ------- ---------------------------------- -------- --------- ---
     3       2 /u02/oradata/redologs/redo03a.log  NO       INACTIVE   10
     3       2 /u02/oradata/redologs/redo03b.log  NO       INACTIVE   10
     4       2 /u02/oradata/redologs/redo04a.log  NO       CURRENT    10
     4       2 /u02/oradata/redologs/redo04b.log  NO       CURRENT    10
     5       1 /u02/oradata/redologs/redo05a.log  NO       CURRENT   50
     5       1 /u02/oradata/redologs/redo05b.log  NO       CURRENT   50
     6       1 /u02/oradata/redologs/redo06a.log  NO       INACTIVE  50
     6       1 /u02/oradata/redologs/redo06b.log  NO       INACTIVE  50
     7       1 /u02/oradata/redologs/redo07a.log  NO       INACTIVE  50
     7       1 /u02/oradata/redologs/redo07b.log  NO       INACTIVE  50
     8       1 /u02/oradata/redologs/redo08a.log  NO       INACTIVE  50
     8       1 /u02/oradata/redologs/redo08b.log  NO       INACTIVE  50

EXAMPLE:

Consider the above sample output.  For Thread 2, you have 2 redo log groups with 2 10MB member each. You want to create 4 50MB logs for thread 2.

1. Add 4 new redo log groups to Thread 2, with two member each, the member 50MB in size :

alter database add logfile
thread 2 group 9
('/u02/oradata/redologs/redo09a.log','/u02/oradata/redologs/redo09b.log') size 50M;

alter database add logfile
thread 2 group 10
('/u02/oradata/redologs/redo10a.log','/u02/oradata/redologs/redo10b.log') size 50M;

alter database add logfile
thread 2 group 11
('/u02/oradata/redologs/redo11a.log','/u02/oradata/redologs/redo11b.log') size 50M;

alter database add logfile
thread 2 group 12
('/u02/oradata/redologs/redo12a.log','/u02/oradata/redologs/redo12b.log') size 50M;

2. Once you have added them, rotate the logs ("alter system switch logfile") on instance 2 so that
the active log is 50MB and both of the 10MB logs are inactive.


Rotate the logs:




alter system switch logfile;


select l.group#, l.thread#, f.member, l.archived, l.status, (bytes/1024/1024) fsize
from v$log l, v$logfile f where f.group# = l.group#
and l.thread#=2 ;



repeat until the active log is 50MB and both of the 10MB logs are inactive.


3. Once both of the 10MB logs are inactive, you can drop the redo log groups with the 10mb members.




alter database drop logfile group 3;
alter database drop logfile group 4;


Result:

GROUP# THREAD# MEMBER                            ARCHIVED STATUS     MB
------ ------- --------------------------------- -------- ---------- ---
     5       1 /u02/oradata/redologs/redo05a.log  NO       CURRENT    50
     5       1 /u02/oradata/redologs/redo05b.log  NO       CURRENT    50
     6       1 /u02/oradata/redologs/redo06a.log  NO       INACTIVE   50
     6       1 /u02/oradata/redologs/redo06b.log  NO       INACTIVE   50
     7       1 /u02/oradata/redologs/redo07a.log  NO       INACTIVE   50
     7       1 /u02/oradata/redologs/redo07b.log  NO       INACTIVE   50
     8       1 /u02/oradata/redologs/redo08a.log  NO       INACTIVE   50
     8       1 /u02/oradata/redologs/redo08b.log  NO       INACTIVE   50
     9       2 /u02/oradata/redologs/redo09a.log  NO       CURRENT    50
     9       2 /u02/oradata/redologs/redo09b.log  NO       CURRENT    50
    10       2 /u02/oradata/redologs/redo10a.log  NO       INACTIVE   50
    10       2 /u02/oradata/redologs/redo10b.log  NO       INACTIVE   50
    11       2 /u02/oradata/redologs/redo11a.log  NO       INACTIVE   50
    11       2 /u02/oradata/redologs/redo11b.log  NO       INACTIVE   50
    12       2 /u02/oradata/redologs/redo12a.log  NO       INACTIVE   50
    12       2 /u02/oradata/redologs/redo12b.log  NO       INACTIVE   50