Resize Online Redologs Files 12c RAC

This is the one of the Important step to stabilize your RAC environment. At least we have to maintain 3 Online redolog groups for each Instance and each group consists of 2 members each & each member in different diskgroups

[oracle@tnc1 ~]$ db

[oracle@tnc1 ~]$ sql

SQL*Plus: Release 12.1.0.2.0 Production on Wed Feb 24 00:09:06 2016

Copyright (c) 1982, 2014, Oacle.  All rights reserved.

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Advanced Analytics and Real Application Testing options

SQL> show parameter db_name;

NAME        TYPE   VALUE

------------------------------------ ----------- ------------------------------

db_name      string   p1tncd

script # Use following Query to get size of the redolog file GROUPS # 

SQL> 

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 a50 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

/

output #

Currently On RAC database, IF YOU have 2 instances / threads and each thread have only 2 redolog groups / 2 members on different diskgroups.At the same time, we are increasing size of the redolog files and adding additional redologfile group for each instance/thread as follows 

SQL> alter database add logfile thread 1 group 5 ('+FRA/P1TNCD/ONLINELOG/redo05.log','+SDXH/P1TNCD/ONLINELOG/redo05.log') size 100m;

SQL> alter database add logfile thread 1 group 6 ('+FRA/P1TNCD/ONLINELOG/redo06.log','+SDXH/P1TNCD/ONLINELOG/redo06.log') size 100m;

SQL> alter database add logfile thread 1 group 7 ('+FRA/P1TNCD/ONLINELOG/redo07.log','+SDXH/P1TNCD/ONLINELOG/redo07.log') size 100m;

SQL> alter database add logfile thread 2 group 8 ('+FRA/P1TNCD/ONLINELOG/redo08.log','+SDXH/P1TNCD/ONLINELOG/redo08.log') size 100m;

SQL> alter database add logfile thread 2 group 9 ('+FRA/P1TNCD/ONLINELOG/redo09.log','+SDXH/P1TNCD/ONLINELOG/redo09.log') size 100m;

SQL> alter database add logfile thread 2 group 10 ('+FRA/P1TNCD/ONLINELOG/redo10.log','+SDXH/P1TNCD/ONLINELOG/redo10.log') size 100m;

Make checkpoint global and make status of online redolog groups are "INACTIVE" and drop them on database immediately 

SQL> alter system checkpoint global;

SQL> alter database drop logfile group 1;

SQL> alter database drop logfile group 2;

SQL> alter database drop logfile group 3;

SQL> alter database drop logfile group 4;

--Nikhil Tatineni--

--12C RAC CLUSTER--