redo in RAC environment

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 --
--
-- Please note, this is a sample script
-- provided for educational purposes only
-- and is not certified by Oracle Support for any purpose.

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 /u04/oradata/redologs/redo03a.log  NO       INACTIVE   10
     3       2 /u04/oradata/redologs/redo03b.log  NO       INACTIVE   10
     4       2 /u04/oradata/redologs/redo04a.log  NO       CURRENT    10
     4       2 /u04/oradata/redologs/redo04b.log  NO       CURRENT    10
     5       1 /u04/oradata/redologs/redo05a.log  NO       CURRENT   100
     5       1 /u04/oradata/redologs/redo05b.log  NO       CURRENT   100
     6       1 /u04/oradata/redologs/redo06a.log  NO       INACTIVE  100
     6       1 /u04/oradata/redologs/redo06b.log  NO       INACTIVE  100
     7       1 /u04/oradata/redologs/redo07a.log  NO       INACTIVE  100
     7       1 /u04/oradata/redologs/redo07b.log  NO       INACTIVE  100
     8       1 /u04/oradata/redologs/redo08a.log  NO       INACTIVE  100
     8       1 /u04/oradata/redologs/redo08b.log  NO       INACTIVE  100

Steps to add redo log groups to Oracle RAC setup

1. Check existing redo log groups & redo log file size.

SQL> column REDOLOG_FILE_NAME format a50;
SQL> set lines 1000
SQL> SELECT a.GROUP#, a.THREAD#, a.SEQUENCE#,
 a.ARCHIVED, a.STATUS, b.MEMBER AS REDOLOG_FILE_NAME,
 (a.BYTES/1024/1024) AS SIZE_MB FROM v$log a
JOIN v$logfile b ON a.Group#=b.Group#
ORDER BY a.GROUP#;

 GROUP#    THREAD#  SEQUENCE# ARC STATUS           REDOLOG_FILE_NE              SIZE_MB
------- ---------- ---------- --- ---------------- --------------------------- ----------
      1          1          3 YES ACTIVE           +REDO/boston/redo01.log      100
      2          1          4 NO  CURRENT          +REDO/boston/redo02.log      100
      3          2          1 YES INACTIVE         +REDO/boston/redo03.log      100
      4          2          2 NO  CURRENT          +REDO/boston/redo04.log      100

2. As we checked redo log file location & size, we will proceed for redo log group addtions.

SQL> ALTER DATABASE ADD LOGFILE THREAD 1
GROUP 5 ('+REDO/boston/redo05.log') SIZE 100m,
GROUP 6 ('+REDO/boston/redo06.log') SIZE 100m;

Database altered.
SQL> ALTER DATABASE ADD LOGFILE THREAD 2
GROUP 7 ('+REDO/boston/redo07.log') SIZE 100m,
GROUP 8 ('+REDO/boston/redo08.log') SIZE 100m;

Database altered.

We can use below commands also:

ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 5 '+REDO' SIZE 100m;
ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 6 '+REDO' SIZE 100m;
ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 7 '+REDO' SIZE 100m;
ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 8 '+REDO' SIZE 100m;

3. Check for newly added redo log groups:

SQL> column REDOLOG_FILE_NAME format a50;
SQL> set lines 1000
SQL> SELECT a.GROUP#, a.THREAD#, a.SEQUENCE#,
 a.ARCHIVED, a.STATUS, b.MEMBER AS REDOLOG_FILE_NAME,
 (a.BYTES/1024/1024) AS SIZE_MB FROM v$log a
JOIN v$logfile b ON a.Group#=b.Group#
ORDER BY a.GROUP#;

    GROUP#    THREAD#  SEQUENCE# ARC STATUS           REDOLOG_FILE_NAME                                     SIZE_MB
---------- ---------- ---------- --- ---------------- -------------------------------------------------- ----------
         1          1          3 YES INACTIVE         +REDO/boston/redo01.log                                 100
         2          1          4 NO  CURRENT          +REDO/boston/redo02.log                                 100
         3          2          1 YES INACTIVE         +REDO/boston/redo03.log                                 100
         4          2          2 NO  CURRENT          +REDO/boston/redo04.log                                 100
         5          1          0 YES UNUSED           +REDO/boston/redo05.log                                 100
         6          1          0 YES UNUSED           +REDO/boston/redo06.log                                 100
         7          2          0 YES UNUSED           +REDO/boston/redo07.log                                 100
         8          2          0 YES UNUSED           +REDO/boston/redo08.log                                 100

Steps to drop redo log groups

1. While dropping redo log group & we need to check the status of redo log groups. redo log group status should be INACTIVE or UNUSED.

SQL>  select group#,thread#,members,status from v$log;

    GROUP#    THREAD#    MEMBERS     STATUS
---------- ---------- ---------- ----------------
         1          1          2    INACTIVE
         2          1          2    INACTIVE
         3          2          2    CURRENT
         4          2          2    INACTIVE
         5          1          2    INACTIVE
         6          1          2    INACTIVE
         7          2          2    INACTIVE
         8          2          2    CURRENT

2. To drop inactive/unused redo log group:

SQL> Alter database drop logfile group 1;
Database altered.

3. Check again to verify the redo log groups:

SQL>  select group#,thread#,members,status from v$log;