How To Resize the Online Redo Logfiles (Single Instance)


1. First see the size of the current logs:
sqlplus /nolog
SQL> connect / as sysdba
SQL> select group#, bytes, status from v$log;
GROUP# BYTES STATUS
———- ———- —————-
1 1048576 INACTIVE
2 1048576 CURRENT
3 1048576 INACTIVE
Logs are 1MB from above, let’s size them to 10MB.
2. Retrieve all the log member names for the groups:
SQL> select group#, member from v$logfile;
GROUP# MEMBER
————— —————————————-
1 /usr/oracle/dbs/log1PROD.dbf
2 /usr/oracle/dbs/log2PROD.dbf
3 /usr/oracle/dbs/log3PROD.dbf
3. Let’s create 3 new log groups and name them groups 4, 5, and 6, each 10MB in size:
SQL> alter database add logfile group 4 '/usr/oracle/dbs/log4PROD.dbf' size 10M; 
 SQL> alter database add logfile group 5 '/usr/oracle/dbs/log5PROD.dbf' size 10M;
 SQL> alter database add logfile group 6 '/usr/oracle/dbs/log6PROD.dbf' size 10M;
4. Now run a query to view the v$log status:
SQL> select group#, status from v$log;
GROUP# STATUS
--------- ----------------
1 INACTIVE
2 CURRENT
 
3 INACTIVE 
 
4 UNUSED
 
5 UNUSED
 
6 UNUSED 
 
From the above we can see log group 2 is current, and this is one of the
smaller groups we must drop. Therefore let’s switch out of this group into
one of the newly created log groups.
5. Switch until we are into log group 4, so we can drop log groups 1, 2, and 3:
SQL> alter system switch logfile;
** repeat as necessary until group 4 is CURRENT **
6. Run the query again to verify the current log group is group 4:
SQL> select group#, status from v$log;
GROUP# STATUS
——— —————-
1 INACTIVE
2 INACTIVE
3 INACTIVE
4 CURRENT
5 UNUSED
6 UNUSED
Note: redo log Group 1 or 2 or 3 can be active after “alter system switch log file” which means could not be dropped, in this case,
you need to do “alter system checkpoint” to make redo log groups 1,2 and 3 inactive.
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;
Verify the groups were dropped, and the new groups’ sizes are correct.
SVRMGR> select group#, bytes, status from v$log;
GROUP# BYTES STATUS
——— ——— —————-
4 10485760 CURRENT
5 10485760 UNUSED
6 10485760 UNUSED
8. At this point, you consider taking a backup of the database.
9. You can now go out to the operating system and delete the files associated
with redo log groups 1, 2, and 3 in step 2 above as they are no longer
needed:
% rm /usr/oracle/dbs/log1PROD.dbf
% rm /usr/oracle/dbs/log2PROD.dbf
% rm /usr/oracle/dbs/log3PROD.dbf
How to resize redo logfile group in Oracle RAC.
1. First see the size of the current logs:
SQL> select group#, bytes, status from v$log;
GROUP# BYTES STATUS
———- ———- —————-
21 1073741824 INACTIVE
22 1073741824 CURRENT
23 1073741824 INACTIVE
24 1073741824 INACTIVE
25 1073741824 INACTIVE
26 1073741824 INACTIVE
27 1073741824 INACTIVE
28 1073741824 ACTIVE
29 1073741824 CURRENT
30 1073741824 INACTIVE
31 1073741824 INACTIVE
32 1073741824 INACTIVE
12 rows selected.
Logs are 1GB from above, let’s size them to 500MB.
2. Retrieve all the log member names for the groups:
SQL> select group#, member from v$logfile;
GROUP#
———-
MEMBER
——————————————————————————–
31
+DATA/prod/onlinelog/group_31.391.787059447
32
+DATA/prod/onlinelog/group_32.390.787059453
21
+DATA/prod/onlinelog/group_21.258.787054781
22
+DATA/prod/onlinelog/group_22.256.787054787
23
+DATA/prod/onlinelog/group_23.274.787054793
24
+DATA/prod/onlinelog/group_24.273.787054799
25
+DATA/prod/onlinelog/group_25.271.787054805
26
+DATA/prod/onlinelog/group_26.394.787054839
27
+DATA/prod/onlinelog/group_27.395.787054845
28
+DATA/prod/onlinelog/group_28.396.787054851
29
+DATA/prod/onlinelog/group_29.397.787054855
30
+DATA/prod/onlinelog/group_30.398.787054861
12 rows selected.
3- Let’s create 3 new log groups per instance and name them groups 40,41,42 and 50,51 and 52 and each 500MB in size:
SQL> ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 40 '+DATA' SIZE 500m;
Database altered.
SQL> ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 41 '+DATA' SIZE 500m;
Database altered.
SQL> ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 42 '+DATA' SIZE 500m;
Database altered.
SQL> ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 50 '+DATA' SIZE 500m;
Database altered.
SQL> ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 51 '+DATA' SIZE 500m;
Database altered.
SQL> ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 52 '+DATA' SIZE 500m;
Database altered.
4. Now run a query to view the v$log status:
SQL> select group#, bytes, status from v$log;
GROUP# BYTES STATUS
---------- ---------- ----------------
 21 1073741824 INACTIVE
 22 1073741824 CURRENT
 23 1073741824 INACTIVE
 24 1073741824 INACTIVE
 25 1073741824 INACTIVE
 26 1073741824 INACTIVE
 27 1073741824 INACTIVE
 28 1073741824 ACTIVE
 29 1073741824 CURRENT
 30 1073741824 INACTIVE
 31 1073741824 INACTIVE
 32 1073741824 INACTIVE
 40 524288000 UNUSED
 41 524288000 UNUSED
 42 524288000 UNUSED
 50 524288000 UNUSED
 51 524288000 UNUSED
 52 524288000 UNUSED
18 rows selected.
5. Now drop Unactive redo log group
SQL> alter database drop logfile group 21;
Database altered.
SQL> alter database drop logfile group 23;
Database altered.
SQL> alter database drop logfile group 24;
Database altered.
SQL> alter database drop logfile group 25;
Database altered.
SQL> alter database drop logfile group 26;
Database altered.
SQL> alter database drop logfile group 27;
Database altered.
SQL> alter database drop logfile group 30;
Database altered.
SQL> alter database drop logfile group 31;
Database altered.
SQL> alter database drop logfile group 32;
Database altered.
SQL> select group#, bytes, status from v$log;
GROUP# BYTES STATUS
———- ———- —————-
22 1073741824 CURRENT
28 1073741824 ACTIVE
29 1073741824 CURRENT
40 524288000 UNUSED
41 524288000 UNUSED
42 524288000 UNUSED
50 524288000 UNUSED
51 524288000 UNUSED
52 524288000 UNUSED
9 rows selected.
SQL>
6- wait for some time and drop the 22,28,29 groups
Advertisements

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

%d bloggers like this: