Manage Redo log file in RAC Environment

Check the redo log file with thread. Thread represent the instance no present in the RAC database.Suppose 3 Node RAC has 3 threads number.

Resize the redo log file in RAC Environment as follows:

1. Check the size of redo log and how many redo log file present in each instance.
You got information of each instance from thread column. Each thread(instance) has 2 redo log group means two files.

select GROUP#,THREAD#,MEMBERS,STATUS,BYTES from v$log;

GROUP# THREAD# MEMBERS STATUS   BYTES
------ ------- ------- -------- ----------
     1       1       1 INACTIVE 52428800
     2       1       1 CURRENT	52428800
     3       2       1 CURRENT  52428800
     4       2       1 INACTIVE 52428800	  

2. Check the thread information if you needed which thread belong to which instance.

select thread#,status,enabled,instance from v$thread;

THREAD# STATUS ENABLED  INSTANCE
------- ------ -------- --------
      1 OPEN   PUBLIC   ORCL_1
      2 OPEN   PUBLIC   ORCL_2

3. Retrieve all the information of member and location.

select group#, member from v$logfile;

Group# Member
------ ------
1      +DATA/orcl/onlinelog/group_1.391.787059447
2      +DATA/orcl/onlinelog/group_2.390.787059453
3      +DATA/orcl/onlinelog/group_3.258.787054781
4      +DATA/orcl/onlinelog/group_4.256.787054787

4. You want to increase the size of redo log files.

ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 5 '+DATA' SIZE 500m;
ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 6 '+DATA' SIZE 500m;
ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 7 '+DATA' SIZE 500m;
ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 8 '+DATA' SIZE 500m;
OR
alter database add logfile instance 'ORCL_1' group 5 '+DATA' size 500m;
alter database add logfile instance 'ORCL_1' group 6 '+DATA' size 500m;
alter database add logfile instance 'ORCL_2' group 5 '+DATA' size 500m;
alter database add logfile instance 'ORCL_2' group 6 '+DATA' size 500m;


5. Check the status and size increased to 500MB.

select GROUP#,THREAD#,MEMBERS,STATUS,BYTES from v$log;

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

6.If log file is in active state then you use the following command to convert them into inactive state.

Alter system switch logfile;
alter system checkpoint;


7. Now drop the inactive redo log old group.

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