How to add redo log files in RAC environment in Oracle

Leave a reply

Step1: Check the Size of Redo log files in Oracle instances of RAC

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
/
GROUP# THREAD# MEMBER                             ARCHIVED STATUS    MB
------ ------- ---------------------------------- -------- --------- ---
     1       2 /u03/oradata/redologs/redo01a.log  NO       INACTIVE   10
     1       2 /u03/oradata/redologs/redo01b.log  NO       INACTIVE   10
     2       2 /u03/oradata/redologs/redo02a.log  NO       CURRENT    10
     2       2 /u03/oradata/redologs/redo02b.log  NO       CURRENT    10
     3       1 /u03/oradata/redologs/redo03a.log  NO       CURRENT   200
     3       1 /u03/oradata/redologs/redo03b.log  NO       CURRENT   200
     4       1 /u03/oradata/redologs/redo04a.log  NO       INACTIVE  200
     4       1 /u03/oradata/redologs/redo04b.log  NO       INACTIVE  200
     5       1 /u03/oradata/redologs/redo05a.log  NO       INACTIVE  200
     5       1 /u03/oradata/redologs/redo05b.log  NO       INACTIVE  200
     6       1 /u03/oradata/redologs/redo06a.log  NO       INACTIVE  200
     6       1 /u03/oradata/redologs/redo06b.log  NO       INACTIVE  200

Step 2: You need to remove the RAC2 instance thread 2 files of 10 MB and create 4 new files of 200MB.

Add 4 new group to Thread 2 of 200MB files

alter database add logfile
thread 2 group 7
('/u03/oradata/redologs/redo07a.log','/u03/oradata/redologs/redo07b.log') size 200M;

alter database add logfile
thread 2 group 8
('/u03/oradata/redologs/redo08a.log','/u03/oradata/redologs/redo08b.log') size 200M;

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

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

Step 3: Connect to instance 2 for switch the logfile 

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 ;

Step 4: Drop the log file of 10 MB from instance 2:

alter database drop logfile group 1;
alter database drop logfile group 2;

Step 5: Verify the redo log files in RAC environment:

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
/

Leave a Reply