How to Increase or Add Redo Log Files in RAC Environment

Sometime DBA'S need to work on the task to increase the size of the redologs based on requirement of more data being pushed on. The procedure remain the same if you working on RAC environment for ODA'S and EXADATA machines the slightest difference you will be looking is some varied diskgroup names DATA, RECO and DBFS.

#####################

Script to check Redo logs

#####################

spool redologs.txt
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

################
Output of the script
################

GROUP# THREAD# MEMBER                              ARCHIVED   STATUS     Size (MB)
------ ------- ---------------------------------- ---------- ---------- ---------
     1       1 +DG1/<DB Name>/ONLINELOG/group_1    YES        INACTIVE         100
     1       1 +DG2/<DB Name>/ONLINELOG/group_1    YES        INACTIVE         100
     2       1 +DG1/<DB Name>/ONLINELOG/group_2    YES        INACTIVE         100
     2       1 +DG2/<DB Name>/ONLINELOG/group_2    YES        INACTIVE         100
     3       2 +DG1/<DB Name>/ONLINELOG/group_3    NO         CURRENT          100
     3       2 +DG2/<DB Name>/ONLINELOG/group_3    NO         CURRENT          100
     4       2 +DG1/<DB Name>/ONLINELOG/group_4    YES        UNUSED           100
     4       2 +DG2/<DB Name>/ONLINELOG/group_4    YES        UNUSED           100
     5       3 +DG1/<DB Name>/ONLINELOG/group_5    YES        UNUSED           100
     5       3 +DG2/<DB Name>/ONLINELOG/group_5    YES        UNUSED           100
     6       3 +DG1/<DB Name>/ONLINELOG/group_6    YES        UNUSED           100
     6       3 +DG2/<DB Name>/ONLINELOG/group_6    YES        UNUSED           100

#############
Add Redo Logs
#############

First Add or Resize the redo logs depending on your requirement . You can drop the redologs later, once the addition is done.

alter database add logfile thread 1 group 7 ('+DG1','+DG2') size  250M;
alter database add logfile thread 1 group 8 ('+DG1','+DG2') size  250M;
alter database add logfile thread 2 group 9 ('+DG1','+DG2') size  250M;
alter database add logfile thread 2 group 10 ('+DG1','+DG2') size  250M;
alter database add logfile thread 3 group 11 ('+DG1','+DG2') size  250M;
alter database add logfile thread 3 group 12 ('+DG1','+DG2') size  250M;

###########
Log Switch
###########

Do the log switch to and ensure the status is in INACTIVE state before you drop the redo logs. Use the below commannds to change the state of logfiles.

alter system switch logfile;
alter system checkpoint;
alter system checkpoint global;


Once you find the logfile in INACTIVE state drop them using the below command:
ex: alter database drop logfile group 1;   =====> Whichever is in INACTIVE state.

#############
Desired Result
#############

Use the same script mentioned above to crosscheck your output

GROUP# THREAD# MEMBER                              ARCHIVED   STATUS     Size (MB)
------ ------- ---------------------------------- ---------- ---------- ---------
     7       1 +DG1/<DB Name>/ONLINELOG/group_7     YES        INACTIVE         250
     7       1 +DG2/<DB Name>/ONLINELOG/group_7     YES        INACTIVE         250
     8       1 +DG1/<DB Name>/ONLINELOG/group_8     YES        INACTIVE         250
     8       1 +DG2/<DB Name>/ONLINELOG/group_8     YES        INACTIVE         250
     9       2 +DG1/<DB Name>/ONLINELOG/group_9     NO         CURRENT          250
     9       2 +DG2/<DB Name>/ONLINELOG/group_9     NO         CURRENT          250
    10       2 +DG1/<DB Name>/ONLINELOG/group_10    YES        UNUSED           250
    10       2 +DG2/<DB Name>/ONLINELOG/group_10    YES        UNUSED           250
    11       3 +DG1/<DB Name>/ONLINELOG/group_11    YES        UNUSED           250
    11       3 +DG2/<DB Name>/ONLINELOG/group_11    YES        UNUSED           250
    12       3 +DG1/<DB Name>/ONLINELOG/group_12    YES        UNUSED           250
    12       3 +DG2/<DB Name>/ONLINELOG/group_12    YES        UNUSED           250


You are DONE with Addition and resize of redolog groups.

#############
More Thoughts
#############

You may get through these errors when you are not properly executing the redo logs tasks. These can be ignored.

SYS@<DB Name>1>alter database drop logfile group 7;
alter database drop logfile group 7
*
ERROR at line 1:
ORA-01624: log 7 needed for crash recovery of Name <DB Name> (thread 2)
ORA-00312: online log 7 thread 2: '+DG1/<DB Name>/ONLINELOG/group_7.'
ORA-00312: online log 7 thread 2: '+DG2/<DB Name>/ONLINELOG/group_7.'

SYS@<DB Name>1>
alter database drop logfile group 12;SYS@<DB Name>1>
alter database drop logfile group 12
*
ERROR at line 1:
ORA-01567: dropping log 12 would leave less than 2 log files for <DB Name> <DB Name>3 (thread 3)
ORA-00312: online log 12 thread 3: '+DG1/<DB Name>/ONLINELOG/group_12.'
ORA-00312: online log 12 thread 3: '+DG2/<DB Name>/ONLINELOG/group_12.'

########################
Work Arounds for Errors
########################

PRCR-1079 : Failed to start resource ora.dbname.db
CRS-5017: The resource action "ora.dbname.db start" encountered the following error:
ORA-01618: redo thread 3 is not enabled - cannot mount
. For details refer to "(:CLSN00107:)" in crsd trace file

CRS-2674: Start of 'ora.dbaname.db' on 'node03' failed
CRS-5017: The resource action "ora.dbname.db start" encountered the following error:
ORA-01618: redo thread 4 is not enabled - cannot mount
. For details refer to "(:CLSN00107:)" in crsd trace

SQL> alter database enable public thread 3;

This will resolve the issue. Hurray!!!!!!!!!!