Thursday, March 23, 2017

Add/Increase The Size Of Redo Log Files 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.



1. First Get what log members you currently have and their sizes 
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
/

Sample Output :-

SYS@orcl1>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

/

GROUP#        THREAD#    MEMBER    ARCHIVED   STATUS     Size (MB)
------ ------- ----------------------------------------- ---------- ---------- -----
     1    1 +DATA/orcl/onlinelog/group_1.261.936514659    YES     INACTIVE    50
     2    1 +DATA/orcl/onlinelog/group_2.262.936514661    NO      CURRENT     50
     3    2 +DATA/orcl/onlinelog/group_3.266.936515201    NO      CURRENT     50
     4    2 +DATA/orcl/onlinelog/group_4.267.936515205    YES     INACTIVE    50

Consider the above sample output.  
For Both threads, you have 2 redo log groups with 2 50MB member each. You want to create 3 100MB logs for each thread.

SYS@orcl1>alter database add logfile thread 1 group 5 '+DATA' size 100m;

SYS@orcl1>alter database add logfile thread 1 group 6 '+DATA' size 100m;

SYS@orcl1>alter database add logfile thread 1 group 7 '+DATA' size 100m;

SYS@orcl1>alter database add logfile thread 2 group 8 '+DATA' size 100m;

SYS@orcl1>alter database add logfile thread 2 group 9 '+DATA' size 100m;

SYS@orcl1>alter database add logfile thread 2 group 10 '+DATA' size 100m;


2. Once you have added them, rotate the logs ("alter system switch logfile") on respective instance so that the active log is 100MB and both of the 10MB logs are inactive.

Rotate the logs:
  
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#= <thread_no>;

repeat until the active log is 100MB and both of the 50MB logs are inactive.

3. Once both of the 50MB logs are inactive, you can drop the redo log groups with the 10mb members.

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

Final Result :-

SYS@orcl1>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
/

GROUP# THREAD# MEMBER      ARCHIVED   STATUS     Size (MB)
------ ------- ----------------------------------------- ---------- ---------- -----
     5   1 +DATA/orcl/onlinelog/group_5.375.939365981    YES       INACTIVE     100
     6   1 +DATA/orcl/onlinelog/group_6.376.939366025    YES       INACTIVE     100
     7   1 +DATA/orcl/onlinelog/group_7.377.939366065    NO         CURRENT     100
     8   2 +DATA/orcl/onlinelog/group_8.378.939366095    YES       INACTIVE     100
     9   2 +DATA/orcl/onlinelog/group_9.379.939366117    YES       INACTIVE     100
    10   2 +DATA/orcl/onlinelog/group_10.380.939366143   NO         CURRENT     100
SYS@orcl1>

Note: From 11gR2 onwards, this note applies only to administrator-managed databases.
For policy-managed databases using OMF and ASM, RAC automatically allocates redo threads and undo ts when the instance is started.




No comments:

Post a Comment