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;