How To Add or Increase The Size Of Redo Log Files In Rac Environment

To add or resize redo logfiles in a RAC steps are

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

Sample output

GROUP# THREAD# MEMBER                            ARCHIVED STATUS    MB
------ ------- --------------------------------- -------- --------- ---
     3       2 /u04/oradata/redologs/redo03.log  NO       INACTIVE   10
     4       2 /u04/oradata/redologs/redo04.log  NO       CURRENT    10
     5       1 /u04/oradata/redologs/redo05.log  NO       CURRENT   100
     6       1 /u04/oradata/redologs/redo06.log  NO       INACTIVE  100
     7       1 /u04/oradata/redologs/redo07.log  NO       INACTIVE  100
     8       1 /u04/oradata/redologs/redo08.log  NO       INACTIVE  100

 

Add Redolog first:

alter database add logfile thread 2 group 9 ('/u06/oradata/redologs/redo09.log') size  100M;

alter database add logfile thread 2 group 10 ('/u06/oradata/redologs/redo10.log') size 100M;

alter database add logfile thread 2 group 11 ('/u06/oradata/redologs/redo11.log') size 100M;

alter database add logfile thread 2 group 12 ('/u06/oradata/redologs/redo12.log') size 100M;

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

Delete logfile group 3 and 4:

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

Result:

GROUP# THREAD# MEMBER                            ARCHIVED STATUS     MB
------ ------- --------------------------------- -------- ---------- ---
     5       1 /u04/oradata/redologs/redo05.log  NO       CURRENT    100 
     6       1 /u04/oradata/redologs/redo06.log  NO       INACTIVE   100 
     7       1 /u04/oradata/redologs/redo07.log  NO       INACTIVE   100 
     8       1 /u04/oradata/redologs/redo08.log  NO       INACTIVE   100 
     9       2 /u06/oradata/redologs/redo09.log  NO       CURRENT    100 
    10       2 /u06/oradata/redologs/redo10.log  NO       INACTIVE   100 
    11       2 /u06/oradata/redologs/redo11.log  NO       INACTIVE   100 
    12       2 /u06/oradata/redologs/redo12.log  NO       INACTIVE   100
Advertisements

About Sher khan

Senior Oracle DBA, Oracle 10g , Oracle 11g OCE ,Oracle 12C and Oracle 11g OPN certified Specialist, OCP Certified in Oracle 9i,10g , 11g and 12C. I am working in Oracle technology since 2004. Currently working in U.A.E Email: sher487@hotmail.com
This entry was posted in Oracle RAC. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s