How To Resize the Online Redo Logfiles in Oracle Database

Steps:


1. Check 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. Find all the log member names for the groups:

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

GROUP# MEMBER
————— —————————————-
1 /u01/oracle/db/log1PROD.dbf
2 /u01/oracle/db/log2PROD.dbf
3 /u01/oracle/db/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  '/u01/oracle/db/log4PROD.dbf' size 10M; 
SQL> alter database add logfile group 5  '/u01/oracle/db/log5PROD.dbf' size 10M;
SQL> alter database add logfile group 6  '/u01/oracle/db/log6PROD.dbf' size 10M;

4. Run 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  
 

We can see log group 2 is current, and this is one of the smaller groups which we need to drop. Let’s switch log 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.

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

GROUP# BYTES STATUS
——— ——— —————-
4 10485760 CURRENT
5 10485760 UNUSED
6 10485760 UNUSED

8. Take backup of the database if required.


9. At the operating system level delete the files associated
with redo log groups 1, 2, and 3 

 rm /u01/oracle/db/log1PROD.dbf
 rm /u01/oracle/db/log2PROD.dbf
 rm /u01/oracle/db/log3PROD.dbf




If you like please follow and comment