REDO RESIZE ON RAC

PRECHECK

**********

Check the size of present REDO

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

SQL> set lines 132

SQL> set trims on

SQL> select * from v$log;

SQL> col member format a35

SQL> select * from v$logfile;

Check for the recommended size by running any of the below queries providing peak hour on your environment.

SELECT
(SELECT ROUND (AVG (BYTES) / 1024 / 1024, 2) FROM V$LOG) AS "Redo size (MB)", ROUND ( (20 / AVERAGE_PERIOD) * (SELECT AVG (BYTES) FROM V$LOG) / 1024 / 1024, 2) AS "Recommended Size (MB)" FROM (SELECT AVG ( (NEXT_TIME - FIRST_TIME) * 24 * 60) AS AVERAGE_PERIOD FROM V$ARCHIVED_LOG WHERE FIRST_TIME > SYSDATE - 3 AND TO_CHAR (FIRST_TIME, 'HH24:MI') BETWEEN '16:00' AND '17:00');

SELECT

(SELECT ROUND(AVG(BYTES) / 1024 / 1024, 2) FROM V$LOG) AS "Redo size (MB)",

ROUND((20 / AVERAGE_PERIOD) * (SELECT AVG(BYTES)

FROM V$LOG) / 1024 / 1024, 2) AS "Recommended Size (MB)"

FROM (SELECT AVG((NEXT_TIME - FIRST_TIME) * 24 * 60) AS AVERAGE_PERIOD

FROM V$ARCHIVED_LOG

WHERE FIRST_TIME > SYSDATE - 3

AND TO_CHAR(FIRST_TIME, 'HH24:MI') BETWEEN

&START_OF_PEAK_HOURS AND &END_OF_PEAK_HOURS);

SQL> select group#,thread#,members,status from v$log;

    GROUP#    THREAD#    MEMBERS STATUS

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

         1          1          2 INACTIVE

         2          1          2 CURRENT

         3          2          2 CURRENT

         4          2          2 INACTIVE

         5          3          2 ACTIVE

         6          3          2 CURRENT

Check every time for the status of group you’re going to drop.

Note:

To drop the group the status should be INACTIVE, if it’s no then switch the log file to make it forcibly inactive.

SQL> alter system switch logfile;

Continue this until the group you want to drop comes to INACTIVE state.

EXECUTION

**********

If your each instance has only two groups then create another temporary group.

In RAC, each logfile is associated with thread# each instance can only use one thread,You need a minimal of two group for each thread#.

ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 8 ('+ DISKGROUP_NAME ') SIZE 100M;

When the status is inactive.

Drop the logfile

*************

SQL>  alter database drop logfile group 1;

To remove the file from asm disks (if it is not deleted automatically).

$ export ORACLE_SID=+ASM1

$ sqlplus / as sysdba

SQL>  ALTER DISKGROUP DISKGROUP_NAME DROP FILE '+DISKGROUP_NAME/PATH/group_1.XXXXX';

ADD the logfile with your desired size

*******************************

SQL>  ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 1 ('+ DISKGROUP_NAME ','+ DISKGROUP_NAME ') SIZE 500M;

After all done just drop the temporary group

Drop the logfile

*************

SQL>  alter database drop logfile group 8;

Repeat the above steps for the entire instances log file on RAC.

POST CHECK

***********

Check the size of REDO.

SQL> SELECT a.group#, a.member, b.bytes FROM v$logfile a, v$log b WHERE a.group# = b.group#;

Check the status

SQL> select group#,thread#,members,status from v$log;

SQL> set lines 132

SQL> set trims on

SQL> select * from v$log;

SQL> col member format a35

SQL> select * from v$logfile;

There you good to go.................................................