Database setup has 2 nodes 11.1.0.7 RAC database on AIX 6.1 with ASM, I need to resize the redo log files from 1 GB to 3 GB.
I followed the below steps to do the same. Before resize each thread was having 3 redo log groups with 1 member of 50mb in each group. After resize each thread is having 10 redo log groups with 3 members of 3GB in each group.
At the end the cluster will have 20 redo log groups with 3 members in each group.
Check the existing redo log files information by using the below queries:
select thread#,group#,bytes/1024/1024,members,status from v$log;
SQL> SELECT a.group#, a.member, b.bytes/1024/1024 MB FROM v$logfile a, v$log b WHERE a.group# = b.group# order by 1;
2.Here in my case I am using three disks SYSTEM1,SYSTEM2,SYSTEM3 for storing redo log files in this setup for each node I am adding 10 new redo log groups with three members of 3GB using the below commands .
Providing the sample output for few of redo log files as below:-
01:33:31 SQL> ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 21 (‘+SYSTEM1′,’+SYSTEM2′,’+SYSTEM3’) SIZE 3G;
Database altered.
Elapsed: 00:01:33.27
01:35:28 SQL> ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 23 (‘+SYSTEM1′,’+SYSTEM2′,’+SYSTEM3’) SIZE 3G;
Database altered.
Elapsed: 00:01:50.10
01:38:14 SQL> ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 24 (‘+SYSTEM1′,’+SYSTEM2′,’+SYSTEM3’) SIZE 3G;
Database altered.
Elapsed: 00:01:43.95
01:40:09 SQL> ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 25 (‘+SYSTEM1′,’+SYSTEM2′,’+SYSTEM3’) SIZE 3G;
Database altered.
Elapsed: 00:01:40.37
01:41:52 SQL> ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 22 (‘+SYSTEM1′,’+SYSTEM2′,’+SYSTEM3’) SIZE 3G;
Database altered.
Elapsed: 00:01:37.76
01:43:33 SQL> ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 26 (‘+SYSTEM1′,’+SYSTEM2′,’+SYSTEM3’) SIZE 3G;
Database altered.
Elapsed: 00:01:39.10
01:46:02 SQL> ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 27 (‘+SYSTEM1′,’+SYSTEM2′,’+SYSTEM3’) SIZE 3G;
Database altered.
Elapsed: 00:01:35.74
01:47:59 SQL> ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 28 (‘+SYSTEM1′,’+SYSTEM2′,’+SYSTEM3’) SIZE 3G;
Database altered.
Elapsed: 00:01:30.14
01:49:32 SQL> ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 29 (‘+SYSTEM1′,’+SYSTEM2′,’+SYSTEM3’) SIZE 3G;
Database altered.
Elapsed: 00:01:46.66
01:51:22 SQL> ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 30 (‘+SYSTEM1′,’+SYSTEM2′,’+SYSTEM3’) SIZE 3G;
Database altered.
Elapsed: 00:01:23.21
01:52:49 SQL> ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 31 (‘+SYSTEM1′,’+SYSTEM2′,’+SYSTEM3’) SIZE 3G;
Database altered.
Elapsed: 00:01:25.48
01:54:18 SQL> ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 32 (‘+SYSTEM1′,’+SYSTEM2′,’+SYSTEM3’) SIZE 3G;
Database altered.
Elapsed: 00:01:25.81
01:55:46 SQL> ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 33 (‘+SYSTEM1′,’+SYSTEM2′,’+SYSTEM3’) SIZE 3G;
Database altered.
Elapsed: 00:01:28.82
01:57:33 SQL> ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 34 (‘+SYSTEM1′,’+SYSTEM2′,’+SYSTEM3’) SIZE 3G;
Database altered.
Elapsed: 00:01:33.66
01:59:10 SQL> ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 35 (‘+SYSTEM1′,’+SYSTEM2′,’+SYSTEM3’) SIZE 3G;
Database altered.
Elapsed: 00:02:29.46
02:01:44 SQL> ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 36 (‘+SYSTEM1′,’+SYSTEM2′,’+SYSTEM3’) SIZE 3G;
Database altered.
Elapsed: 00:02:11.68
02:04:04 SQL> ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 37 (‘+SYSTEM1′,’+SYSTEM2′,’+SYSTEM3’) SIZE 3G;
Database altered.
Elapsed: 00:02:24.39
02:06:32 SQL> ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 38 (‘+SYSTEM1′,’+SYSTEM2′,’+SYSTEM3’) SIZE 3G;
Database altered.
Elapsed: 00:02:38.93
02:09:16 SQL> ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 39 (‘+SYSTEM1′,’+SYSTEM2′,’+SYSTEM3’) SIZE 3G;
Database altered.
Now check the status of the newly added redo log files and groups :-
select thread#,group#,members,status from v$log;
To Drop the old redo log files need to switch the log file on all the instance until all current online redologs are located on the newly added groups.
SQL> alter system switch logfile;
System altered.
Check the status again after switching the log files on all nodes before dropping the old redo log file groups and also confirm that none of them have a status of ACTIVE.
select thread#,group#,members,status from v$log;
Issue global check point on any one node to turn all the ACTIVE redo log groups to INACTIVE.
SQL> alter system checkpoint global;
System altered.
Drop the old redo log files groups as below:-
02:13:44 SQL> alter database drop logfile group 6;
Database altered.
Elapsed: 00:00:06.98
02:13:51 SQL> alter database drop logfile group 7;
Database altered.
Elapsed: 00:00:08.61
02:14:00 SQL> alter database drop logfile group 8;
Database altered.
Elapsed: 00:00:07.52
02:14:07 SQL> alter database drop logfile group 9;
Database altered.
Elapsed: 00:00:08.54
02:14:16 SQL> alter database drop logfile group 10;
Database altered.
Elapsed: 00:00:08.23
02:14:24 SQL> alter database drop logfile group 11;
Database altered.
Elapsed: 00:00:07.15
02:14:31 SQL> alter database drop logfile group 12;
Database altered.
Elapsed: 00:00:08.19
02:14:39 SQL> alter database drop logfile group 13;
Database altered.
02:14:47 SQL> alter database drop logfile group 15;
Database altered.
Elapsed: 00:00:08.72
02:14:56 SQL> alter database drop logfile group 16;
Database altered.
Elapsed: 00:00:06.81
02:15:03 SQL> alter database drop logfile group 17;
Database altered.
Elapsed: 00:00:04.91
02:15:08 SQL> alter database drop logfile group 18;
Database altered.
Elapsed: 00:00:07.12
02:15:15 SQL> alter database drop logfile group 19;
Database altered.
Elapsed: 00:00:07.09
02:15:22 SQL> alter database drop logfile group 20;
Database altered.
Elapsed: 00:00:07.02
At the end please check the redo log size by issuing the below command and validate to make sure.
select thread#,group#,bytes/1024/1024,members,status from v$log;
GROUP# MEMBER MB
—— ———————————————————— ———-
21 +SYSTEM3/test/onlinelog/group_21.337.860463293 3072
21 +SYSTEM1/test/onlinelog/group_21.302.860463235 3072
21 +SYSTEM2/test/onlinelog/group_21.339.860463265 3072
22 +SYSTEM2/test/onlinelog/group_22.343.860463747 3072
22 +SYSTEM3/test/onlinelog/group_22.341.860463777 3072
22 +SYSTEM1/test/onlinelog/group_22.334.860463715 3072
23 +SYSTEM1/test/onlinelog/group_23.303.860463385 3072
23 +SYSTEM2/test/onlinelog/group_23.340.860463419 3072
23 +SYSTEM3/test/onlinelog/group_23.338.860463451 3072
24 +SYSTEM2/test/onlinelog/group_24.341.860463541 3072
24 +SYSTEM1/test/onlinelog/group_24.306.860463505 3072
24 +SYSTEM3/test/onlinelog/group_24.339.860463571 3072
25 +SYSTEM2/test/onlinelog/group_25.342.860463647 3072
25 +SYSTEM1/test/onlinelog/group_25.304.860463613 3072
25 +SYSTEM3/test/onlinelog/group_25.340.860463675 3072
26 +SYSTEM2/test/onlinelog/group_26.344.860463895 3072
26 +SYSTEM3/test/onlinelog/group_26.342.860463921 3072
26 +SYSTEM1/test/onlinelog/group_26.335.860463863 3072
27 +SYSTEM3/test/onlinelog/group_27.343.860464045 3072
27 +SYSTEM1/test/onlinelog/group_27.336.860463983 3072
27 +SYSTEM2/test/onlinelog/group_27.345.860464017 3072
28 +SYSTEM2/test/onlinelog/group_28.346.860464111 3072
28 +SYSTEM1/test/onlinelog/group_28.337.860464083 3072
28 +SYSTEM3/test/onlinelog/group_28.344.860464139 3072
29 +SYSTEM3/test/onlinelog/group_29.345.860464245 3072
29 +SYSTEM2/test/onlinelog/group_29.347.860464211 3072
29 +SYSTEM1/test/onlinelog/group_29.338.860464177 3072
30 +SYSTEM2/test/onlinelog/group_30.348.860464315 3072
30 +SYSTEM3/test/onlinelog/group_30.346.860464341 3072
30 +SYSTEM1/test/onlinelog/group_30.339.860464287 3072
31 +SYSTEM1/test/onlinelog/group_31.340.860464373 3072
31 +SYSTEM3/test/onlinelog/group_31.347.860464425 3072
31 +SYSTEM2/test/onlinelog/group_31.349.860464399 3072
32 +SYSTEM1/test/onlinelog/group_32.341.860464461 3072
32 +SYSTEM2/test/onlinelog/group_32.350.860464489 3072
32 +SYSTEM3/test/onlinelog/group_32.348.860464517 3072
33 +SYSTEM2/test/onlinelog/group_33.351.860464591 3072
33 +SYSTEM3/test/onlinelog/group_33.349.860464623 3072
33 +SYSTEM1/test/onlinelog/group_33.342.860464565 3072
34 +SYSTEM3/test/onlinelog/group_34.350.860464711 3072
34 +SYSTEM2/test/onlinelog/group_34.352.860464685 3072
34 +SYSTEM1/test/onlinelog/group_34.343.860464657 3072
35 +SYSTEM2/test/onlinelog/group_35.353.860464807 3072
35 +SYSTEM3/test/onlinelog/group_35.351.860464849 3072
35 +SYSTEM1/test/onlinelog/group_35.344.860464755 3072
36 +SYSTEM3/test/onlinelog/group_36.352.860464999 3072
36 +SYSTEM2/test/onlinelog/group_36.354.860464957 3072
36 +SYSTEM1/test/onlinelog/group_36.345.860464913 3072
37 +SYSTEM2/test/onlinelog/group_37.355.860465095 3072
37 +SYSTEM3/test/onlinelog/group_37.353.860465139 3072
37 +SYSTEM1/test/onlinelog/group_37.346.860465049 3072
38 +SYSTEM2/test/onlinelog/group_38.356.860465247 3072
38 +SYSTEM1/test/onlinelog/group_38.347.860465197 3072
38 +SYSTEM3/test/onlinelog/group_38.354.860465295 3072
39 +SYSTEM3/test/onlinelog/group_39.355.860465439 3072
39 +SYSTEM1/test/onlinelog/group_39.348.860465365 3072
39 +SYSTEM2/test/onlinelog/group_39.357.860465401 3072
40 +SYSTEM1/test/onlinelog/group_40.310.860463033 3072
40 +SYSTEM2/test/onlinelog/group_40.338.860463067 3072
40 +SYSTEM3/test/onlinelog/group_40.336.860463097 3072
Database Blog 