How to resize REDO on RAC instance with ASM option

In this post, i try to explain how we can resize redolog files on RAC instance while we have ASM

1. First check existing status wiht below query:

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
Sampe output can be like below:

GROUP# THREAD# MEMBER                                        ARCHIVED    STATUS    MB
—— ——- ———————————             ——– ——— —
1    2    +ORADATA/test/onlinelog/group_8.882.743077403        NO        CURRENT     100
2    2    +ORADATA/test/onlinelog/group_9.610.743077405        YES       INACTIVE    100
3    1    +ORADATA/test/onlinelog/group_10.871.743077407    YES       INACTIVE    100
4    1    +ORADATA/test/onlinelog/group_11.886.743078145    YES       INACTIVE    100

2. Now our purpose is increase redosize from 100Mb to 500 Mb

3. We are going to add our new redo file by below command:

alter database add logfile group 5 ‘+ORADATA’ size 500M;      << run this command on node 2

alter database add logfile group 6 ‘+ORADATA’ size 500M;    << run this command on node 2

alter database add logfile group 7 ‘+ORADATA’ size 500M;    << run this command on node 1

alter database add logfile group 8 ‘+ORADATA’ size 500M;    << run this command on node 1

4. We run below query again

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
/

Sampe output can be like below:

GROUP# THREAD# MEMBER                                                                     ARCHIVED     STATUS        MB
—— ——- ———————————                                                     ——–             ———           —
1    2    +ORADATA/test/onlinelog/group_1.882.743077403        NO                CURRENT        100
2    2    +ORADATA/test/onlinelog/group_2.610.743077405       YES              INACTIVE       100
3    1    +ORADATA/test/onlinelog/group_3.871.743077407       YES              INACTIVE       100
4    1    +ORADATA/test/onlinelog/group_4.886.743078145        YES              INACTIVE       100
5    2    +ORADATA/test/onlinelog/group_4.1728.743078149      NO                UNUSED           500
6    2    +ORADATA/test/onlinelog/group_6.1728.743077542     NO                UNUSED           500
7    1    +ORADATA/test/onlinelog/group_7.1728.743078149     NO                  UNUSED         500
8    1    +ORADATA/test/onlinelog/group_5.1728.743077852     NO                  UNUSED        500

5. Switch until we are into log group 5,6,7,8 so we can drop log groups 1, 2,3 and 4:

SQL> alter system switch logfile;
** repeat as necessary until group 5 first is CURRENT **

6. Run the query again to verify the current log group is group 4, if its INACTIVE than you can drop related group safely

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

7. Now drop redo log groups 1, 2, and 3:

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;

Verify the groups were dropped, and the new groups’ sizes are correct.

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
/

About these ads

2 Responses to How to resize REDO on RAC instance with ASM option

  1. john says:

    Hello
    Assume,
    db_create_file_dest=+DATA
    db_create_online_log_dest_1=+LOGS
    db_create_online_log_dest_2=+FRA

    If I issue, alter database add logfile clause,

    Does it create a new logfile in the +DATA diskgroup, logfile in the +LOGS disk group, and a logfile in the +FRA disk group.
    OR
    a new logfile in the +LOGS disk group, and a logfile in the +FRA disk group
    ?

    • Gunes Erol says:

      Here is the your answer wiht demo:
      SQL> select group_number,name from v$asm_diskgroup;

      GROUP_NUMBER NAME
      ———— ——————————
      1 DAT
      2 SGFRA
      3 ORAFRA
      4 ORADATA

      SQL> show parameter db_create_file_dest

      NAME TYPE VALUE
      ———————————— ———– ——————————
      db_create_file_dest string +ORADATA

      SQL> alter system set db_create_online_log_dest_1=’+ORAFRA’;

      System altered.

      SQL> select group#,member from v$logfile;

      GROUP# MEMBER
      ———- ———–
      3 +ORADATA/pri/onlinelog/group_3.263.764940777
      2 +ORADATA/pri/onlinelog/group_2.262.764940759
      1 +ORADATA/pri/onlinelog/group_1.261.764940741
      4 +ORADATA/pri/onlinelog/group_4.270.767899263
      5 +ORADATA/pri/onlinelog/group_5.269.767899287
      6 +ORADATA/pri/onlinelog/group_6.268.767899307
      7 +ORADATA/pri/onlinelog/group_7.267.767899329

      7 rows selected.

      SQL> alter database add logfile size 100M;

      System altered.

      SQL> select group#,member from v$logfile;

      GROUP# MEMBER
      ———- ———–
      3 +ORADATA/pri/onlinelog/group_3.263.764940777
      2 +ORADATA/pri/onlinelog/group_2.262.764940759
      1 +ORADATA/pri/onlinelog/group_1.261.764940741
      4 +ORADATA/pri/onlinelog/group_4.270.767899263
      5 +ORADATA/pri/onlinelog/group_5.269.767899287
      6 +ORADATA/pri/onlinelog/group_6.268.767899307
      7 +ORADATA/pri/onlinelog/group_7.267.767899329
      8 +SGFRA/pri/onlinelog/group_3.271.524940785

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: