HOW TO RESIZE REDOLOG

**********HOW TO RESIZE REDOLOG**************

+++++++++++++++++++++++++

THE BELOW IS A EXAMPLE OF RESIZING REDOLOGS FOR DATABASE

1)Login database and find the log group and member info and size of them using  below query.

set lines 300

col MEMBER for a65;

SELECT a.group#, a.member, (b.bytes)/1024/1024  FROM v$logfile a, v$log b WHERE a.group# = b.group#;

    GROUP# MEMBER                                                  (B.BYTES)/1024/1024

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

         1 +DATAC1/EVNAPSQ1/ONLINELOG/group_6.1703.1009951733                     4096

         2 +DATAC1/EVNAPSQ1/ONLINELOG/group_5.1704.1009951731                     4096

         3 +DATAC1/EVNAPSQ1/ONLINELOG/group_4.1705.1009951727                     4096

2)Verify the mount point space availability to resize the log files.

3)The database only have 3 groups, add the 2 new groups 1st.

set linesize 300

column REDOLOG_FILE_NAME format a50

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# ASC;

ALTER DATABASE ADD LOGFILE

GROUP   9 ( '+DATA/<sid>/redo01g09.log', '+DATA/<sid>/redo02g09.log' ) SIZE 4 G  REUSE,

GROUP  10 ( '+DATA/<sid>/redo02g10.log', '+DATA/<sid>/redo01g10.log' ) SIZE 4 G  REUSE,

GROUP  11 ( '+DATA/<sid>/redo02g11.log', '+DATA/<sid>/redo01g11.log' ) SIZE 4 G  REUSE

ALTER DATABASE ADD LOGFILE

INSTANCE 'i2'

GROUP  12 ( '+DATA/<sid>/redo01g12.log', '+DATA/<sid>/redo02g12.log' ) SIZE 4 G  REUSE,

GROUP  13 ( '+DATA/<sid>/redo01g13.log', '+DATA/<sid>/redo02g13.log' ) SIZE 4 G  REUSE,

GROUP  14 ( '+DATA/<sid>/redo01g14.log', '+DATA/<sid>/redo02g14.log' ) SIZE 4 G  REUSE;

ALTER DATABASE ADD LOGFILE

INSTANCE 'i3'

GROUP  15 ( '+DATA/<sid>/redo01g15.log', '+DATA/<sid>/redo02g15.log' ) SIZE 4 G  REUSE,

GROUP  16 ( '+DATA/<sid>/redo02g16.log', '+DATA/<sid>/redo01g16.log' ) SIZE 4 G  REUSE,

GROUP  17 ( '+DATA/<sid>/redo01g17.log', '+DATA/<sid>/redo02g17.log' ) SIZE 4 G  REUSE;

alter database enable thread 2;

alter database enable thread 3;

Switch the logs:

alter system switch logfile;

wait for a minute or so.

alter system switch logfile;

4) check the status of the group member  before drop the existing one.

select group#, status from v$log;

 GROUP# STATUS

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

         1 INACTIVE

         2 INACTIVE

         3 CURRENT

as per the output we can drop the above group 1 and recreate the group1 by below query.

ALTER DATABASE DROP LOGFILE GROUP 1;

run the below statement to make sure group 1 is not showing before removing:

select * from v$log;

remove the old ones:

rm /<PATH>/redo11.log

rm /<PATH>/redo12.log

Add the group 1 with new size:

alter database add logfile group 1 (

   '/<PATH>/redo11.log',

   '/<PATH>/redo12.log') size 600m;

Switch the logs:

alter system switch logfile;

5)again check the status of the redo group by above query  and recreate only if the status of the group 2 and 3 is in inactive status. If the log groyup status is active then issue the below command to switch the log file and recreate it.

select group#, status from v$log;

If log 2 is inactive, drop it, if not, you can switch the logs until it's inactive before dropping.

ALTER DATABASE DROP LOGFILE GROUP 2;

run the below to make sure group 2 is not showing before removing:

select * from v$log;

remove the old ones:

rm /<PATH>/redo21.log

rm /<PATH>/redo22.log

add the group 2 with new size:

alter database add logfile group 2 (

   '/<PATH>/redo21.log',

   '/<PATH>/redo22.log') size 600m;

Switch the logs:

alter system switch logfile;

6)again check the status of the redo group by above query  and recreate only if the status of the group 2 and 3 is in inactive status. If the log groyup status is active then issue the below command to switch the log file and recreate it.

select group#, status from v$log;

If log 3 is inactive, drop it, if not, you can switch the logs until it's inactive before dropping.

ALTER DATABASE DROP LOGFILE GROUP 3;

run the below to make sure group 3 is not showing before removing:

select * from v$log;

remove the old ones:

rm /<PATH>/redo31.log

rm /<PATH>/redo32.log

alter database add logfile group 3 (

   '/<PATH>/redo31.log',

   '/<PATH>/redo32.log') size 600m;

Switch the logs:

alter system switch logfile;

select * from v$log;

It should show the new size and a total of 5 groups.