how to resize and multiplex redo group members under ASM/RAC

## ## Objective ## In this example I have two ASM disk groups for redo logs: REDOA REDOB There are FOUR instances, each instance will have TWO redo groups with TWO members each, one on REDOA the other on REDOB. Originally this database was created with a single REDO member in REDOA disk group sized 50mb -- my goal here is to: a) "resize" it to 256mb b) multiplex to REDOB ## ## Procedure ## sqlplus /nolog connect / as sysdba SQL> col member format a35 SQL> select * from v$logfile; GROUP# STATUS TYPE MEMBER IS_ ---------- ------- ------- ----------------------------------- --- 1 ONLINE +REDOA/dbname/redo01.log NO 2 STALE ONLINE +REDOA/dbname/redo02.log NO 7 ONLINE +REDOA/dbname/redo07.log NO 8 STALE ONLINE +REDOA/dbname/redo08.log NO 5 STALE ONLINE +REDOA/dbname/redo05.log NO 6 ONLINE +REDOA/dbname/redo06.log NO 3 ONLINE +REDOA/dbname/redo03.log NO 4 ONLINE +REDOA/dbname/redo04.log NO 8 rows selected. SQL> set lines 132 SQL> set trims on SQL> select * from v$log; GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- 1 1 53 52428800 1 NO CURRENT 2274385 08-OCT-08 2 1 52 52428800 1 NO INACTIVE 2114717 07-OCT-08 3 2 27 52428800 1 NO CURRENT 2206475 08-OCT-08 4 2 26 52428800 1 NO INACTIVE 2030730 07-OCT-08 5 3 17 52428800 1 NO INACTIVE 1780062 06-OCT-08 6 3 18 52428800 1 NO CURRENT 2051445 07-OCT-08 7 4 21 52428800 1 NO CURRENT 2072021 07-OCT-08 8 4 20 52428800 1 NO INACTIVE 1932027 07-OCT-08 8 rows selected. -- first create temporary redo-groups -- so that we can delete GROUP 1-8 and recreate them -- with a bigger 256m size ... -- -- NOTE: -- I didn't really have to create these temp groups with -- 2 members each ... one member each would suffice -- ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 10 ('+REDOA/DBNAME/redo01-tmp.log','+REDOB/DBNAME/redo01-tmp.log') SIZE 10m, GROUP 20 ('+REDOA/DBNAME/redo02-tmp.log','+REDOB/DBNAME/redo02-tmp.log') SIZE 10m; ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 30 ('+REDOA/DBNAME/redo03-tmp.log','+REDOB/DBNAME/redo03-tmp.log') SIZE 10m, GROUP 40 ('+REDOA/DBNAME/redo04-tmp.log','+REDOB/DBNAME/redo04-tmp.log') SIZE 10m; ALTER DATABASE ADD LOGFILE THREAD 3 GROUP 50 ('+REDOA/DBNAME/redo05-tmp.log','+REDOB/DBNAME/redo05-tmp.log') SIZE 10m, GROUP 60 ('+REDOA/DBNAME/redo06-tmp.log','+REDOB/DBNAME/redo06-tmp.log') SIZE 10m; ALTER DATABASE ADD LOGFILE THREAD 4 GROUP 70 ('+REDOA/DBNAME/redo07-tmp.log','+REDOB/DBNAME/redo07-tmp.log') SIZE 10m, GROUP 80 ('+REDOA/DBNAME/redo08-tmp.log','+REDOB/DBNAME/redo08-tmp.log') SIZE 10m; SQL> select * from v$log; GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- 1 1 53 52428800 1 NO CURRENT 2274385 08-OCT-08 2 1 52 52428800 1 NO INACTIVE 2114717 07-OCT-08 3 2 27 52428800 1 NO CURRENT 2206475 08-OCT-08 4 2 26 52428800 1 NO INACTIVE 2030730 07-OCT-08 5 3 17 52428800 1 NO INACTIVE 1780062 06-OCT-08 6 3 18 52428800 1 NO CURRENT 2051445 07-OCT-08 7 4 21 52428800 1 NO CURRENT 2072021 07-OCT-08 8 4 20 52428800 1 NO INACTIVE 1932027 07-OCT-08 10 1 0 10485760 2 YES UNUSED 0 20 1 0 10485760 2 YES UNUSED 0 30 2 0 10485760 2 YES UNUSED 0 GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- 40 2 0 10485760 2 YES UNUSED 0 50 3 0 10485760 2 YES UNUSED 0 60 3 0 10485760 2 YES UNUSED 0 70 4 0 10485760 2 YES UNUSED 0 80 4 0 10485760 2 YES UNUSED 0 16 rows selected. SQL> -- now run "alter system switch logfile;" on all instances -- untill all of the GROUP 1-8 become "INACTIVE" -- like shown below: -- SQL> r 1* select * from v$log GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- 1 1 61 52428800 1 NO INACTIVE 2288120 08-OCT-08 <-- should be "INACTIVE" 2 1 60 52428800 1 NO INACTIVE 2288098 08-OCT-08 <-- should be "INACTIVE" 3 2 31 52428800 1 NO INACTIVE 2288234 08-OCT-08 <-- should be "INACTIVE" 4 2 30 52428800 1 NO INACTIVE 2288221 08-OCT-08 <-- should be "INACTIVE" 5 3 21 52428800 1 NO INACTIVE 2288289 08-OCT-08 <-- should be "INACTIVE" 6 3 22 52428800 1 NO INACTIVE 2288294 08-OCT-08 <-- should be "INACTIVE" 7 4 25 52428800 1 NO INACTIVE 2288317 08-OCT-08 <-- should be "INACTIVE" 8 4 24 52428800 1 NO INACTIVE 2288311 08-OCT-08 <-- should be "INACTIVE" 10 1 62 10485760 2 NO CURRENT 2288123 08-OCT-08 20 1 59 10485760 2 NO INACTIVE 2288084 08-OCT-08 30 2 32 10485760 2 NO CURRENT 2288243 08-OCT-08 GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- 40 2 29 10485760 2 NO INACTIVE 2288217 08-OCT-08 50 3 23 10485760 2 NO CURRENT 2288296 08-OCT-08 60 3 20 10485760 2 NO INACTIVE 2288287 08-OCT-08 70 4 26 10485760 2 NO ACTIVE 2288319 08-OCT-08 80 4 27 10485760 2 NO CURRENT 2288322 08-OCT-08 16 rows selected. -- now drop GROUP 1-8 -- alter database drop logfile group 1; alter database drop logfile group 2; alter database drop logfile group 3; alter database drop logfile group 4; alter database drop logfile group 5; alter database drop logfile group 6; alter database drop logfile group 7; alter database drop logfile group 8; SQL> select * from v$log; GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- 10 1 62 10485760 2 NO CURRENT 2288123 08-OCT-08 20 1 59 10485760 2 NO INACTIVE 2288084 08-OCT-08 30 2 32 10485760 2 NO CURRENT 2288243 08-OCT-08 40 2 29 10485760 2 NO INACTIVE 2288217 08-OCT-08 50 3 23 10485760 2 NO CURRENT 2288296 08-OCT-08 60 3 20 10485760 2 NO INACTIVE 2288287 08-OCT-08 70 4 26 10485760 2 NO INACTIVE 2288319 08-OCT-08 80 4 27 10485760 2 NO CURRENT 2288322 08-OCT-08 8 rows selected. SQL> ## remove the actuall files (GROUP 1-8) from the ASM ## ## export ORACLE_HOME=/u01/app/oracle/product/10.2.0/asm export PATH=$ORACLE_HOME/bin:$PATH export ORACLE_SID=+ASM2 sqlplus /nolog connect / as sysdba ALTER DISKGROUP REDOA DROP FILE '+REDOA/dbname/redo01.log'; ALTER DISKGROUP REDOA DROP FILE '+REDOA/dbname/redo02.log'; ALTER DISKGROUP REDOA DROP FILE '+REDOA/dbname/redo07.log'; ALTER DISKGROUP REDOA DROP FILE '+REDOA/dbname/redo08.log'; ALTER DISKGROUP REDOA DROP FILE '+REDOA/dbname/redo05.log'; ALTER DISKGROUP REDOA DROP FILE '+REDOA/dbname/redo06.log'; ALTER DISKGROUP REDOA DROP FILE '+REDOA/dbname/redo03.log'; ALTER DISKGROUP REDOA DROP FILE '+REDOA/dbname/redo04.log'; exit ## verify files were removed ## asmcmd ASMCMD> cd REDOA ASMCMD> find . * +REDOA/DBNAME/ +REDOA/DBNAME/CONTROLFILE/ +REDOA/DBNAME/CONTROLFILE/Current.256.666981619 +REDOA/DBNAME/ONLINELOG/ +REDOA/DBNAME/ONLINELOG/group_10.271.667595443 +REDOA/DBNAME/ONLINELOG/group_20.272.667595443 +REDOA/DBNAME/ONLINELOG/group_30.265.667595411 +REDOA/DBNAME/ONLINELOG/group_40.266.667595411 +REDOA/DBNAME/ONLINELOG/group_50.267.667595411 +REDOA/DBNAME/ONLINELOG/group_60.268.667595411 +REDOA/DBNAME/ONLINELOG/group_70.269.667595413 +REDOA/DBNAME/ONLINELOG/group_80.270.667595413 +REDOA/DBNAME/control01.ctl +REDOA/DBNAME/redo01-tmp.log +REDOA/DBNAME/redo02-tmp.log +REDOA/DBNAME/redo03-tmp.log +REDOA/DBNAME/redo04-tmp.log +REDOA/DBNAME/redo05-tmp.log +REDOA/DBNAME/redo06-tmp.log +REDOA/DBNAME/redo07-tmp.log +REDOA/DBNAME/redo08-tmp.log ASMCMD> exit -- now re-create it with "dual" members and bigger size -- ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 1 ('+REDOA/DBNAME/redo01.log','+REDOB/DBNAME/redo01.log') SIZE 256000K, GROUP 2 ('+REDOA/DBNAME/redo02.log','+REDOB/DBNAME/redo02.log') SIZE 256000K; ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 3 ('+REDOA/DBNAME/redo03.log','+REDOB/DBNAME/redo03.log') SIZE 256000K, GROUP 4 ('+REDOA/DBNAME/redo04.log','+REDOB/DBNAME/redo04.log') SIZE 256000K; ALTER DATABASE ADD LOGFILE THREAD 3 GROUP 5 ('+REDOA/DBNAME/redo05.log','+REDOB/DBNAME/redo05.log') SIZE 256000K, GROUP 6 ('+REDOA/DBNAME/redo06.log','+REDOB/DBNAME/redo06.log') SIZE 256000K; ALTER DATABASE ADD LOGFILE THREAD 4 GROUP 7 ('+REDOA/DBNAME/redo07.log','+REDOB/DBNAME/redo07.log') SIZE 256000K, GROUP 8 ('+REDOA/DBNAME/redo08.log','+REDOB/DBNAME/redo08.log') SIZE 256000K; SQL> select * from v$log; GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- 1 1 0 262144000 2 YES UNUSED 0 2 1 0 262144000 2 YES UNUSED 0 3 2 0 262144000 2 YES UNUSED 0 4 2 0 262144000 2 YES UNUSED 0 5 3 0 262144000 2 YES UNUSED 0 6 3 0 262144000 2 YES UNUSED 0 7 4 0 262144000 2 YES UNUSED 0 8 4 0 262144000 2 YES UNUSED 0 10 1 62 10485760 2 NO CURRENT 2288123 08-OCT-08 20 1 59 10485760 2 NO INACTIVE 2288084 08-OCT-08 30 2 32 10485760 2 NO CURRENT 2288243 08-OCT-08 GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- 40 2 29 10485760 2 NO INACTIVE 2288217 08-OCT-08 50 3 23 10485760 2 NO CURRENT 2288296 08-OCT-08 60 3 20 10485760 2 NO INACTIVE 2288287 08-OCT-08 70 4 26 10485760 2 NO INACTIVE 2288319 08-OCT-08 80 4 27 10485760 2 NO CURRENT 2288322 08-OCT-08 16 rows selected. -- now run "alter system switch logfile;" on all instances -- untill all of the GROUP 10-80 become "INACTIVE" -- like shown below: -- 1* select * from v$log GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- 1 1 67 262144000 2 NO CURRENT 2289166 08-OCT-08 2 1 64 262144000 2 NO INACTIVE 2289154 08-OCT-08 3 2 37 262144000 2 NO INACTIVE 2289194 08-OCT-08 4 2 38 262144000 2 NO CURRENT 2289199 08-OCT-08 5 3 28 262144000 2 NO INACTIVE 2289258 08-OCT-08 6 3 29 262144000 2 NO CURRENT 2289266 08-OCT-08 7 4 32 262144000 2 NO CURRENT 2289332 08-OCT-08 8 4 29 262144000 2 NO INACTIVE 2289315 08-OCT-08 10 1 66 10485760 2 NO INACTIVE 2289163 08-OCT-08 20 1 65 10485760 2 NO INACTIVE 2289157 08-OCT-08 30 2 36 10485760 2 NO INACTIVE 2289192 08-OCT-08 GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- 40 2 35 10485760 2 NO INACTIVE 2289189 08-OCT-08 50 3 27 10485760 2 NO INACTIVE 2289254 08-OCT-08 60 3 26 10485760 2 NO INACTIVE 2289243 08-OCT-08 70 4 30 10485760 2 NO INACTIVE 2289322 08-OCT-08 80 4 31 10485760 2 NO INACTIVE 2289329 08-OCT-08 16 rows selected. -- now drop GROUP 10-80 -- alter database drop logfile group 10; alter database drop logfile group 20; alter database drop logfile group 30; alter database drop logfile group 40; alter database drop logfile group 50; alter database drop logfile group 60; alter database drop logfile group 70; alter database drop logfile group 80; SQL> select * from v$log; GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- 1 1 67 262144000 2 NO CURRENT 2289166 08-OCT-08 2 1 64 262144000 2 NO INACTIVE 2289154 08-OCT-08 3 2 37 262144000 2 NO INACTIVE 2289194 08-OCT-08 4 2 38 262144000 2 NO CURRENT 2289199 08-OCT-08 5 3 28 262144000 2 NO INACTIVE 2289258 08-OCT-08 6 3 29 262144000 2 NO CURRENT 2289266 08-OCT-08 7 4 32 262144000 2 NO CURRENT 2289332 08-OCT-08 8 4 29 262144000 2 NO INACTIVE 2289315 08-OCT-08 8 rows selected. SQL> ## and finally remove the actuall files (GROUP 10-80) from the ASM ## ## export ORACLE_HOME=/u01/app/oracle/product/10.2.0/asm export PATH=$ORACLE_HOME/bin:$PATH export ORACLE_SID=+ASM2 sqlplus /nolog connect / as sysdba ALTER DISKGROUP REDOA DROP FILE '+REDOA/DBNAME/redo01-tmp.log'; ALTER DISKGROUP REDOA DROP FILE '+REDOA/DBNAME/redo02-tmp.log'; ALTER DISKGROUP REDOA DROP FILE '+REDOA/DBNAME/redo03-tmp.log'; ALTER DISKGROUP REDOA DROP FILE '+REDOA/DBNAME/redo04-tmp.log'; ALTER DISKGROUP REDOA DROP FILE '+REDOA/DBNAME/redo05-tmp.log'; ALTER DISKGROUP REDOA DROP FILE '+REDOA/DBNAME/redo06-tmp.log'; ALTER DISKGROUP REDOA DROP FILE '+REDOA/DBNAME/redo07-tmp.log'; ALTER DISKGROUP REDOA DROP FILE '+REDOA/DBNAME/redo08-tmp.log'; ALTER DISKGROUP REDOB DROP FILE '+REDOB/DBNAME/redo01-tmp.log'; ALTER DISKGROUP REDOB DROP FILE '+REDOB/DBNAME/redo02-tmp.log'; ALTER DISKGROUP REDOB DROP FILE '+REDOB/DBNAME/redo03-tmp.log'; ALTER DISKGROUP REDOB DROP FILE '+REDOB/DBNAME/redo04-tmp.log'; ALTER DISKGROUP REDOB DROP FILE '+REDOB/DBNAME/redo05-tmp.log'; ALTER DISKGROUP REDOB DROP FILE '+REDOB/DBNAME/redo06-tmp.log'; ALTER DISKGROUP REDOB DROP FILE '+REDOB/DBNAME/redo07-tmp.log'; ALTER DISKGROUP REDOB DROP FILE '+REDOB/DBNAME/redo08-tmp.log'; exit ## verify files were removed ## asmcmd ASMCMD> cd REDOA ASMCMD> find . * +REDOA/DBNAME/ +REDOA/DBNAME/CONTROLFILE/ +REDOA/DBNAME/CONTROLFILE/Current.256.666981619 +REDOA/DBNAME/ONLINELOG/ +REDOA/DBNAME/ONLINELOG/group_1.264.667596567 +REDOA/DBNAME/ONLINELOG/group_2.263.667596569 +REDOA/DBNAME/ONLINELOG/group_3.262.667596571 +REDOA/DBNAME/ONLINELOG/group_4.261.667596573 +REDOA/DBNAME/ONLINELOG/group_5.260.667596575 +REDOA/DBNAME/ONLINELOG/group_6.259.667596577 +REDOA/DBNAME/ONLINELOG/group_7.258.667596579 +REDOA/DBNAME/ONLINELOG/group_8.257.667596581 +REDOA/DBNAME/control01.ctl +REDOA/DBNAME/redo01.log +REDOA/DBNAME/redo02.log +REDOA/DBNAME/redo03.log +REDOA/DBNAME/redo04.log +REDOA/DBNAME/redo05.log +REDOA/DBNAME/redo06.log +REDOA/DBNAME/redo07.log +REDOA/DBNAME/redo08.log ASMCMD> cd +REDOB/ ASMCMD> pwd +REDOB ASMCMD> find . * +REDOB/DBNAME/ +REDOB/DBNAME/CONTROLFILE/ +REDOB/DBNAME/CONTROLFILE/Current.256.666981619 +REDOB/DBNAME/ONLINELOG/ +REDOB/DBNAME/ONLINELOG/group_1.265.667596567 +REDOB/DBNAME/ONLINELOG/group_2.266.667596569 +REDOB/DBNAME/ONLINELOG/group_3.267.667596571 +REDOB/DBNAME/ONLINELOG/group_4.268.667596573 +REDOB/DBNAME/ONLINELOG/group_5.269.667596575 +REDOB/DBNAME/ONLINELOG/group_6.270.667596577 +REDOB/DBNAME/ONLINELOG/group_7.271.667596579 +REDOB/DBNAME/ONLINELOG/group_8.272.667596583 +REDOB/DBNAME/control02.ctl +REDOB/DBNAME/redo01.log +REDOB/DBNAME/redo02.log +REDOB/DBNAME/redo03.log +REDOB/DBNAME/redo04.log +REDOB/DBNAME/redo05.log +REDOB/DBNAME/redo06.log +REDOB/DBNAME/redo07.log +REDOB/DBNAME/redo08.log ASMCMD> exit